Python 和 MySQLdb：表替换导致语法错误

发布于 2025-01-08 04:33:47 字数 356 浏览 4 评论 0原文

我需要时不时地动态更改表和变量，所以我写了一个像这样的python方法：

    selectQ ="""SELECT * FROM  %s WHERE %s = %s;""" 
    self.db.execute(selectQ,(self.table,self.columnSpecName,idKey,))
    return self.db.store_result()

但是这会导致语法错误异常。我尝试调试它，所以我打印了方法中的变量并手动填充它们，这有效。所以我不确定我做错了什么？

是因为我尝试使用桌子的替代品吗？

另外，如何调试 mysqldb 以便将替换的查询打印为字符串？

原文

I need to dynamically change tables and variables from time to time, so I wrote a python method like this:

    selectQ ="""SELECT * FROM  %s WHERE %s = %s;""" 
    self.db.execute(selectQ,(self.table,self.columnSpecName,idKey,))
    return self.db.store_result()

However this results in a syntax error exception. I tried debugging it so I printed the variables in the method and filled them in manually, and that worked. So I am not sure what I am doing wrong ?

Is it because I try to use a substitute for a table ?

Also how do I debug mysqldb so it prints the substituted query as a string ?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

空袭的梦i 2025-01-15 04:33:47

DB API 中的参数替换仅适用于值，不适用于表或字段。您需要对这些使用普通的字符串替换：

selectQ ="""SELECT * FROM  %s WHERE %s = %%s;""" % (self.table,self.columnSpecName)
self.db.execute(selectQ,(idKey,))
return self.db.store_result()

请注意，值占位符有一个双 % - 这样，初始字符串替换就可以将其保留下来。

Parameter substitution in the DB API is only for values - not tables or fields. You'll need to use normal string substitution for those:

selectQ ="""SELECT * FROM  %s WHERE %s = %%s;""" % (self.table,self.columnSpecName)
self.db.execute(selectQ,(idKey,))
return self.db.store_result()

Note that the value placeholder has a double % - this is so that it's left alone by the initial string substitution.

回复收藏 0 原文

我不咬妳我踢妳 2025-01-15 04:33:47

这是一个完整的工作示例

def rtnwkpr(tick, table, col):

    import MySQLdb as mdb
    tickwild = tick + '%'       
    try:
        con = mdb.connect(host, user, password, db);
        cur = con.cursor()
        selectq = "SELECT price FROM %s WHERE %s LIKE %%s;" % (table, col)
        cur.execute(selectq,(tickwild))
        return cur.fetchall()

Here is a full working example

def rtnwkpr(tick, table, col):

    import MySQLdb as mdb
    tickwild = tick + '%'       
    try:
        con = mdb.connect(host, user, password, db);
        cur = con.cursor()
        selectq = "SELECT price FROM %s WHERE %s LIKE %%s;" % (table, col)
        cur.execute(selectq,(tickwild))
        return cur.fetchall()

回复收藏 0 原文

素罗衫 2025-01-15 04:33:47

您必须使用字符串替换来添加表名和列名，驱动程序将仅处理参数。

Ed：NM，丹尼尔回答得更快更完整

回复收藏 0 原文

装迷糊 2025-01-15 04:33:47

您的意思是写：

selectQ = """SELECT * FROM %s WHERE %s = %s;""" % (self.table,self.columnSpecName,idKey) #maybe the idkey should be self.idkey? don't know the rest of the code

self.db.execute(selectQ)

这只是字符串格式的错误吗？

顺便说一句，为什么你要为这种工作编写显式 SQL？更好地使用神奇的 sqlalchemy 进行 python sql 操作..

Did you mean to write:

selectQ = """SELECT * FROM %s WHERE %s = %s;""" % (self.table,self.columnSpecName,idKey) #maybe the idkey should be self.idkey? don't know the rest of the code

self.db.execute(selectQ)

and this is just a mistake with string formatting?

Btw why do you write explicit SQL for this kind of work? better use magical sqlalchemy for python sql manipulation..

回复收藏 0 原文

~没有更多了~