Working with Databases in Python

This tutorial covers how to work with databases in Python, introducing Object-Relational Mappers (ORMs) like SQLAlchemy, executing raw SQL queries, and providing an example of a simple database application.

1. Object-Relational Mappers (ORMs)

Object-Relational Mappers (ORMs) allow you to interact with databases using Python objects instead of writing raw SQL queries. One popular ORM for Python is SQLAlchemy. You can install it using pip:

pip install SQLAlchemy

Setting Up SQLAlchemy

Here’s how to set up SQLAlchemy to connect to a SQLite database:

from sqlalchemy import create_engine

# Create an engine to the SQLite database
engine = create_engine('sqlite:///example.db')

2. Executing SQL Queries

While ORMs are great for simplifying database interactions, you can also execute raw SQL queries if needed. Here’s how you can do it using SQLAlchemy:

from sqlalchemy import text

# Executing a raw SQL query
with engine.connect() as connection:
    result = connection.execute(text("SELECT * FROM users"))
    for row in result:
        print(row)

3. Example: A Simple Database Application

Let’s build a simple database application using SQLAlchemy to manage a list of users.

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# Create a base class for our classes
Base = declarative_base()

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

# Create the SQLite database
engine = create_engine('sqlite:///example.db')
Base.metadata.create_all(engine)

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

# Adding a new user
new_user = User(name='Alice', age=30)
session.add(new_user)
session.commit()

# Querying users
users = session.query(User).all()
for user in users:
    print(f'User: {user.name}, Age: {user.age}')

This code sets up a SQLite database, defines a User class, adds a user, and retrieves all users from the database.

4. Summary

In this tutorial, we covered how to work with databases in Python using ORMs like SQLAlchemy, executing raw SQL queries, and provided an example of a simple database application. Mastering these skills allows you to effectively manage data in your Python applications.

0 Interaction

843 Views

31 Likes