curious paramstyle qmark behavior

Jon Clements joncle at googlemail.com
Fri Oct 20 22:23:50 CEST 2006


BartlebyScrivener wrote:

> With
>
> aColumn = "Topics.Topic1"'
>
> The first statement "works" in the sense that it finds a number of
> matching rows.
>
> c.execute ("SELECT Author, Quote, ID, Topics.Topic1, Topic2 FROM
> QUOTES7 WHERE " + aColumn + " LIKE ?", ("%" + sys.argv[1] + "%",))
>
> I've tried about 20 different variations on this next one. And it finds
> 0 records no matter what I do. Is there some violation when I use two
> qmarks?
>
> c.execute ("SELECT Author, Quote, ID, Topics.Topic1, Topic2 FROM
> QUOTES7 WHERE ? LIKE ?", (aColumn, "%" + sys.argv[1] + "%"))
>
> I'm using mx.ODBC and Python 2.4.3 to connect to an MS Access DB.
>
> Thank you,

At a guess; it's probably translating the first '?' (the one after the
WHERE) as a string literal: so your query string is effectively "select
<fields> from <table> where 'somestring' like '%<sys.argv[1]>%'".

I would try re-writing it like:
c.execute("select <fcolumns> from <table> where %s like ?" % aColumn,
"%" + sys.argv[1] + "%")

I don't use mx.ODBC, and definately don't use Access (gagging sounds...
but if you're stuck with it, so be it)...

hth,

Jon.




More information about the Python-list mailing list