Overview
- An SQLite table can be created on an in-memory database or on a disk file based database.
- Creating a table in SQLite involves first selecting a database file and loading it.
- In SQLite, tables can be created in the in-memory databases as well.

Creating a SQLite table using Python
- The sqlite3 module provides the interface for connecting to the SQLite database.
- Using the connect() method of sqlite3 module a database connection can be created by specifying the name of the database file. This database will act as the main database.
- Databases present in other disk files can be attached to the database connection using the ATTACH statement, supported by the SQLite.
- In-memory databases can me made and connected to by specifying the argument to the connect() function as “:memory:” instead of a file name.
- Regardless of the classification of in-memory and disk file based databases SQLite will have only one database as the main database and one database as the temp database.
- A temp database is an in-memory SQLite database that will have one or more temporary tables.
- Once a database connection is obtained to an SQLite database, CREATE TABLE statement can be executed through a cursor object obtained using the connection object.
- Example Python programs are given that create tables in in-memory databases and disk file based databases.
Example 1 – Creating a table in an in-memory SQLite database
# ----Example Python Program to create tables in-memory databases----
# Import the sqlite3 module
import sqlite3
# Create database connection to an in-memory database
connectionObject = sqlite3.connect(":memory:")
# Obtain a cursor object
cursorObject = connectionObject.cursor()
# Create a table in the in-memory database
createTable = "CREATE TABLE EMP(id int, FirstName varchar(32), LastName varchar(32), dept int)"
cursorObject.execute(createTable)
# Print the tables
# .tables command will not work as it is not SQL...hence querying the SQLite_master
cursorObject.execute("select * from SQLite_master where type=\"table\"")
print("Tables available in the in-memory database(main):")
tables = cursorObject.fetchall()
print("Listing tables from SQLite_master:")
for table in tables:
print("------------------------------------------------------")
print("DB Object Name: %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]))
print("------------------------------------------------------")
connectionObject.close()Output
Tables available in the in-memory database(main):
Listing tables from SQLite_master:
------------------------------------------------------
DB Object Name: table
Name of the database object: EMP
Table Name: EMP
Root page: 2
SQL statement: CREATE TABLE EMP(id int, FirstName varchar(32), LastName varchar(32), dept int)
------------------------------------------------------Example 2 – Creating a table in a persistent SQLite database
# ----Example Python Program to create tables in disk file based SQLite databases----
# Import the sqlite3 module
import sqlite3
# Create a database connection to a disk file based database
connectionObject = sqlite3.connect("weather.db")
# Obtain a cursor object
cursorObject = connectionObject.cursor()
# Drop any existing table with the same name
dropTable = "drop table temperature"
cursorObject.execute(dropTable)
# Create a table in the disk file based database
createTable = "CREATE TABLE temperature(id int, temp numeric(3,1))"
cursorObject.execute(createTable)
# Insert EOD stats into the reports table
insertValues = "INSERT INTO temperature values(1,40.1)"
cursorObject.execute(insertValues)
insertValues = "INSERT INTO temperature values(2,65.4)"
cursorObject.execute(insertValues)
# Select from the temperature table
queryTable = "SELECT * from temperature"
queryResults = cursorObject.execute(queryTable)
# Print the Temperature records
print("(CityId, Temperature)")
for result in queryResults:
print(result)
connectionObject.close()Output
(CityId, Temperature)
(1, 40.1)
(2, 65.4)