sql query advise

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 =3D """SELECT oppc_id, limitedDate FROM
db1.partner_promoCode"""
update_promoCode_record =3D """UPDATE db2.partner_promoCode SET
limitedDate =3D%s WHERE oppc_id =3D%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 =3D 'localhost'
dbuser =3D 'user'
dbpasswd =3D 'password'

try:
# connect to db=09
origin =3D MySQLdb.connect (host =3D dbhost,
user =3D dbuser,
passwd =3D dbpasswd,
)
except MySQLdb.Error, e:
print "Error %s" % e
sys.exit (1)
=09
=09
select_promoCode_records =3D """SELECT oppc_id, limitedDate FROM
db1.partner_promoCode"""
update_promoCode_record =3D """UPDATE db2.partner_promoCode SET
limitedDate =3D%s WHERE oppc_id =3D%s"""

org =3D origin.cursor()
org.execute(select_promoCode_records)
results =3D org.fetchall()

try:=09
for row in results:
oppc_id, date =3D row=09
org.execute(update_promoCode_record, (int(date), int(oppc_id)))
source.commit()
except:
print "Error: enable to put data"
# bye!
origin.close()
source.close() =09

[/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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: sql query advise

hi martin,

On Fri, Apr 23, 2010 at 9:50 PM, Martin Gainty 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 UPDA=
TE;
> UPDATE db2.partner_promoCode SET limitedDate =3D%s WHERE oppc_id =3D%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 confidentia=
lité
>
> Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene
> Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugt=
e
> 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 vo=
us n'êtes pas le
> destinataire prévu, nous te demandons avec bonté que pour satis=
faire
> 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=C3=
=A9 que les email
> peuvent facilement être sujets à la manipulation, nous ne pouvo=
ns accepter
> aucune responsabilité pour le contenu fourni.
>
>
>
>
>> Date: Fri, 23 Apr 2010 20:28:32 +0200
>> Subject: sql query advise
>> From: norman@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 =3D """SELECT oppc_id, limitedDate FROM
>> db1.partner_promoCode"""
>> update_promoCode_record =3D """UPDATE db2.partner_promoCode SET
>> limitedDate =3D%s WHERE oppc_id =3D%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 =3D 'localhost'
>> dbuser =3D 'user'
>> dbpasswd =3D 'password'
>>
>> try:
>> # connect to db
>> origin =3D MySQLdb.connect (host =3D dbhost,
>> user =3D dbuser,
>> passwd =3D dbpasswd,
>> )
>> except MySQLdb.Error, e:
>> print "Error %s" % e
>> sys.exit (1)
>>
>>
>> select_promoCode_records =3D """SELECT oppc_id, limitedDate FROM
>> db1.partner_promoCode"""
>> update_promoCode_record =3D """UPDATE db2.partner_promoCode SET
>> limitedDate =3D%s WHERE oppc_id =3D%s"""
>>
>> org =3D origin.cursor()
>> org.execute(select_promoCode_records)
>> results =3D org.fetchall()
>>
>> try:
>> for row in results:
>> oppc_id, date =3D 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 ǝʇÄ=
±É¹Ê=8D 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=3Dmgainty@hotmail.com
>>
>
> ________________________________
> The New Busy think 9 to 5 is a cute idea. Combine multiple calendars with
> Hotmail. Get busy.



--=20
¿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=3Dgcdmg-mysql-2@m.gmane.o rg