Python pypyodbc 如何将变量插入到执行语句中？

Question

我正在使用 Python 3.3、pypyodbc 1.2.1 和一个通过 Flexquarters QODBC 版本 14 访问的 Quickbooks Enterprise 12 公司文件。我是编程和 python 的新手，所以仍在学习:)我可以运行查询使用pypyodbc 示例很好，并产生了预期的结果。

注意执行中的硬编码电子邮件地址。这按预期工作:

def get_customer_id(search_col,search_str):
    '''(str,str) --> str

    >>>get_customer_id(email, foo@foo.com)
    80000001-1385782702
    '''
    cur.execute("SELECT listid FROM CUSTOMER WHERE email='foo@foo.com'")
    for row in cur.fetchall():
        for field in row: 
            return field

如果我尝试使用我从 pypyodbc 文档中读取的参数来做同样的事情，我会抛出一个错误。我认为引号和参数标记有问题。

def get_customer_id(search_col,search_str):
    '''(str,str) --> str

    >>>get_customer_id(email, foo@foo.com)
    80000001-1385782702
    '''
    cur.execute("SELECT listid FROM CUSTOMER WHERE email=?",(search_str,))
    for row in cur.fetchall():
        for field in row: 
            return field

想变得更像 python ？我真的很想重用该功能来搜索不同的列。像这样的东西:

cur.execute("SELECT listid FROM CUSTOMER WHERE search_str=search_col")

我看过其他几个线程，其中大多数似乎只是在处理参数，而不是要搜索的列。谁能帮我学习这个？

PS 忘记包含回溯:

Traceback (most recent call last):
  File "C:\Users\Mike\Documents\Projects\qb_sync\quickbooks.py", line 32, in <module>
    print(get_customer_id('email','foo@foo.com'))
  File "C:\Users\Mike\Documents\Projects\qb_sync\quickbooks.py", line 27, in get_customer_id
    cur.execute("SELECT listid FROM CUSTOMER WHERE email=?",[search_str,])
  File "C:\Python\lib\site-packages\pypyodbc.py", line 1457, in execute
    self._BindParams(param_types)
  File "C:\Python\lib\site-packages\pypyodbc.py", line 1420, in _BindParams
    check_success(self, ret)
  File "C:\Python\lib\site-packages\pypyodbc.py", line 982, in check_success
    ctrl_err(SQL_HANDLE_STMT, ODBC_obj.stmt_h, ret, ODBC_obj.ansi)
  File "C:\Python\lib\site-packages\pypyodbc.py", line 960, in ctrl_err
    raise Error(state,err_text)
pypyodbc.Error: ('HY004', '[HY004] [Microsoft][ODBC Driver Manager] SQL data type out of range')
[Finished in 1.7s]

Answer 1

我觉得用

cur.execute("""SELECT listid FROM CUSTOMER WHERE ?=?""",[column, email])

不能被数据库引擎而不是 pypyodbc 或任何其他 odbc 接口(interface)接受。这是数据库引擎拒绝接受在列名上使用参数的查询。

可能您必须尝试这样做才能重用函数:

# First construct your dynamic query for the targeted column
sql = """SELECT listid FROM CUSTOMER WHERE %s=?""" %(column) 

# Then provide the dynamic value for the dynamic query string
cur.execute(sql, (value,))