Python-SQLAlchemy的使用

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件。
MySQL是一种关系数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。之前我们学习了如何使用PyMySQL，接下来记录下在Python中使用SQLAlchemy与PyMySQL结合实现与MySQL相连接：

下载SQLAlchemy

pip install sqlalchemy

创建表

import sqlalchemy
import sqlalchemy.ext.declarative
import sqlalchemy.orm

# 初始化数据库连接:root用户名，123456为密码，localhost本机地址，pythondb数据库名称,charset=utf8用于支持插入中文
engine = sqlalchemy.create_engine("mysql+pymysql://root:123456@localhost/pythondb?charset=utf8")

# 创建orm基类
Base = sqlalchemy.ext.declarative.declarative_base()

class User(Base):
    __tablename__ = "user"
    id = sqlalchemy.Column(sqlalchemy.Integer,primary_key = True)
    name = sqlalchemy.Column(sqlalchemy.String(32))
    password = sqlalchemy.Column(sqlalchemy.String(64))

# 创建表结构
Base.metadata.create_all(engine)

外键关联

• ForeignKey与relationship

import sqlalchemy
import sqlalchemy.orm
import sqlalchemy.ext.declarative

engine = sqlalchemy.create_engine("mysql+pymysql://root:123456@localhost/pythondb?charset=utf8")

Base = sqlalchemy.ext.declarative.declarative_base()

class Student(Base):
    __tablename__ = "student"
    id = sqlalchemy.Column(sqlalchemy.Integer,primary_key=True)
    name = sqlalchemy.Column(sqlalchemy.String(32))

class Record(Base):
    __tablename__ = "record"
    id = sqlalchemy.Column(sqlalchemy.Integer,primary_key=True)
    record = sqlalchemy.Column(sqlalchemy.Integer)
    # 添加外键
    name_id = sqlalchemy.Column(sqlalchemy.Integer,sqlalchemy.ForeignKey("student.id"))
    # 建立关系
    student = sqlalchemy.orm.relationship("Student",backref="student_record")

Base.metadata.create_all(engine)

Session = sqlalchemy.orm.sessionmaker(bind=engine)
session = Session()

#===================插入数据===================
s1 = Student(name = "laowang")
s2 = Student(name = "laozhang")
s3 = Student(name = "xiaoma")

r1 = Study_Record(record = 90,name_id = 1)
r2 = Study_Record(record = 90,name_id = 2)
r3 = Study_Record(record = 85,name_id = 3)

session.add_all([s1,s2,s3,r1,r2,r3])

data = session.query(Student).filter(Student.name == "laowang").first()
print(data.student_record)      # 获取laowang的record信息,若没有relationship，将无法获取到
session.commit()

• 多对多关系

import sqlalchemy
import sqlalchemy.orm
import sqlalchemy.ext.declarative

engine = sqlalchemy.create_engine("mysql+pymysql://root:123456@localhost/pythondb?charset=utf8")
Base = sqlalchemy.ext.declarative.declarative_base()

m_t_m = sqlalchemy.Table("m_t_m_table",Base.metadata,
            sqlalchemy.Column("books_id",sqlalchemy.Integer,sqlalchemy.ForeignKey("books.id")),
            sqlalchemy.Column("authors_id",sqlalchemy.Integer,sqlalchemy.ForeignKey("authors.id"))
            )

class Books(Base):
    __tablename__ = "books"
    id = sqlalchemy.Column(sqlalchemy.Integer,primary_key=True)
    name = sqlalchemy.Column(sqlalchemy.String(32))
    authors = sqlalchemy.orm.relationship("Authors",secondary=m_t_m,backref="books")

class Authors(Base):
    __tablename__ = "authors"
    id = sqlalchemy.Column(sqlalchemy.Integer,primary_key=True)
    name = sqlalchemy.Column(sqlalchemy.String(64))

Base.metadata.create_all(engine)

Session = sqlalchemy.orm.sessionmaker(bind=engine)
session = Session()

# 插入数据
b1 = Books(name="Python核心编程")
b2 = Books(name="Python基础编程")
b3 = Books(name="Python网络编程")

a1 = Authors(name="老王")
a2 = Authors(name = "老张")

b1.authors = [a1]
b2.authors = [a2]
b3.authors = [a1,a2]

session.add_all([b1,b2,b3,a1,a2])

# 查询数据,
book = session.query(Books).filter(Books.name == "Python核心编程").first()
print(books.authors)    # 打印Python核心编程的所有作者，返回的是一个列表

author = session.query(Authors).filter(Authors.name == "老王").first()
print(author.books)     # 打印老王的所有书,这里books对应的是backref里的值

session.commit()

插入数据

# 生成Session类
Session = sqlalchemy.orm.sessionmaker(bind=engine)
# 创建session对象
session = Session()

user = User(name="laowang",password="123456")       # 指定参数，不能用位置参数

session.add(user)

# 统一提交
session.commit()

查询

• 查询所有

# 查询所有
data = session.query(User).all()    # 返回User对象列表;
# 获取所有User与名称
data = session.query(User,User.name).all()      # 返回(User对象，name)的元组列表
# 获取所有名称
data = session.query(User.name).all()       # 返回(name)的元组列表

• 查询多条与单条数据

# 查询多条,返回一个列表
data = session.query(User).filter_by(name ="laowang").all()
# 查询一条 
data = session.query(User).filter_by(name ="laowang").first()

• filter

# 单个条件
data = session.query(User).filter(User.name == "laowang").first()

# 模糊查询
data = session.query(User).filter(User.name.like("l%")).all()

# 分组查询(select name from user group by name;)
data = session.query(User.name).group_by(User.name).all()

# 或关系(select * from user where name = "laowang" or name = "xiaoma";)
data = session.query(User).filter(User.name.in_(["laowang","xiaoma"])).all()
也可以这样写：
data = session.query(User).filter(sqlalchemy.or_(User.name == "laowang",User.name == "xiaoma")).all()



# 查询多个条件
data = session.query(User).filter(User.name == "laowang").filter(User.password == "123456").all()
也可以这样写:
data = session.query(User).filter(User.name == "laowang",User.password == "123456").all()
又或者这样写：
data = session.query(User).filter(sqlalchemy.and_(User.name == "laowang",User.password == "123456")).all()


# 关联查询(select * from User,Student where User.id = Student.id)
data = session.query(User,Student).filter(User.id == Student.id).all()

• filter_by

# 单个条件
data = session.query(User).filter_by(name = "laowang").first()
# 多个条件
data = session.query(User).filter_by(name = "laowang").filter_by(password = "123456").first()
也可以这样写:
data = session.query(User).filter_by(name = "laowang",password = "123456").first()

注：filter_by与filter的区别,filter主要适用于>,<,==,!= 这种比较关系，并且用用户类调用位置参数,而filter_by则只需用参数名即可，而且filter_by似乎只是用与=

修改

student = session.query(Student).filter(Student.id == 1).first()
student.name = "laoli"

session.commit()

删除

s = session.query(Student).filter(Student.name == "laowang").first()
session.delete(s)

session.commit()