Strings: double versus single quotes

[DL Neil]

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