[DB-SIG] db module wrapper

Randall Smith randall at tnr.cc
Fri Aug 20 22:45:01 CEST 2004


Vernon Cole wrote:
> Ian:
>   I think that would be an SQL syntax error. My SQL query analyser (MsSQL)
> replies to
> "select * from tname where col = bob"
> with an error message "invalid column name 'bob'".
> 
> I think you would have to phrase it like:
>    query = ("select * from tname where col = '", name, "'")
> The single tics are part of the query syntax.
You don't have to manually quote 'bob'.  The database module knows 
'name' is a string and quotes it properly using the parameter 
functionality that already exists in all DB API2 modules.
> 
> MsSQL accepts that construct whether 'col' is a numeric or a string valued
> column.
> YMMV
> 
> I'm still thinking about how .executemany() might work...
> -----
Easy.  Look at my previous posts.

l1 = ['harry', 'sam', 'charley']
l2 = [1, 2, 3]

query = "select * from tname where col1 = ", l1, " and col2 = ", l2
cursor.execute(query)

query = []
query.append('select * from tname where col1 = ')
query.append(l1)
query.append('and col2 = ')
query.append(l2)
cursor.execute(query)

> Vernon
> 
> -----Original Message-----
> From: Randall Smith [mailto:randall at tnr.cc]
> Sent: Friday, August 20, 2004 1:52 PM
> To: Ian Bicking
> Cc: db >> Python DB-SIG Mailing List
> Subject: Re: [DB-SIG] db module wrapper
> 
> 
> Assume that the first item in the list is always sql.  Is that a valid 
> assumption?
> 
> Randall
> 
> Ian Bicking wrote:
> 
>>Just thought about it, how would you deal with:
>>
>>name = 'bob'
>>query = ['select * from tname where col = ', name]
>>
>>The query is just two strings -- the first is a SQL expression, the 
>>second some data.  But there's no way to tell that just from looking at 
>>the query list.
>>
>>
>>
>>Randall Smith wrote:
>>
>>
>>>Ian,
>>>
>>>What do you think about Vernon Cole's idea with passing a query in as 
>>>a list.  I thought about it and I'm starting to like it.  Check this out.
>>>
>>>l1 = ['harry', 'sam', 'charley']
>>>l2 = [1, 2, 3]
>>>
>>>query = "select * from tname where col1 = ", l1, " and col2 = ", l2
>>>cursor.execute(query)
>>>
>>>query = []
>>>query.append('select * from tname where col1 = ')
>>>query.append(l1)
>>>query.append('and col2 = ')
>>>query.append(l2)
>>>cursor.execute(query)
>>>
>>>Note: This is like executemany.
>>>
>>>There is no need for parsing at all and the line breaking encouraged 
>>>by using a list makes for readable code.
>>>
>>>What do you think?
>>>
>>>Randall
>>
>>
> _______________________________________________
> DB-SIG maillist  -  DB-SIG at python.org
> http://mail.python.org/mailman/listinfo/db-sig


More information about the DB-SIG mailing list