hello, i have to write a query which has to pull data from a remote mysql server, modify the table scheme, format some of the fields and then populate the new database.
i am using MySQLdb which is a python interface to mysql db. how would i write a query to do this update from from a single statement that uses tables from both databases? in essence how to merge these two lines into one statement: select_promoCode_records = """SELECT oppc_id, limitedDate FROM db1.partner_promoCode""" update_promoCode_record = """UPDATE db2.partner_promoCode SET limitedDate =%s WHERE oppc_id =%s""" here is a simplified version of what i have so far. [code] #!/usr/local/bin/python2.6 # -*- coding: utf-8 -*- # import MySQLdb # connect to the MySQL server and select the databases dbhost = 'localhost' dbuser = 'user' dbpasswd = 'password' try: # connect to db origin = MySQLdb.connect (host = dbhost, user = dbuser, passwd = dbpasswd, ) except MySQLdb.Error, e: print "Error %s" % e sys.exit (1) select_promoCode_records = """SELECT oppc_id, limitedDate FROM db1.partner_promoCode""" update_promoCode_record = """UPDATE db2.partner_promoCode SET limitedDate =%s WHERE oppc_id =%s""" org = origin.cursor() org.execute(select_promoCode_records) results = org.fetchall() try: for row in results: oppc_id, date = row org.execute(update_promoCode_record, (int(date), int(oppc_id))) source.commit() except: print "Error: enable to put data" # bye! origin.close() source.close() [/code] thanks -- ¿noʎ uɐɔ uʍop ǝpısdn ǝʇıɹʍ uɐɔ ı - %>>> "".join( [ {'*':'@','^':'.'}.get(c,None) or chr(97+(ord(c)-83)%26) for c in ",adym,*)&uzq^zqf" ] ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org