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 = '**[email protected]'
  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 python.org/dev/peps/pep-0263 for details — Fasih Ahmed
– Fasih Ahmed, Commented Sep 6, 2017 at 5:50
Hi I am facing still this error - "pandas.io.sql.DatabaseError: Execution failed on sql 'SELECT * FROM ? where ?=?': near "?": syntax error" — Fasih Ahmed
– Fasih Ahmed, Commented Sep 7, 2017 at 4:14

PRMoureu · Accepted Answer · 2017-09-07 05:13:28Z

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 7, 2017 at 5:13

answered Sep 6, 2017 at 5:54

PRMoureu

13.4k6 gold badges46 silver badges52 bronze badges

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

5 Comments

Fasih Ahmed Over a year ago

Hi PRMoureu, I am facing this error "pandas.io.sql.DatabaseError: Execution failed on sql 'SELECT * FROM ? where ?=?': near "?": syntax error"

Fasih Ahmed Over a year ago

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

PRMoureu Over a year ago

nice, sorry for the first answer, i didnt pay attention for the table blocking

Fasih Ahmed Over a year ago

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

Martijn Pieters Over a year ago

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.

Collectives™ on Stack Overflow

how to insert variables in read_sql_query using python

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related