Python: Calling queries from a file with variables

Question

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.

cs95 · Accepted Answer · 2018-05-18 05:45:46Z

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.

edited May 18, 2018 at 5:45

answered Jun 23, 2017 at 16:46

cs95

406k106 gold badges744 silver badges797 bronze badges

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

Comments

Avinash Rao · Accepted Answer · 2017-06-24 02:33:55Z

-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

edited Jun 24, 2017 at 2:33

answered Jun 23, 2017 at 16:49

Avinash Rao

594 bronze badges

2 Comments

Ilja Everilä Over a year ago

As with the other answer, never use string formatting with SQL queries to pass values. Always use your DB-API's or lib's placeholders.

Avinash Rao Over a year ago

Like name binding in oracle queries?

Collectives™ on Stack Overflow

Python: Calling queries from a file with variables

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related