Python Insert into SQLite Table

Learn to execute the SQLite INSERT Query from Python to add new rows to the SQLite table using a Python sqlite3 module.

Goals of this lesson: –

• Insert single and multiple rows into the SQLite table
• Insert Integer, string, float, double, and datetime values into SQLite table
• Use a parameterized query to insert Python variables as dynamic data into a table

Prerequisites

If a table is not present in your SQLite database, then please refer to create SQLite table from Python.

Python example to insert a single row into SQLite table

Follow the below steps: –

As of now, the SqliteDb_developers table is empty, so let’s insert data into it.

Example

import sqlite3

try:
    sqliteConnection = sqlite3.connect('SQLite_Python.db')
    cursor = sqliteConnection.cursor()
    print("Successfully Connected to SQLite")

    sqlite_insert_query = """INSERT INTO SqliteDb_developers
                          (id, name, email, joining_date, salary) 
                           VALUES 
                          (1,'James','james@pynative.com','2019-03-17',8000)"""

    count = cursor.execute(sqlite_insert_query)
    sqliteConnection.commit()
    print("Record inserted successfully into SqliteDb_developers table ", cursor.rowcount)
    cursor.close()

except sqlite3.Error as error:
    print("Failed to insert data into sqlite table", error)
finally:
    if sqliteConnection:
        sqliteConnection.close()
        print("The SQLite connection is closed")

Output

Using Python variables in SQLite INSERT query

Sometimes we need to insert a Python variable value into a table’s column. This value can be anything, including integer, string, float, and DateTime. For example, in the registration form person enter his/her details. You can take those values in Python variables and insert them into the SQLite table.

We use a parameterized query to insert Python variables into the table. Using a parameterized query, we can pass python variables as a query parameter in which placeholders (?)

import sqlite3

def insertVaribleIntoTable(id, name, email, joinDate, salary):
    try:
        sqliteConnection = sqlite3.connect('SQLite_Python.db')
        cursor = sqliteConnection.cursor()
        print("Connected to SQLite")

        sqlite_insert_with_param = """INSERT INTO SqliteDb_developers
                          (id, name, email, joining_date, salary) 
                          VALUES (?, ?, ?, ?, ?);"""

        data_tuple = (id, name, email, joinDate, salary)
        cursor.execute(sqlite_insert_with_param, data_tuple)
        sqliteConnection.commit()
        print("Python Variables inserted successfully into SqliteDb_developers table")

        cursor.close()

    except sqlite3.Error as error:
        print("Failed to insert Python variable into sqlite table", error)
    finally:
        if sqliteConnection:
            sqliteConnection.close()
            print("The SQLite connection is closed")

insertVaribleIntoTable(2, 'Joe', 'joe@pynative.com', '2019-05-19', 9000)
insertVaribleIntoTable(3, 'Ben', 'ben@pynative.com', '2019-02-23', 9500)

Output:

Note: If you have a date column in the SQLite table, and you want to insert the Python DateTime variable into this column then please refer to working with SQLite DateTime values in Python.

Python Insert multiple rows into SQLite table using the cursor’s executemany()

In the above example, we have used execute() method of cursor object to insert a single record. Still, sometimes we need to insert multiple rows into the table in a single insert query.

For example, You wanted to add all records from the CSV file into the SQLite table. Instead of executing the INSERT query every time to add each record, you can perform a bulk insert operation in a single query using a cursor’s executemany() function.

The executemany() method takes two arguments SQL query and records to update.

import sqlite3

def insertMultipleRecords(recordList):
    try:
        sqliteConnection = sqlite3.connect('SQLite_Python.db')
        cursor = sqliteConnection.cursor()
        print("Connected to SQLite")

        sqlite_insert_query = """INSERT INTO SqliteDb_developers
                          (id, name, email, joining_date, salary) 
                          VALUES (?, ?, ?, ?, ?);"""

        cursor.executemany(sqlite_insert_query, recordList)
        sqliteConnection.commit()
        print("Total", cursor.rowcount, "Records inserted successfully into SqliteDb_developers table")
        sqliteConnection.commit()
        cursor.close()

    except sqlite3.Error as error:
        print("Failed to insert multiple records into sqlite table", error)
    finally:
        if sqliteConnection:
            sqliteConnection.close()
            print("The SQLite connection is closed")

recordsToInsert = [(4, 'Jos', 'jos@gmail.com', '2019-01-14', 9500),
                   (5, 'Chris', 'chris@gmail.com', '2019-05-15', 7600),
                   (6, 'Jonny', 'jonny@gmail.com', '2019-03-27', 8400)]

insertMultipleRecords(recordsToInsert)

Output

 verify the result by selecting data from SQLite table from Python.

Let’s understand the above example

• After connecting to SQLite, We prepared a list of records to insert into the SQLite table. Each entry in the list is nothing but a table tuple (row)
• SQL INSERT statement contains the parameterized query, which uses the placeholder (?) for each column value.
• Next, Using cursor.executemany(sqlite_insert_query, recordList) , we inserted multiple rows into the table.
• To get to know the number of records inserted, we used a cursor.rowcount method.

Next Steps:

To practice what you learned in this article, Solve a Python Database Exercise project to Practice Database operations.