Create SQLite database Python | Sqlite3 Python example

 

Create SQLite database in Python

 

Introduction:

Database: A database is a collection of data (or information) stored in a format that can be easily accessed.

SQLite is a self-contained, embedded, high-reliability, file-based RDBMS (Relational Database Management System) that is very helpful for creating or managing the database. It is the lite weighted, and most used database engine on the World Wide Web.

        On this page, we will see how to create the database and connect it with the Python program (Create SQLite database Python). Python already has a library to use SQLite database in Python. The name of that library is sqlite3. Using this library, we can use the SQLite database in Python.

Creating and connecting the database with Python:

To create and connect the SQLite database with Python, we will use a code which is given in below. Using this code, we will connect the Python program with the database and then we create a table in the database.

# Creating and connecting SQLite database with Python program
# First, we need to import the library of SQLite that is "sqlite3"
import sqlite3

# Creating a connection object to connect the database with Python
conn = sqlite3.connect("School. db") 

# Now, we create a table in the created database
conn.execute('''
       create table Student( 
            std_id int primary key,
            name varchar(50),
            class varchar()20
       );
''')

# After writing your query close the connection
conn.close()

 

Note: After running this Python program the database will be created in the same folder where your main python file is present.

 

Insert Query – How to store data in Database:

By the Python program inserting the data into the SQLite database is very simple and easy. Unlike MySQL database, we don’t need to create a cursor object in the Python program to execute any queries. Using a connection object, we can write and execute any number of queries. So, to insert data into table of the database write a small insert query in the execute() function and then commit that changed data with the help of the commit() function. For example, see the following code.

# Inserting records into the table of the database 
import sqlite3
conn = sqlite3.connect("School.db")

# writing query in a variable to insert records
insert = '''
     insert into Student(std_id, name, class) values (101, "Chetan", "BCA");
'''

conn.execute(insert)
conn.commit() # use the commit() function in every change to save the changes in the table or database.
conn.close()

 

Select Query in Python SQLite:

Using a select query we can read or access the inserted data from the database. Below is the syntax of the select query.

conn.execute(“SELECT * FROM Table_name”);

Example:

import sqlite3
conn = sqlite3.connect("School.db")

data = conn.execute("select * from Student")
print("Student ID\t","Name\t","Class")
for n in data:
    print(n) # it prints the records in the tuple 
    
    print(n[0], "\t", n[1], "\t", n[2]) # printing all records without tuple

conn.close()

Update Query in Python SQLite:

An update query is used to update or modify the data from the table of the database. Below is given the syntax of the update query.

conn.execute(“UPDATE Table_name SET update_value WHERE [condition]”­)

Python example to update the records or data from the table of the database.

Example:

import sqlite3
conn = sqlite3.connect("School.db")

# Update Query
conn.execute("update Student set name = 'Amrut' where std_id = 105;")
conn.commit()
conn.close()

Delete Query in Python SQLite:

Normally, a delete query is used to delete or remove the records from the table of the database. Below is the syntax of the delete query.

conn. execute(DELETE FROM Table_name WHERE [condition];)

Python example for deleting the record from the table of the database.

Example:

import sqlite3
conn = sqlite3.connect("School.db")

# Getting student id from user to delete the record
value = input("Enter the Student Id : ")

conn.execute("delete from Student where std_id = "+value)
conn.commit()
conn.close()