CODEWITHSUNDEEP
pythonsql-serverpandas
tekstar
tekstar

Reputation: 51

passing string arguments to filter database rows in python

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

Upvotes: 0

Views: 1963

Answers (2)

Rob Bricheno
Rob Bricheno

Reputation: 4653

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

Upvotes: 1

Nishant Nawarkhede
Nishant Nawarkhede

Reputation: 8400

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

Upvotes: 1

Related Questions