passing string arguments to filter database rows in python

Question

i have a written the below function to filter a column in a sql query, the function takes a string argument which will be inputted in the 'where clause'

def summaryTable(machineid):
 df=pd.read_sql(""" SELECT fld_ATM  FROM [003_tbl_ATM_Tables] 
    WHERE (LINK <> 1) AND (fld_ATM =('machineid')) ;
     """,connection)
connection.close()
return df

the function returns an empty Dataframe. i know the query itself is correct 'cause i get the expected data when i 'hardcode' the machine id

'machineid' seems to be part of the string as a word, not a variable. You should use a formatter (see pyformat.info) to pass the machineid as a string-variable into your query — offeltoffel
– offeltoffel, Commented May 22, 2018 at 13:01
@offeltoffel No, they should not use string formatting for passing variables to SQL queries. It's a habit that will bite in the long run (read about SQL injection). — Ilja Everilä
– Ilja Everilä, Commented May 22, 2018 at 13:05
Could you please include the definition of connection, or in other words what DB-API driver you are using, if not using SQLAlchemy. — Ilja Everilä
– Ilja Everilä, Commented May 22, 2018 at 13:11
Hi Ilja. i am using - connection=pyodbc.connect() and passing the normal parameters ie server details etc — tekstar
– tekstar, Commented May 22, 2018 at 13:24

Rob Bricheno · Accepted Answer · 2018-05-22 13:33:21Z

2

Use params to pass a tuple of parameters including machineid to read_sql. pyodbc replaces the ? character in your query with parameters from the tuple, in order. Their values will be safely substituted at runtime. This avoids dangerous string formatting issues which may result in SQL injection.

df = pd.read_sql(""" SELECT fld_ATM  FROM [003_tbl_ATM_Tables] 
                     WHERE (LINK <> 1) AND (fld_ATM = ?) ;
                 """, connection, params=(machineid,))

edited May 22, 2018 at 13:33

answered May 22, 2018 at 13:12

Rob Bricheno

4,66317 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

tekstar Over a year ago

this doesn't work , i get the following error ('The SQL contains 0 parameter markers, but 1 parameters were supplied', 'HY000')

Ilja Everilä Over a year ago

:name style placeholders aren't widely supported by DB-API drivers, and pyodbc is no exception. It's more of an SQLAlchemy thing.

Rob Bricheno Over a year ago

Fixed to avoid use of named parameters.

Ilja Everilä · Accepted Answer · 2018-05-22 13:25:00Z

1

You need to add machineid to query using params.

# ? is the placeholder style used by pyodbc. Some use %s, for example.
query = """ SELECT fld_ATM  FROM [003_tbl_ATM_Tables] 
        WHERE (LINK <> 1) AND (fld_ATM = ?) ;
         """
data_df = pd.read_sql_query(query, engine, params=(machineid, ))

edited May 22, 2018 at 13:25

Ilja Everilä

53.4k9 gold badges138 silver badges142 bronze badges

answered May 22, 2018 at 12:59

Nishant Nawarkhede

8,40813 gold badges61 silver badges83 bronze badges

Collectives™ on Stack Overflow

passing string arguments to filter database rows in python

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related