Getting Started with SQLAlchemy: Basic CRUD Operations

Photo by Maxime Lebrun on Unsplash

In this tutorial, we will explore the fundamental concepts of SQLAlchemy and guide you through setting it up for your Python projects. We will also demonstrate how to perform basic CRUD (Create, Read, Update, Delete) operations using SQLAlchemy.

Prerequisites

Before we dive into the world of SQLAlchemy, make sure you have the following prerequisites:

1. Python: SQLAlchemy is a Python library, so you’ll need Python installed on your system. You can download Python from python.org.
2. MySQL Database: We’ll be using MySQL as our database system. Ensure you have MySQL installed and running. You can download MySQL from mysql.com.
3. SQLAlchemy: Install SQLAlchemy using pip:

pip install sqlalchemy

Setting up the Database

We’ll start by creating a simple database and defining tables using SQLAlchemy. Open your Python code editor and follow along.

# Import necessary modules
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import declarative_base, sessionmaker, relationship

# Create an engine to connect to your MySQL database
engine = create_engine("mysql+pymysql://<user>:<password>@localhost/<db_name>?charset=utf8mb4")

# Establish a connection
connection = engine.connect()

# Create a base class for declarative class definitions
Base = declarative_base()
Base.metadata.bind = engine

# Create a session to interact with the database
Session = sessionmaker(bind=engine)
session = Session()

# Define your database schema
class Users(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(length=10))
    age = Column(Integer)

class Products(Base):
    __tablename__ = 'products'
    id = Column(Integer, primary_key=True)
    product_name = Column(String(length=10))
    user_id = Column(Integer, ForeignKey('users.id'))

In this code, we’ve created a connection to our MySQL database and defined two tables: Users and Products with their respective columns.

Performing CRUD Operations

Now that we have our database and tables set up, let’s explore how to perform basic CRUD operations using SQLAlchemy.

Creating a User

To add a user to the database, use the add_users function:

def add_users(user_name, user_age):
    row = Users(name=user_name, age=user_age)
    session.add(row)
    session.commit()
    print("User added successfully!")
    session.close()

Creating a Product

To add a product, use the add_product function:

def add_product(product_name):
    row = Products(product_name=product_name)
    session.add(row)
    session.commit()
    print("Product added successfully!")
    session.close()

Reading Users

To retrieve all users from the database, use the read_users function:

def read_users():
    users = session.query(Users).all()
    session.close()
    return users

Updating a User

To update a user’s information, use the update_user function:

def update_user(id, new_name, new_age):
    user = session.get(Users, id)
    if user:
        user.name = new_name
        user.age = new_age
        session.commit()
        print("User updated successfully")
    else:
        print("User not found")
    session.close()

Deleting a User

To delete a user, use the delete_user function:

def delete_user(id):
    row = session.query(Users).filter_by(id=int(id)).first()
    if row:
        session.delete(row)
        session.commit()
        print("User deleted successfully!")
    else:
        print("User not found")
    session.close()

Putting it All Together

Now that we have defined our CRUD operations, you can use them as needed in your Python application to interact with the MySQL database.

# Example usage
add_users("Alice", 30)
add_product("Sample Product")
users = read_users()
for user in users:
    print(user.id, user.name, user.age)
update_user(1, "Haley", 25)
delete_user(1)

This code demonstrates how to create, read, update, and delete data using SQLAlchemy.

Conclusion

In this tutorial, we’ve covered the basics of SQLAlchemy and shown you how to perform CRUD operations with a MySQL database. SQLAlchemy is a powerful tool for database interaction in Python applications, and you can build upon this foundation to create more complex database-driven applications.

For more advanced use cases and additional features, refer to the SQLAlchemy documentation.

Happy coding!