INSERT INTO Table - sqlite3 - 2 Python Examples

Insert Row into sqlite3 Table

You can insert one or more rows into sqlite3 table using the 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. Additionally, we will explore how to check if the row insertion is successful and cover a few edge cases.

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, passing the SQL insert query to the execute() method.

In addition to these steps, you can use parameterized queries for better security and flexibility. We'll discuss that in detail below.

Examples

1. Insert row into sqlite3 table

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

Python Program

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

# Insert a row
c.execute('''INSERT INTO students (rollno, name, class)
             VALUES (1, 'Alex', 8)''')

# Commit the changes to the database
conn.commit()
# Close the connection
conn.close()

In this code, the query inserts a record into the students table with a roll number of 1, a name of Alex, and a class of 8.

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

To know if the INSERT INTO query has worked, we can check the last row ID inserted by the sqlite3 connection cursor. If the lastrowid is not zero 0, the insertion is successful.

Python Program

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

# Insert a row
c.execute('''INSERT INTO students (rollno, name, class)
             VALUES (1, 'Glen', 8)''')

# Check if insertion was successful by printing last row id
print(c.lastrowid)

# Commit the changes to the database
conn.commit()
# Close the connection
conn.close()

Output

2

If you see a non-zero ID echoed to the console, the INSERT INTO statement worked. A zero ID would indicate failure, and you should debug the program.

3. Insert multiple rows using executemany()

If you want to insert multiple rows at once, you can use the executemany() method. This is more efficient than inserting rows one at a time, especially for large datasets.

Python Program

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

# List of records to insert
records = [(2, 'John', 9), (3, 'Sara', 10), (4, 'Jake', 8)]

# Insert multiple rows using executemany()
c.executemany('''INSERT INTO students (rollno, name, class)
                 VALUES (?, ?, ?)''', records)

# Commit the changes to the database
conn.commit()
# Close the connection
conn.close()

In this example, we insert multiple records at once using the executemany() method. Notice that the query uses placeholders ? for the values, which is a secure way to handle user inputs.

4. Using Parameterized Queries for Security

Parameterized queries protect your database from SQL injection attacks. Instead of directly inserting values into the query string, you pass the values as parameters to the query.

In the previous example, the executemany() method already used parameterized queries with placeholders ?. Here's an example with execute() for a single insertion:

Python Program

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

# Insert a row with parameterized query
c.execute('''INSERT INTO students (rollno, name, class)
             VALUES (?, ?, ?)''', (5, 'Nina', 7))

# Commit the changes to the database
conn.commit()
# Close the connection
conn.close()

By using the ? placeholders, we ensure that values are passed as parameters, not as raw text, which prevents SQL injection attacks.

Summary

In this tutorial of Python Examples, we learned how to insert rows into a sqlite3 table using the INSERT INTO query. We covered the following:

  • Basic row insertion.
  • Checking if the insertion was successful using lastrowid.
  • Inserting multiple rows with executemany().
  • Using parameterized queries to enhance security and prevent SQL injection attacks.

Python Libraries