Python Sqlite CRUD Operations - Connect Python with SQlite Database

Published December 10, 2021

If you would like to become a Python backend programmer, you'll need to learn how to connect Python to an SQLite database and execute standard CRUD tasks. By the end of this, the post you will have learned:

  • How to Create a Python SQLite Connection

  • How to Create an SQLite Database Table

  • How to perform ou CRUD operations

  1. How to Connect Python with SQLite Database

  • Step 1:   First of all, we need to import the sqlite3 module in our python code. 

 

import sqlite3

 

  • Step 2: Using the  connect () method connect your python to  the SQLite  database

# Connect to sqlite database

con = sqlite3.connect('studentDb.db')

  • Step 3:  You have now connected to the SQLite database, using the cursor () to return your cursor object.

# Cursor object

cur = con.cursor()

  • Step 4:  You can now run any SQL query using the execute() method. For instance, let's select data from the Studentsmarks table in the database.

# Execute SQL query

cur.execute("Select * from Studentsmarks")

  • Step 5: lastly, close your database connection using the con.close() method.

# Close connection

con.close()

 

  1. How do i create new table in SQLite Database

 

SQLite databases use tables to store the user data, thus you need to create tables to store and retrieve your information.  In the steps below post, we are going to create an SQLite table "Studentsmarks” with unit_ID as the primary key.

Step 1: First of all, establish a database connection

# Connect to sqlite database

con = sqlite3.connect('studentDb.db')

try:

Step 2: Call your cursor object

# Cursor object

    cur = con.cursor()

Step 3: Now, run the table creation query

  # Execute query

    cur.execute('''CREATE TABLE Studentsmarks (

    unit_Id INTEGER PRIMARY KEY AUTOINCREMENT,

    UnitName TEXT (500) NOT NULL,

    Student TEXT (252) NOT NULL,

    Age INTEGER,

    Country TEXT (252) NOT NULL);''')

    # Commit changes

    con.commit()

    # Print successful message

    print(' Studentsmarks table created successfully')

    except:

    print('Error occurred...')

    # Roll back if in case of issue

    con.rollback()

Step 4: Close connection

con.close()

 

  1. SQLite CRUD operations

 

The SQLite CRUD operations include four important operations:

  • Create

  • Retrieve

  • Update

  • Delete

 

Create

This operation involves inserting a new row in a SQlite database table.  To create a new row, follow the following simple guidelines:

import sqlite3

db=sqlite3.connect('studentdb')

qry="insert into Studentsmarks (name, age, marks) values('Ranjha', 20, 50);"

try:

    cur=db.cursor()

    cur.execute(qry)

    db.commit()

    print ("Marks added successfully")

except:

    print ("error in operation")

    db.rollback()

db.close()

 

Retrieve

This operation involves retrieving a particular row or the entire database table information using the fetchone () and fetchall () methods respectively.

To fetch a single query:

import sqlite3

db=sqlite3.connect('studentdb')

sql="SELECT * from Studentsmarks;"

cur=db.cursor()

cur.execute(sql)

while True:

    record=cur.fetchone()

    if record==None:

        break

    print (record)

db.close()

 

To fetch all database queries:

 

 

Update

This operation involves altering the information in the database table.  Using this operation you can be able to update any row or the entire table information.  This operation works with conditions.  For example, in the following code, we are going to update the query where 

import sqlite3

db=sqlite3.connect('studentdb')

qry="update Studentsmarks set age=? where name=?;"

try:

    cur=db.cursor()

    cur.execute(qry, (19,'Shamir'))

    db.commit()

    print("record updated successfully")

except:

    print("error in operation")

    db.rollback()

db.close()

 

Delete

This operation allows you to drop a particular entry in the database table. You can drop a particular row or drop the entire table. For instance, in the following code, we are going to drop the query where

import sqlite3

db=sqlite3.connect('test.db')

qry="DELETE from student where name=?;"

try:

    cur=db.cursor()

    cur.execute(qry, ('Bill',))

    db.commit()

    print("student deleted successfully")

except:

    print("error in operation")

    db.rollback()

db.close()

Conclusion

In this post, we have learned how to connect the python program to an SQLite database, create a table and finally perform various CRUD operations. These operations are very important and are common when you start your backend software development practices

Article Contributed By :
https://www.rrtutors.com/site_assets/profile/assets/img/avataaars.svg

1180 Views
Projects By category
Categories