Thanks to everyone who responded for the information and tips.
* That line I had:
> for line in inputlist:
> updatequery = "update resultstable set fqdn = line.split(",")[1]
> where ip = line.split(",")[0];"
was totally bogus. I was typing and thinking, and not at the same rate:-) The
real thing would be something more like:
for line in inputlist:
fqdn = line.split(",")[1]
ip = line.split(",")[0]
updatequery = "update resultstable set fqdn = '%s' where ip = '%s';" %
(fqdn, ip)
* I will try a first version with a single connection and close, looping
through executes in the middle. Should have thought of that first...
Thanks again,
Sam
_____________________________________
----- Original Message ----
From: Eric Walstad <[EMAIL PROTECTED]>
To: wormwood_3 <[EMAIL PROTECTED]>
Cc: Python Tutorlist <tutor@python.org>
Sent: Sunday, October 7, 2007 11:07:12 PM
Subject: Re: [Tutor] Updating MySQL Database
Hey Sam,
wormwood_3 wrote:
> Hello all,
>
> I have a script which takes data from a file or MySQL DB, looks up some
> stuff, then can print results to console or file. I would also like it to be
> able to update a MySQL database with the results. Does anyone have any ideas
> on how to do this? I can update records just fine, but what is the best way
> to do LOTS of updates aside from running an update statement per record?
I recommend not worrying about speed on your first iteration of your
script. I'd first define 'too slow', then write the script the way that
feels intuitive to you. If your script passes your threshold of 'too
slow', then look at optimizing it.
If optimization is really necessary, I'd look into .executemany(). If
that is still too slow for you then I'd consider writing the update SQL
to a file and then calling mysql, passing it the sql file your script
created. I found this last approach the fastest for a data import
script I once wrote for importing millions of records to a PostgreSQL
database.
-E
_______________________________________________
Tutor maillist - Tutor@python.org
http://mail.python.org/mailman/listinfo/tutor
