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.
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)