Create Tables In SQLite Database Using Python


  • 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.

Create a SQLite Table

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)"



# 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("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]))





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"



# Create a table in the disk file based database

createTable = "CREATE TABLE temperature(id int, temp numeric(3,1))"



# Insert EOD stats into the reports table

insertValues = "INSERT INTO temperature values(1,40.1)"



insertValues = "INSERT INTO temperature values(2,65.4)"



# Select from the temperature table

queryTable = "SELECT * from temperature"

queryResults = cursorObject.execute(queryTable)


# Print the Temperature records

print("(CityId, Temperature)")

for result in queryResults:






(CityId, Temperature)

(1, 40.1)

(2, 65.4)



Copyright 2020 ©