[Tutor] Updating MySQL Database

[Rick Pasotto]

On Sun, Oct 07, 2007 at 06:07:45PM -0700, 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];"
>         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?

Good grief! Open the connection and create the cursor ONCE, use it as
many times as you need, and then close it (them). Closing the connection
automatically closes the cursor.

CAUTION: the following is not fully tested but I believe it to be correct.

connection = MySQLdb.connect(db=self.todatabase,host-self.host,
             user=self.user, passwd=self.passwd, port=int(self.port))
cursor = connection.cursor()
for line in inputlist:
	(ip,fqdn) = line.split(',')
	updatequery = "update resultstable set %s where ip = %s" % (fqdn,ip)
	cursor.execute(updatequery)
connection.close()

Alternatively you could do:

connection = MySQLdb.connect(db=self.todatabase,host-self.host,
             user=self.user, passwd=self.passwd, port=int(self.port))
cursor = connection.cursor()
updatequery = "update resultstable set %s where ip = %s"
for line in inputlist:
	vals = list(line.split(','))
	vals.reverse()
	cursor.execute(updatequery,vals)
connection.close()

I think the second version is more efficient.

or you could do:

connection = MySQLdb.connect(db=self.todatabase,host-self.host,
             user=self.user, passwd=self.passwd, port=int(self.port))
cursor = connection.cursor()
updatequery = "update resultstable set %s where ip = %s"
for line in inputlist:
	(ip,fqdn) = line.split(',')
	cursor.execute(updatequery,(fqdn,ip))
connection.close()

Note that for these last two there is a comma between the arguments to
cursor.execute() rather than a percent.

-- 
"I am only one. But I am one. I cannot do everything but I can do
 something. And I will not let what I cannot do interfere with what
 I can do." -- Edward E. Hule
    Rick Pasotto    rick at niof.net    http://www.niof.net