Python:psycopg2.ProgrammingError:INSERT 的表达式多于目标列 [英] Python: psycopg2.ProgrammingError:INSERT has more expressions than target columns
问题描述
我是 Python 新手,我似乎无法弄清楚为什么会出现此错误.它告诉我我有太多参数,但是该表有 8 列,我传递了 8 个参数.到底是怎么回事?这个错误是否具有误导性,真正的问题是我试图传入可能为 None
或类型为 Boolean
的值,使用 %s
?
I am new to python and I can't seem to figure out why I am getting this error. It is telling me I have too many parameters however the table has 8 columns, and I pass it 8 parameters. What is going on? Could this error be misleading and the real problem is the fact that I am trying to pass in values that could be None
or could be of type Boolean
using %s
?
这是代码片段
db.cursor().execute("CREATE TABLE temp_clean_mp_duplicates (id bigint, distinctid character varying(255), created timestamp without time zone, email character varying(255), created_exist boolean, email_exist boolean, user_exist boolean, distinct_id_found boolean, CONSTRAINT temp_clean_mp_duplicates_pkey PRIMARY KEY(id));")
mp_email = None
email_exist = False
if "$email" in mp_properties :
mp_email = mp_properties["$email"]
email_exist = True
mp_distinct_id = result["$distinct_id"]
db_cursor.execute("SELECT u.id, u.email, udm.distinctid, u.sessionId FROM users as u,user_distinctid_map as udm where u.id = udm.user_id and udm.distinctid = %s and lower(email) = lower(%s)", (mp_distinct_id,mp_email,))
distinct_id_found = True
if db_cursor.rowcount == 0 :
distinct_id_found = False
created = None
created_exist = False
if "$created" in mp_properties :
created = mp_properties["$created"]
created_exist = True
db_cursor.execute("SELECT u.id, u.email, u.sessionId FROM users as u where lower(u.email) = lower(%s)", (mp_email,))
user_id = 0
user_exist = False
if db_cursor.rowcount > 0 :
user_id = db_cursor.fetchone()[0]
user_exist = True
db.cursor().execute("INSERT INTO temp_clean_mp_duplicates (id, distinctid, created, email) VALUES (%s,%s,%s,%s,%s,%s,%s,%s);", (user_id, mp_distinct_id, created, mp_email, created_exist, email_exist, user_exist, distinct_id_found))
推荐答案
如您所见,问题确实出在 INSERT
语句上:
As you found, the issue is indeed the INSERT
statement:
INSERT INTO temp_clean_mp_duplicates (id, distinctid, created, email) VALUES (%s,%s,%s,%s,%s,%s,%s,%s);", (user_id, mp_distinct_id, created, mp_email, created_exist, email_exist, user_exist, distinct_id_found)
您是对的,表有八列,但您告诉 SQL 您只使用 INSERT INTO temp_clean_mp_duplicates 设置其中四列(id, distinctid, created, email)
- 括号中的列是它将尝试设置的列.
You're right that the table has eight columns, but you're telling SQL that you're only setting four of them with INSERT INTO temp_clean_mp_duplicates (id, distinctid, created, email)
- the columns in the parenthesis are the ones that it will try to set.
以下是我对 INSERT 语句的看法:
Here's how I think of an INSERT statement:
INSERT INTO <tablename> (<target columns>) VALUES (<values on new row>)
因此,如果您尝试设置所有八个列名,则需要将所有列名放在括号中:
So if you're trying to set all eight of them, you would need to put all the column names in the parenthesis:
INSERT INTO temp_clean_mp_duplicates (id, distinctid, created, email, created_exist, email_exist, user_exist, distinct_id_found) VALUES (%s,%s,%s,%s,%s,%s,%s,%s);", (user_id, mp_distinct_id, created, mp_email, created_exist, email_exist, user_exist, distinct_id_found)
上面的查询应该可以工作.
The above query should work.
这篇关于Python:psycopg2.ProgrammingError:INSERT 的表达式多于目标列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!