Problem adding quotes to SQL inside Python

Question

I want to add quotes i.e. '' around my input_date in the following sql query.

sql = "select num_store, sum(gross_sales) as gross_sales from dtm.sales where day = " + input_date + " and num_store = '" + store + "' GROUP BY num_store"

The dates I am querying on are of type ANSIDATE. I want to add the '' around the input date as cleanly as possible so that it would still detect the fields.

The query that I am running directly on the vectorwise database using SQuirrel SQL CLient to verify is like so:

SELECT num_store, sum(gross_sales) as gross_sales FROM dtm.sales WHERE day = '2019-06-21' AND num_store = 69 GROUP BY num_store

What version of SQL are you using?

Tim Biegeleisen
– Tim Biegeleisen

2019-07-23 09:19:10 +00:00
Commented Jul 23, 2019 at 9:19 — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 23, 2019 at 9:19
The version I am using is sqlite3

S.Jackson
– S.Jackson

2019-07-23 09:23:54 +00:00
Commented Jul 23, 2019 at 9:23 — S.Jackson
– S.Jackson, Commented Jul 23, 2019 at 9:23

Tim Biegeleisen · Accepted Answer · 2019-07-23 09:32:47Z

2

You should be using a prepared statement:

import sqlite3

# obtain connection and cursor
sql = """select num_store, sum(gross_sales) as gross_sales
         from dtm.sales 
         where day = ? and num_store = ?
         group by num_store"""
params = (input_date, store,)
c.execute(sql, params)
# c.fetchall()

Using a prepared statement frees you from worrying about how to escape the literal values in your query.

answered Jul 23, 2019 at 9:32

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

1 Comment

S.Jackson Over a year ago

Thanks ! But I need the sql request to stay the same for the sake of the whole project and the behavior of the django-rest-framework api I am developing

noobjs · Accepted Answer · 2019-07-23 11:19:53Z

1

query = """ select num_store, sum(gross_sales) as gross_sales from 
 dtm.sales where day = {input_date} and num_store = {input_store} GROUP BY 
 num_store """

dict = new Dict()
dict['input_date'] = datetime.strptime(value, '%Y-%m-%d ')
dict['input_store'] = value
query.format(**dict)

edited Jul 23, 2019 at 11:19

answered Jul 23, 2019 at 9:26

noobjs

615 bronze badges

8 Comments

S.Jackson Over a year ago

It didn't work ! How to apply what you suggested to the first query which is inside Python not the second one that is being used to test directly against the database ?

S.Jackson Over a year ago

what import to use for dict ?

noobjs Over a year ago

@S.Jackson You dont need to import dictionary it is in standard lib

S.Jackson Over a year ago

I used it and I get this error AttributeError: 'str' object has no attribute 'strftime'

S.Jackson Over a year ago

My date is only YYYY-MM-DD

|

Jean-Baptiste Graille · Accepted Answer · 2019-07-23 09:35:07Z

1

If I've understood your problem, you'll need to convert this 'ANSIDATE' in a string. Your ANSIDATE follows this pattern '%Y-%m-%d' (yyyy-mm-dd) and you could do:

sql = "select num_store, sum(gross_sales) as gross_sales from dtm.sales where day = " + input_date.strftime('%Y-%m-%d') + ...

answered Jul 23, 2019 at 9:35

Jean-Baptiste Graille

1174 bronze badges

2 Comments

S.Jackson Over a year ago

from where do you get .strftime ?? is there an import i should make ?

Jean-Baptiste Graille Over a year ago

import datetime

Collectives™ on Stack Overflow

Problem adding quotes to SQL inside Python

3 Answers 3

1 Comment

8 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

8 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related