[Tutor] Updating MySQL Database

[Eric Brunson]

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 <eric at ericwalstad.com>
> To: wormwood_3 <wormwood_3 at yahoo.com>
> Cc: Python Tutorlist <tutor at 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 at python.org
> http://mail.python.org/mailman/listinfo/tutor
>