SQLite Python: Inserting Data

Summary: in this tutorial, you will learn how to insert rows into a table in the SQLite database from a Python program using the sqlite3 module.

To insert rows into a table in an SQLite database, you use the following steps:

1. First, connect to the SQLite database by calling the sqlite3.connect() function. The function returns a Connection object.
2. Second, create a Cursor object by calling the cursor() method of the Connection object.
3. Third, execute an INSERT statement to insert data into the target table. If you want to pass arguments to the INSERT statement, use the question mark (?) as the placeholder for each argument.

Inserting data into a table example

We’ll use the projects and tasks tables created in the creating tables tutorial for the demonstration.

The following program inserts data into the projects and tasks tables:

import sqlite3

def add_project(conn, project):
    sql = ''' INSERT INTO projects(name,begin_date,end_date)
              VALUES(?,?,?) '''
    cur = conn.cursor()
    cur.execute(sql, project)
    conn.commit()
    return cur.lastrowid


def add_task(conn, task):
    sql = '''INSERT INTO tasks(name,priority,status_id,project_id,begin_date,end_date)
             VALUES(?,?,?,?,?,?) '''
    cur = conn.cursor()
    cur.execute(sql, task)
    conn.commit()
    return cur.lastrowid


def main():
    try:
        with sqlite3.connect('my.db') as conn:
            # add a new project
            project = ('Cool App with SQLite & Python', '2015-01-01', '2015-01-30')
            project_id = add_project(conn, project)
            print(f'Created a project with the id {project_id}')

            # tasks
            tasks = [
                ('Analyze the requirements of the app', 1, 1, project_id, '2015-01-01', '2015-01-02'),
                ('Confirm with user about the top requirements', 1, 1, project_id, '2015-01-03', '2015-01-05')
            ]
            for task in tasks:
                task_id = add_task(conn, task)
                print(f'Created a project with the id {task_id}')


    except sqlite3.Error as e:
        print(e)

if __name__ == '__main__':
    main()Code language: PHP (php)

First, import the sqlite3 module:

import sqlite3Code language: JavaScript (javascript)

Second, define a function called add_project that inserts a new row into the projects table:

def add_project(conn, project):
    sql = ''' INSERT INTO projects(name,begin_date,end_date)
              VALUES(?,?,?) '''
    cur = conn.cursor()
    cur.execute(sql, project)
    conn.commit()
    return cur.lastrowidCode language: PHP (php)

In this function:

1. Initialize an INSERT statement. The question mark (?) is the placeholder that will be replaced by the corresponding field values such as name, begin date, and end date.
2. Next, call the cursor() method of the Connection object to create a new cursor.
3. Then, execute the INSERT statement with the values provided by the project tuple. The project variable can be a tuple or a list that includes name, begin date, and end date field values.
4. After that, apply the change permanently to the database by calling the commit() method of the Connection object.
5. Finally, return the inserted id in the projects table using the lastrowid property of the cursor object.

Third, define another function called add_task that inserts a task into the tasks table:

def add_task(conn, task):
    sql = '''INSERT INTO tasks(name,priority,status_id,project_id,begin_date,end_date)
             VALUES(?,?,?,?,?,?) '''
    cur = conn.cursor()
    cur.execute(sql, task)
    conn.commit()
    return cur.lastrowidCode language: PHP (php)

The add_task() function works the same as the add_project() function except for the INSERT statement.

Fourth, define the main() function that opens a connection to the SQLite database and inserts rows into the projects and tasks tables:

def main():
    try:
        with sqlite3.connect('my.db') as conn:
            # add a new project
            project = ('Cool App with SQLite & Python', '2015-01-01', '2015-01-30')
            project_id = add_project(conn, project)
            print(f'Created a project with the id {project_id}')

            # tasks
            tasks = [
                ('Analyze the requirements of the app', 1, 1, project_id, '2015-01-01', '2015-01-02'),
                ('Confirm with user about the top requirements', 1, 1, project_id, '2015-01-03', '2015-01-05')
            ]
            for task in tasks:
                task_id = add_task(conn, task)
                print(f'Created a project with the id {task_id}')


    except sqlite3.Error as e:
        print(e)Code language: PHP (php)

In the main() function:

• Open a connection to the my.db database using the connect() method of the sqlite3 module.
• Call the add_project() function to insert a new row into the projects table. The add_project() function uses the Connection object and a tuple that includes the project data including name, begin date, and end date.
• Define a list of tuples representing the tasks and call the add_tasks() function to insert each task into the tasks table.
• If any error occurs, display its message in the except block.

Finally, run the program to insert rows into these tables.

Verify the inserts

First, connect to my.db database file using the sqlite3 program.

Second, run the following commands to format the output:

.header on
.mode columnCode language: Shell Session (shell)

Third, retrieve data from the projects table using the following query:

select * from projects;Code language: Shell Session (shell)

Output:

id  name                           begin_date  end_date
--  -----------------------------  ----------  ----------
1   Cool App with SQLite & Python  2015-01-01  2015-01-30Code language: JavaScript (javascript)

Finally, query data from the tasks table:

select * from tasks;Code language: SQL (Structured Query Language) (sql)

Output:

id  name                                          priority  project_id  status_id  begin_date  end_date
--  --------------------------------------------  --------  ----------  ---------  ----------  ----------
1   Analyze the requirements of the app           1         1           1          2015-01-01  2015-01-02
2   Confirm with user about the top requirements  1         1           1          2015-01-03  2015-01-05Code language: JavaScript (javascript)

Summary

• Use the execute() statement of the Cursor object to execute an INSERT statement to insert a row into a table.
• Use the commit() method of the Connection object to apply the change to the database permanently.