Databases
StackOverflow2013
Postsdbo
POPython pypyodbc how do I insert variables to the execute statement?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPython pypyodbc how do I insert variables to the execute statement?
    primarykey
    data
    text
    <p>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.</p> <p>Notice the hardcoded email address in the execute. This works as expected:</p> <pre><code>def get_customer_id(search_col,search_str): '''(str,str) --&gt; str &gt;&gt;&gt;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 </code></pre> <p>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.</p> <pre><code>def get_customer_id(search_col,search_str): '''(str,str) --&gt; str &gt;&gt;&gt;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 </code></pre> <p>Trying to be more pythonistic? I really want to reuse the function to search different columns. Something like:</p> <pre><code>cur.execute("SELECT listid FROM CUSTOMER WHERE search_str=search_col") </code></pre> <p>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?</p> <p>PS forgot to include the traceback:</p> <pre><code>Traceback (most recent call last): File "C:\Users\Mike\Documents\Projects\qb_sync\quickbooks.py", line 32, in &lt;module&gt; 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] </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMLindsay
    1. USMLindsay
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POPython pypyodbc how do I insert variables to the execute statement?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COOne usually says pythonic, not pythonistic :)
      singulars
      1. POPython pypyodbc how do I insert variables to the execute statement?
      1. USvaultah
    2. COok good to know :)
      singulars
      1. POPython pypyodbc how do I insert variables to the execute statement?
      1. USMLindsay
    3. COI checked with pyodbc also, and it produces the same error.
      singulars
      1. POPython pypyodbc how do I insert variables to the execute statement?
      1. USMLindsay
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload