How to pass the user input data to SQL syntax in python

1
votes

I want to pass the user input variables to SQL via pandas.read_sql_query by using connection between pycharm IDE version 2020.2 and Microsoft SQL Server 2019 which shown by the code below :

InputID = int(input('Give me your input MeterpointID: ')) 
OccurDate = input('What is the beginning occurrence datetime (yyyy-mm-dd hh:mm:ss):') 
SQL_Query = pd.read_sql_query('select * from tblprofilewhimp_norm where 
MeterpointID = InputID and Date_M > \'OccurDate\'', conn)

However, this code doesn't work and the IDE showed me the error like this :

pandas.io.sql.DatabaseError: Execution failed on sql 'select * from tblprofilewhimp_norm where MeterpointID = InputID and Date_M > 'OccurDate'': ('42S22', "[42S22] [Microsoft][ODBC Driver 17 for SQL Server] [SQL Server]Invalid column name 'InputID'. (207) (SQLExecDirectW)")

So, could you tell me how to handle this problem ?

Thank you in advance.

2
pythonsql
Please fix your code formatting. - Klaus D.
The error is indicating that the column InputID doesn't exist. Could you post a sample of the data? - will.cass.wrig

2 Answers

0
votes

You'll need to look up the placeholder format used by the SQL library, then pass the values as the params argument to pd.read_sql_query.

Assuming the SQL library is pyodbc, the placeholder format is ? and the syntax would be:

SQL_Query = pd.read_sql_query(
    'select * from tblprofilewhimp_norm where MeterpointID = ? and Date_M > ?',
    conn,
    params=(InputID, OccurDate),
)
-1
votes

Use format strings to concatenate strings and variables:

SQL_Query = pd.read_sql_query('select * from tblprofilewhimp_norm where MeterpointID = {} and Date_M > \'{}\''.format(InputID, OccurDate), conn)