本节内容：
- ORM介绍
- sqlalchemy安装
- sqlalchemy基本使用
- 多外键关联
- 多对多关系
- 表结构设计作业
1.ORM介绍
orm英文全称object relational mapping,就是对象映射关系程序，简单来说我们类似python这种面向对象的程序来说一切皆对象，但是我们使用的数据库却都是关系型的，为了保证一致的使用习惯，通过orm将编程语言的对象模型和数据库的关系模型建立映射关系，这样我们在使用编程语言对数据库进行操作的时候可以直接使用编程语言的对象模型进行操作就可以了，而不用直接使用sql语言。
orm的优点：
- 隐藏了数据访问细节，“封闭”的通用数据库交互，ORM的核心。他使得我们的通用数据库交互变得简单易行，并且完全不用考虑该死的SQL语句。快速开发，由此而来。
- ORM使我们构造固化数据结构变得简单易行。
缺点：
- 无可避免的，自动化意味着映射和关联管理，代价是牺牲性能（早期，这是所有不喜欢ORM人的共同点）。现在的各种ORM框架都在尝试使用各种方法来减轻这块（LazyLoad，Cache），效果还是很显著的。
2.sqlalchemy安装
参见：http://www.cnblogs.com/alex3714/articles/5978329.html
3.sqlalchemy基本使用
之前，我们创建一个表是这样的
CREATE TABLE user (
id
INTEGER
NOT
NULL
AUTO_INCREMENT,
name
VARCHAR
(32),
password
VARCHAR
(64),
PRIMARY
KEY
(id)
)
这只是最简单的sql表，如果再加上外键关联什么的，一般程序员的脑容量是记不住那些sql语句的，于是有了orm,实现上面同样的功能，代码如下
1 import sqlalchemy 2 from sqlalchemy import create_engine 3 from sqlalchemy.ext.declarative import declarative_base 4 from sqlalchemy import Column, Integer, String 5 6 engine = create_engine("mysql+pymysql://root:alex3714@localhost/testdb", 7 encoding='utf-8', echo=True) 8 9 10 Base = declarative_base() #生成orm基类 11 12 class User(Base): 13 __tablename__ = 'user' #表名 14 id = Column(Integer, primary_key=True) 15 name = Column(String(32)) 16 password = Column(String(64)) 17 18 Base.metadata.create_all(engine) #创建表结构
除上面的创建之外，还有一种创建表的方式，虽不常用，但还是看看吧
1 from sqlalchemy import Table, MetaData, Column, Integer, String, ForeignKey 2 from sqlalchemy.orm import mapper 3 4 metadata = MetaData() 5 6 user = Table('user', metadata, 7 Column('id', Integer, primary_key=True), 8 Column('name', String(50)), 9 Column('fullname', String(50)), 10 Column('password', String(12)) 11 ) 12 13 class User(object): 14 def __init__(self, name, fullname, password): 15 self.name = name 16 self.fullname = fullname 17 self.password = password 18 19 mapper(User, user) #the table metadata is created separately with the Table construct, then associated with the User class via the mapper() function
事实上，我们用第一种方式创建的表就是基于第2种方式的再封装。
最基本的表我们创建好了，那我们开始用orm创建一条数据试试
Session_class = sessionmaker(bind=engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例 Session = Session_class() #生成session实例 user_obj = User(name="alex",password="alex3714") #生成你要创建的数据对象 print(user_obj.name,user_obj.id) #此时还没创建对象呢，不信你打印一下id发现还是None Session.add(user_obj) #把要创建的数据对象添加到这个session里， 一会统一创建 print(user_obj.name,user_obj.id) #此时也依然还没创建 Session.commit() #现此才统一提交，创建数据
查询
my_user = Session.query(User).filter_by(name="alex").first() print(my_user)
此时你看到的输出是这样的应该
<__main__.User object at 0x105b4ba90>
sqlalchemy帮你把返回的数据映射成一个对象啦，这样你调用每个字段就可以跟调用对象属性一样啦，like this..
1 print(my_user.id,my_user.name,my_user.password) 2 3 输出 4 1 alex alex3714
不过刚才上面的显示的内存对象对址你是没办法分清返回的是什么数据的，除非打印具体字段看一下，如果想让它变的可读，只需在定义表的类下面加上这样的代码
1 def __repr__(self): 2 return "<User(name='%s', password='%s')>" % ( 3 self.name, self.password)
修改
1 my_user = Session.query(User).filter_by(name="alex").first() 2 3 my_user.name = "Alex Li" 4 5 Session.commit()
回滚
1 my_user = Session.query(User).filter_by(id=1).first() 2 my_user.name = "Jack" 3 4 5 fake_user = User(name='Rain', password='12345') 6 Session.add(fake_user) 7 8 print(Session.query(User).filter(User.name.in_(['Jack','rain'])).all() ) #这时看session里有你刚添加和修改的数据 9 10 Session.rollback() #此时你rollback一下 11 12 print(Session.query(User).filter(User.name.in_(['Jack','rain'])).all() ) #再查就发现刚才添加的数据没有了。 13 14 # Session 15 # Session.commit()
获取所有数据
1 print(Session.query(User.name,User.id).all() )
多条件查询
1 objs = Session.query(User).filter(User.id>0).filter(User.id<7).all()
上面2个filter的关系相当于 user.id >1 AND user.id <7 的效果
统计和分组
1 Session.query(User).filter(User.name.like("Ra%")).count()
分组
1 from sqlalchemy import func 2 print(Session.query(func.count(User.name),User.name).group_by(User.name).all() )
相当于原生sql为
1 SELECT count(user.name) AS count_1, user.name AS user_name 2 FROM user GROUP BY user.name
输出为
[(1, 'Jack'), (2, 'Rain')]
外键关联
创建一个addresses表，跟user表关联
from sqlalchemy import ForeignKey from sqlalchemy.orm import relationship class Address(Base): __tablename__ = 'addresses' id = Column(Integer, primary_key=True) email_address = Column(String(32), nullable=False) user_id = Column(Integer, ForeignKey('user.id')) user = relationship("User", backref="addresses") #这个nb，允许你在user表里通过backref字段反向查出所有它在addresses表里的关联项 def __repr__(self): return "<Address(email_address='%s')>" % self.email_address
表创建好后，我们可以这样反查试试
1 obj = Session.query(User).first() 2 for i in obj.addresses: #通过user对象反查关联的addresses记录 3 print(i) 4 5 addr_obj = Session.query(Address).first() 6 print(addr_obj.user.name) #在addr_obj里直接查关联的user表
创建关联对象
1 obj = Session.query(User).filter(User.name=='rain').all()[0] 2 print(obj.addresses) 3 4 obj.addresses = [Address(email_address="r1@126.com"), #添加关联对象 5 Address(email_address="r2@126.com")] 6 7 8 Session.commit()
4.多外键关联
一种很常见的情况就是两张表存在的外键超过一个，如Customer表中有两个字段关联Address表
1 rom sqlalchemy import Integer, ForeignKey, String, Column 2 from sqlalchemy.ext.declarative import declarative_base 3 from sqlalchemy.orm import relationship 4 5 Base = declarative_base() 6 7 class Customer(Base): 8 __tablename__ = 'customer' 9 id = Column(Integer, primary_key=True) 10 name = Column(String) 11 12 billing_address_id = Column(Integer, ForeignKey("address.id")) 13 shipping_address_id = Column(Integer, ForeignKey("address.id")) 14 15 billing_address = relationship("Address") 16 shipping_address = relationship("Address") 17 18 class Address(Base): 19 __tablename__ = 'address' 20 id = Column(Integer, primary_key=True) 21 street = Column(String) 22 city = Column(String) 23 state = Column(String)
上面创建表结构是没有问题的，但你Address表中插入数据时会报下面的错
1 sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join 2 condition between parent/child tables on relationship 3 Customer.billing_address - there are multiple foreign key 4 paths linking the tables. Specify the 'foreign_keys' argument, 5 providing a list of those columns which should be 6 counted as containing a foreign key reference to the parent table. 7 #外键同时关联一个字段，出现冲突，系统不知道该如何分配
解决办法：
1 class Customer(Base): 2 __tablename__ = 'customer' 3 id = Column(Integer, primary_key=True) 4 name = Column(String) 5 6 billing_address_id = Column(Integer, ForeignKey("address.id")) 7 shipping_address_id = Column(Integer, ForeignKey("address.id")) 8 9 billing_address = relationship("Address", foreign_keys=[billing_address_id]) 10 shipping_address = relationship("Address", foreign_keys=[shipping_address_id]) #再relationship中加入关联字段，就能分清楚到底是谁外键调用了Address的id
5.多对多关系
现在来设计一个能描述“图书”与“作者”的关系的表结构，需求是
- 一本书可以有好几个作者一起出版
- 一个作者可以写好几本书
此时你会发现，用之前学的外键好像没办法实现上面的需求了，因为
当然你更不可以像下面这样干，因为这样就你就相当于有多条书的记录了
此时，我们可以再搞出一张中间表，就可以了
这样就相当于通过book_m2m_author表完成了book表和author表之前的多对多关联
1 #一本书可以有多个作者，一个作者又可以出版多本书 2 3 4 from sqlalchemy import Table, Column, Integer,String,DATE, ForeignKey 5 from sqlalchemy.orm import relationship 6 from sqlalchemy.ext.declarative import declarative_base 7 from sqlalchemy import create_engine 8 from sqlalchemy.orm import sessionmaker 9 10 11 Base = declarative_base() 12 13 book_m2m_author = Table('book_m2m_author', Base.metadata, 14 Column('book_id',Integer,ForeignKey('books.id')), 15 Column('author_id',Integer,ForeignKey('authors.id')), 16 ) 17 18 class Book(Base): 19 __tablename__ = 'books' 20 id = Column(Integer,primary_key=True) 21 name = Column(String(64)) 22 pub_date = Column(DATE) 23 authors = relationship('Author',secondary=book_m2m_author,backref='books') 24 25 def __repr__(self): 26 return self.name 27 28 class Author(Base): 29 __tablename__ = 'authors' 30 id = Column(Integer, primary_key=True) 31 name = Column(String(32)) 32 33 def __repr__(self): 34 return self.name
接下来创建几本书和作者
1 Session_class = sessionmaker(bind=engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例 2 s = Session_class() #生成session实例 3 4 b1 = Book(name="跟Alex学Python") 5 b2 = Book(name="跟Alex学把妹") 6 b3 = Book(name="跟Alex学装逼") 7 b4 = Book(name="跟Alex学开车") 8 9 a1 = Author(name="Alex") 10 a2 = Author(name="Jack") 11 a3 = Author(name="Rain") 12 13 b1.authors = [a1,a2] #这就是给多对多表赋值，它会自动生成 14 b2.authors = [a1,a2,a3] 15 16 s.add_all([b1,b2,b3,b4,a1,a2,a3]) #统一创建 17 18 s.commit() #提交后才有效
此时，手动连上mysql，分别查看这3张表，你会发现，book_m2m_author中自动创建了多条纪录用来连接book和author表
mysql> select * from books;
+
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
+
|
id
| name | pub_date |
+
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
+
|
1
| 跟Alex学Python | NULL |
|
2
| 跟Alex学把妹 | NULL |
|
3
| 跟Alex学装逼 | NULL |
|
4
| 跟Alex学开车 | NULL |
+
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
+
4
rows
in
set
(
0.00
sec)
mysql> select
*
from
authors;
+
-
-
-
-
+
-
-
-
-
-
-
+
|
id
| name |
+
-
-
-
-
+
-
-
-
-
-
-
+
|
10
| Alex |
|
11
| Jack |
|
12
| Rain |
+
-
-
-
-
+
-
-
-
-
-
-
+
3
rows
in
set
(
0.00
sec)
mysql> select
*
from
book_m2m_author;
+
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
+
| book_id | author_id |
+
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
+
|
2
|
10
|
|
2
|
11
|
|
2
|
12
|
|
1
|
10
|
|
1
|
11
|
+
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
+
5
rows
in
set
(
0.00
sec)
此时，用orm查一下数据
1 print('--------通过书表查关联的作者---------') 2 3 book_obj = s.query(Book).filter_by(name="跟Alex学Python").first() 4 print(book_obj.name, book_obj.authors) 5 6 print('--------通过作者表查关联的书---------') 7 author_obj =s.query(Author).filter_by(name="Alex").first() 8 print(author_obj.name , author_obj.books) 9 s.commit()
输出如下
1 --------通过书表查关联的作者--------- 2 跟Alex学Python [Alex, Jack] 3 --------通过作者表查关联的书--------- 4 Alex [跟Alex学把妹, 跟Alex学Python]
perfect！！！！
多对多删除
删除数据时不用管boo_m2m_authors ， sqlalchemy会自动帮你把对应的数据删除
通过书删除作者
1 author_obj =s.query(Author).filter_by(name="Jack").first() 2 3 book_obj = s.query(Book).filter_by(name="跟Alex学把妹").first() 4 5 book_obj.authors.remove(author_obj) #从一本书里删除一个作者 6 s.commit()
直接删除作者
删除作者时，会把这个作者跟所有书的关联关系数据也自动删除
1 author_obj =s.query(Author).filter_by(name="Alex").first() 2 # print(author_obj.name , author_obj.books) 3 s.delete(author_obj) 4 s.commit()
处理中文
sqlalchemy设置编码字符集一定要在数据库访问的URL上增加charset=utf8，否则数据库的连接就不是utf8的编码格式
eng = create_engine('mysql://root:root@localhost:3306/test2?charset=utf8',echo=True)
堡垒机代码示例：
参见：https://github.com/triaquae/py3_training/tree/master/%E5%A0%A1%E5%9E%92%E6%9C%BA
3141
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
