字符串格式化程序上的 python psycopg2 内部错误

Question

您好，我正在尝试以下脚本

import psycopg2 as pq
import os

# Create the database
os.system('dropdb ptest')
os.system('createdb ptest')
# connect to the database
cn = pq.connect('dbname=ptest user=myname')
cr = cn.cursor()

# the wierd tuple at the EOL below is to preserve the list 
# lookup in case that is where the error is.
cr.execute('CREATE TABLE id1 (%s varchar, %s int PRIMARY KEY, %s int, %s int, %s varchar)' % tuple(['fund', 'sleeve_id', 'sub_id', 'level_id', 'Entity_id'])
cr.execute('INSERT INTO id1 (%s,%s,%s,%s,%s) VALUES ("%s","%s","%s","%s","%s");' % tuple(['fund', 'sleeve_id', 'sub_id', 'level_id', 'Entity_id']+['RHUBARB RHUBARB - RHUBARB RHUBARB', '2', '1', '1', 'CRUMB1']))

我收到错误

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "<string>", line 1, in <module>
psycopg2.InternalError: current transaction is aborted, commands ignored until end of transaction block

我做错了什么？

顺便说一句，该行发生了同样的错误

cr.execute('INSERT INTO id1 (%s,%s,%s,%s,%s) VALUES ("%s","%s","%s","%s","%s");' % ('fund', 'sleeve_id', 'sub_id', 'level_id', 'Entity_id','RHUBARB RHUBARB - RHUBARB RHUBARB', '2', '1', '1', 'CRUMB1'))

Answer 1

我认为您必须提交创建表的第一条语句，然后才能对其运行任何插入。尝试在两个 SQL 语句之间运行 cn.commit() 看看是否可以解决问题。

或者，在创建与数据库的初始连接时设置 autocommit=True。