SqlAlchemy 使用方法

简介

SQLAlchemy 是一个用于 Python 的数据库工具包，它提供了一种方便的方式来连接和操作数据库。它支持多种数据库，包括 MySQL、PostgreSQL、Oracle 和 SQL Server。

安装

pip install sqlalchemy

连接数据库

要连接数据库，您需要创建一个 Engine 对象。Engine 对象代表与数据库的连接。

from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root:password@localhost/mydb")

创建数据库表

from sqlalchemy import Table, Column, Integer, String

metadata = MetaData()

users = Table(
    "users",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("name", String(255)),
    Column("email", String(255)),
)

metadata.create_all(engine)

插入数据

Insert 语句使用方法

from sqlalchemy import insert

stmt = insert(users).values(name="John Doe", email="johndoe@example.com")

engine.execute(stmt)

查询数据

要查询数据，您可以使用 Select 语句

from sqlalchemy import select

stmt = select(users).where(users.c.name == "John Doe")

results = engine.execute(stmt)

for row in results:
    print(row)

更新数据

要更新数据，您可以使用 Update 语句

from sqlalchemy import update

stmt = update(users).where(users.c.name == "John Doe").values(email="johndoe@example.com")

engine.execute(stmt)

删除数据

要删除数据，您可以使用 Delete 语句

from sqlalchemy import delete

stmt = delete(users).where(users.c.name == "John Doe")

engine.execute(stmt)

示例代码

from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, insert, select, update, delete

engine = create_engine("mysql+pymysql://root:password@localhost/mydb")

metadata = MetaData()

users = Table(
    "users",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("name", String(255)),
    Column("email", String(255)),
)

metadata.create_all(engine)

stmt = insert(users).values(name="John Doe", email="johndoe@example.com")

engine.execute(stmt)

stmt = select(users).where(users.c.name == "John Doe")

results = engine.execute(stmt)

for row in results:
    print(row)

stmt = update(users).where(users.c.name == "John Doe").values(email="johndoe@example.com")

engine.execute(stmt)

stmt = delete(users).where(users.c.name == "John Doe")

engine.execute(stmt)

输出：

(1, 'John Doe', 'johndoe@example.com')