On Tue, 21 Jun 2005, Mike Hansen wrote:
> I'm having trouble loading an image into a Postgre database. The code is
> below as well as the traceback in the apache log. Is something up with
> my sqlStatement? Do I need to use something other than %s? How can I
> avoid that type error?
Hi Mike,
At the moment, you're trying to do the direct value interpolation like
this:
> sqlStatement = """INSERT INTO images (image)
> VALUES (%s);
> """ % (data_obj)
> cur.execute(sqlStatement)
The issue is that this style of direct SQL interpolation won't work very
well on non-SQLish data. Python has no clue what the context of your
interpolation is: when you ask it to do:
######
sqlStatement = """INSERT INTO images (image)
VALUES (%s); """ % data_obj
######
it has no idea that you're constructing an SQL statement, and it's
oblivious to the special SQL quoting rules you need to use to pass binary
data to the database.
Another instance of this problem comes up even with normal string data:
something like:
cursor.execute("insert into people(name) values ('%s')" % name)
will break as soon as someone with the name "O'Reilly" meets the
application.
Thankfully, you don't have to change much to fix the formatting bug. The
only thing you'll need to do is let the SQL cursor do the value formatting
for you:
######
sqlStatement = """INSERT INTO images (image)
VALUES (%s);
cur.execute(sqlStatement, (data_obj))
######
Cursors know how to do the proper value interpolation that's specific to
SQL: just pass the extra values to the execute() function, and the SQL
driver will do the hard work.
Hope this helps!
_______________________________________________
Tutor maillist - Tutor@python.org
http://mail.python.org/mailman/listinfo/tutor
