Using python variable in SQL query

Question

I would like to use python to do an SQL query that prompts the user for input to complete the query. Currently when I run my code it will tell me I have a TypeError (see below for full error message). My code will be posted below first.

import pyodbc
from datetime import datetime
import pandas as pd

def today():
    return datetime.now().strftime('%Y-%m-%d')
##    return (datetime.now()+timedelta(days=1)).strftime('%Y-%m-%d')


#create connection string
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=OPTSAPPS02,1433\\SQLEXPRESS;'
                      'Database=OPTS Production Tracking Service;'
                      'UID=fetch;'
                      'PWD=Reader123')

cursor = conn.cursor()

#create query parameter for target
targetDTString = today()+' 23:59'
targetDate = datetime.strptime(targetDTString,'%Y-%m-%d %H:%M')

###FIND A WAY TO TAKE INPUT FOR QUERY AND MAKE IT SO YOU CAN SEARCH/FILTER BY MOTHER AND STAMPER NUMBER####

query = cursor.execute("""SELECT TOOL_OUT, TANK, STATION, FORMING_CURRENT, TIME, BUILDING_CURRENT, CYCLE_TIME FROM ELECTROFORMING
    WHERE PROCESS='ELECTROFORMING' AND (TOOL_OUT=?)""", (input("TOOL_OUT: ")))
    
DF = pd.read_sql_query(query,conn)
print(DF)

Traceback (most recent call last): File "C:\Users\mberardi\Documents\python scripts\print-eforming-stats.py", line 39, in DF = pd.read_sql_query(query,conn) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "C:\Users\mberardi\AppData\Local\anaconda3\Lib\site-packages\pandas\io\sql.py", line 486, in read_sql_query return pandas_sql.read_query( ^^^^^^^^^^^^^^^^^^^^^^ File "C:\Users\mberardi\AppData\Local\anaconda3\Lib\site-packages\pandas\io\sql.py", line 2328, in read_query cursor = self.execute(sql, params) ^^^^^^^^^^^^^^^^^^^^^^^^^ File "C:\Users\mberardi\AppData\Local\anaconda3\Lib\site-packages\pandas\io\sql.py", line 2260, in execute raise TypeError("Query must be a string unless using sqlalchemy.") TypeError: Query must be a string unless using sqlalchemy.

Answer 1

You have to pass query directly as a string without using conn.cursor() and pass the TOOL_OUT value into params as a tuple:

query = """SELECT TOOL_OUT, TANK, STATION, FORMING_CURRENT, TIME, BUILDING_CURRENT, 
    CYCLE_TIME FROM ELECTROFORMING
    WHERE PROCESS='ELECTROFORMING' AND (TOOL_OUT=?)"""

tool_out = input("TOOL_OUT: ")
DF = pd.read_sql_query(query, conn, params=(tool_out,))
print(DF)