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.

Inserting a new row into a table from Python

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

First, import the built-in sqlite3 module:

import sqlite3Code language: Python (python)

Second, connect to an SQLite database file by calling the connect() function from the sqlite3 module:

with sqlite3.connect(database) as conn:Code language: Python (python)

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

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

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

Fourth, execute an INSERT statement that inserts a row into a table:

cursor.execute(insert_statement)Code language: Python (python)

Fifth, apply the change permanently to the SQLite database by calling the commit() method of the Connection object:

conn.commit()Code language: Python (python)

If you want to pass arguments to the INSERT statement, use the question mark (?) as the placeholder for each. For example:

INSERT INTO table_name(c1, c2)
VALUES(?,?)Code language: Python (python)

In this statement, c1 and c2 are columns of the table table_name. The question mark (?) are placeholders for the c1 and c2 columns.

Inserting data into a table in Python example

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