How do you create an index on the tables themselves in a database?

Question

I'm using APSW to wrap my SQL code in Python, like so:

connection=apsw.Connection("dbfile"); cursor=connection.cursor()
cursor.execute("create table foo(x,y,z)")
cursor.execute("create table bar(a,b,c)")
cursor.execute("create table baz(one,two,three)")

I plan on using a GUI framework to display these table names (and subsequently their columns and rows). I would also like to sort these table names by different criteria.

Is there a way to create an index on the tables themselves to be used for sorting – e.g.

cursor.execute("CREATE INDEX index_name ON foo")

Answer 1

You can list all the tables in the sqlite database with

cursor.execute(
    "SELECT tbl_name FROM sqlite_master WHERE type='table'")

table_names = cursor.fetchall()

Once you have the table names, you can use string formatting to form the CREATE INDEX commands.

Answer 2

In addition to @unutbu answer, you also have the PRAGMA table_info() function:

PRAGMA table_info(table-name);

It returns information on individual tables.