SQL Query via python

[Gerhard Haering]

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 - gh at ghaering.de - Python, web & database development
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 196 bytes
Desc: Digital signature
URL: <http://mail.python.org/pipermail/python-list/attachments/20050523/d5385e97/attachment.sig>