Velocity Reviews

Velocity Reviews (http://www.velocityreviews.com/forums/index.php)
-   Python (http://www.velocityreviews.com/forums/f43-python.html)
-   -   Re: SQL Query via python (http://www.velocityreviews.com/forums/t345221-re-sql-query-via-python.html)

Steve Holden 05-22-2005 04:31 PM

Re: SQL Query via python
 
Jeff Elkins wrote:
> I'm attempting to pass an SQL query via the console:
>
> $ ./getbd month 05
>
> The arguments get seem to passed correctly (via print statements) and then:
>
> cursor.execute ("""
> SELECT name, month, day ,category, city FROM bday
> WHERE %s = %s
> """,(arg1,arg2))
>
> No results. However, if I hardcode the WHERE argument with a field name:
>
> cursor.execute ("""
> SELECT name, month, day ,category, city FROM bday
> WHERE month = %s
> """,(arg2))
>
> It works.
>

Because here you aren't trying to parameterize the name of a database
object.

> How can I code the left side of the WHERE clause so I can pass an arbitrary
> field name to search on?
>
>

You might have to construct the SQL statement to include the names of
tables and columns. It's still better to use parameterization for data
substitutions, though, because then you don't have to perform any quoting.
--
Steve Holden +1 703 861 4237 +1 800 494 3119
Holden Web LLC http://www.holdenweb.com/
Python Web Programming http://pydish.holdenweb.com/


Scott David Daniels 05-23-2005 03:14 PM

Re: SQL Query via python
 
Steve Holden wrote:
> Jeff Elkins wrote:
>>... cursor.execute("SELECT name, month, day ,category, city "
>> " FROM bday WHERE %s = %s", (arg1,arg2))
>> No results. However, if I hardcode the WHERE argument with a field name:
>> cursor.execute("SELECT name, month, day ,category, city "
>> " FROM bday WHERE month = %s", (arg2,))
>> Works.
>>

> Because here you aren't trying to parameterize the name of a database
> object.
>
>> How can I code the left side of the WHERE clause so I can pass an
>> arbitrary field name to search on?
>>
>>

> You might have to construct the SQL statement to include the names of
> tables and columns. It's still better to use parameterization for data
> substitutions, though, because then you don't have to perform any quoting.


I'm sure Steve knows this, but for those wondering:
A database system will often produce an optimized execution plan
from the first argument (and cache that plan). The string had
better be good enough for the DBMS to determine how to best perform
the query. This means only data can be parameterized, not table or
field names). The query plan includes things like which indexes to
use and what tables to access in what order.

--Scott David Daniels
Scott.Daniels@Acm.Org


All times are GMT. The time now is 11:11 AM.

Powered by vBulletin®. Copyright ©2000 - 2014, vBulletin Solutions, Inc.
SEO by vBSEO ©2010, Crawlability, Inc.