Storing SQL statements in a properties file to be used by Python scripts?
I don’t know how best to frame this question, but I will try.
I have several python scripts that are executed via crontab. These scripts perform some database operations such as selects and updates (on different databases) and some use the same SQL statements. Anyway, I have my SQL queries that are being used are stored in a static dictionary in the util.py file and I import it whenever I need it from different scripts, however I was wondering which are the best methods for storing static SQL statements to use different scripts? Reading is important, so I need some kind of file format that I can indent SQL queries correctly.
source to share
2 options.
ConfigParser
I would use the good old INI / ConfigParser format. They used to be a lot of them, but still more for json, except that json is not suitable for sql.
Here 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 innie. Note that you need to indent at least one space on each subsequent line after select =, otherwise configparser will try to parse other lines rather than group them with select.
[sql]
select=select *
from customers
where custid = %(custid)s
select2=<some other stuff>
And the printout:
:select *:
:from customers:
:where custid = %(custid)s:
pros: this is not a code file, so in theory you could let someone customize sql. in theory.
cons: ini-based sql has a fair bit of overhead to fetch variables, and since you want to add more functionality, you end up doing a lot of workarounds.
simple old 3-line variables in a module
Another alternative is just the Python module and using three quoted strings. I usually used this for sqls and not ini these days.
pros: simple.
minus: only Python and invalid syntax typo will stop your script invocation.
say this is sql_test_so08.py
select = """
select *
from customers
where custid = %(custid)s
"""
select2 = """<some other stuff>"""
in script:
import sql_test_so08
print sql_test_so08.select
One of the benefits is that I can use Template / $ var replacements to replace in application constants that I import from other sources, rather than hard-code them into sql. Let's say you have invalid_customer_unpaid flag set to 3.
in sql.py files you would 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 direct variable substitution as shown here. Too much risk from SQL injection. But you can apply the same idea using the RDBMS binding parameter support.
source to share