pass captured value from input() to where clause - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: General Coding Help (https://python-forum.io/forum-8.html) +--- Thread: pass captured value from input() to where clause (/thread-20913.html) |
pass captured value from input() to where clause - metro17 - Sep-06-2019 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) RE: pass captured value from input() to where clause - micseydel - Sep-06-2019 Replace line 14 with SQL_Query = pd.read_sql_query('''select DoctorName,0900 from [TestDb].[dbo].[EveryDayAvailability where DoctorName={}] '''.format(DName), conn) RE: pass captured value from input() to where clause - ndc85430 - Sep-06-2019 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.
RE: pass captured value from input() to where clause - metro17 - Sep-07-2019 (Sep-06-2019, 06:40 PM)micseydel Wrote: Replace line 14 with 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)") RE: pass captured value from input() to where clause - micseydel - Sep-07-2019 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.) RE: pass captured value from input() to where clause - metro17 - Sep-09-2019 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 |