What SQLite Column Name Can Be/Cannot Be

SQLite table and column name requirements

If you use brackets or quotes you can use any name and there is no restriction :

create table [--This is a_valid.table+name!?] (x int);

But table names that don't have brackets around them should be any alphanumeric combination that doesn't start with a digit and does not contain any spaces.

You can use underline and $ but you can not use symbols like: + - ? ! * @ % ^ & # = / \ : " '

Sqlite3 Python: can't use limit as column name

"limit" is an SQL keyword, for example, as in

SELECT foo 
  FROM bar
  LIMIT 10;

If you want to use "limit" as a column name in sqlite, it needs to be quoted, in one of these ways:

• 'limit'
• "limit"
• [limit]
• `limit`

So for example, your statement could be

        cur.execute("""CREATE TABLE categories (
            priority text,
            name text,
            type text,
            increment text,
            total real,
            "limit" real)""")

Note that it must be quoted in other statements too, for example

"""INSERT INTO categories ("limit") VALUES (?);"""

What are valid table names in SQLite?

I haven't found a reference for it, but table names that are valid without using brackets around them should be any alphanumeric combination that doesn't start with a digit:

abc123 - valid
123abc - not valid
abc_123 - valid
_123abc - valid
abc-abc - not valid (looks like an expression)
abc.abc - not valid (looks like a database.table notation)

With brackets you should be able to use pretty much anything as a table name:

[This should-be a_valid.table+name!?]

SQLite column in table has same name as table

This is not about the table/column name: having a table column with the same name as the table it belongs to is unambiguous in SQL, since the syntax is clear enough about where a column name or a table name is expected.

Table country is not part of the from clause of your query, hence it is not available in the select clause.

You need to join that table, presumably with the incountry table:

inner join country on country.cid = incountry.cid

Then you can add country.country to the select clause.

SQLite, LIKE column name

You can use pragma_table_info() with the table's name:

SELECT name 
FROM pragma_table_info('tablename')
WHERE name LIKE 'thing%'

You can use this query:

SELECT GROUP_CONCAT(name) AS columns 
FROM pragma_table_info('tablename')
WHERE name LIKE 'thing%'

which returns only 1 column columns with a string value like 'thing_column1,thing_column2,thing_column3' and you can use it to construct a SELECT statement in your application.

---

Related Topics