[Tutor] Building an SQL query (Gabriel Farrell)

[Gabriel Farrell]

On Fri, Jun 03, 2005 at 03:50:09PM -0400, Lloyd Kvam wrote:
> The code to update the database should look something like:
> the_cursor.execute( sql_cmd, data)
> 
In PyGreSQL/pgdb it's cursor.execute(query[, params]) but it means
more or less the same thing because pgdb's paramstyle (I knew from the
DB-API[1] to look in help(pgdb) for "paramstyle") is "pyformat".  I
googled that and found some explanation of pyformat in a message[2] on
the DB-SIG mailing list.  To quickly summarize that message, pyformat
means the string fed to cursor.execute() should follow all the usual
rules of Python string formatting.

Knowing this, I can now execute my query thusly:

>>> import pgdb
>>> db = pgdb.connect(database='asdc')
>>> cursor = db.cursor()
>>> data = {
... 'noteType': None,
... 'note': "Lion's Mane",
... 'recordIdentifier': 'gsf136'
... }
>>> cursor.execute("INSERT INTO notes (notetype, note, recordidentifier) \
... VALUES (%(noteType)s, %(note)s, %(recordIdentifier)s)", data)
>>> db.commit()

Note that the re matching I had to do before is now taken care of by
pgdb (in the _query() function Danny Yoo was kind enough to track
down).  Before the query gets to PostgreSQL, the None value turns into
a NULL and "Lion's Mane" transforms into 'Lion''s Mane'.  No re
incantations necessary!

gabe

[1] http://www.python.org/peps/pep-0249.html
[2] http://aspn.activestate.com/ASPN/Mail/Message/db-sig/1632007