Parsing a variable into a sql statement in Python [duplicate]

Question

I have the following insert statement that let me parse sql query into a python file and then returning a dataframe of that data that is collected from the query

   params = 'DRIVER={ODBC Driver 13 for SQL Server};' \
     'SERVER=localhost;' \
     'PORT=XXX;' \
     'DATABASE=database_name;' \
     'UID=XXX;' \
     'PWD=XXX;'

   params = urllib.parse.quote_plus(params)

   db = create_engine('mssql+pyodbc:///?odbc_connect=%s' % params)

   sql = '''
   select * from table_name
   where column_name= variable_in_python
   '''

   dataframe = pd.read_sql_query(sql, db)

Now, after the 'where' statement, I want to have a variable that I declare in Python, for example an id-number (let's say 1123581321). This variable needs to come in that 'variable_python'-place.

I tried:

   import pyodbc as py
   import urllib
   from sqlalchemy import create_engine
   import pandas as pd 

   x = 1123581321

   params = 'DRIVER={ODBC Driver 13 for SQL Server};' \
     'SERVER=localhost;' \
     'PORT=XXX;' \
     'DATABASE=database_name;' \
     'UID=XXX;' \
     'PWD=XXX;'

   params = urllib.parse.quote_plus(params)

   db = create_engine('mssql+pyodbc:///?odbc_connect=%s' % params)

   sql = '''
   select * from table_name
   where column_name= 'x'
   '''

   dataframe = pd.read_sql_query(sql, db)

This obviously doesn't work. But I have not a single idea how I can do this (if it can be done).

Any suggestions are more than welcome!

Answer 1

You can use ? as a placeholder in the query and pass the value as a parameter to the read_sql_query function:

sql = '''
select * from table_name
where column_name= ?
'''

dataframe = pd.read_sql_query(sql, db, params=(variable_in_python,))

Answer 2

You can do something like:

sql = '''
   select * from table_name
   where column_name= {}
   '''.format(variable_in_python)

For more information, have a look at https://docs.python.org/3/tutorial/inputoutput.html