On 2020-05-24 9:58 AM, DL Neil via Python-list wrote:
On 24/05/20 5:43 PM, Frank Millman wrote:
On 2020-05-23 9:45 PM, DL Neil via Python-list wrote:
My habit with SQL queries is to separate them from other code, cf the
usual illustration of having them 'buried' within the code,
immediately before, or even part of, the query call.
I like that idea, as I find that I am embedding more and more SQL in
my code.
How do you handle parameters? Do you leave placeholders ('?' or '%s')
in the query, and leave it to the 'importer' of the query to figure
out what is required?
Yes. Most "connector" software includes a feature which auto-magically
escapes all variable-data - a valuable safety feature!
I've been experimenting by going further and providing app.devs with
functions/methods, a mini-API if you will. Given that many?most don't
like having to deal with SQL, the extra 'insulation' boosts my personal
popularity...
(and I need as much of that as I can get!)
Ok. I will have to give it some thought.
I generate most of my SQL dynamically, constructing the query
programmatically using the meta-data in my system.
But now I am constructing some more complex queries, which I can't
generate automatically yet. I am hoping that a pattern emerges which I
can use to automate them, but for now I am doing it by hand.
There are a number of parameters required, and it will not be obvious at
first sight what values are required. If I am going to keep the queries
in a separate module, I think that I will have to provide some sort of
accompanying documentation with each query explaining what the required
parameters are.
Thinking aloud, I may set up a separate module for the queries, but make
each one a 'function', which specifies what data is required. The caller
calls the function with the data as an argument, and the function uses
it to build the parameter list and returns the SQL along with the
parameters. The function can also contain documentation explaining how
the query works.
As you say, this has the benefit of separating the SQL from the Python
code, so I will definitely pursue this idea.
Thanks
Frank
--
https://mail.python.org/mailman/listinfo/python-list
