sqlite3 double quote behavior

[Chris Angelico]

On Tue, 13 Dec 2022 at 19:52, Roel Schroeven <roel at roelschroeven.net> wrote:
> Like Lars Liedtke this is not an exact answer to your question, but you
> can side-step the issue by using parametrized queries, i.e. instead of
>
>      cur.execute('SELECT name, location FROM persons WHERE name = "John
> Doe"')
>
> do
>
>      cur.execute('SELECT name, location FROM persons WHERE name = ?',
> ('John Doe',))
>

That's the wrong behaviour though. According to the SQL standard, the
second query should be equivalent to this:

cur.execute("SELECT name, location FROM persons WHERE name = 'John Doe'")

What the OP wanted was like your first query, and proper DBMSes like
PostgreSQL will handle it accordingly. The question is how to get
SQLite3 to also do so.

I don't use SQLite3 much so I'm not really one to judge, but maybe it
would be worth exposing the sqlite3_db_config() function to Python?
Yes, it would be more than a trivial change, but it should be
reasonably straight-forward. In order to be useful, it would probably
also need an associated IntEnum for all those lovely opaque numbers
that define the verbs.

ChrisA