SQL Query via python

[Jeff Elkins]

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

 

[Dennis Lee Bieber]

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)

--

 

[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]

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

 

[Austyn Bontrager]

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

 

[Gerhard Haering]

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 - (e-mail address removed) - Python, web & database development

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

iD8DBQFCkgXkdIO4ozGCH14RArjUAJsHgGZ9vVRFgTxB4ocv9e3+Jya5ZACePYa6
R6fjarUVA5P99/UUBnqgTdo=
=9SOq
-----END PGP SIGNATURE-----

 

[Frithiof Andreas Jensen]

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.