Python Insert Rows into SQLite Database

In this article, we’re going to learn how to insert new data rows into SQLite database in Python.

You can read below post to learn how to create new SQLite database and table:

Python Creating SQLite Database and Table

For example, we have a SQLite database stored at D:\ToriCode\db\contacts.db and has table name contacts with 3 column id, first_name, last_name.

Step 1 - Create connection to a SQLite database

The Python code below to initialize the connection to a SQLite database.

import sqlite3 
from sqlite3 import Error

connection = None
try:
    connection = sqlite3.connect('D:\ToriCode\db\contacts.db')
except Error as e:
    print(e)
finally:
    if connection:
        print('Connect to database successfully.')

Step 2 - Insert new rows to SQLite data table

In the Python code snippets below, we’re going to use the connection object above to insert new rows.

Firstly, let’s define a new function to insert a contact Python object into SQLite database table.

def insert_contact(conn, contact):
    sql_insert = """
        INSERT INTO contacts(first_name, last_name)
        VALUES(?, ?);
    """
    try:
        cursor = conn.cursor()
        cursor.execute(sql_insert, contact)
        cursor.execute('COMMIT')
        return cursor.lastrowid
    except Error as e:
        print(e)
    finally: 
        print('Execute SQL script successfully.')

Below Python code, invoking insert_contact function to insert rows and close the connection.

contact_id = insert_contact(connection, ('Nancy', 'Harwood'))
print('New contact ID: ', contact_id)
contact_id = insert_contact(connection, ('Alan', 'Whitaker'))
print('New contact ID: ', contact_id)
contact_id = insert_contact(connection, ('Micah', 'Mcfarland'))
print('New contact ID: ', contact_id)
contact_id = insert_contact(connection, ('Marion', 'Paul'))
print('New contact ID: ', contact_id)

connection.close()

The complete Python program code:

import sqlite3 
from sqlite3 import Error

connection = None
try:
    connection = sqlite3.connect('D:\ToriCode\db\contacts.db')
except Error as e:
    print(e)
finally:
    if connection:
        print('Connect to database successfully.')

def insert_contact(conn, contact):
    sql_insert = """
        INSERT INTO contacts(first_name, last_name)
        VALUES(?, ?);
    """
    try:
        cursor = conn.cursor()
        cursor.execute(sql_insert, contact)
        cursor.execute('COMMIT')
        return cursor.lastrowid
    except Error as e:
        print(e)
    finally: 
        print('Execute SQL script successfully.')
    
contact_id = insert_contact(connection, ('Nancy', 'Harwood'))
print('New contact ID: ', contact_id)
contact_id = insert_contact(connection, ('Alan', 'Whitaker'))
print('New contact ID: ', contact_id)
contact_id = insert_contact(connection, ('Micah', 'Mcfarland'))
print('New contact ID: ', contact_id)
contact_id = insert_contact(connection, ('Marion', 'Paul'))
print('New contact ID: ', contact_id)

connection.close()

Output:

Connect to database successfully.
Execute SQL script successfully.
New contact ID:  1
Execute SQL script successfully.
New contact ID:  2
Execute SQL script successfully.
New contact ID:  3
Execute SQL script successfully.
New contact ID:  4

After executing the Python program above, we will get the data rows as below in our database.

Python Insert Rows into SQLite Database

Python Insert Rows into SQLite Database

Step 1 - Create connection to a SQLite database

Step 2 - Insert new rows to SQLite data table

Tags: