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?

score 1 · Accepted Answer · answered Nov 21 '14 at 07:46

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 '14 at 07:46

kums

2,661
2
13
16

score 0 · Answer 2 · edited May 23 '17 at 10:24

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 '17 at 10:24

Community

1
1

answered Nov 20 '14 at 14:54

Adrien

543
1
5
7

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. – Nisha Nov 20 '14 at 15:02
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')` – Adrien Nov 20 '14 at 15:41

score 0 · Answer 3 · answered Nov 21 '14 at 08:10

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 '14 at 08:10

Ayush

909
1
13
32

String replacement in SQL Query using Python

3 Answers3