Learning SQLite3 in Python: Basic CRUD Operations

Learning SQLite3 in Python: Basic CRUD Operations

Photo by Pixabay from Pexels

“In a world of data, small and grand,

SQLite3 and Python hand in hand.

A serverless wonder, light yet bright,

Embeds in apps, brings data to light.

With Python’s library, vast and wide,

The `sqlite3` module by our side.

No extra instals, no complex strain,

Just import it, and we’re ready to train”

If you are a data analyst working with Python, then I am sure you are also facing some problems, as I faced a couple of years back until I learned the importance of SQL.

SQLite3 is a self-contained and powerful tool capable of handling efficient data storage and retrieval, ensuring data integrity and security through ACID compliance, constraints, and robust security features.

It is a serverless SQL database engine, one of the first priorities for embedding databases in applications. Python, with its extensive standard library, provides an sqlite3 module that allows you to interact with SQLite databases. In this blog, we’ll explore the basics of using SQLite3 in Python, focusing on CRUD operations: Create, Read, Update, and Delete.

Table of Contents

1. Introduction to SQLite3
2. Setting Up SQLite3 in Python
3. Creating a Database and Table
4. Inserting Data (Create)
5. Fetching Data (Read)
6. Updating Data (Update)
7. Deleting Data (Delete)
8. Conclusion

1. Introduction to SQLite3

Less than 500KB in size and with no extra memory occupation or separate servers, SQLite3 is a lightweight, disk-based database. It is most popular for Python and useful for small to medium-sized applications. Its simplicity and efficiency always make it an excellent choice for many application developers who need to store data locally.

2. Setting Up SQLite3 in Python

Before diving into CRUD operations, let’s set up the SQLite3 environment in Python.

First, ensure you have Python installed on your machine. You can check this by running:

bash

Copy code

python - version

To use SQLite3 in Python, you need to import the sqlite3 module. No additional installation is required as it is included in Python’s standard library.

python

Copy code

import sqlite3

3. Creating a Database and Table

To create a database, you need to connect to it. If the database does not exist, SQLite will create it for you. Let’s create a database named example.db.

python

Copy code

import sqlite3
# Connect to the database (or create it if it doesn't exist)
conn = sqlite3.connect('example.db')
# Create a cursor object
cur = conn.cursor()
# Create a table
cur.execute('''
CREATE TABLE IF NOT EXISTS students (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER,
grade TEXT
)
''')
# Commit the changes and close the connection
conn.commit()
conn.close()

4. Inserting Data (Create)

To insert data into the table, you use the INSERT INTO SQL command. Let’s add some student records to our students table.

python

Copy code

import sqlite3
# Connect to the database
conn = sqlite3.connect('example.db')
cur = conn.cursor()
# Insert data
cur.execute('''
INSERT INTO students (name, age, grade)
VALUES ('Alice', 21, 'A')
''')
cur.execute('''
INSERT INTO students (name, age, grade)
VALUES ('Bob', 22, 'B')
''')
cur.execute('''
INSERT INTO students (name, age, grade)
VALUES ('Charlie', 23, 'C')
''')
# Commit the changes and close the connection
conn.commit()
conn.close()

5. Fetching Data (Read)

To read data from the table, you use the SELECT SQL command. Let’s fetch all student records from the students table.

python

Copy code

import sqlite3
# Connect to the database
conn = sqlite3.connect('example.db')
cur = conn.cursor()
# Fetch data
cur.execute('SELECT * FROM students')
rows = cur.fetchall()
# Print the results
for row in rows:
print(row)
# Close the connection
conn.close()

6. Updating Data (Update)

To update existing records, you use the UPDATE SQL command. Let’s update Bob’s grade to ‘A’.

python

Copy code

import sqlite3
# Connect to the database
conn = sqlite3.connect('example.db')
cur = conn.cursor()
# Update data
cur.execute('''
UPDATE students
SET grade = 'A'
WHERE name = 'Bob'
''')
# Commit the changes and close the connection
conn.commit()
conn.close()

7. Deleting Data (Delete)

To delete records, you use the DELETE SQL command. Let’s delete Charlie’s record from the table.

python

Copy code

import sqlite3
# Connect to the database
conn = sqlite3.connect('example.db')
cur = conn.cursor()
# Delete data
cur.execute('''
DELETE FROM students
WHERE name = 'Charlie'
''')
# Commit the changes and close the connection
conn.commit()
conn.close()

Conclusion

In this blog, we’ve covered the basics of CRUD operations using SQLite3 in Python such as Create, Read, Update, and Delete with minimum memory and lightweight versatile and easy-to-use database engine that work harmoniously with Python

Where using this you can efficiently manage your SQLite databases in Python and build more sophisticated data-driven applications.