String replacement in SQL Query using Python

Question

I have two queries in SQL which are the following:

q1 = select date_hour from table

And, the second query is:

q2 = select date(date_hour) from table

The only difference between these queries is the string date_hour and date(date_hour). SO, I tried parameterising my query in the following manner:

q1 = select %s from table
cur.execute(q1,'date')
cur.execute(q1,'date(date_hour)')

However, this throws an error which is:

not all arguments converted during string formatting

Why am I getting this error? How can I fix it?

kums · Accepted Answer · 2014-11-21 07:46:00Z

1

Change the comma in cur.execute to %

Change this:

q1 = "select %s from table"
cur.execute(q1,'date')
cur.execute(q1,'date(date_hour)')

to:

q1 = "select %s from table"
cur.execute(q1 % 'date')
cur.execute(q1 % 'date(date_hour)')

answered Nov 21, 2014 at 7:46

kums

2,7012 gold badges16 silver badges16 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 10:24:12Z

0

It's unclear wich sql library you're using but assuming it uses the Python DB API:

Sql parameters are typically used for values, not columns names (while this is possible using stored procedures).

It seems you're confused between string formatting in python and sql parametized queries.

While %s can be used to format a string (see formatting strings) this is not the way to set sql parameters.

See this response to use sql parameters in python.

By the way i can't see anything wrong with this simple code:

cursor=cnx.curor()
query="select date_hour from table"
cursor.execute(query)
query="select date(date_hour) from table"
cursor.execute(query)

edited May 23, 2017 at 10:24

CommunityBot

11 silver badge

answered Nov 20, 2014 at 14:54

Adrien

5531 gold badge5 silver badges7 bronze badges

2 Comments

Nisha Over a year ago

I don't want the same query to be written twice. The two queries are exactly same except for one difference.That is why, i want to parameterize them.

Adrien Over a year ago

As i said parameters are meant to be used for values not columns name. Still if you really insist on using the same string this should do the trick: cur.execute('select %s from table1' % 'date_hour')

Ayush · Accepted Answer · 2014-11-21 08:10:46Z

0

Change your code to something like this:

q1 = "select %s from table" cur.execute(q1,['date']) cur.execute(q1,['date(date_hour)'])

Check this

answered Nov 21, 2014 at 8:10

Ayush

9391 gold badge14 silver badges35 bronze badges

Collectives™ on Stack Overflow

String replacement in SQL Query using Python

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related