How to Insert data into SQLite Database Table in Python

Once we create a SQLite database table in Python, THe next thing is to insert some data in it. To insert data in SQlite Database table we use the insert Query.

In this article we will discuss how you to insert differnt types of data in SQLite database table in Python.

Create Sqlite Database in Python

To create a Sqlite database in Python use the sqlite3 module. sqlite3 module is a python built-in module which have a lot of method for working with SQLite databases.The best way to create a Sqlite database in Python:

• import the sqlite3 module
• use the sqlite3.connect('database_name.db') funciton

Follwoing is the Python code that create a SQLite database in Python.

import sqlite3
# it will create a databse with name sqlite.db
connection= sqlite3.connect('sqlite.db') 
cursor = connection.cursor()
connection.close() 

A databse file with the name sqlite.db will created after the execturion of the above code. connect() mehtod is used to make a connectoin to this database file. {alertSuccess}

Create a table in Sqlite database in Python

To create a table in SQlite database , use the cursor.executer('table_query') function in python. It will create a table in a database with the parameter specified in the table query. the ‘table_query‘ is nothing but a SQL command for creating a table in the database.Follwoing is a python code that create a table in sqlite database in python.

import sqlite3
# it will create a databse with name sqlite.db
connection= sqlite3.connect('sqlite.db') 
cursor= connection.cursor()
table_query = '''CREATE TABLE Student
               (Name text, Course text, Age real)'''
               
cursor.execute(table_query)
# you need to commit changes as well
connection.commit()
# you also need to close  the connection
connection.close()   

The above python code will create a database table with the name ‘Student’, the column’s names are ‘Name’, ‘Course’, ang ‘Age’. These are also called the attributes of ‘Student’.

Insert Data in Sqlite Database table in Python

To insert data in SQLite Databases table use the sqlite3 module. sqlite3 is a very helpful module if you are working with SQlite Database related tasks.

To insert data in SQLite database use the cursor.execute(insert_query) funciton. It will exectute the insert query and will insert data in the database table.Follow these steps to insert data in a Sqlite database in Python

• import sqlite3 module
• create connectoin to Sqlite file
• build the cursor using the con.cursor() mehtod in python
• use the cursor.execute(isnert_query) funciton
• commit the changes and close the database connectioon

These are the steps of inserting any data in Sqlite3 database.

Following is a python code that insert data into the Student table. We will insert data of 3 students to the Studnet table in the Sqlite database in Python.

  
import sqlite3
# it will create a databse with name sqlite.db
connection= sqlite3.connect('sqlite.db') 
cursor= connection.cursor()
table_query = '''CREATE TABLE Student if not Exists
               (Name text, Course text, Age real)'''
       
             
# student list 

students_data = [
 ['AlixaProDev','CS',19],
 ['Alixawebdev','BBa',21],
  ['AskALixa','Software',22]
]
insert_q = []

# creating the insert query for each student
for std_data in students_data:
    name = std_data[0]
    course = std_data[1]
    age = std_data[2]
    q=f"INSERT INTO Student VALUES ('{name}','{course}','{age}')"
    insert_q.append(q)

# executing the insert queries
for q in insert_q:
    cursor.execute(q)


# you need to commit changes as well
connection.commit()
# you also need to close  the connection
connection.close()
 

This program will insert students data into student table in Sqlite database in Python.

Retrieve the students data from the Students table in Databases in Python

We have inserted data in Stuent table. To Retrieve the data from the database we use the cursor.execute(select_query) mehtod. We first need to build a slect query for the database table.Below is the query that get data back from the sqlite database table in Python

import sqlite3
# it will connect us to the 'sqlite.db. database
connection= sqlite3.connect('sqlite.db') 
cursor= connection.cursor()
q="Select * from Student"

students_data = cursor.execute(q)
for data in students_data:
    print(data)

# you also need to close  the connection
connection.close()