python + psycopg2 = 未知类型? [英] python + psycopg2 = unknown types?

问题描述

似乎当我使用 callproc() 时，psycopg2 没有正确地将字符串转换为文本或字符变化.

It seems when I use callproc(), psycopg2 isn't properly casting strings as text or character varying.

例如:

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)

产量:

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

首先，bureau ama"是unknown而不是text/character，而row["report_dte"]都是未知的，这里是数据库中的日期类型，python中的datetime.date对象.

First, the "bureau ama" is unknown instead of text/character, and row["report_dte"] are all unknown, where they are date types in the database, and datetime.date objects in python.

有什么想法吗?使用 python 2.6.5.使用 cur.mogrify()，查询看起来像:

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

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

不确定E'bureau pitcher ama' 是什么意思...

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

推荐答案

根据您更新的 mogrify() 输出，您的字符串和日期时间似乎被正确解释.E'foo bar' 是 Postgres 的转义字符串常量".它允许您在文本中表示 C 样式的转义序列，例如用于制表符的 \t.此外，您在 psycopg2.ProgrammingError 中看到的 unknown 无需担心，这是正常行为.您可能首先检查函数调用的参数数量是否正确，然后尝试使用手写参数调用过程以确定哪个参数可能导致问题:

Based on your updated mogrify() output, your strings and datetimes appear to be correctly interpreted. The E'foo bar' is Postgres' "escape string constant". It lets you represent C-style escape sequences, like \t for tab, in text. Also the unknown you see in the psycopg2.ProgrammingError are nothing to worry about, that is normal behavior. You might first check that the number of arguments to your function call are correct, and perhaps try calling the procedure with a hand-written params to identify which param might be causing a problem:

测试程序:

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.

这篇关于python + psycopg2 = 未知类型?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！