wormwood_3 wrote:
> 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...
>
Let me add another point to the "don't put your bind variables in the
statement yourself" camp, like you're doing.
If the database supports prepared statements, the db module will offer
the statement without specific values to the database which will then
preprocess and cache the prepared statement. Then, when you call the
statement repeatedly with different bind variables, the database will be
able to pull the preprocessed statement from cache and bind the variable
values to it, saving the overhead of "compiling" the statement repeatedly.
I don't believe mysql supports this, I know Oracle does, but no matter,
don't do:
updatequery = "update resultstable set fqdn = '%s' where ip = '%s'" % (fqdn, ip)
curs.execute( updatequery )
do
updatequery = "update resultstable set fqdn = %s where ip = %s"
curs.execute( updatequery, ( fqdn, ip ) )
Hope that helps,
e.
P.S. you don't need the semicolon.
> 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
>
_______________________________________________
Tutor maillist - Tutor@python.org
http://mail.python.org/mailman/listinfo/tutor
