12
votes

I saw a similar question in Stack Overflow pertaining to Android, but I was wondering whether I should use backticks (`) or double quotes (") - using Python - to select table names or rowid or what have you.

I tried single quotes - like this select 'rowid', * from 'tbl' order by 'rowid'. The single quotes worked in some cases but not all. I learned to use double quotes or backticks, and I was looking at SQLite Database Browser and I noticed that it used backticks.

I really like to put double quotes around my strings in Python because I'm coming from Java, so it is natural to do cursor.execute("select 'rowid',* from 'table';"), and it would be just as easy to do backticks (the double quotes would require a backslash and make the query look a little confusing). However, I just wanted to make sure that the backticks are portable (all versions of Windows, Linux, Os x, etc.).

2
@Martijn Pieters The thing is, is I actually got an incorrect output when I used single quotes. - dylnmc
Note that you cannot use single quotes here, single quotes are for literal string values only: sqlite.org/lang_keywords.html - Martijn Pieters♦
Hm. For some reason - perhaps you can explain - when I did "select 'rowid', * from table;" I got ('rowid', name,...). As laalto just posted - "Single quotes are for string literals, not identifiers." - dylnmc
Sorry, had my terms mixed up there. You need to use double quotes for identifiers. Or backticks. Or square brackets. - Martijn Pieters♦
Square brackets work in sqlite... I didn't know that. Thanks guys. - dylnmc

2 Answers

31
votes

The SQL standard says that strings must use 'single quotes', and identifiers (such as table and column names), when quoted, must use "double quotes".

For compatibility with MySQL, SQLite also allows to use single quotes for identifiers and double quotes for strings, but only when the context makes the meaning unambiguous. (In SELECT 'rowid' ..., a string is allowed, so a string is what you get.) If possible, always use the standard SQL quotes.

For compatibility with MySQL, SQLite also allows `backticks` for identifiers.

For compatibility with Microsft databases, SQLite also allows [brackets] for identifiers.

(This works in all SQLite versions.)

4
votes

Prefer double quotes for quoting identifiers such as column or table names. It's the SQL standard.

Backticks also work but they're only supported for MySQL syntax compatibility.

Single quotes are for string literals, not identifiers. That's why you'll get the literal value when using them.

Further reading: http://www.sqlite.org/lang_keywords.html