There is a Python adapter for SQLITE called "APSW". It has a config()
function. I looked in the codebase and it defines the two configuration
constants needed to turn off the double quote behavior (see
https://sqlite.org/quirks.html). These constants are
SQLITE_DBCONFIG_DQS_DDL and SQLITE_DBCONFIG_DQS_DML.
This makes me think that the double-quote behavior can be turned off by
Python code, though I haven't tried it.
From the APSW docs (see
https://rogerbinns.github.io/apsw/tips.html#about-python-apsw-and-sqlite-versions):
"APSW wraps the SQLite C API. That means when SQLite adds new constant
or API, then so does APSW. You can think of APSW as the Python
expression of SQLite’s C API. You can lookup SQLite APIs to find which
APSW functions and attributes call them."
On 12/15/2022 2:18 PM, John K. Parejko wrote:
Thanks for the discussion. I’m aware that SQLite has several different options
for identifier quoting, but they’re not cross-compatible with other SQL,
whereas double quotes are (modulo this strange SQLite behavior).
Is anyone here familiar with the python sqlite3 implementation? I wonder how
hard it would be to raise up the `sqlite3_db_config` generically, or have a
specific function to set just the DQS_DDL and DQS_DML settings? It looks like
everything interesting is in `Modules/_sqlite/module.c`, but I’m not familiar
with the cpython internals.
John
On 13Dec 2022, at 13:58, Chris Angelico <ros...@gmail.com> wrote:
On Wed, 14 Dec 2022 at 08:19, Roel Schroeven <r...@roelschroeven.net> wrote:
Chris Angelico schreef op 13/12/2022 om 20:01:
On Wed, 14 Dec 2022 at 06:00, Roel Schroeven <r...@roelschroeven.net> wrote:
Stefan Ram schreef op 13/12/2022 om 8:42:
"John K. Parejko" <parej...@gmail.com> writes:
I was just burned by this, where some tests I’d written
against an sqlite database did not fail in the way that they
“should” have, because of this double-quoted string issue.
In standard SQL, double quotes denote identifiers that are
allowed to contain special characters.
Or that are equal SQL keywords, which can be a reason to double-quote
them. SQL engines sometimes add new keywords; explicitly marking string
literals as string literals prevents future conflicts and confusion.
Perhaps it's a better idea to use [identifier] or `identifier` instead
though (I just learned about those on
https://sqlite.org/lang_keywords.html). Both are not standard SQL ([] is
used in MS Access and SQL Server, `` is used in MySQL) but both work in
SQLite. That should prevent any ambiguity and confusion, if it doesn't
bother you too much that it's not standard SQL.
Why not just use "identifier" which is standard SQL?
If you accidentally type [identifire] or `identifire`, SQLite will
produce an unknown identifier error, alerting you immediately to your typo.
If you accidentally type "identifire", SQLite will silently treat it as
a string literal instead of an identifier, causing more difficult to
diagnose problems.
Okay, so..... exactly the same as if you use standard double quotes,
but change the configuration option. So the options are: make
everything worse for everyone by exacerbating the problem of
non-standard identifier quoting, or get this API so SQLite can be
configured, like the OP actually asked for.
Yeah. Let's not do the wrong thing.
ChrisA
--
https://mail.python.org/mailman/listinfo/python-list
--
https://mail.python.org/mailman/listinfo/python-list
