Geek asks and answers

Python + psycopg2 = unknown types?

It seems that when I use callproc (), psycopg2 incorrectly sheds strings depending on the text or character.

For instance:

values = [pid, 4, 4, 'bureau ama', 0, 130, row['report_dte'], row['report_dte'], 1, 1, 1, None, None, 'published', row['report_dte']]
cur.callproc('header', values)

Productivity:

psycopg2.ProgrammingError: function header(integer, integer, integer, unknown, integer, integer, unknown, unknown, integer, integer, integer, unknown, unknown, unknown, unknown) does not exist
LINE 1: SELECT * FROM header(509952,4...

Firstly, the "bureau ama" is unknown instead of text / symbol, and the string ["report_dte"] is unknown where they are date types in the database and datetime.date objects in python.

Any idea? Using python 2.6.5. Using cur.mogrify (), the query looks like this:

SELECT header(509952,4,4,E'bureau ama',0,130,'2011-01-24','2011-01-24',1,1,1,NULL,NULL,E'published','2011-01-24')

Not sure what it means E'bureau pitcher ama'...

+3
source share
1 answer

mogrify() , , . E'foo bar' escape- Postgres. escape- C-, \t , . unknown, psycopg2.ProgrammingError, , . , , , , :

:

CREATE OR REPLACE FUNCTION
    foo (num INTEGER, name VARCHAR, ts TIMESTAMP)
RETURNS TABLE (num INTEGER, name VARCHAR, ts TIMESTAMP)
AS $$ SELECT $1, $2, $3; $$
LANGUAGE SQL;

:

% python
>>> import datetime
>>> import psycopg2
>>> conn = psycopg2.connect("user=postgres")
>>> r = conn.cursor()
>>> args = [1, "hello", datetime.datetime.now()]
>>> r.callproc('foo', args)
[1, 'hello', datetime.datetime(2011, 3, 10, 18, 51, 24, 904103)]

>>> r.callproc('fooxyz', args)
psycopg2.ProgrammingError: function fooxyz(integer, unknown, unknown) does not exist

LINE 1: SELECT * FROM fooxyz(1,E'hello','2011-03-10T18:51:24.904103'...
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
+2

More articles:


All Articles