MySQL tables join with python

Question

i'm trying to fuse 2 MySQL tables in 1. Both tables have a column with the same values, so i would actually do a join. I'll show you the code i'm using to do that:

import MySQLdb
import _mysql

mobility = MySQLdb.connect(host="127.0.0.1", user="root", passwd="", db="Mobility")
mobility_cursor = mobility.cursor()

datasets = _mysql.connect(host='127.0.0.1',user='root',passwd='',db='datasets')
datasets.query("SELECT * FROM process_manager_Temp1")
proc_man_Temp1 = datasets.store_result()
proc_man_Temp1_dicts = proc_man_Temp1.fetch_row(maxrows=0, how=1)

for row in proc_man_Temp1_dicts:
    mobility_cursor.execute("UPDATE `process_manager_Temp` SET(`Resource`, `Format`) VALUES (%s,%s) WHERE `process`=%s ", (row['Resource'],row['format'],row['process'])
    mobility.commit()

It gives me an error on the last line. It says

siimobility.commit() SyntaxError: invalid syntax

Antbody know what's wrong ? Oe may be there's a quicker way to do this.

Fabricator · Accepted Answer · 2014-05-31 07:39:07Z

2

I've not used your syntax before. I would try the following instead:

update process_manager_temp set resource = %s, format = %s where process = %s

on a side note, you probably don't need 2 different db connectors. just using MySQLdb is fine.

answered May 31, 2014 at 7:39

Fabricator

12.8k2 gold badges29 silver badges40 bronze badges

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

4 Comments

giogix Over a year ago

It is a different syntax but it usually works. Anyway your syntax is better readable. But that's not the problem. I still get the same error.

Fabricator Over a year ago

hmm. is that a mysql error, or python error? looks like you are missing a right paren in the 2nd to the last line.

giogix Over a year ago

Ok, i searched the error for 1 hour. Sorry about this question !! That was the problem, thanks. Anyway, are there any other methods to do this kind of join easier ?

Fabricator Over a year ago

I use sqlyog sja to generate a lot of automated reports. I think it also has a feature sync between dbs. you can give that a try.

Collectives™ on Stack Overflow

MySQL tables join with python

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related