How to Use SQLAlchemy with Python

SQLAlchemy is an open-source SQL toolkit and Object-Relational Mapping (ORM) system for Python. It allows you to interact with databases in an efficient and Pythonic way. This tutorial will guide you through the installation and basic usage of SQLAlchemy with practical examples.

Prerequisites

• Python 3.x installed on your machine.
• A basic understanding of Python programming language.
• A running database server (e.g., PostgreSQL, MySQL, SQLite).

1. Installing SQLAlchemy

To install SQLAlchemy, open your terminal and run:

pip install sqlalchemy

If you are connecting to a specific database, you might need to install the corresponding database driver as well. For example:

• For PostgreSQL:

  pip install psycopg2

• For MySQL:

  pip install pymysql

• For SQLite: No extra installation is needed as it’s included with Python standard library.

2. Creating a Database Connection

To start using SQLAlchemy, you need to create an engine that maintains the connection to your database:

from sqlalchemy import create_engine

# Example for SQLite
engine = create_engine('sqlite:///example.db')

# Example for PostgreSQL
e = create_engine('postgresql://username:password@host:port/dbname')

Make sure to replace the connection string with your actual database credentials.

3. Defining a Model

SQLAlchemy uses classes to define the structure of your database tables. Here’s an example:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)

This code defines a User model that corresponds to a table named users.

4. Creating the Database Table

To create the table in the database, use the following command:

Base.metadata.create_all(engine)

This command creates all tables defined in the model.

5. Adding Records

To add records to your database, create a session and add new entries:

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)
session = Session()

new_user = User(name='Alice', age=30)
session.add(new_user)
session.commit()

This code creates a new user and commits the transaction to the database.

6. Querying Records

You can query records using the session object as well:

all_users = session.query(User).all()
for user in all_users:
    print(user.name, user.age)

7. Updating Records

To update a record, first retrieve it and then modify the attributes:

user = session.query(User).filter_by(name='Alice').first()
user.age = 31
session.commit()

8. Deleting Records

Delete a record using:

session.delete(user)
session.commit()

Where user is the instance of the User class you want to delete.

9. Conclusion

By following this tutorial, you have learned the basics of using SQLAlchemy for database operations in Python. With its powerful ORM capabilities, SQLAlchemy simplifies complex database interactions and makes data management easier. Continue exploring SQLAlchemy’s extensive features and documentation to further enhance your database applications.

About the Author

Dorian Kane

Author

View All Posts