Every day, we encounter a significant amount of data, much of which is structured, especially in industry, academia, and other fields. In this discussion, we will explore relational databases and their implementation using the Python module SQLAlchemy. Additionally, we will learn about the SQLite module. Let’s start with an introduction.

What is an RDBMS?
A relational database is a type of database that organizes data into tables, also known as records, where data is stored in rows and columns. This tabular format allows for different data types to be systematically arranged. An RDBMS (Relational Database Management System) manages these records, enabling efficient storage, retrieval, and manipulation of the data. It also allows for connections between different tables using keys. Common examples of RDBMS include SQL Server, PostgreSQL, IBM DB2, Microsoft Access, and SQLite.

Installing the required Modules

We can install the sqlAlchemy module by using the below command.

pip install  sqlalchemy

We can install the SQLite module by writing the command

pip install pysqlite

And then we will also be installing the pandas’ module also to open the tabular information from a file.

pip install pandas

We will be using the following CSV file to lead data.

#importing the required modules
from sqlalchemy import create_engine 
import pandas as pd

#getting the csv data using the read_csv() function inside the pandas module
data = pd.read_csv('this.csv')

# Create the db engine using create_engine() method
engine = create_engine('sqlite:///:memory:')

# Store the dataframe as a table using to_sql() method
data.to_sql('std_data_table', engine)

# Writing a query to read all the data of the table and executing it using the function read_sql_query()
res = pd.read_sql_query('SELECT * FROM std_data_table', engine)
print('Printing the table:')
print(res) #printing the result

Let us also execute another query.
Example of reading using  sqlAlchemy:

res = pd.read_sql_query('SELECT Name, RollNo, Department FROM std_data_table WHERE YOB=2001', engine)
print(res) 

Output:

Name RollNo Department
0 ABC 4 CSE
1 PQR 2 EEE
2 ORT 6 Civil

Inserting Data using sqlAlchemy

We can also insert the data into the table using the sql.execute() function available in pandas.

#importing the required modules
from sqlalchemy import create_engine 
import pandas as pd
from pandas.io import sql

#getting the csv data using the read_csv() function inside the pandas module
data = pd.read_csv('this.csv')

# Create the db engine using create_engine() method
engine = create_engine('sqlite:///:memory:')

# Store the dataframe as a table using to_sql() method
data.to_sql('std_data_table', engine)

# Inserting another row into the table
sql.execute('INSERT INTO std_data_table VALUES(?,?,?,?,?,?)', engine, params=[(5,'STR',6,'CSE','B',2002)])

# Read from the relational table
res = pd.read_sql_query('SELECT * FROM std_data_table', engine)
print(res)

Output:

index Name RollNo Department Section YOB
0 0 ABC 4 CSE A 2001
1 1 XYZ 5 ECE A 2000
2 2 PQR 2 EEE B 2001
3 3 RST 1 Mech C 2002
4 4 ORT 6 Civil A 2001
5 5 STR 6 CSE B 2002

Delete Data using sqlAlchemy

Let us see an example to delete a data. We can use the sql.execute() function by writing the query to delete the data

#importing the required modules
from sqlalchemy import create_engine 
import pandas as pd
from pandas.io import sql

#getting the csv data using the read_csv() function inside the pandas module
data = pd.read_csv('this.csv')

# Create the db engine using create_engine() method
engine = create_engine('sqlite:///:memory:')

# Store the dataframe as a table using to_sql() method
data.to_sql('std_data_table', engine)

# Deleting the row from the table where roll no is 6 and department is CSE
sql.execute('Delete from std_data_table where RollNo = (?) AND Department=(?) ', engine,  params=[(6,'CSE')])

# Read from the relational table
res = pd.read_sql_query('SELECT * FROM std_data_table', engine)
print(res)

Output:

index Name RollNo Department Section YOB
0 0 ABC 4 CSE A 2001
1 1 XYZ 5 ECE A 2000
2 2 PQR 2 EEE B 2001
3 3 RST 1 Mech C 2002
4 4 ORT 6 Civil A 2001

Inserting Data Using SQLite Module

import sqlite3 #importing the module

connection = sqlite3.connect("students_details.db") #connecting to the database named students_details
cursor = connection.cursor() #forming the connection and to update the records
 
#query to create a table named stds with rows Roll_no, name, class, and section as columns
query = """CREATE TABLE stds (Roll_no INTEGER PRIMARY KEY, name VARCHAR(20), class INTEGER, Section VARCHAR(2));"""
cursor.execute(query) #executing the above query

#writing the queries to insert the rows into the table
query1 = """INSERT INTO stds VALUES ('1','ABC','4','A');"""
query2 = """INSERT INTO stds VALUES ('2','XYZ','4','A');"""
query3 = """INSERT INTO stds VALUES ('3','PQR','4','B');"""

#executing the queries
cursor.execute(query1)
cursor.execute(query2)
cursor.execute(query3)

#commiting the changes done by adding the rows
connection.commit()
connection.close() #closing the connection

In this example, we first created the connection with the  database and then created the table by executing the SQL query. After this, the queries return the insert the rows into the table created, and then executing the queries. Finally, we are committing the changes done and closing the connection.

Reading Data Using the SQLite Module

Now, we will look at how to read data using the SQLite module in Python. We will follow a similar procedure to establish a connection as demonstrated in the previous example. Then, we will write a query to select the desired rows. Finally, we will execute the query using either the fetchall() or fetchone() function. The fetchall() function retrieves all the rows from the table, while the fetchone() function retrieves rows one at a time.

Here’s a detailed step-by-step explanation and example code:

1. Establish a Connection

First, establish a connection to the SQLite database using the sqlite3 module.

import sqlite3 #importing the module

connection = sqlite3.connect("students_details.db")#connecting to the database named students_details
cursor = connection.cursor()#forming the connection and to update the records

cursor.execute("SELECT * FROM stds") #execting the query to select all the data from the table
print("The student details : ")
 
#We use .fetchmany() to load optimal no of rows and overcome memory issues in case of large datasets
#In case the number of rows in the table is small, you can use the fetchall() method to fetch all rows from the database table
 
result = cursor.fetchall()  #using the fetchall() function to get all the rows of the table
for res in result: #printing the rows feteched 
    print(res)

Output:

The student details :
(1, ‘ABC’, 4, ‘A’)
(2, ‘XYZ’, 4, ‘A’)
(3, ‘PQR’, 4, ‘B’)

Conclusion

In this comprehensive guide to Python relational databases, we have explored the fundamentals of relational databases, their significance in managing structured data, and their implementation using Python. We covered essential concepts such as relational database management systems (RDBMS), SQLAlchemy for database operations, and SQLite for lightweight database management. By integrating Python with relational databases, developers can efficiently handle large volumes of data, perform complex queries, and maintain data integrity. Whether you are building small-scale applications or enterprise-level systems, understanding and leveraging relational databases in Python is crucial for effective data management and manipulation.