Database bind variables?

[Andrew Fabbro]

Python + Postgres. Finding good documentation on working with
databases in python is proving more challenging than writing the code


In perl, I often do things like this:

$sql = $dbh->prepare ("
UPDATE accounts
SET note = ?
WHERE account_number = ?");

and then later come back with

$note = "It's raining, it's pouring";
$sql = $dbh->execute($note, 123);

I haven't found a way to do this yet with pygresql (or should I be
using something else? if only the docs where there...

Quoting is not a pleasant solution. Neither is the % interpolation -
it would fail in the above case because the apostrophe in $note would
confuse it.

Help!?

-Drew

 

[David M. Cook]

I haven't found a way to do this yet with pygresql (or should I be
using something else? if only the docs where there...

All the postgresql adaptors I'm aware of use "pyformat" interpolation, which
is similar to python's dictionary string interpolation, e.g.

cursor.execute("select * from baz where foo=%(foo)s", {'foo' : 1234})

This does any necessary quoting for you.

Dave Cook

 

[Rene Pijlman]

Andrew Fabbro:

Python + Postgres. Finding good documentation on working with
databases in python is proving more challenging than writing the code

After scanning this newsgroup and other sources I decided to use the
pyPgSQL database driver, instead of PyGreSQL in the PostgreSQL
distribution (DaMn CaPS). It appears to be better maintained.
http://pypgsql.sourceforge.net/

It supports DBAPI.
http://www.python.org/peps/pep-0249.html

With some additional notes in the README:
http://pypgsql.sourceforge.net/README.html

This is all the documentation I've needed so far.

[parameters]

I haven't found a way to do this yet with pygresql

With pyPgSQL / DB-API it works like this.

from pyPgSQL import PgSQL

assert PgSQL.paramstyle == "pyformat"

db = PgSQL.connect(host=DB_HOST, database=DB_DATABASE,
user=DB_USERNAME, password=DB_PASSWORD,

# Enable Unicode support, which you may not need.
client_encoding="utf-8", unicode_results=1)
cursor.execute("set client_encoding to unicode")

get_cities = db.cursor()

def getCitiesInCountry(language, country):
get_cities.execute("select name, name_in_url, index "
"from location "
"where location.country = %(country)s and "
"location.location_type = 'cities' and "
"location.language = %(language)s "
"order by location.index",
{'language': language, 'country': country} )
# [...]