[Tutor] Updating MySQL Database

[Steve Willoughby]

Rick Pasotto wrote:

> 	(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.

Not only is it more efficient, I'd say it's the ONLY way you should be 
doing this.  using % to splice the data values into the SQL query string 
itself is fraught with problems, SQL injection vulnerability and getting 
proper data syntax being the most obvious ones.  Letting the MySQL 
module do it for you, by using %s (and ONLY %s regardless of data type!) 
where you want data to go, and supplying a list of values to 
cursor.execute(), lets the library code properly escape and quote the 
data as appropriate.  It understands things like datetime, float, 
string, None, etc. natively which is a big win.