[Tutor] Building an SQL query (Gabriel Farrell)

Lloyd Kvam python at venix.com
Fri Jun 3 22:15:52 CEST 2005

The code to update the database should look something like:
	the_cursor.execute( sql_cmd, data)

I am not using postgresql so I do not know the place-holder mark for
your module.  You can get that from the module documentation.

So sql_cmd could be something like
	"UPDATE my_table SET title=<PLACE_HOLDER> where record_id=<PLACE_HOLDER>"
If data gets passed as a tuple (some modules support dict), data is
	("Lion's Mane", 12345)

For mysql the place-holder is %s, and the code would be
	the_cursor.execute("UPDATE my_table SET title=%s where record_id=%s",
		("Lion's Mane", 12345) )

> I'm just getting started with Python and PostgreSQL but I found that
> str(tuple(valueList)) wouldn't work for me because I had a few values
> with apostrophes.  PostgreSQL needed 'Lion''s Mane' but Python was
> sending it "Lion's Mane", so I ended up writing this little function:
> def sqlNice(valueList):
>     count = 1
>     y = '('
>     for x in valueList:
>         x = x.replace("'", "''")
>         y = y + "'" + x + "'"
>         if count < len(valueList):
>             y = y + ', '
>         count = count + 1
>     y = y + ')'
>     y = y.replace("'NULL'", 'NULL')
>     return y
> Does anyone see any major stumbling blocks in that?  
> On a side note, I've struggled with PyGreSQL.  At first I was using
> the pg module, but I switched to pgdb when insert() wasn't working for
> me and I thought I would have less trouble using something that's
> DB-API compliant.  There seemed to be more documentation there, and I
> figured it's a good idea to go with the standard.  However, it does
> seem like I'm covering ground I'm sure someone else has already
> crossed when I create these re functions to manipulate queries.  For
> inserts, at least, it seems a Python dictionary should be able to do
> the job nicely.
> gabe

Lloyd Kvam
Venix Corp
Lloyd Kvam
Venix Corp

More information about the Tutor mailing list