Thread: Error in PostgreSQL query with psycopg

Hello,

Am not sure whether i can post such a question over here, still the
error is in my postgresql query syntax, hence i post it in this mailing
list. sorry if am wrong.

I am getting a 'type error' with the following query.

[Code]

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))

Line80:
main(num_days)

[/Code]

Error:

Traceback (most recent call last): File
"/usr/lib/cgi-bin/ConfSachem/page2.py", line 80, in ? main(num_days)
File "/usr/lib/cgi-bin/ConfSachem/page2.py", line 73, in main
DbaseInsert(conf_name, host_name, start_day, end_day, num_days,
start_time, end_time) File "/usr/lib/cgi-bin/ConfSachem/page2.py", line
68, in DbaseInsert query = cursor.execute("""INSERT INTO ConfMain
(ConfName, ConfHost, ConfStart, ConfEnd, ConfDays, ConfStartTime,
ConfEndTime, ConfSize) VALUES (\'%s\', \'%s\', \'%s\', \'%s\', %i,
\'%s\', \'%s\', %i)""" % (conf_name, host_name, start_day, end_day,
int(num_days), start_time, end_time, aud_size)) TypeError: int argument
required

The input is from the form and is transfered to variables. These
variables are passed as arguments to the DbaseInsert() function.

ConfName and ConfHost are varchars
ConfStart and ConfEnd are date
ConfStartTime and ConfEndTime are time
ConfDays and ConfSize are integer

I am going wrong either in passing the values through arguments or in
the string formatting used? How will i pass the date and time data which
are in the form of 'dd-mm-yyyy' and 'hh:mm:ms' as required by the date
and time data types of PostgreSQL ?

Am i wrong in useing back slash escaped single quotes to quote strings
in the query? Am using it with values for strings, date and time, while
i dont use it with %i for integers.
--
With Regards

Parthan ('technofreak')
------------------------------
Research Assistant @ NRC-FOSS and Python\PHP Developer
A Proud Tuxian and an Ubuntero

Parthan wrote:
> Hello,
>
> Am not sure whether i can post such a question over here, still the
> error is in my postgresql query syntax, hence i post it in this mailing
> list. sorry if am wrong.
>
> I am getting a 'type error' with the following query.
>
> [Code]
>
> 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);
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.
--
   Richard Huxton
   Archonet Ltd

On 7/31/06, Richard Huxton <dev@archonet.com> wrote:
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

>> 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);
> The interface deals with quoting the "?" based on the type definitions
> in the database.

You do not have to quote the query in psycopg... Psycopg does it for you.

See the bottom of this for an example using a dictionary:

http://www.devx.com/opensource/Article/29071/0/page/3

Joshua D. Drake



>
> Trying to quote things yourself is tricky. You'll need to handle
> single-quotes in a string yourself - easy to get it wrong.


--

    === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
    Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Parthan wrote:
> Hello,
>
> Am not sure whether i can post such a question over here, still the
> error is in my postgresql query syntax, hence i post it in this
> mailing list. sorry if am wrong.
>
> I am getting a 'type error' with the following query.
>
> [Code]
>
> 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))
Try a newline at the end of the query as :

query = cursor.execute("""INSERT INTO ConfMain (ConfName, ConfHost,
ConfStart, ConfEnd, ConfDays, ConfStartTime, ConfEndTime, ConfSize)
VALUES (\'%s\', \'%s\', \'%%\', \'%%\', %i, \'%%\', \'%%\', %i);\n""" %
(conf_name, host_name, start_day, end_day, int(num_days), start_time,
end_time, aud_size))

I don't remember if it is necessary or not but I've been making it a
habit of using line continuation if it is a multi-line query

D. C. Horst
Evanston, IL.

Parthan,

never, never, never try quoting on your own! You can only fail. The only choice is to fail now or later.

The only recommended way with Python and DBAPI2.0 is:

lala="huibuh"
cs.execute ("select bla from foo where schupp=%s", (lala,))

or better

cs.execute ("select bla from foo where schupp=%(lala)s", dict(lala="huibuh"))

(that's with psycopg2)

Harald




--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Let's set so double the killer delete select all.

> never, never, never try quoting on your own! You can only fail. The
> only choice is to fail now or later.

Nonetheless, in case it's useful, here's a barebones wrapper I call on
everything:

def sqlValue (value):
   if value is None:
    return "NULL"
   elif type(value) == types.StringType:
    value = value.replace("'", "''")
    # This is Postgres-specific - sigh
    value = value.replace("\\", "\\\\")
    return "'" + value + "'"
   elif type(value) == types.UnicodeType:
    return "'" + value.encode("UTF-8") + "'"
   else:
    return repr(value)

This has worked for me for several years, although I'm certain someone
will immediately poke holes in it.  That's the other reason I posted
it!

- John Burger
   MITRE

Hii,

With regards to the previous mail and replaies, I tried with this code. Although it doesn't give any error when i run it as a script,  but the return value is NONE for 'q', which is assigned to return value of cursor.execute(), and the data are not inserted into the database.

[code]

name = 'GLV'
host = 'Parthan'
start = '04-08-2006'
end = '04-08-2006'
days = 1
starttime = '15:00:00'
endtime = '18:00:00'
size = 20

arglist =  (name, host, start, end, days, starttime, endtime, size)

connection = connect("dbname=TimeTable user=myname password=mypassword")
cur = connection.cursor()
q = cur.execute("INSERT INTO ConfMain (ConfName, ConfHost, ConfStart, ConfEnd, ConfDays, ConfStartTime, ConfEndTime, ConfSize) VALUES (%s, %s, %s, %s, %i, %s, %s, %i);", arglist)
print q

<Result: q=> None

Am not getting any other error, I tried this one by calling the script in the temrinal. When i ran a similar one with the browser, it says premature end of script :(

http://pastebin.de/9994 --> this is my apache error log (/var/log/apache2/error.log)

--
With Regards


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

On Jul 31 10:40, Parthan SR wrote:
> On 7/31/06, Richard Huxton <dev@archonet.com> wrote:
> 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 (?, ?, ?, ?, ?, ?, ?, ?)

Did you follow the 120 character? It points to ',' just after '?'. Can
you please replace '?' character with '%s'. I'm not sure but, IIRC,
psycopg variable using method (e.g. "$1, $2, ..." or "?, ?, ...")
should be adjustable.


Regards.

On Mon, Jul 31, 2006 at 09:47:40PM -0400, John D. Burger wrote:
> >never, never, never try quoting on your own! You can only fail. The
> >only choice is to fail now or later.
>
> Nonetheless, in case it's useful, here's a barebones wrapper I call on
> everything:

It'll work fine on single byte encodings (ascii and latin*) and for
well-formed utf8. If somebody can supply malformed utf8 you may have a
problem. It will definitly break for some eastern encodings.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.