Python Forum

Python Forum > Python Coding > General Coding Help > pass captured value from input() to where clause
Full Version: pass captured value from input() to where clause
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

metro17

Sep-06-2019, 06:49 AM
How can pass the value captured in a variable using input() to a where clause in a select statement?

def ada():
    import pyodbc
    import pandas as pd
    conn = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};'
                          'Server=ABCD400;'
                          'Database=TestDb;'
                          'Trusted_Connection=yes;')
    SQL_Query = pd.read_sql_query('''select distinct DoctorName,Department from [TestDb].[dbo].[EveryDayAvailability] ''', conn)
    df = pd.DataFrame(SQL_Query, columns=['DoctorName','Department'])
    print (df)
    DName = input("Please provide the name of the Doctor you would like to visit ?")
    print("Printed below are the list of appointments for this doctor ")
    SQL_Query = pd.read_sql_query('''select DoctorName,0900 from [TestDb].[dbo].[EveryDayAvailability where DoctorName=DName] ''', conn)
    df = pd.DataFrame(SQL_Query, columns=['DoctorName','0900'])
    print (df)

micseydel

Sep-06-2019, 06:40 PM
Replace line 14 with
SQL_Query = pd.read_sql_query('''select DoctorName,0900 from [TestDb].[dbo].[EveryDayAvailability where DoctorName={}] '''.format(DName), conn)

ndc85430

Sep-06-2019, 06:44 PM
No, one should never be writing queries by essentially joining strings as that's vulnerable to SQL injection. It looks like read_sql_query takes an argument, params that's used for passing parameters to the query (see the docs), so you should be using that.

metro17

Sep-07-2019, 12:28 AM
(Sep-06-2019, 06:40 PM)micseydel Wrote: [ -> ]Replace line 14 with
SQL_Query = pd.read_sql_query('''select DoctorName,0900 from [TestDb].[dbo].[EveryDayAvailability where DoctorName={}] '''.format(DName), conn)

It still gives me an error:
>>> def ada():
... import pyodbc
... import pandas as pd
... conn = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};'
... 'Server=usmlvv1sql400;'
... 'Database=TestDb;'
... 'Trusted_Connection=yes;')
... SQL_Query = pd.read_sql_query('''select distinct DoctorName,Department from [TestDb].[dbo].[EveryDayAvailability] ''', conn)
... df = pd.DataFrame(SQL_Query, columns=['DoctorName','Department'])
... print (df)
... DName = input("Please provide the name of the Doctor you would like to visit ?")
... print("Printed below are the list of appointments for this doctor ")
... SQL_Query = pd.read_sql_query('''select DoctorName,0900 from [TestDb].[dbo].[EveryDayAvailability where DoctorName={}] '''.format(DName), conn)
... df = pd.DataFrame(SQL_Query, columns=['DoctorName','0900'])
... print (df)
...
>>> ada()
DoctorName Department
0 John Nephrology
1 Sharon ENT
2 Steve Cardiology
Please provide the name of the Doctor you would like to visit ?John
Printed below are the list of appointments for this doctor
Traceback (most recent call last):
File "C:\Users\A\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\io\sql.py", line 1431, in execute
cur.execute(*args)
pyodbc.ProgrammingError: ('42S02', "[42S02] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'TestDb.dbo.EveryDayAvailability where DoctorName='John''. (208) (SQLExecDirectW)")

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "<stdin>", line 13, in ada
File "C:\Users\A\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\io\sql.py", line 314, in read_sql_query
parse_dates=parse_dates, chunksize=chunksize)
File "C:\Users\A\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\io\sql.py", line 1468, in read_query
cursor = self.execute(*args)
File "C:\Users\A\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\io\sql.py", line 1445, in execute
raise_with_traceback(ex)
File "C:\Users\A\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\compat\__init__.py", line 420, in raise_with_traceback
raise exc.with_traceback(traceback)
File "C:\Users\A\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\io\sql.py", line 1431, in execute
cur.execute(*args)
pandas.io.sql.DatabaseError: Execution failed on sql 'select DoctorName,0900 from [TestDb].[dbo].[EveryDayAvailability where DoctorName='John'] ': ('42S02', "[42S02] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'TestDb.dbo.EveryDayAvailability where DoctorName='John''. (208) (SQLExecDirectW)")

micseydel

Sep-07-2019, 01:16 AM
It's because it needs quotes, but as ndc85430 what I suggested is a bad (terrible) practice. Find an ORM or some library that does this kind of thing for you. (I would recommend one if I could, but I'd have to do research that you could just do yourself.)

metro17

Sep-09-2019, 05:24 AM
where do I need the quotes?
I still can't get it.Can you please be precise
Thanks

updated code:

def ada():
import pyodbc
import pandas as pd
conn = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};'
'Server=ABCD400;'
'Database=TestDb;'
'Trusted_Connection=yes;')
global DName
SQL_Query = pd.read_sql_query("select distinct DoctorName,Department from [TestDb].[dbo].[EveryDayAvailability] ", conn)
df = pd.DataFrame(SQL_Query, columns=['DoctorName','Department'])
print (df)
print("Printed is the list of Doctors by department ")
DName = input("Please provide the name of the Doctor you would like to visit ?")
SQL_Query = pd.read_sql_query("select DoctorName,0900 from [TestDb].[dbo].[EveryDayAvailability] where DoctorName={}".format("DName"), conn)
DoctorName=DName
df = pd.DataFrame(SQL_Query, columns=['DoctorName','0900'])
print (df)

>>> ada()
DoctorName Department
0 John Nephrology
1 Sharon ENT
2 Steve Cardiology
Printed is the list of Doctors by department
Please provide the name of the Doctor you would like to visit ?John
Traceback (most recent call last):
File "C:\Users\ABC123\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\io\sql.py", line 1431, in execute
cur.execute(*args)
pyodbc.ProgrammingError: ('42S22', "[42S22] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name 'DName'. (207) (SQLExecDirectW)")

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "<stdin>", line 14, in ada
File "C:\Users\ABC123\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\io\sql.py", line 314, in read_sql_query
parse_dates=parse_dates, chunksize=chunksize)
File "C:\Users\ABC123\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\io\sql.py", line 1468, in read_query
cursor = self.execute(*args)
File "C:\Users\ABC123\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\io\sql.py", line 1445, in execute
raise_with_traceback(ex)
File "C:\Users\ABC123\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\compat\__init__.py", line 420, in raise_with_traceback
raise exc.with_traceback(traceback)
File "C:\Users\ABC123\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\io\sql.py", line 1431, in execute
cur.execute(*args)
pandas.io.sql.DatabaseError: Execution failed on sql 'select DoctorName,0900 from [TestDb].[dbo].[EveryDayAvailability] where DoctorName=DName': ('42S22', "[42S22] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name 'DName'. (207) (SQLExecDirectW)")
>>> def ada():
... import pyodbc
... import pandas as pd
... conn = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};'
... 'Server=ABCD400;'
... 'Database=TestDb;'
... 'Trusted_Connection=yes;')
... global DName
... SQL_Query = pd.read_sql_query("select distinct DoctorName,Department from [TestDb].[dbo].[EveryDayAvailability] ", conn)
... df = pd.DataFrame(SQL_Query, columns=['DoctorName','Department'])
... print (df)
... print("Printed is the list of Doctors by department ")
... DName = input("Please provide the name of the Doctor you would like to visit ?")
... SQL_Query = pd.read_sql_query("select DoctorName,0900 from [TestDb].[dbo].[EveryDayAvailability] where DoctorName=s%" %(DName), conn)
... df = pd.DataFrame(SQL_Query, columns=['DoctorName','0900'])
... print (df)
...
>>> ada()
DoctorName Department
0 John Nephrology
1 Sharon ENT
2 Steve Cardiology
Printed is the list of Doctors by department
Please provide the name of the Doctor you would like to visit ?John
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "<stdin>", line 14, in ada
ValueError: incomplete format
Python Forum > Python Coding > General Coding Help > pass captured value from input() to where clause