如何解决此message错误消息(python)
如何解决此message错误消息(python)
提问于 2018-08-01 20:43:22
我最近修改了我的insert查询，并开始收到以下错误消息：
error: argument 1 must be a string or unicode object: got tuple instead以下是导致此错误的SQL插入字符串：
timestamp = 'NOW()::timestamp'
sql_insert_command =
"INSERT INTO test_table(
article_id,
date_published,
date_modified,
row_created_on,
row_modified_on )"
"VALUES(%s,%s,%s,%s,%s)",
(new_record[0],new_record[1],new_record[2],timestamp,timestamp)这是要传递给我的查询函数的SQL字符串：
('INSERT INTO test_table(
article_id,
date_published,
date_modified,
row_created_on,
row_modified_on)
VALUES(%s,%s,%s,%s,%s)',
('0530973',
'2018-01-10 17:29:00',
'2018-02-15 11:58:32',
'NOW()::timestamp',
'NOW()::timestamp'))这是我的旧字符串，它可以工作，但违反了从不做的声明：http://initd.org/psycopg/docs/usage.html#query-parameters
sql_insert_command =
"INSERT INTO test_table(
article_id,
date_published,
date_modified,
row_created_on,
row_modified_on ) " \
"VALUES('" + new_record[0] + "','" +
new_record[1]+ "','" +
new_record[2] + "'," +
timestamp + "," +
timestamp + ")"这是使用上面的命令传递给我的查询函数的SQL字符串：
INSERT INTO test_table(article_id,
date_published,
date_modified,
row_created_on,
row_modified_on)
VALUES('0530973',
'2018-01-10 17:29:00',
'2018-02-15 11:58:32',
NOW()::timestamp,
NOW()::timestamp)导致此错误消息的原因是什么？我该如何解决这个问题呢？
下面是我用来连接到数据库并插入记录的代码：
class DatabaseConnection(object):
_instance = None
def __new__(cls):
if cls._instance is None:
cls._instance = object.__new__(cls)
db_config = {'dbname': 'development', 'host': 'localhost',
'password': 'somepassword', 'port': 5432, 'user': 'postgres'}
try:
print('connecting to PostgreSQL database...')
connection = DatabaseConnection._instance.connection = psycopg2.connect(**db_config)
connection.autocommit = True
cursor = DatabaseConnection._instance.cursor = connection.cursor()
cursor.execute('SELECT VERSION()')
db_version = cursor.fetchone()
except Exception as error:
print('Error: connection not established {}'.format(error))
DatabaseConnection._instance = None
else:
print('connection established\n{}'.format(db_version[0]))
return cls._instance
def __init__(self):
self.connection = self._instance.connection
self.cursor = self._instance.cursor
def insert_new_records(self, insert_query):
try:
# used for testing
print (insert_query)
result = self.cursor.execute(insert_query)
except Exception as error:
print('error execting query "{}", error: {}'.format(insert_query, error))
return None
else:
return result
def __del__(self):
self.connection.close()
self.cursor.close()我用来插入记录的代码：
new_record = ("0530973", "2018-01-10 17:29:00", "2018-02-15 11:58:32",
"0530974", "2018-01-10 17:29:00", "2018-02-15 11:58:32")
timestamp = 'NOW()::timestamp'
sql_insert_command =
"INSERT INTO test_table(
article_id,
date_published,
date_modified,
row_created_on,
row_modified_on ) " \
"VALUES('" + new_record[0] + "','" +
new_record[1]+ "','" +
new_record[2] + "'," +
timestamp + "," +
timestamp + ")"回答 1
Stack Overflow用户
回答已采纳
发布于 2018-08-01 21:33:09
由execute解释时，变量timestamp会错误地用单引号括起来。
最简单的方法是只使用字符串，而不是sql文本中的变量：
cur.execute("""
INSERT INTO test_table(
article_id,
date_published,
date_modified,
row_created_on,
row_modified_on )
VALUES(%s,%s,%s,now()::timestamp,now()::timestamp)""",
(new_record[0],new_record[1],new_record[2])
)通过format()实现更优雅的解决方案
cur.execute("""
INSERT INTO test_table(
article_id,
date_published,
date_modified,
row_created_on,
row_modified_on )
VALUES(%s,%s,%s,{},{})""".format(timestamp,timestamp),
(new_record[0],new_record[1],new_record[2])
)您还可以使用extensions中的函数AsIs()：
from psycopg2.extensions import AsIs
cur.execute("""
INSERT INTO test_table(
article_id,
date_published,
date_modified,
row_created_on,
row_modified_on )
VALUES(%s,%s,%s,%s,%s)""",
(new_record[0],new_record[1],new_record[2],AsIs(timestamp),AsIs(timestamp))
)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51633597
复制相关文章
相似问题