Using the MySQLdb module

[Gerhard Haering]

Carsten Gaebler wrote:
> In Michael Mayhew wrote:
> 
>>_mysql_exceptions.OperationalError: (1054, "Unknown column 'atr' in 
>>'field list'")
>>
>>sql = "insert into refFlatHum values ( %s, %s, %d, %d )" % ('atr', 
>>'chr1', 90810, 92190)
>>
>>c.execute(sql)
> 
> 
> What you need here is documentation on SQL, not on MySQLdb. :-) If you want to
> use strings in a query you need to enclose them in single or double quotes but
> the query string in your example actually looks like this:
> 
> "insert into  refFlatHum values ( atr, chr1, 90810, 92190)"
> 
> The correct syntax would be:
> 
> "insert into  refFlatHum values ( 'atr', 'chr1', 90810, 92190)"

So far, so good.

> Note the single quotes around atr and chr1. To achieve this in your code you
> will have to say:
> 
> sql = "insert into refFlatHum values ( %s, %s, %d, %d )" % \
> ("'atr'", "'chr1'", 90810, 92190

Please don't construct statements manually like this. You'll save 
yourself a lot of potential trouble if you let the DB-API module do the 
quoting, like I described in my other post:

cu.execute("insert into refFlatHum values ( %s, %s, %s, %s )",
	("atr", "chr1", 90810, 92190))

Note the use of %s for all types. The DB-API module will quote each 
supported type in the correct way. For numbers, the correct way is to 
just insert the str() representation in the query. For strings, the 
correct way is to enclose them in single quotes, and escape any existing 
single quotes with another single quote. For BLOBs and whatnot, the 
correct way is another one, but I'll leave these details to the DB-API 
module :-)

-- Gerhard