Python: Using SQLAlchemy to perform database operations

-

In this article, we will see a simple approach for performing database operations using SqlAlchemy in Python programming language. Python is a versatile and very powerful programming language that is widely used in various fields such as web development, data analysis, artificial intelligence, scientific computing, automation, etc. While working on any of the fields in Python, we need to access the database and perform operations on it. It is highly recommended that the database operations should be performed using a very powerful database access object model. This is where SQLAlchemy comes into the picture.

SQLAlchemy is a powerful SQL toolkit and Object-Relational Mapping (ORM) library for Python. It provides a flexible and efficient way to interact with relational databases using Python objects. It provides a full suite of enterprise-level persistence patterns, designed for efficient and high-performing database access To understand and implement the code for this article, you must have hands-on experience with Python Object Oriented Programming programming.

Figure 1 shows the approach for the implementation of database operations using SqlAlchemy



Figure 1: The Python with SqlAlchemy


The code for this article is developed using PyCharm and Macbook. In this article,  the MySQL Database is used. The database named Home is created that contains the Expenses table with the fields as shown in Listing 1.

Create Database Home;

use Home;

Create Table Expenses (
 ExpensesId int Primary Key,
 ExpensesType varchar(100) Not Null,
 VendorName varchar(100) Not Null,
 AmountPaid int not null,
 PaidBy varchar(100) Not Null
);


Listing 1: The Script for Home Database and Expenses Table


Make sure that the following packages should be installed

pip install sqlalchemy mysql pymysql 

Step 1: Open PyCharm and create a new Python project named pythonProject.  In the project add a new Python file named expensesmodel.py. In this file, we will add the code for establishing the mapping to the Expenses table. Listing 2 shows the code for defining mapping.



# define the expenses model

from sqlalchemy import Column, Integer, String

from main import Base
# The Model class is a subclass of the Base class, which is the declarative base class
# provided by SQLAlchemy. This class represents the Expenses table in the database.

class Expenses(Base):
    __tablename__ = 'Expenses'
    ExpensesId = Column(Integer, primary_key=True)
    ExpensesType = Column(String(100), nullable=False)
    VendorName = Column(String(100), nullable=False)
    AmountPaid = Column(Integer, nullable=False)
    PaidBy = Column(String(100), nullable=False)

    def __repr__(self):
        return "<Expenses(ExpensesId='%s', ExpensesType='%s', VendorName='%s', 
        "AmountPaid='%s', PaidBy='%s')>" % (
            self.ExpensesId, self.ExpensesType, self.VendorName, self.AmountPaid, 
       self.PaidBy)

Listing 2: Mapping with Expenses Table

As shown in Listing 2,  the Expenses class is created that defines mapping with each column of the Expenses table using the Column() method imported from SqlAlchemy. For each mapped column the datatype and constraints are defined to make sure that while performing database operations then constraints will be followed.

Step 2: In the project add a new Python file named dbOperations.py. In this file, we will add code for the class that contains methods for performing database operations. The name of the class will be DbOperations. In this class, we will have the parameterized constructor of type sessions. This object is used to handle database interaction to perform CRUD operations. The session object has the following methods:

  • add(): to add a new record in the table
  • commit(): to save the transaction
  • query(): to query the table to filter record(s) based on criteria.
  • delete(): to delete the record
Listing 3 shows the code for the DbOperations class.


 from expensesmodel import Expenses

# define the db operations class

class DbOperations:
    # code for methods to perform CRUD operations on Expenses table using Expenses class
    def __init__(self, session):
        self.session = session

    def add_expense(self, expense):
        self.session.add(expense)
        self.session.commit()
        return expense

    def get_expense(self, expense_id):
        expense = self.session.query(Expenses).filter_by(ExpensesId=expense_id).first()
        if expense is None:
            raise Exception("No expense found with id: " + str(expense_id))
        return expense

    def get_all_expenses(self):
        expenses = self.session.query(Expenses).all()
        return expenses

    def update_expense(self, expense_id, new_expense):
        expense = self.session.query(Expenses).filter_by(ExpensesId=expense_id).first()
        if expense is None:
            raise Exception("No expense found with id: " + str(expense_id))
        expense.ExpensesType = new_expense.ExpensesType
        expense.VendorName = new_expense.VendorName
        expense.AmountPaid = new_expense.AmountPaid
        expense.PaidBy = new_expense.PaidBy
        self.session.commit()
        return expense

    def delete_expense(self, expense_id):
        expense = self.session.query(Expenses).filter_by(ExpensesId=expense_id).first()
        if expense is None:
            raise Exception("No expense found with id: " + str(expense_id))
        self.session.delete(expense)
        self.session.commit()
        return expense

Listing 3: The DbOperations class to perform CRUD operations

Step 3: In the main.py we will access the Expenses and DbOperations classes to complete the example code.  To perform the database operations we need the following objects:

  •  create_engine from sqlalchemy
    • This creates an instance of the Engine class that represents the connection to the database and it manages the process of connecting to the database server to issue commands and handle transactions.
  • sessionmaker function from the sqlalchemy.orm
    • This function returns a new Session class. This class manages conversations with the database to perform CRUD operations. 
    • This function accepts the bind parameter. This parameter is used to connect the Session to a specific Engine that has the database connection with it.
  • declarative_base from the sqlalchemy.orm 
    • This function returns a new Base class. This Base class is used as a base class by all classes that are used to define mapping with the database table.

 Listing 4 shows the code in main.py file that performs final CRUD operations with hard-coded values.


from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import declarative_base


# Define the connection string
DATABASE_CONNECTION = ('mysql+pymysql://root:root123@localhost/Home')

Base = declarative_base()

engine = create_engine(DATABASE_CONNECTION)

Session = sessionmaker(bind=engine)

if __name__ == '__main__':
    from expensesmodel import Expenses
    from dbOperations import DbOperations

    Base.metadata.create_all(engine)
    session = Session()
    # Create an instance of the DbOperations class and pass the session object to it.
    db_operations = DbOperations(session)
    # Create an instance of the Expenses class and add it to the database.
    expense = Expenses(ExpensesId=102, ExpensesType='Food', VendorName='Ganesh', AmountPaid=5000, PaidBy='Mahesh')
    # Add the expense to the database using the add_expense() method of the DbOperations class.
    db_operations.add_expense(expense)
    print(db_operations.get_all_expenses())
    # Get the expense with id 102 using the get_expense() method of the DbOperations class.
    expense = db_operations.get_expense(102)
    expense.AmountPaid = 6000
    db_operations.update_expense(102, expense)
    print(db_operations.get_all_expenses())

    # Delete the expense with id 101 using the delete_expense() method of the DbOperations class.
    db_operations.delete_expense(102)
    print(db_operations.get_all_expenses())
    # Close the session and dispose of the engine.
    session.close()
    engine.dispose()

Listing 4: The main.py with the code for CRUD operations with values

 
I have added hard-coded values, but you can use the input() function to accept inputs from the end-user to perform CRUD operations.  


Conclusion: Python the most popular language has the SQlAlchemy ORM for performing database operations easily.

 

  




Popular posts from this blog

Uploading Excel File to ASP.NET Core 6 application to save data from Excel to SQL Server Database

-
Image
Recently, while conducting a session on ASP.NET Core 6, my client raised a query regarding uploading excel files to the ASP.NET Core MVC application and then further saving data from excel to the SQL Server Database table. I gave him the solution and then thought to write a post on it. As we all know, the Excel Workbook is one of the most heavily used files to save data. But to persist data it is recommended that it should be stored in an SQL Server database.  If the data is stored in an SQL Server database it can be queried easily.  Figure 1, explains an implementation of the application which we are going to discuss in the article. Figure 1: The ASP.NET Core That accepts Excel files and then further store data from Excel Files in SQL Server Database The   ExcelDataReader.DataSet Library To read excel files in ASP.NET Core applications, we need to use the ExcelDataReader.DataSet package. This is a lightweight and fast library for reading Excel files in .NET applications....
Read more

ASP.NET Core 6: Downloading Files from the Server

-
Image
In this article, we will implement the file downloading feature using ASP.NET Core 6 application. In real-world applications, we can have various files like reports, spreadsheets, images, PDF documents stored on the server. (Note that all cloud-hosted applications prefer to store these files on the storage services, e.g. Blob, S3, etc.). But what if the client prefers to store files on the separate file server mapped to the web application host server? Then, in that case, we may need to download these files from the file server. In this article, we will implement exactly the same thing.   Step 1: Open Visual Studio 2022 and create a new ASP.NET Core MVC Application targetted to .NET 6. Name this application as Core6_FileDownload. In this project add a new folder and name it as ServerFiles. In this file add some images. (You can add Excel, PDF, Word, Files). Step 2: Since we need to read the ServerFiles folder using the ASP.NET Core application, we need to add the IFi...
Read more

ASP.NET Core 6: Using Entity Framework Core with Oracle Database with Code-First Approach

-
Image
In this article, we will see how to use Oracle Database with Entity Framework Core with Code-First Approach. The reason behind writing a post on this is, recently while conducting training, one of my students raised the query regarding the EF Core with Oracle. Since Oracle is one of the most powerful databases, various enterprise applications are using it for the past several years and since the .NET Core is the most popular technology for building modern apps, the scenarios for accessing the Oracle database in .NET Core apps the use Entity Framework Core is common. Oracle Data Provider for .NET Entity Framework Core The Oracle Data Provider for .NET i.e. ODP.NET is used to access data from the Oracle database in the .NET Core apps using Entity Framework Core. This uses the Oracle.EntityFrameworkCore.dll, a managed library. This data provider allows using Code-First and Database-First approaches to perform Read/Write Operations with Oracle database.    Implementation To implem...
Read more