CODEWITHSUNDEEP
pythonmysqlpandassqlalchemysql-update
Matthewj28
Matthewj28

Reputation: 307

SQL Alchemy update multiple WHERE conditions

I want to update a column rtrend in a MySQL table (mydata1) from a pandas dataframe (getdata1) column rtrend making sure the values correspond to the appropriate date (dt)

e.g. rtrend value for 2016-08-09 from getdata1 df updates the appropriate MySQL mydata1 2016-08-09 rtrend value.

In addition specifying a particular security id e.g. security_id == 'GS' in this case

updateexample = update(mydata1)
      .where(mydata1.c.security_id=='GS', mydata1.c.dt==getdata1['dt'])
      .values(rtrend=getdata1['rtrend'])

This produces AttributeError: 'Series' object has no attribute 'translate' Please could someone help with the right way to go about this?

Example of current table and dataframe:

pandas dataframe 'getdata1'

dt         | security_id | rtrend
2016-08-09 | GS          | 1
2016-08-10 | GS          | -1

MySQL table 'mydata1'

dt         | security_id | rtrend
2016-08-09 | GS          | NULL
2016-08-10 | GS          | NULL

Upvotes: 1

Views: 7502

Answers (1)

Matthewj28
Matthewj28

Reputation: 307

Here is my solution using the above guidance

http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.bindparam

updatecode1 = update(mydata1).
    where(and_(mydata1.c.dt == bindparam('1dt'), mydata1.c.security_id == bindparam('1security_id')) ).
    values({'rtrend' : bindparam('1rtrend'),'ma200' : bindparam('1ma200')})

Then loop to define each value and update table

b1 = 'GS'

i = 0

for i in range(len(getdata1)):
    a1 = getdata1.iloc[i]['dt']
    c1 = int(getdata1.iloc[i]['rtrend'])
    d1 = float(getdata1.iloc[i]['MA200'])

    conn.execute(updatecode1, {'1dt' : a1, '1security_id' : b1, '1rtrend' : c1, '1ma200' : d1})

I'm sure there is probably a more efficient way than looping in this fashion but this worked for my problem.

NOTE: in 'c1' and 'd1' above I have used int() and float() simply to match data types to my table.

Upvotes: 3

Related Questions