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 _______________________________________________ Tutor maillist - Tutor@python.org http://mail.python.org/mailman/listinfo/tutor