Overview
- Every SQLite database has a special table named sqlite_master, which is a system created table.
- The sqlite_master table of a database contains the list of tables, indices and other details specific to that database.
- The type column of the sqlite_master table denotes what type of database object the record is about. The type column will hold the value "table" for a database table and the value "index" for a database index.
- The sql column of the sqlite_master table contains the SQL statement with which a table or index has been created.
Getting the list of tables and indexes present in a SQLite database using Python
- The sqlite3 is the python database client for SQLite database.
- A database connection to an SQLite database can be created by calling the connect() method of the sqlite3 module.
- Through the connection object a sqlite3.Cursor object can be obtained.
- A SQL statement like a SELECT statement can be executed on the SQLite server by calling the execute() method of the cursor object and passing the SQL statement as parameter.
- The Python example below queries the sqlite_master table and prints the list of tables and indices present in the SQLite main database loaded through the file info.dbs.
Example
# Import the python module - sqlite3
import sqlite3
# Create database connection to a main database which is stored in a file
connectionObject = sqlite3.connect("info.dbs")
# Obtain a cursor object
cursorObject = connectionObject.cursor()
# Print the tables and indices present in the SQLite main database
cursorObject.execute("select * from SQLite_master")
tables = cursorObject.fetchall()
print("Listing tables and indices from main database:")
for table in tables:
print("Type of database object: %s"%(table[0]))
print("Name of the database object: %s"%(table[1]))
print("Table Name: %s"%(table[2]))
print("Root page: %s"%(table[3]))
print("SQL statement: %s"%(table[4]))
connectionObject.close()Output
Listing tables and indices from main database:
Type of database object: table
Name of the database object: tokens
Table Name: tokens
Root page: 2
SQL statement: CREATE TABLE tokens(id int, value varchar(12), weight int)
Type of database object: table
Name of the database object: routes
Table Name: routes
Root page: 3
SQL statement: CREATE TABLE routes(id int, start int, stop int,hops int )
Type of database object: index
Name of the database object: idx_index
Table Name: tokens
Root page: 4
SQL statement: CREATE INDEX idx_index on tokens(value)