SQLite Python: Creating Tables

Summary: in this tutorial, you will learn how to create tables in an SQLite database from the Python program using the sqlite3 module.

Creating new tables from Python

To create a new table in an SQLite database from a Python program, you follow these steps:

First, import the built-in sqlite3 module:

import sqlite3Code language: JavaScript (javascript)

Next, create a connection to an SQLite database file by calling the connect() function of the sqlite3 module:

with sqlite3.connect(database) as conn:Code language: JavaScript (javascript)

The connect() function returns a Connection object that represents the database connection to the SQLite file specified by the database argument.

Then, create a Cursor object by calling the cursor() method of the Connection object:

cursor = conn.cursor()

After that, pass the CREATE TABLE statement to the execute() method of the Cursor object to execute the statement that creates a new table:

cursor.execute(create_table)Code language: CSS (css)

Finally, apply the changes to the database by calling the commit() function:

conn.commit()Code language: CSS (css)

Here’s the complete code:

import sqlite3

database = '<your_database>'
create_table = '<create_table_statement>'

try:
    with sqlite3.connect(database) as conn:
        cursor = conn.cursor()
        cursor.execute(create_table)   
        conn.commit()

except sqlite3.OperationalError as e:
    print(e)Code language: Python (python)

For demonstration purposes, we will create two tables: projects and tasks as shown in the following database diagram:

Here are the CREATE TABLE statements that create the projects and tasks tables:

CREATE TABLE IF NOT EXISTS projects (
    id INTEGER PRIMARY KEY, 
    name text NOT NULL, 
    begin_date DATE, 
    end_date DATE
);

CREATE TABLE IF NOT EXISTS tasks (
    id INTEGER PRIMARY KEY, 
    name text NOT NULL, 
    priority INT, 
    project_id INT NOT NULL, 
    status_id INT NOT NULL, 
    begin_date DATE NOT NULL, 
    end_date DATE NOT NULL, 
    FOREIGN KEY (project_id) 
    REFERENCES projects (id) 
        ON DELETE CASCADE 
        ON UPDATE CASCADE
);Code language: SQL (Structured Query Language) (sql)

The following program that uses the above CREATE TABLE statements to create the tables:

import sqlite3

sql_statements = [ 
    """CREATE TABLE IF NOT EXISTS projects (
            id INTEGER PRIMARY KEY, 
            name text NOT NULL, 
            begin_date DATE, 
            end_date DATE
        );""",

    """CREATE TABLE IF NOT EXISTS tasks (
            id INTEGER PRIMARY KEY, 
            name TEXT NOT NULL, 
            priority INT, 
            project_id INT NOT NULL, 
            status_id INT NOT NULL, 
            begin_date DATE NOT NULL, 
            end_date DATE NOT NULL, 
            FOREIGN KEY (project_id) REFERENCES projects (id)
        );"""
]

# create a database connection
try:
    with sqlite3.connect('my.db') as conn:
        # create a cursor
        cursor = conn.cursor()

        # execute statements
        for statement in sql_statements:
            cursor.execute(statement)

        # commit the changes
        conn.commit()

        print("Tables created successfully.")
except sqlite3.OperationalError as e:
    print("Failed to create tables:", e)
Code language: Python (python)

How it works.

First, import the sqlite3 module:

import sqlite3Code language: Python (python)

Second, define a list that stores the CREATE TABLE statements:

sql_statements = [ 
    """CREATE TABLE IF NOT EXISTS projects (
            id INTEGER PRIMARY KEY, 
            name text NOT NULL, 
            begin_date DATE, 
            end_date DATE
        );""",

    """CREATE TABLE IF NOT EXISTS tasks (
            id INTEGER PRIMARY KEY, 
            name TEXT NOT NULL, 
            priority INT, 
            project_id INT NOT NULL, 
            status_id INT NOT NULL, 
            begin_date DATE NOT NULL, 
            end_date DATE NOT NULL, 
            FOREIGN KEY (project_id) REFERENCES projects (id)
        );"""
]Code language: Python (python)

Third, create a connection to the my.db SQLite database file by calling the sqlite3.commit() function. Use the with statement to close the database connection automatically:

with sqlite3.connect('my.db') as conn:Code language: Python (python)

Fourth, create a Cursor object from the Connection object:

cursor = conn.cursor()Code language: Python (python)

Fifth, iterate over statements of the SQL statement list and execute each by calling the execute() method of the cursor object:

for statement in sql_statements:
   cursor.execute(statement)Code language: Python (python)

Sixth, apply the changes to the SQLite database file by calling the commit() method of the conn object:

conn.commit()Code language: CSS (css)

Seventh, display a message indicating that the table has been created successfully:

print("Tables created successfully.")Code language: Python (python)

Eight, wrap the code within the try...except statement to handle the error and display the error message if an error occurs during the table creation process:

print("Failed to create tables:", e)Code language: Python (python)

Open your terminal and run the program:

python create_table.pyCode language: Shell Session (shell)

A success message will appear if the tables are created successfully:

Tables created successfully.Code language: Shell Session (shell)

Summary

• Use the execute() method of a Cursor object to execute the CREATE TABLE statement for creating a new table in the SQLite database file.