Re: Error in PostgreSQL query with psycopg

On 7/31/06, Richard Huxton <dev(at)archonet(dot)com> wrote:
>
> > Line68:
> > query = cursor.execute("""INSERT INTO ConfMain (ConfName, ConfHost,
> > ConfStart, ConfEnd, ConfDays, ConfStartTime, ConfEndTime, ConfSize)
> > VALUES (\'%s\', \'%s\', \'%%\', \'%%\', %i, \'%%\', \'%%\', %i);""" %
> > (conf_name, host_name, start_day, end_day, int(num_days), start_time,
> > end_time, aud_size))
>
> Does psycopg not handle quoting for you? Almost all database interfaces
> nowadays support something like:
> result = myquery.execute('INSERT INTO foo (anInt,aDate,aText) VALUES
> (?,?,?)', query_parameters);Traceback (most recent call last): File
> "/usr/lib/cgi-bin/ConfSachem/page2.py", line 75, in ? main(num_days) File
> "/usr/lib/cgi-bin/ConfSachem/page2.py", line 68, in main query =
> cursor.execute('INSERT INTO ConfMain (ConfName, ConfHost, ConfStart,
> ConfEnd, ConfDays, ConfStartTime, ConfEndTime, ConfSize) VALUES (?, ?, ?, ?,
> ?, ?, ?, ?)' % (conf_name, host_name, start_day, end_day, num_days,
> start_time, end_time, aud_size)) TypeError: not all arguments converted
> during string formatting
> The interface deals with quoting the "?" based on the type definitions
> in the database.
>
> Trying to quote things yourself is tricky. You'll need to handle
> single-quotes in a string yourself - easy to get it wrong.
>
>
I tried to remove the single quotes and use the way you have told me as
above. Still i get an error...

[Code]

query = cursor.execute('INSERT INTO ConfMain (ConfName, ConfHost,
ConfStart, ConfEnd, ConfDays, ConfStartTime, ConfEndTime, ConfSize) VALUES
(?, ?, ?, ?, ?, ?, ?, ?)', (conf_name, host_name, start_day, end_day,
num_days, start_time, end_time, aud_size))

[Error]

Traceback (most recent call last): File
"/usr/lib/cgi-bin/ConfSachem/page2.py", line 75, in ? main(num_days) File
"/usr/lib/cgi-bin/ConfSachem/page2.py", line 68, in main query =
cursor.execute('INSERT INTO ConfMain (ConfName, ConfHost, ConfStart,
ConfEnd, ConfDays, ConfStartTime, ConfEndTime, ConfSize) VALUES (?, ?, ?, ?,
?, ?, ?, ?)', (conf_name, host_name, start_day, end_day, num_days,
start_time, end_time, aud_size)) psycopg.ProgrammingError: ERROR: syntax
error at or near "," at character 120 INSERT INTO ConfMain (ConfName,
ConfHost, ConfStart, ConfEnd, ConfDays, ConfStartTime, ConfEndTime,
ConfSize) VALUES (?, ?, ?, ?, ?, ?, ?, ?)

Charecter 120 : ConfEndTime in the specified line.

Instead when I use a different way of implementing string formatting, I get
a different error,

[Code]

query = cursor.execute('INSERT INTO ConfMain (ConfName, ConfHost,
ConfStart, ConfEnd, ConfDays, ConfStartTime, ConfEndTime, ConfSize) VALUES
(?, ?, ?, ?, ?, ?, ?, ?)' % (conf_name, host_name, start_day, end_day,
num_days, start_time, end_time, aud_size))

[Error]

Traceback (most recent call last): File
"/usr/lib/cgi-bin/ConfSachem/page2.py", line 75, in ? main(num_days) File
"/usr/lib/cgi-bin/ConfSachem/page2.py", line 68, in main query =
cursor.execute('INSERT INTO ConfMain (ConfName, ConfHost, ConfStart,
ConfEnd, ConfDays, ConfStartTime, ConfEndTime, ConfSize) VALUES (?, ?, ?, ?,
?, ?, ?, ?)' % (conf_name, host_name, start_day, end_day, num_days,
start_time, end_time, aud_size)) TypeError: not all arguments converted
during string formatting

Now, I want to know how will you present a tuple of variables (of types int,
text, date, time) to the query string. I know am making mistakes in passing
the variables using the string formatting. :(

--
With Regards

---
Parthan.S.R.
Research Assistant
National Resource Center for Free/Open Source Software
Python Developer n00b