Re: SQL Query via python

Discussion in 'Python' started by Steve Holden, May 22, 2005.

  1. Steve Holden

    Steve Holden Guest

    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/
     
    Steve Holden, May 22, 2005
    #1
    1. Advertising

  2. 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 David Daniels, May 23, 2005
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Rob
    Replies:
    1
    Views:
    654
  2. Anonymous
    Replies:
    0
    Views:
    1,476
    Anonymous
    Oct 13, 2005
  3. Jeff Elkins

    SQL Query via python

    Jeff Elkins, May 21, 2005, in forum: Python
    Replies:
    6
    Views:
    731
    Frithiof Andreas Jensen
    May 24, 2005
  4. Replies:
    5
    Views:
    229
    ara.t.howard
    Nov 6, 2007
  5. JohnnyQ

    multiple SQL line query via Perl

    JohnnyQ, Oct 3, 2003, in forum: Perl Misc
    Replies:
    1
    Views:
    139
    Greg Bacon
    Oct 4, 2003
Loading...

Share This Page