Strings: double versus single quotes
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
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!