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
