Sqlite - Use backticks (`) or double quotes (") with python
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.)
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