2

I have a few queries in my python script which also use variables as parameters to retrieve data. '''SELECT * FROM order_projections_daily WHERE days >= ''' + each + '''AND days < ''' + next_monday

How can I store queries like this in a separate file and call it directly from there rather than cramming them in the code?

I have tried storing the queries in a file and calling them as a string but it doesn't work with variables. it works with: '''SELECT * FROM order_projections_daily'''

This is a very simple query, I am using much more complicated queries in the real case.

Braiam
  • 1
  • 11
  • 47
  • 78
Keshav Malpani
  • 113
  • 1
  • 5

2 Answers2

5

Use parameterised strings—

'''SELECT * FROM order_projections_daily WHERE days >= %(start)s AND days < %(end)s'''

Later, when executing the query, build a params dict like this:

params = {'start': ..., 'end': ...}

These params should then be passed to the DBC driver function which will take care of inserting the parameters:

cursor.execute(query, params)

Note: Do not inject format strings into your query, you're liable to SQL injection that way.

cs95
  • 379,657
  • 97
  • 704
  • 746
-5

Use python string formatting.

In your separate file save query as below e.g:

query = "select * from my_persons where name = {name} and age = {age}"

In python file, format the query this way:

sql = query.format(name="jack", age=27)

You can save all your queries in a separate python file as string values and import all of them into your code anywhere. In my eg I am assuming query is saved in a separate python file.

Formatting your query

query='''SELECT * FROM order_projections_daily WHERE days >={each} AND days < {next_monday}

Format it as:

sql = query.format(each=each, next_monday=next_monday)

It's a good practice to use format method or % or maybe even join rather than using string concatenation as string concatenation creates intermediate string objects.

Ok so formatting is a bad idea then, have a look at this. Name binding in oracle https://stackoverflow.com/a/33882805/2196290

Avinash Rao
  • 59
  • 4