sqlite3 double quote behavior
Roel Schroeven
roel at roelschroeven.net
Tue Dec 13 09:15:36 EST 2022
Op 13/12/2022 om 14:23 schreef Thomas Passin:
> 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.
> The rule is "Use single quotes for string values and double quotes for
> database names such as schema, table and column names; for backwards
> compatibility SQLite will accept double quotes for string values, but
> you may get a surprise if the string value looks like a database name."
What I missed at first is the case where you really want to use an
identifier, not a string. Then you use double quotes, and would like to
get an error ("unknown identifier" or something like that) in case of a
typo, instead of the database engine silently presuming your
wrongly-spelled identifier is a string. That case can't be solved with
parametrized queries, and does really require the ability to enable more
strict behavior.
+1 to expose the sqlite3_db_config() function, or maybe just a special
case for this specific option.
--
"Honest criticism is hard to take, particularly from a relative, a friend,
an acquaintance, or a stranger."
-- Franklin P. Jones
More information about the Python-list
mailing list