How to update records in MySQL using SqlAlchemy python

Question

so Im attempting to updated records in database by id's.

The problem is it overwrites all rows ```id and Cost`` with the last row of the list
I dont get any errors, but it should match the id's then replace only Cost

database:

table =

id             Cost
000074800795   157.05974458228403
000074801136   80.637745302714
000074801299    7
000074800300   13
000074800955   10

my code:

df = pd.DataFrame({ "id": [000074800795 , 000074801136, 000074801299,000074800300, 000074800955] ,
                    "Cost" : [157.05974458228403 ,80.637745302714, 7, 13, 10] }) 

# replacing Null
df = df.where((pd.notnull(df)), None)

# reset index for For loop
df = df.reset_index()

df.id = df.id.astype(str)
#df.Cost = df.Cost.astype(str)

df.dtypes
out[101]: 
index  int64
id    object
Cost  object
dtype: object

values_list = []

for i in range(len(df)):
    values_list.append({ "id": df["id"][i] , 
                         "Cost": df["Cost"][i] ,
                       })

print(values_list[0:2])
[{'id': '000074800795', 'Cost': 157.05974458228403}, {'id': '000074801136', 'Cost': 80.637745302714}]

Loading at db.

The problem is it overwrite ```id and Cost`` with the last row of the list
I dont get any errors, but it should match the id's then replace only Cost

engine = db.create_engine("my connection")

connection = engine.connect()
metadata = db.MetaData()

# Creating Table
data = db.Table('table', metadata, 
              db.Column('id', db.String(100), nullable=True),    
              db.Column('Cost', db.String(100), nullable=True),  
              extend_existing=True 
              )

metadata.create_all(engine)

query = db.update(data)
ResultProxy = connection.execute(query, values_list)

engine.dispose()

Output:

table =

id             Cost
000074800955   10
000074800955   10
000074800955   10
000074800955   10
000074800955   10

What version of SQL Alchemy are you using and what are your imports? — spen.smith
– spen.smith, Commented Jan 17, 2020 at 0:52
@open.smith I havent setting up nay keys, just dropped the data in there. about the version, I dont know how to check that. python is 3.7 — Peter
– Peter, Commented Jan 17, 2020 at 0:56
Go to a terminal and type: pip freeze | grep sqlalchemy. I think you’re misusing the update function. You seem to be trying to iteratively update every value in the database with the current value in your list, which is why the last one clears them all and makes them all the same. — spen.smith
– spen.smith, Commented Jan 17, 2020 at 1:00
I’m having difficulty finding what code you are referencing/importing... — spen.smith
– spen.smith, Commented Jan 17, 2020 at 1:19

spen.smith · Accepted Answer · 2020-01-22 20:05:47Z

1

Are you doing import sqlalchemy as db? It seems you think the dictionary can work as a primary key update dictionary, but I don't think that's how it works. One standard way of doing it is in the docs, and looks like this:

table.update().where(table.c.id==7).values(name='foo')

To do it this way, you need to filter your table down to one row, then run update on that row using the data in your dataframe. Otherwise, without a query, it seems db.update will update everything in your table.

Try something like this:

import pandas as pd
import sqlalchemy as db

metadata = db.MetaData()

table = db.Table('table', metadata,
                db.Column('id', db.String(100), nullable=True),
                db.Column('Cost', db.String(100), nullable=True),
                extend_existing=True
                )

df = pd.DataFrame({
    'id': ['000074800795', '000074801136', '000074801299', '000074800300', '000074800955'],
    'Cost': ['157.05974458228403', '80.637745302714', '7', '13', '10']
})

for index, row in df.iterrows():
    id_value = row['id']
    cost_value = row['Cost']
    table.update().where(table.c.id == id_value).values(Cost=cost_value)

There are certainly changes we could make to the style here, but hopefully this directs you in the right direction.

edited Jan 22, 2020 at 20:05

answered Jan 17, 2020 at 23:41

spen.smith

5973 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Peter Over a year ago

it do not give errors but it is getting an empty list

spen.smith Over a year ago

Peter, what do you mean? Does this work for you, or are you still having difficulties? Happy to answer more if you add a little more color here.

Collectives™ on Stack Overflow

How to update records in MySQL using SqlAlchemy python

The problem is it overwrite ```id and Cost`` with the last row of the list

Output:

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

The problem is it overwrite ```id and Cost`` with the last row of the list

Output:

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related