英文:
How to fix the psycopg2 syntax error at or near '%'?
问题
基本上是在尝试创建一个INSERT函数，该函数将编译插入操作并执行它。
def Insert(self, tablename, **kwargs):qms = []for idx, item in enumerate(kwargs):qms.append('%%s')qmarks = ', '.join(qms)res = []res.extend(kwargs.keys())res.extend(kwargs.values())res = tuple(res)QUERY = "INSERT INTO " + tablename + " (%s) VALUES (%s)" % (qmarks, qmarks)self.dc.execute(QUERY, res)self.db.commit()
返回错误信息如下：
psycopg2.errors.SyntaxError: syntax error at or near "%"LINE 1: INSERT INTO users (%s, %s) VALUES (%s, %s)
英文:
Basically trying to create ISNERT function, which will compile the insert and execute it.
def Insert(self,tablename, **kwargs):qms = []for idx, item in enumerate(kwargs):qms.append('%%s')qmarks = ', '.join(qms)res = []res.extend(kwargs.keys())res.extend(kwargs.values())res = tuple(res)QUERY = "INSERT INTO "+tablename+" (%s) VALUES (%s)" % (qmarks, qmarks)self.dc.execute(QUERY, res)self.db.commit()
Returns me:
psycopg2.errors.SyntaxError: syntax error at or near "%"LINE 1: INSERT INTO users (%s, %s) VALUES (%s, %s)
答案1
得分: 2
psycopg2的execute()函数只会注入值，而不会注入列名，你需要将真实的列名传递给QUERY：
import psycopg2import psycopg2.extrasclass A():def __init__(self):self.dc = ...self.db = ...def Insert(self, tablename, **kwargs):columns = []valuePlaceholders = []values = []for column, value in kwargs.items():columns.append(column)valuePlaceholders.append('%%s')values.append(value)QUERY = "INSERT INTO " + tablename + " (%s) VALUES (%s)" % (', '.join(columns), ', '.join(valuePlaceholders))print(self.db.mogrify(QUERY, values))#self.db.execute(QUERY, values)#self.db.commit()a = A().Insert('foo', arg1="bar", arg2="test")
输出：
b'INSERT INTO foo (arg1, arg2) VALUES (%s, %s)'
英文:
psycopg2's execute() function just injects the values but not the columns, you need to pass the real column names to QUERY:
import psycopg2import psycopg2.extrasclass A():def __init__(self):self.dc = ...self.db = ...def Insert(self, tablename, **kwargs):columns = []valuePlaceholders = []values = []for column, value in kwargs.items():columns.append(column)valuePlaceholders.append('%%s')values.append(value)QUERY = "INSERT INTO " + tablename + " (%s) VALUES (%s)" % (', '.join(columns), ', '.join(valuePlaceholders))print(self.db.mogrify(QUERY, values))#self.db.execute(QUERY, values)#self.db.commit()a = A().Insert('foo', arg1="bar", arg2="test")
Output:
b'INSERT INTO foo (arg1, arg2) VALUES (%s, %s)'
答案2
得分: 0
你的qms数组被填充了字符串值'%s'。
然后，你的qmarks字符串等于'%s, %s'。
接下来，你将该值注入到你的查询字符串中两次，所以你得到'INSERT INTO users (%s, %s) VALUES (%s, %s)'。
问题在于你已经用不正确的值填充了你的数组。
你需要注入表名和值，而不是%s。
英文:
Your qms array is filled with the string values '%s'.
Your qmarks string then equals '%s, %s'.
After that you inject that value in your query string twice so you have 'INSERT INTO users (%s, %s) VALUES (%s, %s)'.
The issue here is that you have filled your array with incorrect values.
You need to inject your table names and values instead of %s
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论