CODEWITHSUNDEEP
pythonsqlformattingformat
S.Jackson
S.Jackson

Reputation: 159

Problem adding quotes to SQL inside Python

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

Upvotes: 0

Views: 122

Answers (3)

noobjs
noobjs

Reputation: 61

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)

Upvotes: 1

Jean-Baptiste Graille
Jean-Baptiste Graille

Reputation: 107

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') + ...

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

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.

Upvotes: 2

Related Questions