Python SQLAlchemy

For SQL database operations, SQLAlchemy is a powerful and widely used library in Python. It provides various ways to interact with databases, including creating tables, querying, inserting, updating, and deleting data. Below is a detailed SQLAlchemy tutorial, including example code for basic database connection, table creation, as well as querying, inserting, updating, and deleting data.

Introduction

SQLAlchemy is a powerful Python library for database operations. Whether it’s simple data storage or complex data management, SQLAlchemy provides multiple methods for handling databases. This article will comprehensively introduce the basic usage of SQLAlchemy and example code for various operations.

Installing SQLAlchemy

Before using SQLAlchemy, it needs to be installed.

Install it using the following command:

pip install sqlalchemy

Connecting to the Database

First, connect to the database.

from sqlalchemy import create_engine

# Create a database engine
engine = create_engine('sqlite:///my_database.db', echo=True)

# Create a database in memory
# engine = create_engine('sqlite:///:memory:', echo=True)

This code creates a database engine, connecting to the SQLite database, where the echo=True parameter is used to output SQL query statements in the terminal.

Defining Table Structure

Next, create a data table.

from sqlalchemy import Table, Column, Integer, String, MetaData

metadata = MetaData()

# Create a data table
users = Table('users', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String),
    Column('age', Integer)
)

metadata.create_all(engine)

This code uses SQLAlchemy to define a table named users, which includes three fields: id, name, and age.

Inserting Data

# Inserting data
conn = engine.connect()

insert_query = users.insert().values(name='Alice', age=25)
conn.execute(insert_query)

insert_data = [
    {'name': 'Bob', 'age': 30},
    {'name': 'Charlie', 'age': 22}
]

conn.execute(users.insert(), insert_data)

This code queries and prints all data in the users table.

Updating Data

# Updating data
update_query = users.update().where(users.c.id == 1).values(name='Alex')
conn.execute(update_query)

This example demonstrates how to update data in the table.

Deleting Data

# Deleting data
delete_query = users.delete().where(users.c.id == 2)
conn.execute(delete_query)

This code demonstrates how to delete data from the table.