ORM介绍
orm英文全称object relational mapping,就是对象映射关系程序，简单来说我们类似python这种面向对象的程序来说一切皆对象，但是我们使用的数据库却都是关系型的，为了保证一致的使用习惯，通过orm将编程语言的对象模型和数据库的关系模型建立映射关系，这样我们在使用编程语言对数据库进行操作的时候可以直接使用编程语言的对象模型进行操作就可以了，而不用直接使用sql语言。
orm的优点：
- 隐藏了数据访问细节，“封闭”的通用数据库交互，ORM的核心。他使得我们的通用数据库交互变得简单易行，并且完全不用考虑该死的SQL语句。快速开发，由此而来。
- ORM使我们构造固化数据结构变得简单易行。
缺点：
- 无可避免的，自动化意味着映射和关联管理，代价是牺牲性能（早期，这是所有不喜欢ORM人的共同点）。现在的各种ORM框架都在尝试使用各种方法来减轻这块（LazyLoad，Cache），效果还是很显著的。
在Python中，最有名的ORM框架是SQLAlchemy。用户包括openstack＼Dropbox等知名公司或应用，主要用户列表http://www.sqlalchemy.org/organizations.html#openstack
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...]
更多详见：http://docs.sqlalchemy.org/en/latest/dialects/index.html
http://docs.sqlalchemy.org/en/latest/core/engines.html#sqlalchemy.create_engine
安装sqlalchemy
pip install SQLAlchemy pip install pymysql #由于mysqldb依然不支持py3，所以这里我们用pymysql与sqlalchemy交互
3.sqlalchemy基本使用
当我们使用ORM时，首先我们要处理数据库表，然后定义我们自己的类来映射到这些表，在sqlachemy中，这两个任务通常一起被执行，用一个系统知道的 Declarative（声明），这个声明能允许我们创建类来描述实际的数据库表然后映射到数据库。
声明基类
from sqlalchemy.ext.declarative import declarative_base base = declarative_base() #声明基类
现在假设我们要在mysql创建一个这样的user类
CREATE TABLE user (
id INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(32),
password VARCHAR(64),
PRIMARY KEY (id)
)
用python是怎么实现的呢?
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy import Column,String,Integer
Base = declarative_base() #声明基类
engine = create_engine("mysql+pymysql://root:123456@localhost:3306/testdb?charset=utf8",echo = True) #echo = True打印生成对象的所有过程,如果要插入中文，一定要加?charset=utf8
class User(Base):
"""
定义表结构，映射到数据库中
"""
__tablename__ = 'user' #表名
id = Column(Integer,primary_key=True)
fullname = Column(String(32))
password = Column(String(64))
Base.metadata.create_all(engine) #创建表结构
#在打印出来的日志中我们看到已经有正常创建
CREATE TABLE user (
id INTEGER NOT NULL AUTO_INCREMENT,
fullname VARCHAR(32),
password VARCHAR(64),
PRIMARY KEY (id)
)
登陆到mysql中，查看已经正常创建。
当我们表创建完成，再运行上面的代码并不会重复创建。
当我们与数据库交互时，我们要定义一个session作为会话，我们定义一个Session_class类来作为新的session对象的工厂，下面我们开始添加数据
from sqlalchemy.orm import sessionmaker Session_class = sessionmaker(bind = engine) #创建与数据库的会话session class ,注意,这里session_class是类,不是实例 Session = Session_class() #生成Session实例
插入数据
from sqlalchemy.orm import sessionmaker Session_class = sessionmaker(bind = engine) #创建与数据库的会话session class ,注意,这里session_class是类,不是实例 Session = Session_class() #生成Session实例 user_obj = User(fullname = 'lxj',password = '123456') #生成你要创建的数据对象 Session.add(user_obj) #添加数据 print(user_obj.fullname,user_obj.id) #这里id还是为None，说明数据还未添加成功 Session.commit() print(user_obj.id) #这里id就有值了，说明正常添加
插入多条数据
from sqlalchemy.orm import sessionmaker Session_class = sessionmaker(bind = engine) #创建与数据库的会话session class ,注意,这里session_class是类,不是实例 Session = Session_class() #生成Session实例 user_obj = User(fullname = 'sx',password = '123456') #生成你要创建的数据对象 user_obj2 = User(fullname = 'sx1',password = '123456') #生成你要创建的数据对象 Session.add_all([user_obj,user_obj2]) #添加多条数据 Session.commit()
查询
class User(Base):
"""
定义表结构，映射到数据库中
"""
__tablename__ = 'user' #表名
id = Column(Integer,primary_key=True)
fullname = Column(String(32))
password = Column(String(64))
def __repr__(self): #对实例化的对象格式化输出
return "<User(fullname='%s', password='%s')>" % (
self.fullname, self.password)
Base.metadata.create_all(engine) #创建表结构
from sqlalchemy.orm import sessionmaker
Session_class = sessionmaker(bind = engine) #创建与数据库的会话session class ,注意,这里session_class是类,不是实例
Session = Session_class() #生成Session实例
user_objs = Session.query(User) #查询所有数据,类型select * from 表名，这是一个可迭代的对象
print(user_objs)
for user_obj in user_objs: #循环读取每条数据
print(user_obj)
Session.commit()
#输出结果
SELECT user.id AS user_id, user.fullname AS user_fullname, user.password AS user_password
FROM user
<User(fullname='lxj', password='123456')>
<User(fullname='sx', password='123456')>
<User(fullname='sx1', password='123456')>
查询多列
user_objs = Session.query(User.id,User.fullname) #查询id和fullname列
for user_obj in user_objs:
print(user_obj)
#结果：
(1, 'lxj')
(2, 'sx')
(3, 'sx1')
(4, 'lxj')
查询重复项
# del_user = Session.query(User.fullname).distinct().all() #query的distinct用法 del_user = Session.query(distinct(User.fullname)).all() #from sqlalchemy import distinct 外部导入distinct用法 print(del_user) Session.commit()
过滤数据，为了让我们例子更形象化，再添加一条数据，User(fullname = 'lxj',password = '123')
#对中间这部分进行修改
user_objs = Session.query(User).filter_by(fullname = 'lxj') #过滤数据,相当于select * from User where fullname = 'lxj'，这是一个可迭代的对象
for user_obj in user_objs: #循环读取每条数据
print(user_obj)
#结果
<User(fullname='lxj', password='123456')>
<User(fullname='lxj', password='123')>
多条件过滤实现and和or
user_objs = Session.query(User).filter_by(fullname = 'lxj',password = '123') #过滤数据,相当于select * from User where fullname = 'lxj' and password = '123'，这是一个可迭代的对象
#user_objs = Session.query(User).filter(User.fullname=='lxj',User.password == '123') #也可以这么写
for user_obj in user_objs: #循环读取每条数据
print(user_obj)
#结果：<User(fullname='lxj', password='123')>
#实现or
from sqlalchemy.orm import sessionmaker
from sqlalchemy import or_
Session_class = sessionmaker(bind = engine) #创建与数据库的会话session class ,注意,这里session_class是类,不是实例
Session = Session_class() #生成Session实例
user_objs = Session.query(User).filter(or_(User.fullname=='lxj',User.fullname=='sx'))#过滤数据,相当于select * from User where fullname = 'lxj'，这是一个可迭代的对象
for user_obj in user_objs: #循环读取每条数据
print(user_obj)
# user_obj = User(fullname = 'lxj',password = '123')
# Session.add(user_obj)
Session.commit()
#结果：
<User(fullname='lxj', password='123456')>
<User(fullname='sx', password='123456')>
<User(fullname='lxj', password='123')>
读取第一条数据
user_obj = Session.query(User).filter(or_(User.fullname=='lxj',User.fullname=='sx')).first() print(user_obj) #结果：<User(fullname='lxj', password='123456')>
后面query后面还可以搭配好多种函数，如limit，offset等，可以使用dir(user_obj)或则看源码来查看对象下面能调用的方法。这里调用函数的方法大致要和我们编写sql语句的顺序一致。
修改
user_obj = Session.query(User).filter(User.fullname=='sx').first() print(user_obj) user_obj.password = '12345' #对查询出来的数据修改 print(user_obj) Session.commit() #结果 <User(fullname='sx', password='123')> <User(fullname='sx', password='12345')> #Session.query(User).filter(User.fullname=='sx')查询出来的结果是一个可迭代的对象，并不能直接修改，要么就是通过for循环一条条取出数据来修改，要么就像这个例子一样取出一条数据来修改
删除
删除与添加十分类似
user_obj = Session.query(User).filter(User.fullname=='sjj').first() #找到要删除的数据 Session.delete(user_obj) #删除 Session.commit()
回溯
Session_class = sessionmaker(bind = engine) #创建与数据库的会话session class ,注意,这里session_class是类,不是实例 Session = Session_class() #生成Session实例 user_obj = Session.query(User).filter(User.fullname=='sx').first() user_obj.fullname = "spp" fake_user = User(fullname = 'pll',password = '123') Session.add(fake_user) print(Session.query(User).filter(User.fullname.in_(['spp','pll'])).all()) #in_同python中关键字in一样的功能此时看Session里有你添加和修改的数据 Session.rollback() #回溯 print(Session.query(User).filter(User.fullname.in_(['spp','pll'])).all()) #已经没有记录了 # Session.commit()
统计和分组
user_objs = Session.query(User).filter(User.fullname.like("%s%")).all() #all()取出所有数据，并以列表的形式返回
count = Session.query(User).filter(User.fullname.like("%s%")).count() #统计条目
print(user_objs,count)
Session.commit()
结果：
[<User(fullname='sx', password='12345')>, <User(fullname='sx', password='1234')>, <User(fullname='lss', password='1234')>, <User(fullname='lss', password='123')>] 4
分组
from sqlalchemy import func
user = Session.query(User.fullname,func.count(User.fullname)).filter(User.fullname == 'lxj').group_by(User.fullname).all()
#上面这条语句相当于select fullname,count(fullname) from user where fullname = 'lxj' group by fullname;
print(user)
Session.commit()
#结果
[('lxj', 4)]
user = Session.query(User.fullname,func.count(User.fullname),func.max(User.id)).group_by(User.fullname).all() #分组查询
print(user)
#结果：
[('lss', 2, 10), ('lxj', 4, 7), ('sx', 2, 8)]
一些聚合函数的用法
user = Session.query(func.max(User.id)).one() #one与first的区别是，one()返回且仅返回一个查询结果。当结果的数量不足一个或者多于一个时会报错 #上面这条语句相当于select fullname,count(fullname) from user where fullname = 'lxj' group by fullname; print(user) Session.commit() #结果： 10 #还有min，sum聚合函数也是类似的用法。
Using Textual SQL
文本字符串可以灵活地用于查询，通过指定它们与text（）结构的使用，这被大多数适用的方法所接受。 例如，filter（）和order_by（）：
from sqlalchemy import text
user_objs = Session.query(User).filter(text("id>5")).order_by(text("id desc")).all() #支持字符串的形式查询,查询出来的结果按降序显示
# user_objs = Session.query(User).filter(User.id>5).order_by(User.id.desc()).all() #等同于上面这句
print(user_objs)
Session.commit()
联表查询
首先我们对数据库的usertest做个表映射，方法同之前一样
class UserTest(Base):
__tablename__ = 'usertest' # 表名
id = Column(Integer, primary_key=True)
name = Column(String(32))
password = Column(String(64))
def __repr__(self): # 对实例化的对象格式化输出
return "<User(id='%s',fullname='%s', password='%s')>" % (
self.id, self.fullname, self.password)
user_obj = Session.query(User,UserTest).filter(User.fullname == UserTest.name,User.password == UserTest.password).all() #联表user和usertest一同查询 #等同于执行select * from user,usertest where user.fullname = usertest.name and user.password = usertest.password; print(user_obj,len(user_obj)) Session.commit() #将结果 [(<User(id='1',fullname='lxj', password='123456')>, <User(id='1',fullname='lxj', password='123456')>), (<User(id='2',fullname='sx', password='12345')>, <User(id='5',fullname='sx', password='12345')>), (<User(id='7',fullname='lxj', password='1234')>, <User(id='3',fullname='lxj', password='1234')>)] 3
在mysql操作中，联表查询还用到join，我们看看sqlalchemy中的join方法（left join, right join, inner join ,full join）
inner join
user = Session.query(User).join(UserTest).all()
print(user)
#结果直接报错了
sqlalchemy.exc.InvalidRequestError: Could not find a FROM clause to join from. Tried joining to <class '__main__.UserTest'>, but got: Can't find any foreign key relationships between 'user' and 'usertest'
原因是没有定义外键，在sqlalchemy中join用在定义外键联表查询使用，这个我们下面会说，现在我们来看下无关联的查询方式
inner join - 交集
result = Session.query(User).join(UserTest,UserTest.password == User.password)
print(result)
for record in result:
print(record)
#结果：
SELECT user.id AS user_id, user.fullname AS user_fullname, user.password AS user_password
FROM user INNER JOIN usertest ON usertest.password = user.password
<User(id='1',fullname='lxj', password='123456')>
<User(id='2',fullname='sx', password='12345')>
<User(id='7',fullname='lxj', password='1234')>
<User(id='8',fullname='sx', password='1234')>
<User(id='9',fullname='lss', password='1234')>
在结果中，我们看到实际执行的就是inner join语句
left join
result = Session.query(User,UserTest).outerjoin(UserTest,UserTest.id == User.id)
print(result)
for record in result:
print(record)
#以该例为例子left join就是user显示全部行，usertest中如果数据在user表不在usertest表中，就显示NUll，python中就显示None
第二种写法：result = Session.query(User,UserTest).join(UserTest,UserTest.id == User.id,isouter=True)
结果：
SELECT user.id AS user_id, user.fullname AS user_fullname, user.password AS user_password, usertest.id AS usertest_id, usertest.name AS usertest_name, usertest.password AS usertest_password
FROM user LEFT OUTER JOIN usertest ON usertest.id = user.id
(<User(id='1',fullname='lxj', password='123456')>, <User(id='1',fullname='lxj', password='123456')>)
(<User(id='2',fullname='sx', password='12345')>, <User(id='2',fullname='lxj', password='12345')>)
(<User(id='4',fullname='lxj', password='123')>, <User(id='4',fullname='lxj', password='12345')>)
(<User(id='6',fullname='lxj', password='123')>, <User(id='6',fullname='sx', password='12346')>)
(<User(id='7',fullname='lxj', password='1234')>, <User(id='7',fullname='sxaa', password='12346')>)
(<User(id='8',fullname='sx', password='1234')>, <User(id='8',fullname='sxs', password='12346')>)
(<User(id='9',fullname='lss', password='1234')>, None)
(<User(id='10',fullname='lss', password='123')>, None)
right join 与left join类似，把位置换下即可
result = Session.query(UserTest,User).outerjoin(User,UserTest.id == User.id) #如果数据在user表中，不在usertest表，
print(result)
for record in result:
print(record)
#结果：
SELECT usertest.id AS usertest_id, usertest.name AS usertest_name, usertest.password AS usertest_password, user.id AS user_id, user.fullname AS user_fullname, user.password AS user_password
FROM usertest LEFT OUTER JOIN user ON usertest.id = user.id
(<User(id='1',fullname='lxj', password='123456')>, <User(id='1',fullname='lxj', password='123456')>)
(<User(id='2',fullname='lxj', password='12345')>, <User(id='2',fullname='sx', password='12345')>)
(<User(id='3',fullname='lxj', password='1234')>, None)
(<User(id='4',fullname='lxj', password='12345')>, <User(id='4',fullname='lxj', password='123')>)
(<User(id='5',fullname='sx', password='12345')>, None)
(<User(id='6',fullname='sx', password='12346')>, <User(id='6',fullname='lxj', password='123')>)
(<User(id='7',fullname='sxaa', password='12346')>, <User(id='7',fullname='lxj', password='1234')>)
(<User(id='8',fullname='sxs', password='12346')>, <User(id='8',fullname='sx', password='1234')>)
full join 取出所有在A和在B的的值，即除了交集的值
result1 = Session.query(User,UserTest).outerjoin(UserTest,UserTest.id == User.id)
result = Session.query(UserTest,User).outerjoin(User,UserTest.id == User.id)#如果数据在user表中，不在usertest表，
result_full = result1.union(result).all()
#参考mysql语句select * from user left join usertest on user.id = usertest.id union select * from user right join usertest on user.id = usertest.id;
for record in result_full:
print(record)
结果：
(<User(id='1',fullname='lxj', password='123456')>, <User(id='1',fullname='lxj', password='123456')>)
(<User(id='2',fullname='sx', password='12345')>, <User(id='2',fullname='lxj', password='12345')>)
(<User(id='4',fullname='lxj', password='123')>, <User(id='4',fullname='lxj', password='12345')>)
(<User(id='6',fullname='lxj', password='123')>, <User(id='6',fullname='sx', password='12346')>)
(<User(id='7',fullname='lxj', password='1234')>, <User(id='7',fullname='sxaa', password='12346')>)
(<User(id='8',fullname='sx', password='1234')>, <User(id='8',fullname='sxs', password='12346')>)
(<User(id='9',fullname='lss', password='1234')>, None)
(<User(id='10',fullname='lss', password='123')>, None)
(<User(id='3',fullname='lxj', password='1234')>, None)
(<User(id='5',fullname='sx', password='12345')>, None)
Process finished with exit code 0
下面来学习下外键使用，等外键学习完我们再来使用join方法。
外键关联
为了使例子清晰，我们现在先删除之前创建的user表，创建一个addresses表，跟user表关联
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy import Column,String,Integer,ForeignKey,distinct
Base = declarative_base() #声明基类
engine = create_engine("mysql+pymysql://root:123456@localhost:3306/testdb?charset=utf8") #echo = True打印生成对象的所有过程,如果要插入中文，一定要加?charset=utf8
class User(Base):
"""
定义表结构，映射到数据库中
"""
__tablename__ = 'user' #表名
id = Column(Integer,primary_key=True)
fullname = Column(String(32))
password = Column(String(64))
def __repr__(self): #对实例化的对象格式化输出
return "<User(id='%s',fullname='%s', password='%s')>" % (
self.id, self.fullname, self.password)
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"))
def __repr__(self):
return "<Address(email_address='%s')>"%self.email_address
Base.metadata.create_all(engine) #创建表结构
此时我们已经与user表id有外键关联了，在实际的模型中，一个用户通常有多个email地址，一个email地址对应一个用户，这就是数据库设计中一对多关系， 下面我们添加几条数据
user_obj1 = User(fullname = "lxj",password = "123456") user_obj2 = User(fullname = "sx",password = "123456") Session.add_all([user_obj1,user_obj2]) Session.commit() addr_obj1 = Address(email_address = "aa@qq.com",user_id = 1) addr_obj2 = Address(email_address = "bb@qq.com",user_id = 2) addr_obj3 = Address(email_address = "cc@qq.com",user_id = 1) Session.add_all([addr_obj1,addr_obj2,addr_obj3]) Session.commit()
如果我们在address表中添加的user_id字段在user表中找不到，那么会报错，同mysql外键一样，会检查一致性
此时我们来实现下，之前join报错的那个例子
user = Session.query(User).join(Address) print(user,user.all()) #SELECT user.id AS user_id, user.fullname AS user_fullname, user.password AS user_password FROM user INNER JOIN addresses ON user.id = addresses.user_id [<User(id='1',fullname='lxj', password='123456')>, <User(id='2',fullname='sx', password='123456')>]
当有外键关联的时候，join的时候就不需要指定列
接下来我们讲下另外一种场景，如果我们要查user表中lxj所有的邮箱地址，可以用这种方法
user = Session.query(Address).join(User).filter(User.fullname == 'lxj') print(user,user.all()) #结果： SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id FROM addresses INNER JOIN user ON user.id = addresses.user_id WHERE user.fullname = %(fullname_1)s [<Address(email_address='aa@qq.com')>, <Address(email_address='cc@qq.com')>]
但是上面的例子是通过address表来查询，但是在通常情况下我们都是通过user来搜索，这时我们就要在address表中定义一个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") #这个其实是在内存中，并不在mysql表的关系
def __repr__(self):
return "<Address(email_address='%s')>"%self.email_address
user1 = Session.query(User).filter(User.fullname == 'lxj').first() #当我们在address表中定义relationship时，此时我们就能通过user表address查找对应地址信息 print(user1.addresses) address = Session.query(Address).first() #还可以通过address表反查user print(address,address.user.fullname) #结果 [<Address(email_address='aa@qq.com')>, <Address(email_address='cc@qq.com')>] <Address(email_address='aa@qq.com')> lxj
多外键关联
One of the most common situations to deal with is when there are more than one foreign key path between two tables.
Consider a Customer class that contains two foreign keys to an Address class:
下表中，Customer表有2个字段都关联了Address表
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy import Column,String,Integer,ForeignKey,distinct
from sqlalchemy.orm import relationship,sessionmaker
Base = declarative_base() #声明基类
engine = create_engine("mysql+pymysql://root:123456@localhost:3306/testdb?charset=utf8") #echo = True打印生成对象的所有过程,如果要插入中文，一定要加?charset=utf8
class Customer(Base):
"""
定义表结构，映射到数据库中
"""
__tablename__ = 'customer' #表名
id = Column(Integer,primary_key=True)
name = Column(String(64))
billing_address_id = Column(Integer, ForeignKey("address.id"))
shipping_address_id = Column(Integer, ForeignKey("address.id"))
billing_address = relationship("Address")
shipping_address = relationship("Address")
def __repr__(self): #对实例化的对象格式化输出
return "<Customer(id='%s',name='%s')>" % (
self.id, self.name)
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, primary_key=True)
street = Column(String(64))
city = Column(String(64))
state = Column(String(64))
def __repr__(self):
return "<Address(street='%s',city='%s',state='%s')>"%(self.state,self.city ,self.street)
Base.metadata.create_all(engine) #创建表结构
Session_class = sessionmaker(bind = engine) #创建与数据库的会话session class ,注意,这里session_class是类,不是实例
Session = Session_class() #生成Session实例
address_obj = Address(street = 'jiuhelu',city = 'hangzhou',state = 'zhejiang')
Session.add(address_obj)
Session.commit()
创建表没有问题，但是插入address数据会报错
sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Customer.billing_address - there are multiple foreign key paths linking the tables. Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference to the parent table.
修改方法：
class Customer(Base):
"""
定义表结构，映射到数据库中
"""
__tablename__ = 'customer' #表名
id = Column(Integer,primary_key=True)
name = Column(String(64))
billing_address_id = Column(Integer, ForeignKey("address.id"))
shipping_address_id = Column(Integer, ForeignKey("address.id"))
billing_address = relationship("Address",foreign_keys = [billing_address_id] )
shipping_address = relationship("Address",foreign_keys = [shipping_address_id])
def __repr__(self): #对实例化的对象格式化输出
return "<Customer(id='%s',name='%s')>" % (
self.id, self.name)
这样sqlachemy就能分清哪个外键是对应哪个字段了
插入数据
address_obj = Address(street = 'chengfa',city = 'hangzhou',state = 'zhejiang') address_obj1 = Address(street = 'shimen',city = 'jiangshan',state = 'zhejiang') address_obj2 = Address(street = 'dongyuelu',city = 'jiangshan',state = 'zhejiang') Session.add_all([address_obj,address_obj1,address_obj2]) c1 = Customer(name = 'lxj',billing_address = address_obj,shipping_address = address_obj2) c2 = Customer(name = 'sx',billing_address = address_obj1,shipping_address = address_obj1) Session.add_all([c1,c2]) Session.commit()
查询
obj = Session.query(Customer).filter(Customer.name=='lxj').first() print(obj,obj.billing_address,obj.shipping_address) Session.commit() #结果： <Customer(id='1',name='lxj')> <Address(street='zhejiang',city='hangzhou',state='chengfa')> <Address(street='zhejiang',city='jiangshan',state='dongyuelu')>
多对多关联
我们思考一种情况，一个学生对应多个班级，一个班级又有好多学生，这种多对多的关系表结构该如何设计呢？假设我们的班级信息和学生信息是这样
那应该如何定义外键呢？总不能在一个字段里存好几个班级的id吧！更不可能创建多条学员记录来关联班级，那这样就更low了，改个学生信息，所有都要改，而且太冗余了，最好的办法是创建第三张表来关联2这两张表。
下面我们来看看sqlalchemy的具体实现
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy import Column,String,Integer,ForeignKey,distinct,Date,Table
from sqlalchemy.orm import relationship
Base = declarative_base() #声明基类
engine = create_engine("mysql+pymysql://root:123456@localhost:3306/testdb?charset=utf8") #echo = True打印生成对象的所有过程,如果要插入中文，一定要加?charset=utf8
stu_m2m_cls = Table("stu_m2m_cls",Base.metadata,
Column('stu_id',Integer, ForeignKey("students.id")),
Column("cls_id",Integer, ForeignKey("classes.id"))
) #创建第三张表来关联students表和classes表
class Student(Base):
__tablename__ = 'students'
id = Column(Integer,primary_key=True)
name = Column(String(32),nullable=False)
age = Column(Integer,nullable=False)
register_date = Column(Date)
def __repr__(self):
return "<name:%s,age:%s,register_date:%s>"%(self.name,self.age,self.register_date)
class Class(Base):
__tablename__ = 'classes'
id = Column(Integer, primary_key=True)
cls = Column(String(32),nullable=False)
students = relationship("Student",secondary = stu_m2m_cls,backref = "classes") #通过relationship来建立第三张表和class、student建立关系，这条语句也可写在Student种
def __repr__(self):
return "<培训班:%s>"%self.cls
Base.metadata.create_all(engine) #创建表结构
插入数据
stu_obj1 = Student(name = "lxj",age = 21,register_date ="2017-05-12" )
stu_obj2 = Student(name = "sx",age = 21,register_date ="2017-05-10" )
stu_obj3 = Student(name = "lss",age = 3,register_date ="2017-05-12" )
cls_obj1 = Class(cls = "python培训" )
cls_obj2 = Class(cls = "linux培训" )
cls_obj3 = Class(cls = "mysql培训" )
stu_obj1.classes = [cls_obj1,cls_obj2] #建立学生表与班级表建立关系，这里最好直接用.1、单个要添加用stu_obj1.classes.append(cls_obj)2、多个添加用stu_obj1.classes.extend([stu_obj1,stu_obj2])
#踩过的坑，不然会覆盖之前的记录，在这里记录下。
stu_obj2.classes = [cls_obj3]
Session.add_all([stu_obj2,stu_obj1,stu_obj3,cls_obj3,cls_obj2,cls_obj1])
Session.commit()
登陆mysql查看表的记录
查询
stu_obj = Session.query(Student).filter(Student.name == "lxj").first() print(stu_obj.classes) cls_obj = Session.query(Class).filter(Class.cls =='python培训').first() #反查 print(cls_obj.students) 结果： [<培训班:python培训>, <培训班:linux培训>] [<name:lxj,age:21,register_date:2017-05-12>]
更多详见：http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#one-to-many