Python Insert into SQLite Table

This article demonstrates how to execute the SQLite INSERT Query from Python to add new data rows to the SQLite table. You’ll learn how to use Python built-in module sqlite3 to insert data into the SQLite table

Goals of this lesson

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

Prerequisite

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

Python example to insert a single row/record into SQLite table

As of now, the SqliteDb_developers table is empty, so let’s insert data into it. To perform SQLite INSERT query from Python, you need to follow these simple steps: –

• First, Establish a SQLite connection from Python.
• Second, create a cursor object using the connection object.
• Then, Define the SQLite INSERT Query. Here you need to know the table, and it’s column details.
• Execute the INSERT query using the cursor.execute()
• After the successful execution of a SQLite query, Don’t forget to commit your changes to the database.
• Close the SQLite database connection.
• Also, don’t forget to catch SQLite exceptions if any.
• At last, verify the result by selecting data from the SQLite table.

Let’ s see the program now.

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

Understand Python SQLite insert program in detail

import sqlite3

• This line imports the sqlite3 module in our program.
• Using the classes and method defined in the sqlite3 module we can communicate with the SQLite database.

sqlite3.connect() and connection.cursor()

• Using a sqlite3.connect() method we established a connection to the SQLite database from Python.
• Next, we used a connection.cursor() method to get a cursor object from the connection object.

Next, we prepared a SQLite INSERT query to insert a row into a table. In the insert query, we mentioned column names and their values. This table contains five columns.

cursor.execute()

• We executed the operation stored in the SQLite INSERT query using an execute() method of a Cursor object.
• We used connection.commit() to make our changes persistent in the database.
• Using cursor.rowcount we got the number of rows affected.

At last, we are closing cursor, and SQLite connection object inside the finally block once we complete insert operation.

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 (?) used for parameters. Let see the example now.

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: sqlitedb_developers table after inserting a Python variable as a column value.

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 but sometimes in Python application, We need to insert multiple rows into the table in a single insert query.

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

The cursor.executemany() method accepts two arguments SQL query and records list. You can read our separate tutorial on how to use a cursor.executemany() and it’s performance benefits where we covered this function in detail.

Let see the demo now.

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)

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 database 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. At last, don’t forget to commit your changes to the SQLite database.

Next Steps:

To practice what you learned in this article, Please solve a Python Database Exercise project to Practice and master the Python Database operations.