[Tutor] Updating MySQL Database

[Ricardo Aráoz]

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?
> 
> Thanks,
> Sam

Haven't worked with DBs in Python yet but I guess it can't be too
different. What if you put the connect/disconnect outside your loop?

    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:
        updatequery = "update resultstable set fqdn = line.split(",")[1]
where ip = line.split(",")[0];"
        cursor.execute(updatequery)
        queryresults = cursor.fetchall()
    cursor.close()
    connection.close()

Do you need to do the "cursor.fetchall()"? AFAIK an update will return
no data.
You might want to use transactions too (outside the loop).
BTW, I think your updatequery not properly configured, MySQL supports
(?) variable substitution (and even if it didn't your updatequery is
wrong, the variables should be outside the string with a %).
HTH