[Tutor] Updating MySQL Database

[Kent Johnson]

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? Using that method, for example, assuming I have a list of results, each line of the form "ip,fqdn":
> 
>     for line in inputlist:
>         updatequery = "update resultstable set fqdn = line.split(",")[1] where ip = line.split(",")[0];"

This doesn't look like real code, is it missing some quotes and +? It 
also looks like you are embedding the data in the SQL command, this is 
very bad practice, it opens you to SQL injection attacks and doesn't 
correctly handle data with special characters. You should pass the 
parameters in a separate list.

>         connection = MySQLdb.connect(db=self.todatabase, host=self.host, 
>             user=self.user, passwd=self.passwd, port=int(self.port))
>         cursor = connection.cursor()
>         cursor.execute(updatequery)
>         queryresults = cursor.fetchall()
>         cursor.close()
>         connection.close()
> 
> But this means making a connection and query for every line of results, which is a lot. Any ideas on optimization?

There is no need to make a new connection and cursor for each query, you 
can reuse the same cursor, just put the code to aquire and close them 
outside the loop. Also take a look at cursor.executemany().

I'm not familiar with MySQL but there is probably a way to run commands 
from a file, that may be faster than doing it from Python. You could 
build a file with all the required commands.

Kent