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 sqlalchemyimport sqlalchemy.ext.declarativeimport 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 sqlalchemyimport sqlalchemy.ormimport sqlalchemy.ext.declarativeengine = 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 sqlalchemyimport sqlalchemy.ormimport sqlalchemy.ext.declarativeengine = 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()