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

• spen.smith: Does your database have primary keys?
• spen.smith: What version of SQL Alchemy are you using and what are your imports?
• Peter: @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
• spen.smith: 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: I’m having difficulty finding what code you are referencing/importing...

Answer 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.

• Peter: it do not give errors but it is getting an empty list
• spen.smith: 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.