sqlite3 double quote behavior

[Chris Angelico]

On Wed, 14 Dec 2022 at 00:30, Thomas Passin <list1 at tompassin.net> wrote:
>
> On 12/13/2022 4:09 AM, Chris Angelico wrote:
> > 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.
>
>  From reading the SQLite3 documentation on this issue (not from personal
> experience), in fact the second form is actually what one wants, even if
> SQLite3 will usually handle the first form correctly.

No, the two have distinct semantics. BOTH are valid, they just mean
different things.

ChrisA