使用SQLAlchemy查询数据

2023-04-05 00:00:00 sqlalchemy 查询数据

安装SQLAlchemy

pip install SQLAlchemy

连接到数据库

from sqlalchemy import create_engine

# 连接数据库，格式为：数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名
engine = create_engine('mysql+pymysql://user:password@localhost:3306/database_name', echo=True)

创建表格

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

metadata = MetaData()

users = Table('users', metadata,
              Column('id', Integer, primary_key=True),
              Column('name', String(50)),
              Column('email', String(50)),
              Column('password', String(50))
              )

metadata.create_all(engine)

插入数据

from sqlalchemy import insert

conn = engine.connect()

conn.execute(users.insert(), [
    {'name': 'pidancode.com', 'email': 'pidancode@gmail.com', 'password': '123456'},
    {'name': '皮蛋编程', 'email': 'pidan@163.com', 'password': '654321'}
])

查询数据

from sqlalchemy import select

result = conn.execute(select([users.c.name, users.c.email]).where(users.c.name == 'pidancode.com'))

for row in result:
    print(row)

输出结果为：

('pidancode.com', 'pidancode@gmail.com')

更新数据

from sqlalchemy import update

conn.execute(users.update().where(users.c.name == 'pidancode.com').values(email='pidan@126.com'))

删除数据

from sqlalchemy import delete

conn.execute(users.delete().where(users.c.name == '皮蛋编程'))

完整代码如下：

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

metadata = MetaData()

users = Table('users', metadata,
              Column('id', Integer, primary_key=True),
              Column('name', String(50)),
              Column('email', String(50)),
              Column('password', String(50))
              )

engine = create_engine('mysql+pymysql://user:password@localhost:3306/database_name', echo=True)

metadata.create_all(engine)

conn = engine.connect()

conn.execute(users.insert(), [
    {'name': 'pidancode.com', 'email': 'pidancode@gmail.com', 'password': '123456'},
    {'name': '皮蛋编程', 'email': 'pidan@163.com', 'password': '654321'}
])

result = conn.execute(select([users.c.name, users.c.email]).where(users.c.name == 'pidancode.com'))

for row in result:
    print(row)

conn.execute(users.update().where(users.c.name == 'pidancode.com').values(email='pidan@126.com'))

conn.execute(users.delete().where(users.c.name == '皮蛋编程'))

conn.close()

相关文章