[Tutor] Building an SQL query (Gabriel Farrell)
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.
More information about the Tutor