SQL Query via python

Discussion in 'Python' started by Jeff Elkins, May 21, 2005.

  1. Jeff Elkins

    Jeff Elkins Guest

    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.

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


    Thanks,

    Jeff Elkins
     
    Jeff Elkins, May 21, 2005
    #1
    1. Advertising

  2. On Fri, 20 May 2005 23:57:01 -0400, Jeff Elkins
    <> declaimed the following in comp.lang.python:

    >
    > 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))
    >

    You have to remember that .execute(), using the (template,
    (arg...)) format, is designed to apply suitable quoting to the
    arguments. It does not parse the SQL to determine if arguments are being
    used as identifiers and not data values.

    The above sample probably generated:

    SELECT name, month, day, category, city from bday
    WHERE "month" = 5

    note the " around month . Obviously, a string /value/ of "month" will
    never match a numeric value of 5.

    Basically, in any SQL where you are templating /identifiers/
    (field names, table names, etc.), you'll need to do /that/ replacement
    with Python's % operator. THEN let .execute() quote/substitute the
    remaining /data/ values. As others have mentioned though, you'll need to
    pre-validate the identifiers if they are coming from user input, to
    ensure that someone didn't give you something that is executable SQL and
    damaging to the data.

    In your example, something like:

    validFields = [ "name", "month", "day", "category", "city"]
    if arg1.lower() in validFields:
    sql = """select .... where %s = %%s""" % arg1.lower()
    c.execute(sql, (arg2,))
    else:
    print "Invalid selection criteria: '%s'" % arg1
    print "not found in: %s" % ", ".join(validFields)

    --
    > ============================================================== <
    > | Wulfraed Dennis Lee Bieber KD6MOG <
    > | Bestiaria Support Staff <
    > ============================================================== <
    > Home Page: <http://www.dm.net/~wulfraed/> <
    > Overflow Page: <http://wlfraed.home.netcom.com/> <
     
    Dennis Lee Bieber, May 21, 2005
    #2
    1. Advertising

  3. Jeff Elkins

    Jeff Elkins Guest

    On Saturday 21 May 2005 01:32 pm, Dennis Lee Bieber wrote:
    > On Fri, 20 May 2005 23:57:01 -0400, Jeff Elkins


    > You have to remember that .execute(), using the (template,
    > (arg...)) format, is designed to apply suitable quoting to the
    > arguments. It does not parse the SQL to determine if arguments are being
    > used as identifiers and not data values.
    >
    > The above sample probably generated:
    >
    > SELECT name, month, day, category, city from bday
    > WHERE "month" = 5


    This is getting much clearer. Thanks much.

    Jeff
     
    Jeff Elkins, May 21, 2005
    #3
  4. Jeff Elkins

    Jeff Elkins Guest

    Just as an fyi:

    In one weekend I have gone from knowing zip about SQL/Python to implementing
    code on my personal server that emails info to family about birthdays and
    such. I know I could have installed other *nix programs that would do the
    same thing, but so what :)

    Thanks so much to the folks on this list who took the trouble to reply. I hope
    I can pay it forward in the future.

    Jeff Elkins
     
    Jeff Elkins, May 22, 2005
    #4
  5. How about:

    cursor.execute("""
    SELECT name, month, day ,category, city FROM bday
    WHERE %(col_name)s = %%s
    """ % dict(col_name=arg1),
    (arg2)
    )

    The "%(col_name)s" will be replaced by normal Python string
    substitution, while the "%%s" will be quoted by the db module.

    Watch out for SQL injection in arg1, though! Maybe check beforehand that
    it is a string containing only word characters...

    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.
    >
    > How can I code the left side of the WHERE clause so I can pass an arbitrary
    > field name to search on?
    >
    >
    > Thanks,
    >
    > Jeff Elkins
    >
    >
     
    Austyn Bontrager, May 23, 2005
    #5
  6. On Mon, May 23, 2005 at 04:12:31PM +0000, Austyn Bontrager wrote:
    > How about:
    >
    > cursor.execute("""
    > SELECT name, month, day ,category, city FROM bday
    > WHERE %(col_name)s = %%s
    > """ % dict(col_name=arg1),
    > (arg2)
    > )
    >
    > The "%(col_name)s" will be replaced by normal Python string
    > substitution, while the "%%s" will be quoted by the db module.
    >
    > Watch out for SQL injection in arg1, though! Maybe check beforehand that
    > it is a string containing only word characters...


    Maybe what you really need is the ability to search for two fields, or
    both fields?

    Here's an approach that I usually use:

    - Write a search function accepting the parameters you search for.
    - If you fill one of the parameters with None (in SQL: NULL), you don't
    care for its value
    - This way, you can narrow your search as much as you like

    Here's a quick test script, using pysqlite2 to demonstrate the approach.
    It's also not meant as production code, but at least it only works with
    SQL parameter binding.

    - SQLite uses :name for named parameters instead of %(name)s.
    - locals() is a neat hack to access the local variables as a dictionary

    #v+
    from pysqlite2 import dbapi2 as sqlite

    con = sqlite.connect(":memory:")
    cur = con.cursor()
    cur.execute("create table test(a, b)")
    cur.execute("insert into test(a, b) values (1, 2)")
    cur.execute("insert into test(a, b) values (1, 3)")
    cur.execute("insert into test(a, b) values (2, 3)")

    def search(a, b):
    global cur
    cur.execute("""
    select a, b from test
    where :)a is null or a=:a)
    and :)b is null or b=:b)
    """, locals())
    return cur.fetchall()

    print search(2, None)
    print "-" * 50
    print search(None, 3)
    print "-" * 50
    print search(2, 3)
    #v-

    -- Gerhard
    --
    Gerhard Häring - - Python, web & database development

    -----BEGIN PGP SIGNATURE-----
    Version: GnuPG v1.2.5 (GNU/Linux)

    iD8DBQFCkgXkdIO4ozGCH14RArjUAJsHgGZ9vVRFgTxB4ocv9e3+Jya5ZACePYa6
    R6fjarUVA5P99/UUBnqgTdo=
    =9SOq
    -----END PGP SIGNATURE-----
     
    Gerhard Haering, May 23, 2005
    #6
  7. "Jeff Elkins" <> wrote in message
    news:...
    > Just as an fyi:
    >
    > In one weekend I have gone from knowing zip about SQL/Python to

    implementing
    > code on my personal server that emails info to family about birthdays and
    > such.


    Actually - http://www.pythonweb.org/ will handle the SQL (and many other
    tasks) in a much simpler way!

    If you do try the web.database.object examples, be aware that the docs are
    not entirely tracking the code; f.ex. databases have no "type" parameter;
    it's an "adapter"

    i.e.
    connection = web.database.connect(type="sqlite",database="object-simple.db")
    should be:
    connection =
    web.database.connect(adapter="sqlite",database="object-simple.db")'

    there are probably others - it's good to see what the parameters are in
    "idle" if something does not work as advertised.

    Overall, Pythonweb is very impressive work IMO.
     
    Frithiof Andreas Jensen, May 24, 2005
    #7
    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:
    655
  2. Anonymous
    Replies:
    0
    Views:
    1,492
    Anonymous
    Oct 13, 2005
  3. Steve Holden

    Re: SQL Query via python

    Steve Holden, May 22, 2005, in forum: Python
    Replies:
    1
    Views:
    341
    Scott David Daniels
    May 23, 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:
    143
    Greg Bacon
    Oct 4, 2003
Loading...

Share This Page