在python中可以通过写类生成数据库对应的表结构, 生成对象来为表加入数据, 这就是orm框架的本质思想. 其中sqlalchemy就是实现这种框架的一个python模块

使用sqlalchemy对单表进行操作 :

#!/usr/bin/env python
#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Index, UniqueConstraint
from sqlalchemy.orm import sessionmaker

# 传入数据库名, 用户名, 密码以及ip, 建立连接数据库的引擎engine(负责与数据库驱动程序进行交互), 加入echo=True则会打印创建过程
engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/blog_db?charset=utf8")

# 通过继承sqlalchemy生成的基类来建立与数据库表对应的类(一个类在数据库中表现为一张表)
Base = declarative_base()


# 自定义类, 本质就是设置表中字段格式
class User(Base):
    # 设置表名, 固定格式
    __tablename__ = "users"

    # 默认就是自增长, 不加autoincrement=True参数也可
    id = Column(Integer, primary_key=True, autoincrement=True)
    # 创建name字段的同时创建普通索引, 该字段不能为空, String类型对应数据库中的varchar字段
    name = Column(String(32), nullable=True, index=True)
    pwd = Column(String(16))
    address = Column(String(16))

    # 创建联合索引
    __table_args__ = (
        UniqueConstraint(name, pwd, name="n_unique_p"),
    )


# 根据已有的所有类来创建对应的表
Base.metadata.create_all(engine)

# 根据引擎建立mysql与程序之间的会话
MySession = sessionmaker(engine)
session = MySession()

# 设置插入的一条数据
u1 = User(name="test-01", pwd="111")
# 插入数据并提交
session.add(u1)
session.commit()

# 插入多条数据
session.add_all([
    User(name="test-02", pwd="222"),
    User(name="test-03", pwd="333")
])
session.commit()

# 查询指定表的第一条数据
# 根据要查询的表对应的类生成sql语句Query对象
sql_obj = session.query(User)
print(type(sql_obj), sql_obj)

# 执行查询结果, 返回的是查询的表对应的对象
result_first = sql_obj.first()
print("result_first ---> \n", result_first.name)

# 加入过滤条件查询, filter中只能加入条件, filter_by只能及诶如键值对过滤, 返回表对象
result_filter = sql_obj.filter(User.name == "test-02").first()
print("result_filter --->\n", result_filter.name)

# 获得所有查询结果, 返回的是列表, 即使只有一个对象也不例外
result_all = sql_obj.all()
print("result_all --->\n", result_all)
for obj in result_all:
    print(obj.id, obj.name)

# 将查询结果排序显示
result_order = sql_obj.order_by(User.id)
print("result_order --->\n", result_order)
for obj in result_order:
    print(obj.id, obj.name)

# 统计查询个数
result_num = sql_obj.count()
print("result_num --->\n", result_num)
print(result_num)

# 取反查询
result_oth = sql_obj.filter(~User.name == "test-02").all()
print("result_oth --->\n", result_oth)
for obj in result_oth:
    print(obj.id, obj.name)

# 与或查询
from sqlalchemy import or_

result_or = sql_obj.filter(or_(User.id == 1, User.id == 2)).all()
print("result_or --->\n", result_or)
for obj in result_or:
    print(obj.id, obj.name)

# 删除指定条件的数据
session.query(User).filter(User.id > 2).delete()
session.commit()

# 修改指定条件的数据
session.query(User).filter(User.id == 1).update({"name": "test-up"})
session.commit()

# 关闭会话
session.close()

使用sqlalchemy关于一对多类型的表的操作:

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Index, UniqueConstraint, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship

engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/blog_db?charset=utf8")
Base = declarative_base()


class Father(Base):
    __tablename__ = "father"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(16), nullable=True)


class Son(Base):
    __tablename__ = "son"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(20))
    # 创建外键
    son_id_father = Column(Integer, ForeignKey("father.id"))

    # 设置Son与Father之间的双向关系, 第一个参数为类名, backref表示反向的名称为son_name
    father_name = relationship("Father", backref="son_name")


# Base.metadata.create_all(engine)

MySession = sessionmaker(engine)
session = MySession()

# 为father设置数据
f1 = Father(name="peter")
f2 = Father(name="nick")

# 为son设置数据
s1 = Son(name="peter-son-01")
s2 = Son(name="peter-son-02")
s3 = Son(name="peter-son-03")
s4 = Son(name="nickr-son-01")

# 利用relationship设置f1, f2的对应关系
f1.son_name = [s1, s2, s3]
f2.son_name = [s4]

session.add_all([f1, f2])
session.commit()

# 联表查询
sql_obj = session.query(Father.name.label("father_name"), Son.name.label("son_name")).join(Son)
print(sql_obj)
result = sql_obj.all()
for obj in result:
    print(obj)
session.commit()

使用sqlalchemy关于多对多类型的表的操作:

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Index, UniqueConstraint, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship

engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/blog_db?charset=utf8")
Base = declarative_base()


class ManToBook(Base):
    __tablename__ = "mantobook"

    id = Column(Integer, primary_key=True, autoincrement=True)
    book_id = Column(Integer, ForeignKey("book.id"))
    man_id = Column(Integer, ForeignKey("man.id"))


class Book(Base):
    __tablename__ = "book"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(16), nullable=True)

    # 建立Book与Man之间双向的关系, secondary表示根据ManToBook确定其双向的具体关系, backref表示反向的名称为book_name
    man_name = relationship("Man", secondary=ManToBook.__table__, backref="book_name")


class Man(Base):
    __tablename__ = "man"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(20))


Base.metadata.create_all(engine)

MySession = sessionmaker(engine)
session = MySession()

# 设置数据
b1 = Book(name="python")
b2 = Book(name="java")
b3 = Book(name="c#")

m1 = Man(name="lisa")
m2 = Man(name="peter")
m3 = Man(name="nick")

# 利用relationship设置对应关系
# python这本书同时有lisa, peter和nick在读
b1.man_name = [m1, m2, m3]
# lisa 同时拥有python和java
m1.book_name = [b1, b2]

session.add_all([b1, m1])
session.commit()