CODEWITHSUNDEEP
pythonsql
Steven01123581321
Steven01123581321

Reputation: 460

Parsing a variable into a sql statement in Python

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!

Upvotes: 1

Views: 1572

Answers (3)

Rahul P
Rahul P

Reputation: 2663

I like the answer by @blhsing.

Another way is f-strings. I particularly like them because they make things very readable.

For example:

# Query Parameters
column_name = 'x'

and then:

sql = f'''
select * from table_name
where column_name= {column_name}
'''

You could go further with this and use a dictionary of parameters and call each one by key within the f-string itself. The advantage of this method is that if you have a lot of parameters for longer and more complex queries, you always have a dictionary to refer to. Another is that when you do a print(sql) in this case, you can see exactly what parameters you are passing (this helps in the case of longer queries with more parameters).

Upvotes: 1

blhsing
blhsing

Reputation: 106445

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

Upvotes: 1

Meet Taraviya
Meet Taraviya

Reputation: 889

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

Upvotes: 0

Related Questions