Python pypyodbc how do I insert variables to the execute statement?

Question

I am working with Python 3.3, pypyodbc 1.2.1, and a Quickbooks Enterprise 12 company file being access over Flexquarters QODBC version 14. I'm new to programming and python, so still learning :) I can run a query using the pypyodbc examples just fine, and produces expected results.

Notice the hardcoded email address in the execute. This works as expected:

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

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

If I try to do the same thing using the parameters that I am reading from the pypyodbc documentation, I throw an error. I'm having problems with the quotes, and parameter markers I think.

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

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

Trying to be more pythonistic? I really want to reuse the function to search different columns. Something like:

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

I have looked at a few other threads, and most of them seem to just be dealing with the parameter, and not the column to search. Can anyone help me learn this?

PS forgot to include the traceback:

Traceback (most recent call last):
  File "C:\Users\Mike\Documents\Projects\qb_sync\quickbooks.py", line 32, in <module>
    print(get_customer_id('email','[email protected]'))
  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

I think the use of

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

can not be accepted by database engine rather than pypyodbc or any other odbc interface. It's the database engine refuse to accept the query for the use of parameter on column names.

Probably you would have to try this instead to reuse the function:

# 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,))