[DB-SIG] PyFormat and execute

Marc Colosimo mcolosimo at mitre.org
Wed Feb 11 10:45:33 EST 2004


On Feb 10, 2004, at 5:17 PM, Danny Yoo wrote:
>> !#/usr/bin/env python
>>
>> import pgdb
>>
>> db = pgdb.connect(database="test")
>> cursor = db.cursor()
>>
>> my_user = {"id":10,"name":"Marc","pet":None}
>>
>> cursor.execute("INSERT into myTable (my_id, my_name, my_pet_id) values
>> " +
>>              "(%(id)s,'%(name)s',%(pet)s)" % my_user)
>                                              ^^^^^^^^^
> Hi Marc,
>
> Don't do that.  *grin*
>
> Send cursor.execute a second parameter --- that dictionary 'my_user' 
> ---
> and let the driver do the interpolation for you.
>
> ###
>>>> import pgdb
>>>> conn = pgdb.connect(database='dyoo')
>>>> cursor = conn.cursor()
>>>> my_user = {'id' : 10, 'name' : 'dyoo', 'pet' : None}
>>>> cursor.execute('''insert into myTable (my_id, my_name, my_pet_id)
> ...                   values (%(id)s, %(name)s, %(pet)s)''',
> ...                my_user)
>>>> conn.commit()
> ###
>
> Do not try to do the interpolation yourself: let the database handler 
> do
> it.
>
Hi Danny,

Wow! Why isn't there a DB FAQ with this in it? I was just using the 
PyFormat and really didn't understand that the DB-API can handle the 
interpolation for me. I now understand what the [5] next to the 
description for execute was describing.

Does this also escape naughty SQL characters like ' and \ in strings?

And how can one use the type objects to send NUMBERS, DATETIME objects?

Thanks,
Marc





More information about the DB-SIG mailing list