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 - [EMAIL PROTECTED] - Python, web & database development
signature.asc
Description: Digital signature
-- http://mail.python.org/mailman/listinfo/python-list
