On 24/05/20 8:41 PM, Frank Millman wrote:
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.
We have been talking (slightly OT for thread - apologies) about the
narrow sub-objectives of transferring data between a Python application
and an RDBMS. May I advise consideration of the wider specification?
For example, one may find it helpful to use a library/abstraction such
as SQLAlchemy. Such facilitates transaction data being taken directly
from/returned to a Python class! Whereas this discussion (above) only
returns raw data-items, thus necessitating the application programmers
coding an appropriate 'getter' to provide data to the RDBMS
interface-functions, and/or a 'setter' to absorb query-results into the
application's data-class(es)!
Of course, there are plenty of applications where one might eschew such
advantages, eg a simple interface, and at the other end of the scale: if
the data were to be formatted into a pandas data-frame.
Horses for courses!
--
Regards =dn
--
https://mail.python.org/mailman/listinfo/python-list
