Strings: double versus single quotes

[DL Neil]

On 23/05/20 11:03 PM, Skip Montanaro wrote:
> I also agree about SQL. I found that something like this:
> 
> stmt = (
>      """select foo from bar"""
>      """  where a = 'bag'"""
>      """    and c = 'dog'"""
> )
> 
> worked pretty well, served to both satisfy my brain's desire for semantic
> indentation (you should see some of the SQL I inherited - yikes!) and
> maintain a visual distinction between Python and SQL quoting. (Consistently
> using triple quotes minimizes the chance of needing a stray Python
> backslash inside the SQL code.) I'm now retired, so can't double check, but
> I believe SQLite and MSSQL are unusual in their Pythonesque treatment of
> single and double quotes being synonymous. I believe most other dialects
> (Oracle, MySQL, Sybase, PostgreSQL that I checked) only recognize single
> quotes as string delimiters.


The older (and more professional?) RDBMS accept either/both as variable 
delimiters, per Python strings.

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.

This partly because (a) some dev.teams have a specific person handling 
DBA-tasks, and partly (b) in order to make it easy to 
find/correct/re-factor SQL code without it being mixed-in with the Python.

(a) using a separate and specific module for SQL constants (which may 
include queries as text, or prepared queries), means that the 'DBA' may 
develop independently of the Python devs; that integration of code 
happens in the VCS; that the Python code may separate (stub) or 
integrate during unit-testing, according to progress and choice.

(b) physical 'extraction' and separation make it easier to develop and 
test each component (SQL, Python, ...) separately - either work in SQL 
*or* in Python, and not have the extra 'load' of having to flip one's 
brain between the two; and by using a separate module, makes it easy to 
locate a 'class' of code dealing with particular data and/or 
carrying-out particular functions - much as you might for classes and/or 
libraries.

Oh, and a further benefit: (further to "inherited", above) it becomes 
easier to avoid the massive tangles caused by trying to mix the 
conventions for indenting multi-line SQL code, with those for Python!
-- 
Regards =dn