Storing SQL statements in a properties file to be used by Python scripts?

Question

I don't know how best to word this question but I'll try.

I have a couple of python scripts that are executed through crontab. These scripts do some database operations such as selects and updates (on different databases) and some use the same SQL statements. As it stands I have my SQL queries that are used, stored in a static dictionary in a util.py file and I import it whenever I need it from the different scripts, however I was wondering what was best practices for storing static SQL statements to be used by different scripts? Readability is important so I would need some file format that I can properly indent the SQL queries.

Answer 1

3 options.

(note: things like select * from t where custid = %(custid)s below are fed to postgresql parametrized queries later. Use your RDBMS's parametrized query syntax, NEVER build query strings manually because sql injection without an extremely good reason. Even then limit that to configuration that comes from the app itself - external "data source files" sitting on file systems somewhere may be corrupted by bad actors and can then hitchhike on your app credentials).

ConfigParser

I would use the good old INI/ConfigParser format. Used them a lot before, but tending more to json now, except that json is not great for sql.

Here's the Python

import ConfigParser
cfp = ConfigParser.ConfigParser()
cfp.read("test_so08.ini")
myselect = cfp.get("sql", "select", raw=True)
for line in myselect.split("\n"):
    print ":%s:" % (line)

And the ini. Note that you need to indent by at least one blank on each subsequent line after the select=, otherwise configparser will try to parse the other lines rather than grouping them with select.

[sql]
select=select *
  from customers
  where custid = %(custid)s
select2=<some other stuff>

And the print out:

:select *:
:from customers:
:where custid = %(custid)s:

pros: it's not a code file so theoretically you could let anyone configure the sql. theoretically.

cons: ini-based sql has a fair bit of overhead to fetch the variables and as you want to add more features you end up doing a lot of workarounds.

plain old triple-string variables in module:

Another alternative is to just a Python module and use triple-quoted strings. I've tended to use that for sqls, rather than ini these days.

pros: simple.
cons: Python only and an invalid syntax typo will stop your calling script.

say this is sql_test_so08.py

select = """
  select *
  from customers
  where custid = %(custid)s
  """

select2 = """<some other stuff>"""

in your script:

import sql_test_so08
print sql_test_so08.select

One advantage is that I can use Template/$var substitutions to substitute in application constants that I import from elsewhere, rather than hardcoding them in the sql. Say you have an invalid_customer_unpaid flag set to 3.

in the sql.py files you would something like:

from constants import invalid_customer_unpaid

di_constants = dict(invalid_customer_unpaid=invalid_customer_unpaid)

select_bad="""select... where ... $invalid_customer_unpaid"""

select_bad = Template(select_bad).substitute(di_constants)

Note: I rarely use straight variable substitution, as shown here. Too much risk from SQL injection. But you can apply the same idea using your RDBMS's bind parameter support.

YAML:

Doing a lot more YAML these days.

• JSON for machine-written and machine-read.
• YAML for hand-written, machine-read, as it is much better at supporting quotes and formatting than JSON.

Given test.yaml:

sql:
  select: select * from customers where custid = %(custid)s
  text: 'some text with ''abc'' and "xyz" '   

Then

from yaml import safe_load as yload

with open("test.yaml") as fi:
    di2 = yload(fi)

print (di2["sql"]["select"])

will output: select * from customers where custid = %(custid)s