Python Relational Database

We all deal with a lot of data every day. Most of it, especially in industry, academic, and other domains, the data is structured. We will be discussing the relational database and its implementation using the Python module, namely, sqlAlchemy. We will also learn about the module SQLite. Let us begin with the introduction.

What is an RDBMS?

A relational database is a database that stores the data in tabular form, also called records. In this, the different data types will be in rows and columns. The RDBMS (relational database management system) is used to handle these records and also connect them using different keys. Some of the examples of RDBMS include SQL Server, PostgreSQL, IBM DB2, Microsoft Access, SQLite, etc.

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.

Reading Data using sqlAlchemy

We will be reading the data in our CSV file by first creating the database engine using the sqlAlchemy. Then we convert it into the SQL table and use the read_sql_query method from the pandas module. Let us see the below example for further understanding.

Example of reading using sqlAlchemy:

#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('stds.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

Output:

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:

Inserting Data using sqlAlchemy

We can also insert the data into the table using the sql.execute() function available in pandas.
Example of inserting data using sqlAlchemy:

#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('C:\\Users\\Ch Gayathri Lakshmi\\Downloads\\stds.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:

Delete Data using sqlAlchemy

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

Example of deleting data using sqlAlchemy:

#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('C:\\Users\\Ch Gayathri Lakshmi\\Downloads\\stds.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:

Inserting Data Using SQLite Module

Another module used in Python used for handling the relational database is SQLite. Similar to the sqlAlchemy, we connect to the database and execute the queries. Let us see some examples and understand further.

Example of inserting data using SQLite:

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 SQLite Module

Now we will see the reading operations by following the similar procedure as in the above example to form the connection. Then write the query is to select the required rows. And then execute the query by either using the fetchall() or fetchone() function. The fetchall() is used to get all the rows of the table and fetchone() is used to get the rows one by one.

Example of reading using fetchall() in sqlite:

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:

Example of reading using fetchone() in sqlite:

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 first row:")
 
'''
using the fetchone() to get one record as a tuple 
It can also be used to get the rows one by one, 
If there are no more records then it returns None
'''
one_row = cursor.fetchone() 
print(one_row) 

Output:

Conclusion

In this article, we learned about relational databases and RDBMS. Then we discussed the implementation of these using the Python modules sqlAlchemy and SQLite and saw different important operations.

Hope all the concepts covered are understood and you learned something new. Happy learning!