hi martin, On Fri, Apr 23, 2010 at 9:50 PM, Martin Gainty <mgai...@hotmail.com> wrote: > Norm- > I would strongly suggest locking the table before updating..a SELECT for > UPDATE would accomplish that objective:
thanks for the reply and the advise on locking the table > > """SELECT oppc_id, limitedDate FROM db1.partner_promoCode_record FOR UPDATE; > UPDATE db2.partner_promoCode SET limitedDate =%s WHERE oppc_id =%s""" so in essence one can chain sql statements by using the ';' as a separator. > > http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html > Martin Gainty > ______________________________________________ > Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité > > Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene > Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte > Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht > dient lediglich dem Austausch von Informationen und entfaltet keine > rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von > E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. > > Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le > destinataire prévu, nous te demandons avec bonté que pour satisfaire > informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie > de ceci est interdite. Ce message sert à l'information seulement et n'aura > pas n'importe quel effet légalement obligatoire. Étant donné que les email > peuvent facilement être sujets à la manipulation, nous ne pouvons accepter > aucune responsabilité pour le contenu fourni. > > > > >> Date: Fri, 23 Apr 2010 20:28:32 +0200 >> Subject: sql query advise >> From: nor...@khine.net >> To: mysql@lists.mysql.com >> >> 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=mgai...@hotmail.com >> > > ________________________________ > The New Busy think 9 to 5 is a cute idea. Combine multiple calendars with > Hotmail. Get busy. -- ¿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
