Inserting Data into Database in Python using SQLite3

Insert Data into Database: Till now we have learnt how to create database and tables in SQLite respectively. So, in this tutorial we will focus on How to insert data into table in SQLite in Python?

This tutorial mainly focuses on:

• Inserting single data into SQLite table in Python
• Inserting multiple data into SQLite table in Python
• Parameterized query to insert Dynamic data into SQLite table in Python

In previous tutorial, we have already learnt about how to make connection to our database in python using SQLite. So, don’t worry we will also revise that one also.

Our database, and table present inside the database looks like:

database name: “data.db”
table name: “users”
table columns: “id“, “name“, “age“, “gender”

Single Data – Python program for inserting data into database SQLite

#Importing Database Library
import sqlite3

# Database Connectivity
try:
    con = sqlite3.connect("data.db")
    cursor = con.cursor()
    print("Connected to Database Successfully")
    #Data Insertion Process
    name = input("Enter Name: ")
    age = int(input("Enter Age: "))
    gender = input("Enter your Gender: ")
    query = "INSERT into USERS(name, age, gender) VALUES (?,?,?)"
    data = (name, age, gender,)
    cursor.execute(query, data)
    con.commit()
    if(cursor.execute(query,data)):
        print("Data Inserted Successfully")
    else:
        print("Data not Inserted")
    cursor.close()
except:
    print("Database Error")

# End

Output:

# Connected to Database Successfully
# Getting input from user
# Enter Name: Raj
# Enter Age: 21
# Enter your Gender: M

#Data Inserted Successfully

Our database SQLite table after data insertion process:

«Hence we have successfully inserted data into our database table»

Explanation: I will only explain the main points or the points which are totally new for us. Because as we are working with databases, so I’m assuming that you guys are aware of core concepts of python.

• At line 3 we have imported our sqlite3 library, because the functions we used in our program is already pre-defined in sqlite3 library.
• At line 6 & 23 we used try: & except: method which is useful for handling sql exception.
• At line 7 & 8 we initialized “con” variable with connect() method with our database name “data.db“. The connect() method used here is pre-defined in sqlite3 library.
• From line 11 to 13 we have taken the input from user.
• At line 14 we initialized variable “query” with sql query for data insertion. Here “users” is the name of our table present inside our database “data.db” & name, age and gender are the columns names of our table.
• At line 15 we have a tuple “data” with the values that we have taken from user as input.
• At line 16 cursor.execute(query,data) here cursor storing the database connection & execute() is the function defined in sqlite3 library. The function execute() takes two parameters “database-query” and “data” to be stored in database.
• At line 17 it is necessary to call commit() function after making changes to our database.
• At line 18 it is also necessary to close() our cursor. Basically cursor acts as pointer.

Multiple Data – Python program for inserting data into database SQLite

#Importing Database Library
import sqlite3

# Database Connectivity
try:
    con = sqlite3.connect("data.db")
    cursor = con.cursor()
    print("Connected to Database Successfully")
    #Data inserting Process-(Multiple Records)
    while(True):
        name = input("Enter your Name: ")
        age = int(input("Enter your Age: "))
        gender = input("Enter your gender: ")
        data = (name, age, gender,)
        query = "INSERT into USERS(name,age,gender) VALUES (?,?,?)"
        cursor.execute(query,data)
        con.commit()
        ch = input("Do you want to Enter more Records(Y/N): ")
        if ch == "n" or ch == "N":
            break
        else:
            pass
    cursor.close()
except:
    print("Database Error")

#END

Output:

#Connected to Database Successfully
# Enter your Name: Rajat
# Enter your Age: 23
# Enter your gender: M
# Do you want to Enter more Records(Y/N): y
# Enter your Name: Rashm
# Enter your Age: 34
# Enter your gender: F
# Do you want to Enter more Records(Y/N): n

Explanation: Program explanation is same as I have discussed above. Only difference is that here we have used some basic concepts of Core Python.

Our database SQLite table after data insertion process:

Parameterized Query – Python program for inserting data into database SQLite

#Importing Database Library
import sqlite3

# Database Connectivity
def variables(name,age,gender):
    try:
        con = sqlite3.connect("data.db")
        cursor = con.cursor()
        print("Connected to Database Successfully")
        #Data inserting Process-(Parameterized Query)
        data = (name, age, gender,)
        query = "INSERT into USERS(name,age,gender) VALUES (?,?,?)"
        cursor.execute(query,data)
        con.commit()
        cursor.close()
    except:
        print("Database Error")

variables("Amanda",22,"F")

#END

Our database SQLite table after data insertion process:

Explanation: Program explanation is same as I have discussed above. Only difference is that here we have used parameterized function & some basic concepts of Core Python.