Python sqlite3 – INSERT INTO Table

Insert Row into sqlite3 Table

You can insert one or more rows into sqlite3 table using execute() method.

In this tutorial, we shall go through the sequence of steps required to insert one or more rows into a table in sqlite database using sqlite3 library. Also, we shall learn how to check if the row insertion is successful.

Steps to insert rows into Sqlite Table

To insert a row into sqlite3 table, follow these steps.

1. Create a connection to your sqlite3 database.
2. Get a cursor to the connection.
3. Create a table if not present, or check if the table is present.
4. If the table is present, use execute() method on the cursor, by passing SQL insert query to the execute() method.

You can check if the row is inserted successfully or not with the help of cursor.lastrowid(). We shall see this scenario in Example 2.

Examples

1. Insert row into sqlite3 table

In the following example, we have created a table, if it does not exist, and then used INSERT INTO query to insert a record into the table.

import sqlite3

conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()

# Create table
c.execute('''CREATE TABLE IF NOT EXISTS students
             (rollno real, name text, class real)''')

c.execute('''INSERT INTO students
             VALUES(1, 'Alex', 8)''')
			 
# Commit the changes to db			
conn.commit()

# Close the connection
conn.close()

2. Insert row into sqlite3 table and check if insertion is successful

Well, most often times, we do need to know if the INSERT INTO query has actually worked. We need to know if the record is inserted successfully.

To know that, we can check the last row id that is inserted by the sqlite3 connection cursor. If the lastrowid is not zero 0, then we can assure programmatically that the insertion is successful.

import sqlite3

conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()

# Create table
c.execute('''CREATE TABLE IF NOT EXISTS students
             (rollno real, name text, class real)''')

c.execute('''INSERT INTO students
             VALUES(1, 'Glen', 8)''')

print(c.lastrowid)
			 
# Commit the changes to db			
conn.commit()

# Close the connection
conn.close()

Output

2

If you see a non-zero id echoed to the console, then your INSERT INTO statement has worked for you. If you get a zero, you need to debug your program.

Summary

In this tutorial of Python Examples, we learned how to insert rows into sqlite3 table.