how to insert variables in read_sql_query using python

Question

I am trying to retrieve data from sqlite3 with the help of variables. It is working fine with execute() statement but i would like to retrieve columns also and for that purpose i am using read_sql_query() but i am unable to pass variables in read_sql_query(), please follow below code:

def cal():
  tab = ['LCOLOutput']
  column_name = 'CUSTOMER_EMAIL_ID'
  xyz = '**AVarma1@ra.rockwell.com'
  for index, m in enumerate(tab):
      table_name = m
      sq = "SELECT * FROM ? where ?=?;" , (table_name, column_name, xyz,)
      df = pandas.read_sql_query(sq,conn)
      writer = 
      pandas.ExcelWriter('D:\pandas_simple.xlsx',engine='xlsxwriter')
      df.to_excel(writer, sheet_name='Sheet1')
      writer.save()

I am facing this error-SyntaxError: Non-ASCII character '\xc2' in file D:/Automation/TicketAuditDefects/exp.py on line 90, but no encoding declared; see http://python.org/dev/peps/pep-0263/ for details — Fasih Ahmed, Sep 06 '17 at 05:50
Hi I am facing still this error - "pandas.io.sql.DatabaseError: Execution failed on sql 'SELECT * FROM ? where ?=?': near "?": syntax error" — Fasih Ahmed, Sep 07 '17 at 04:14

PRMoureu · Answer 1 · 2017-09-07T05:13:28.103

4

You need to change the syntax with the method read_sql_query() from pandas, check the doc.

For sqlite, it should work with :

sq = "SELECT * FROM ? where ?=?;" 
param = (table_name, column_name, xyz,)
df = pandas.read_sql_query(sq,conn, params=param)

EDIT : otherwise try with the following formatting for the table :

sq = "SELECT * FROM {} where ?=?;".format(table_name)
param = (column_name, xyz,)
df = pandas.read_sql_query(sq,conn, params=param)

Check this answer explaining why table cannot be passed as parameter directly.

edited Sep 07 '17 at 05:13

answered Sep 06 '17 at 05:54

PRMoureu

12,817
6
38
48

Hi PRMoureu, I am facing this error "pandas.io.sql.DatabaseError: Execution failed on sql 'SELECT * FROM ? where ?=?': near "?": syntax error" – Fasih Ahmed Sep 07 '17 at 04:16
1

Hi PRMoureu, This is working fine-" sq = "SELECT * FROM {} where {}=?;".format(table_name,column_name) param = (xyz,) df = pandas.read_sql_query(sq, conn, params=param)" – Fasih Ahmed Sep 07 '17 at 06:12
nice, sorry for the first answer, i didnt pay attention for the table blocking – PRMoureu Sep 07 '17 at 06:15
no need of sorry dude atleast you tried to answer my question, for me it is a big thing, i thought my question will drown some where in sea – Fasih Ahmed Sep 07 '17 at 08:27
You'll also have to treat the column name as an object. Paramaters are *explicitly only for values*, because the database driver will make sure to escape the notation such that it can't ever be mistaken for an object name (table, column name, index, etc.) or SQL syntax. – Martijn Pieters Jan 15 '19 at 12:32

how to insert variables in read_sql_query using python

1 Answers1