CODEWITHSUNDEEP
pythonsqlsqlite
SpoonMeiser
SpoonMeiser

Reputation: 20417

Is it possible to stop sqlite treating double quoted identifiers as strings?

According to: http://www.sqlite.org/draft/lang_keywords.html

SQLite3 will do what you expect if you do:

select "foo" from bar;

But, if the identifier doesn't exist:

select "non-existant" from bar;

It falls back (for compatibility with old versions I guess) to treating the quoted text as a string.

This causes problems for me, as I'm dynamically creating queries using quoted columns like this, and the latter behaviour returns nonsense results instead of throwing an error.

I'm writing python code, and using a module that wraps a PEP-249 Python Database API Specification v2.0 module, so I can put in database specific hacks where necessary.

Our backend database may change (and indeed, will probably be different for local testing and production at some point), so I want to keep the SQL itself standard if possible.

Is there any way that I can either:

Upvotes: 4

Views: 263

Answers (2)

CL.
CL.

Reputation: 180040

If the column names are prefixed with table names or aliases, they cannot be misinterpreted:

SELECT bar."foo", a."baz" FROM bar, blah AS a

When you handle multiple tables, it's likely that you need to use this anyway to avoid column name conflicts.

Upvotes: 2

Jon Clements
Jon Clements

Reputation: 142136

Don't use quotes, use [ and ]

sqlite> create table blah (name text);
sqlite> select "something" from blah;
sqlite> select "name" from blah;
sqlite> insert into blah values ('hello');
sqlite> select "something" from blah;
something
sqlite> select "name" from blah;
hello
sqlite> select [something] from blah;
Error: no such column: something
sqlite> select [name] from blah;
hello
sqlite>

Attempt to programmatically kludge:

import re
from itertools import cycle

s = 'select "something" from blah;'
sel, cols, rest = re.match(r'(select)\s+(.*?)\s+(from\s+.*)', s).groups()
cols = re.sub('"', lambda g, I=cycle('[]'): next(I), cols)
print ' '.join( (sel, cols, rest) )
# select [something] from blah;

Upvotes: 1

Related Questions