Retrieve The List Of Tables, Indices And The Associated SQL Statements

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)


Copyright 2020 © pythontic.com