What Are Valid Table Names in Sqlite

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 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: + - ? ! * @ % ^ & # = / \ : " '

Are there specific valid table names in Sqlite

Yes table is a keyword in sqlite. You can check the keywords here. Also sqlite keywords are not case sensitive so typing "table" or any other keyword either way will mean the same thing.

SQLite - display table names finishing by _1

This is not how the ESCAPE clause works. To search for an underscore, you must escape the underscore with the escape character:

LIKE '%#_1' ESCAPE '#'

Anyway, .tables is not an SQL command and ignores the ESCAPE clause. To do your own search, you have to run your own query:

SELECT name
FROM sqlite_master
WHERE type = 'table'
  AND name LIKE '%#_1' ESCAPE '#';

How can I list the tables in a SQLite database file that was opened with ATTACH?

The .tables, and .schema "helper" functions don't look into ATTACHed databases: they just query the SQLITE_MASTER table for the "main" database. Consequently, if you used

ATTACH some_file.db AS my_db;

then you need to do

SELECT name FROM my_db.sqlite_master WHERE type='table';

Note that temporary tables don't show up with .tables either: you have to list sqlite_temp_master for that:

SELECT name FROM sqlite_temp_master WHERE type='table';

SQLite creating table name using a variable

datetime.date.today() will return a datetime.date object which you must convert to a string, but even then a string like 2022-03-05 is not a valid name for SQLite.

You must enclose it between square brackets or backticks or double quotes.

Try this:

date_object = datetime.date.today()
sqlite_create_transfer_table = f"""CREATE TABLE IF NOT EXISTS [%s](
                                sender TEXT NOT NULL,
                                recipient TEXT NOT NULL,
                                ID text NOT NULL,
                                Size NOT NULL,
                                Colour NOT NULL,
                                Quantity INTEGER NOT NULL);""" % date_object

Variable table name in sqlite

Unfortunately, tables can't be the target of parameter substitution (I didn't find any definitive source, but I have seen it on a few web forums).

If you are worried about injection (you probably should be), you can write a function that cleans the string before passing it. Since you are looking for just a table name, you should be safe just accepting alphanumerics, stripping out all punctuation, such as )(][;, and whitespace. Basically, just keep A-Z a-z 0-9.

def scrub(table_name):
    return ''.join( chr for chr in table_name if chr.isalnum() )

scrub('); drop tables --')  # returns 'droptables'

---

Related Topics