ORM-SqlAlchemy
ORM：
orm英文全称object relational mapping,就是对象映射关系程序，简单来说我们类似python这种面向对象的程序来说一切皆对象，但是我们使用的数据库却都是关系型的，为了保证一致的使用习惯，通过orm将编程语言的对象模型和数据库的关系模型建立映射关系，这样我们在使用编程语言对数据库进行操作的时候可以直接使用编程语言的对象模型进行操作就可以了，而不用直接使用sql语言。
优点：
隐藏了数据访问细节，“封闭”的通用数据库交互，ORM的核心。他使得我们的通用数据库交互变得简单易行，并且完全不用考虑SQL语句。快速开发，由此而来。
缺点：
无可避免的，自动化意味着映射和关联管理，代价是牺牲性能（早期，这是所有不喜欢ORM人的共同点）。现在的各种ORM框架都在尝试使用各种方法来减轻这块（LazyLoad、Cache）。
安装 SqlAchemy：
终端运行：pip install 下载后的文件地址
使用SqlAchemy：
Dialect用于和数据API进行交流，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作，如：
MySQL-Python mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname> pymysql mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>] MySQL-Connector mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname> cx_Oracle oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]
更多
用Engine/ConnectionPooling/Dialect 进行数据库操作，Engine使用ConnectionPooling连接数据库，然后再通过Dialect执行SQL语句。
import sqlalchemy from sqlalchemy import create_engine # create_engine 方法进行数据库链接，返回一个db对象 engine = create_engine("mysql+pymysql://root:123@localhost/testdb", encoding='utf-8', echo=True) # 数据库类型://用户名:密码（没有密码则为空，不填）@数据库主机地址/数据库名?编码
# echo = True 是为了方便 控制台 logging 输出一些sql信息，默认是False
通过engine对象可以直接execute进行查询
ret = engine.execute('select id from Person') print(ret.fetchall()) >>> [(1,), (2,), (5,), (3,), (4,), (6,)]
也可以通过engin获取链接后再查询
conn = engine.connect() ret = conn.execute('select name from Person') print(ret.fetchall()) >>> [('coco',), ('jack',), ('jerry',), ('kk',), ('rose',), ('tom',)]
区别：
- 直接使用engine的execute执行sql的方式, 叫做
connnectionless执行, - 借助 engine.connect()获取conn, 然后通过conn执行sql, 叫做
connection执行
主要差别在于是否使用transaction模式, 如果不涉及transaction, 两种方法效果是一样的. 官网推荐使用后者。
使用 ORM/Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect 所有组件对数据进行操作。根据类创建对象，对象转换成SQL，执行SQL。
创建表：
import sqlalchemy from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String engine = create_engine("mysql+pymysql://root:alex3714@localhost/testdb", encoding='utf-8', echo=True) Base = declarative_base() #生成orm基类 class User(Base): __tablename__ = 'user' # 表名 id = Column(Integer, primary_key=True) name = Column(String(32)) password = Column(String(64)) Base.metadata.create_all(engine) # 创建表结构
Base.metadata.drop_all(engine) # 删除表格
添加数据：
Session_class = sessionmaker(bind=engine) # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例 Session = Session_class() # 生成 session实例 user_obj = User(name="alex", password="123") # 生成你要创建的数据对象 print(user_obj.name, user_obj.id) # 此时还没创建对象呢，不信你打印一下id发现还是None >>>
alex None
alex None
Session.add(user_obj) # 把要创建的数据对象添加到这个session里， 一会统一创建 print(user_obj.name, user_obj.id) # 此时也依然还没创建
>>>
alex None
alex None
# 添加多条数据
Session.add_all([ User(name="alex", password="123"),
User(name="Tom", password="123"),
User(name="Join", password="123") ])
# 统一提交，创建数据,没有这句话，就是更改表的数据。
Session.commit()
查找数据：
查找数据就用到了Session中的query了 ret = Session.query(User) print(ret) >>> SELECT `Hero`.id AS `Hero_id`, `Hero`.name AS `Hero_name`, `Hero`.password AS `Hero_password` FROM `Hero`
# 这不是MySQL查询语句吗！是的当query后边只有类名的时候，就会转换成查询语句，但是我们不是要找结果吗？ # 我们只需要在后边添加一个all或者first就可以了,先来看一下first的结果 ret = Session.query(User).first() print(ret) >>>
<__main__.User object at 0x103ee5da0> # 这是什么鬼,返回的居然是一个User对象！ # 是的，这个对象就是我们获取的User列表中的第一列，只需要在后边加上id 、name...就可获取数据啦 ret = Session.query(User).first() print(ret.id,ret.name,ret.password) >>> 1 alex 123
#而all 就是返回所有内容的列表了,可以通过循环获取
ret = Session.query(User).all()
for row in ret:
print(row.id,row.name,row.password)
>>>
1 alex 123
2 rose 123
3 Tom 123
4 Join 123
条件查询：
- filter有两种，要注意书写区别: 1、filter_by引用列名时，使用“属性名”，比较使用一个等号“=” 2、filter引用列名时，使用“类名.属性名”的方式，比较使用两个等号“==” ret_1 = Session.query(User).filter_by(name = 'alex').first() ret_2 = Session.query(User).filter(User.name =='Tom').first() print(ret_1.name,ret_2.name) >>> alex Tom - 排序筛选：order_by # 根据表中的id排序查询，默认从小到大
升序：
ret_3 = Session.query(User).order_by(User.id).all() for row in ret_3: print(row.id,row.name) >>> 1 alex 2 rose 3 Tom 4 Join 如果想从大到小排列呢? 那么就 用到了desc
降序：
ret_3 = Session.query(User).order_by(User.id.desc()).all() for row in ret_3: print(row.id,row.name) >>> 4 Join 3 Tom 2 rose 1 alex
- in 操作：in_
# 获取User.name 等于Tom和rose的列表
ret_4 = Session.query(User).filter(User.name.in_(['Tom','rose'])).all()
for row in ret_4:
print(row.id,row.name)
>>>
3 Tom
2 rose
# 获取User.name 不等于Tom、rose的列表,取反 ~
ret_5 = Session.query(User).filter(~User.name.in_(['Tom','rose'])).all()
for row in ret_5:
print(row.id,row.name)
>>>
1 alex
4 Join
- 并且，或者 and_ 、or_ 需要导入 and_ 和 or_
from sqlalchemy import and_,or_
and_：
ret_6 = Session.query(User).filter(and_(User.name == 'alex',User.password == '123')).all()
for row in ret_6:
print(row.id,row.name)
>>>
1 alex
or_：
ret_7 = Session.query(User).filter(or_(User.name == 'alex',User.password == '123')).all()
for row in ret_7:
print(row.id,row.name)
>>>
1 alex
2 rose
3 Tom
4 Join
更多查询：点我！
修改：
my_user = Session.query(User).filter_by(name="alex").first() my_user.name = "Alex_Z" # 提交 Session.commit()
回滚：rollback
my_user = Session.query(User).filter_by(name="alex").first() # 修改名字 my_user.name = "Alex_Z" # 创建Rain fake_user = User(name='Rain', password='12345') Session.add(fake_user) # 回滚，取消所有操作，
注：没有Session.commit就不会修改表格的数据，但是在修改名字和添加表格的之后Session.commit之前查询，的出的结果是修改后的data，所以commit是执行保存操作。 Session.rollback() # 提交 Session.commit() # 查询表格 ret_8 = Session.query(User).all() for row in ret_8: print(row.id,row.name) >>> 1 alex 2 rose 3 Tom 4 Join
外键关联：
（一对多）创建班级和学生表格：
#!/usr/bin/env python # -*- coding: utf-8 -*- __author__ = 'Fade Zhao' import sqlalchemy from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String,ForeignKey from sqlalchemy.orm import sessionmaker,relationship engine = create_engine("mysql+pymysql://root:zhaoyinghan@localhost/school", encoding='utf-8', echo=True) Base = declarative_base() class Grade(Base): __tablename__ = 'Grade' id = Column(Integer,autoincrement = True,primary_key = True) subject = Column(String(32)) teacher_name = Column(String(32)) class Student(Base): __tablename__ = 'student' id = Column(Integer,primary_key = True,autoincrement = True) name = Column(String(32),nullable= False) qq = Column(Integer) Grade_id = Column(Integer,ForeignKey('Grade.id')) # 创建Grade_id列，关联Grade中的id列 Grade_key = relationship('Grade',backref ='my_Grade') # 在数据查询的时候，student 可以利用 Grade_key 来进行对表Grade的数据查询。而Grade同样可以通过backref
的值对student对应的数据进行查询。
# 提交到数据库
Base.metadata.create_all(engine)
将数据添加到表格中：
Session_Class = sessionmaker(bind=engine) Session = Session_Class() # 给班级中添加数据 c_1 = Grade(subject = 'English',teacher_name = 'Ryan') c_2 = Grade(subject = 'Sports',teacher_name = 'Rain') c_3 = Grade(subject = 'Physics',teacher_name = 'Jack') Session.add_all([c_1,c_2,c_3])
# 提交数据，因为student表格中的Grade_id 关联了Grade中的id所以，要先添加此表格的数据 Session.commit() # 学生中添加数据 s_1 = Student(name = 'Tom',qq = 1233123,Grade_id =1) s_2 = Student(name = 'Joker',qq = 321321,Grade_id = 1) s_3 = Student(name = 'Rose',qq = 55555,Grade_id = 3) s_4 = Student(name = 'Jerry',qq = 8888,Grade_id = 2) Session.add_all([s_1,s_2,s_3,s_4])
# 提交
Session.commit()
一对多查询：
# 相当于关联查询中的
SELECT student.id AS student_id, student.name AS student_name, student.qq AS student_qq, student.`Grade_id` AS `student_Grade_id` FROM student, `Grade` WHERE `Grade`.id = student.`Grade_id` ret = Session.query(Student).filter(Grade.id == Student.Grade_id).all() for row in ret: print(row.id,row.name,row.qq) >>> 3 Tom 122123 4 Joker 321321 5 Rose 55555 6 Jerry 8888
# 在sqlalchemy的查询语句中，如过将最后的all、first去掉的话，打印这个结果，就会得出MySQL的原生查询语句：
ret = Session.query(Student.id.label('student_ID'),Student.name.label('student_Name')).join(Grade)
print(ret)
>>>
# 上边的 label 就相当于查询语句中的 AS
SELECT student.id AS `student_ID`, student.name AS `student_Name`FROM student INNER JOIN `Grade` ON `Grade`.id = student.`Grade_id`
（多键关联）创建表格：
#!/usr/bin/env python # -*- coding: utf-8 -*- __author__ = 'Fade Zhao' from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String,ForeignKey from sqlalchemy.orm import sessionmaker,relationship engine = create_engine("mysql+pymysql://root:zhaoyinghan@localhost/school", encoding='utf-8', echo=True) Base = declarative_base() # 多键关联 class Person(Base): __tablename__ = 'Person' id = Column(Integer,primary_key = True) name = Column(String(32), nullable=False) live_address_id = Column(Integer,ForeignKey('addresses.id'),nullable= False) home_address_id = Column(Integer,ForeignKey('addresses.id'),nullable= False) live_address = relationship("Address",foreign_keys=[live_address_id]) home_address = relationship("Address",foreign_keys=[home_address_id])
# 其中foreign_keys =[live_address_id])是因为在此表中，live_address_id和home_address_id 同时关联了Address.id ,防止在添加数据时报错。
class Address(Base):
__tablename__ = 'addresses'
id = Column(Integer,primary_key = True)
city = Column(String(32),nullable= False)
street = Column(String(32),nullable= False)
def __repr__(self)
print(self.city,self.street)
Base.metadata.create_all(engine)
添加数据：
Session_class = sessionmaker(bind=engine) Session = Session_class() addr1 = Address(city = 'beijing',street = 'huilongguan') addr2 = Address(city = 'shanghai',street = 'putuoqu') Session.add_all([addr1,addr2]) Session.commit() Per1 = Person(name = 'alex',live_address_id = 1,home_address_id =2) Per2 = Person(name = 'Tom',live_address_id = 2,home_address_id =2) Session.add_all([Per1,Per2]) Session.commit()
查找数据：
mysql> select *from Person; +----+------+-----------------+-----------------+ | id | name | live_address_id | home_address_id | +----+------+-----------------+-----------------+ | 1 | alex | 1 | 2 | | 2 | Tom | 2 | 2 | +----+------+-----------------+-----------------+ mysql> select *from Addresses; +----+----------+-------------+ | id | city | street | +----+----------+-------------+ | 1 | beijing | huilongguan | | 2 | shanghai | putuoqu | +----+----------+-------------+ Person表中的live_address_id、home_address_id都关联了addresses.id，所以可以通过Person表中的live_address、home_address关联查找到addresses表中的数据 例： ret = Session.query(Person).filter(Person.name == 'alex').first() print(ret.live_address) >>> beijing huilongguan
多对多关联：
创建表格并添加数据：
from sqlalchemy import create_engine,Table from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String,ForeignKey from sqlalchemy.orm import sessionmaker,relationship engine = create_engine("mysql+pymysql://root:zhaoyinghan@localhost/school?charset=utf8", echo=True) Base = declarative_base() class_m2m_student = Table('class_m2m_student',Base.metadata, Column('id',Integer,primary_key= True), Column('classes_id',Integer,ForeignKey('grade.id')), Column('students_id',Integer,ForeignKey('students.id'))) class Grade(Base): '''班级''' __tablename__ = 'grade' id = Column(Integer,primary_key=True) class_name = Column(String(32),nullable=False) course = Column(String(32),nullable=False) teacher = Column(String(32),nullable=False) student = relationship('Students',secondary = class_m2m_student,backref = 'grade') def __repr__(self): return '班级名称%s、科目%s' % (self.class_name,self.course) class Students(Base): '''学生''' __tablename__ = 'students' id = Column(Integer,primary_key=True) student_name = Column(String(32), nullable=False) QQ = Column(Integer,nullable=False) def __repr__(self): return '学生姓名%s、学生QQ%s' % (self.student_name,self.QQ) Base.metadata.create_all(engine) Session_class = sessionmaker(bind=engine) Session = Session_class() # # G_1 = Grade(class_name= '3.1',course='Python',teacher='Jay') G_2 = Grade(class_name= '3.2',course='Linux',teacher='chenyixun') G_3 = Grade(class_name= '3.3',course='Cocos',teacher='liuhuan') G_4 = Grade(class_name= '3.4',course='Unity',teacher='naying') S_1 = Students(student_name = 'Jerry',QQ = '123') S_2 = Students(student_name = 'Tom',QQ = '456') S_3 = Students(student_name = 'Marry',QQ = '789') S_4 = Students(student_name = 'Jack',QQ = '012') # 建立关系 G_1.student = [S_1,S_2] S_2.grade =[G_3] # 添加并提交 Session.add_all([G_1,G_2,G_3,G_4,S_1,S_2,S_3,S_4]) Session.commit()
查找：
# 查找 student_1 = Session.query(Students).filter(Students.id == 1).first() # 获取class_m2m_student表中 对应的student对应的grade_1,所获得的是一个List grade_1 = student_1.grade[0] print(grade_1)
修改：
# 修改关联表格 # 获取所有学生的信息 student_all = Session.query(Students).all() print(student_all) >>> [学生姓名Jerry、学生QQ123, 学生姓名Tom、学生QQ456, 学生姓名Marry、学生QQ789, 学生姓名Jack、学生QQ12]
# 重新建立联系 这个属于覆盖了之前的表关系[学生姓名Jerry、学生QQ123, 学生姓名Tom、学生QQ456, 学生姓名Marry、学生QQ789](自动去重) grade_1.student = student_all[:-1] # 提交 Session.commit()
mysql> select *from grade; +----+------------+--------+-----------+ | id | class_name | course | teacher | +----+------------+--------+-----------+ | 1 | 3.1 | Python | Jay | | 2 | 3.2 | Linux | chenyixun | | 3 | 3.3 | Cocos | liuhuan | | 4 | 3.4 | Unity | naying | +----+------------+--------+-----------+ 4 rows in set (0.00 sec) mysql> select *from students; +----+--------------+-----+ | id | student_name | QQ | +----+--------------+-----+ | 1 | Jerry | 123 | | 2 | Tom | 456 | | 3 | Marry | 789 | | 4 | Jack | 12 | +----+--------------+-----+ 4 rows in set (0.00 sec) mysql> select *from class_m2m_student; +----+------------+-------------+ | id | classes_id | students_id | +----+------------+-------------+ | 1 | 3 | 2 | | 2 | 1 | 1 | | 3 | 1 | 2 | | 4 | 1 | 3 | +----+------------+-------------+
删除：
因为关联表格 class_m2m_student 同时关联student和grade，所以有两种删除方法：
1、通过关联关系和remove删除
2、删除student或grade中的元素后orm自动删除有关联关系的数据
# 通过关联删除 student_2 = Session.query(Students).filter(Students.id == '2').first() print(student_2) grade_1.student.remove(student_2) Session.commit() >>> mysql> select *from class_m2m_student; +----+------------+-------------+ | id | classes_id | students_id | +----+------------+-------------+ | 1 | 3 | 2 | | 2 | 1 | 1 | | 4 | 1 | 3 | +----+------------+-------------+ 3 rows in set (0.00 sec) mysql> select *from grade; +----+------------+--------+-----------+ | id | class_name | course | teacher | +----+------------+--------+-----------+ | 1 | 3.1 | Python | Jay | | 2 | 3.2 | Linux | chenyixun | | 3 | 3.3 | Cocos | liuhuan | | 4 | 3.4 | Unity | naying | +----+------------+--------+-----------+ 4 rows in set (0.01 sec) # 直接删除班级 Session.delete(grade_1) Session.commit() >>> mysql> select *from class_m2m_student; +----+------------+-------------+ | id | classes_id | students_id | +----+------------+-------------+ | 1 | 3 | 2 | +----+------------+-------------+ 1 row in set (0.00 sec)
