[Tutor] Updating MySQL Database

wormwood_3 wormwood_3 at yahoo.com
Mon Oct 8 03:07:45 CEST 2007


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






More information about the Tutor mailing list