0

I'm trying to run the following code in Python to check various columns in SQL for entries containing the @ symbol:

out = pd.read_sql_query('select count(*) from [A].[' + str(Table1) + '] where [' + str(Column1) + '] not like '%@%'', cnxn)

The script has a loop which changes the Table1 and Column1 each time it runs. But the "not like '%@%''" part is causing issues. How do I correct this?

When I run it, I get the error

SyntaxError: invalid syntax

Karen
  • 39
  • 5
  • Try to escape the `'` around the `%@` – Bsquare ℬℬ Jan 08 '19 at 13:27
  • Relevant [binding-list-to-params-in-pandas-read-sql-query-with-other-params](https://stackoverflow.com/questions/36840438/binding-list-to-params-in-pandas-read-sql-query-with-other-params) – stovfl Jan 08 '19 at 14:25

2 Answers2

1

Classic example to use parameterization to avoid quote escaping or enclosures in SQL. Parameterization is supported in pandas' read_sql using params argument. And because you require dynamic table and column identifiers, use str.format for these items since only literal values can be parameterized but use parameterziation in LIKE clause.

Below assumes your DB-API such as sqlite3 or pyodbc uses qmark, ?, placeholders. Otherwise such as pymysql, psycopg2, etc. use %s or named parameters:

query = '''select count(*) 
           from [A].[{tbl}]
           where [{col}] not like ?
        '''
# NOTE USE OF ONE-ITEM TUPLE
out = pd.read_sql(query.format(tbl=str(Table1), col=str(Column1)), cnxn, params=('%@%',))
Parfait
  • 104,375
  • 17
  • 94
  • 125
0

That's not a standard way to build your read_sql_query method, you can try something like this:

query = 'select count(*) from ? where ? not like ?'
out = pd.read_sql_query(query, cnxn, params=(table_name, column_name, expression))
Rafiul Sabbir
  • 626
  • 6
  • 21
  • This gives the error: : Execution failed on sql 'select count(*) from ? where ? not like ?' – Karen Jan 08 '19 at 14:23
  • Can you please provide the full code snippet? It will help me to understand the problem more better. – Rafiul Sabbir Jan 08 '19 at 14:25
  • query = 'select count(*) from ? where ? not like ?' out = pd.read_sql_query(query, cnxn, params=(str(dfb1), str(dfb2), str('%@%'))) – Karen Jan 08 '19 at 14:28
  • Let us assume, the table name is 'user_table' and the column name is 'email'. so the code should be table_name = 'user_table' column_name = 'email' expression = '\'%@%\'' out = pd.read_sql_query(query, cnxn, params=(table_name, column_name, expression) Try with this and let me know the result – Rafiul Sabbir Jan 08 '19 at 14:38