0

I was wondering if there is a way to update all rows of a pandas dataframe in one query to mysql.

I select a dataframe from mysql. Then I do some calculations and then I want the rows in the mysql table to update to the rows in the dataframe. I do not select the complete table so I cannot just replace the table.

the column order/type remain unchanged so it just needs to replace/update the rows and I have a primary key indexed, auto-increment 'id' column if this makes any difference.

thanks

The error I get when trying to create the sql statement from the post Bob commented below.

58 d = {'col1': 'val1', 'col2': 'val2'} 
59 sql = 'UPDATE table SET {}'.format(', '.join('{}=%s'.format(k) for k in d)) 
60 print sql 
61 sql undefined, k = 'col2', global d = {'col1': 'val1', 'col2': 'val2'} 
<type 'exceptions.ValueError'>: zero length field name in format 
    args = ('zero length field name in format',) 
    message = 'zero length field name in format'
user3605780
  • 6,542
  • 13
  • 42
  • 67

1 Answers1

0

I don't think that's possible with Pandas. At least not directly from Pandas. I know that you can use to_sql() to append or replace, but that doesn't help you very much.

You could try converting a dataframe to a dict with to_dict() and then executing an Update statement with values from the dict and a mysql cursor.

UPDATE

You might be using a version (like 2.6) of python that requires positional arguments in the format()

sql = 'UPDATE table SET {0}'.format(', '.join('{0}=%s'.format(k) for k in d))
Bob Haffner
  • 8,235
  • 1
  • 36
  • 43
  • Thanks for the input. Can you give me an example on how to update the dictionary in mysql? I only found [this post](http://stackoverflow.com/questions/3432/multiple-updates-in-mysql) but that works with "insert ignore on duplicate". And is there a maximum number of rows for 1 query? thanks – user3605780 Dec 30 '14 at 10:22
  • Have you seen this post? http://stackoverflow.com/questions/11517106/how-to-update-mysql-with-python-where-fields-and-entries-are-from-a-dictionary – Bob Haffner Dec 30 '14 at 13:09
  • Yes I saw the post, however when I put in that code I get the error: 58 d = {'col1': 'val1', 'col2': 'val2'} 59 sql = 'UPDATE table SET {}'.format(', '.join('{}=%s'.format(k) for k in d)) 60 print sql 61 sql undefined, k = 'col2', global d = {'col1': 'val1', 'col2': 'val2'} : zero length field name in format args = ('zero length field name in format',) message = 'zero length field name in format' – user3605780 Dec 30 '14 at 17:26
  • Sorry, I'm not much help when it comes to mysql. I'm a little surprised that no one else has commented or answered this. – Bob Haffner Dec 31 '14 at 03:51
  • It is not a mysql error. The error is in python when creating the string for the sql statement. I added it in the original question so it would be more readable. – user3605780 Dec 31 '14 at 08:49