Python 3!! How to Use SQLITE 3 Module

 

Hello everyone! In this article, we are going to see some basics of SQLite, easy steps to connect SQLite with python and create the database with it. After that, we will insert and retrieve data from the database.Let’s start!

Basics Of SQLite For Python :-

• It is an embedded relational database engine.
• It is a C library that provides a lightweight disk-based database that doesn’t require a separate server process, which means it is a serverless database.
• It is small, fast, self-contained, has high reliability, and is fully featured.
• The database file that you create resides in your computer only.
• The SQLite database can be accessed using the DB Browser.

Connecting SQLite Database With Python :-

Python

import sqlite3

con = sqlite3.connect("demo1_data.db")

cursor = con.cursor ()

In the above code, you can see that we have imported the sqlite3 to access the functionality of that module. Then we have created the connection object using sqlite3.connect() to interact with the SQLite database. In the connect() method, we have to give the path of the database which you want to access and if the database does not exist then it will create a database.

 After that, we have created the cursor object using the connection. cursor() to execute statements to communicate with the database. Using the methods of connection. cursor() we can execute sqlite3 statements, fetch data from the result sets, and call procedures.

Python

query='create table student(name varchar(15),rollno int(10),semester varchar(15),email varchar(15) )'

cursor.execute(query)

con.commit()

In the next step we have created a variable named query to store the SQL statement to create a table and passed that query in cursor.execute(query) or we can directly pass that statement in the cursor.execute() method. The execute() method of the cursor executes the SQL statement that has been stored in the variable query. For executing multiple statements together we can use cursor.executescript().

After executing we need to commit the connection to save the changes or the work we have done using connection.commit().

Python

import sqlite3

try:

    con = sqlite3.connect("demo1_data.db")

    cursor = con.cursor ()

    query='create table student(name varchar(15),rollno int(10),semester varchar(15),email varchar(15) )'

    cursor.execute(query)

    con.commit()

​

except sqlite3.DatabaseError as e:

          print(e)  

finally:

         cursor.close()

         con.close()

Always make a practice to write code in try block. If any error raises an error, the except block will be executed.

The finally block lets you execute code, regardless of the result of the try and except blocks. So try to make a habit to close the cursor and the connection in the finally block.

Insert And Retrieve Data From Database 

Python

import sqlite3

try:

    connection = sqlite3.connect("demo1.db")

    cursor = connection.cursor ()

    cursor.executescript("""

    insert into student1 VALUES('caryy','6th',1 ,'[email protected]');

    insert into student1 VALUES("John","6th",2 ,'[email protected]');

    insert into student1 VALUES("JENRY","6th",3 ,'[email protected]');

    insert into student1 VALUES("JEFF","6th",4 ,'[email protected]');

    """)

    query = 'select * from student1'

    cursor.execute(query)

    for x in cursor:

        print(x)

    connection.commit()

    print("Connection Established and Table created")

except sqlite3.DatabaseError as e:

    print(e)    

finally:

    cursor.close()

    connection.close()

In the above code, we have inserted and retrieved data from the database named demo1. First, we have made a connection with the database. Then we have created a table named student1. All the insert statements are executed using cursor.executescript().

Once the data is inserted, now we have to retrieve the data. For retrieving the data, we have to store the select statement in one variable named query and execute using cursor.execute(). Now the retrieved data is stored in the cursor object. For accessing the data we have to execute a for loop on the cursor and print it. After that, we have to close the cursor and the connection.

If you like this article, please share it with your family and friends. Also keep visiting SwagBlogger for more new updates and tips.