概述
本节内容
orm英文全称object relational mapping,就是对象映射关系程序,简单来说我们类似python这种面向对象的程序来说一切皆对象,但是我们使用的数据库却都是关系型的,为了保证一致的使用习惯,通过orm将编程语言的对象模型和数据库的关系模型建立映射关系,这样我们在使用编程语言对数据库进行操作的时候可以直接使用编程语言的对象模型进行操作就可以了,而不用直接使用sql语言。
orm的优点:
缺点:
在Python中,最有名的ORM框架是sqlAlchemy。用户包括openstack\DropBox等知名公司或应用,主要用户列表http://www.sqlalchemy.org/organizations.html#openstack
4194de6.png" alt="" width="661" height="463">
Dialect用于和数据API进行交流,根据配置文件的不同调用不同的数据库API,从而实现对数据库的操作,如:
MysqL-Python MysqL+MysqLdb://: @ [: ]/ pyMysqL
MysqL+pyMysqL://: @ / [? ] mysql-connector
MysqL+MysqLconnector://: @ [: ]/ cx_Oracle
oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]更多详见:http://docs.sqlalchemy.org/en/latest/dialects/index.html
pyMysqL
MysqL+pyMysqL://
mysql-connector
MysqL+MysqLconnector://
cx_Oracle
oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]
更多详见:http://docs.sqlalchemy.org/en/latest/dialects/index.html
pyMysqL
MysqL+pyMysqL://
mysql-connector
MysqL+MysqLconnector://
cx_Oracle
oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]
更多详见:http://docs.sqlalchemy.org/en/latest/dialects/index.html
安装sqlalchemy
sqlAlchemypip install pyMysqL #由于MysqLdb依然不支持py3,所以这里我们用pyMysqL与sqlalchemy交互
下面就开始让你见证orm的nb之处,盘古开天劈地之前,我们创建一个表是这样的
sql;gutter:true;">CREATE TABLE user ( id INTEGER NOT NULL AUTO_INCREMENT,name VARCHAR(32),password VARCHAR(64),PRIMARY KEY (id) )
这只是最简单的sql表,如果再加上外键关联什么的,一般程序员的脑容量是记不住那些sql语句的,于是有了orm,实现上面同样的功能,代码如下
sqlalchemy from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column,Integer,Stringengine = 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) #创建表结构
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) #创建表结构
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) #创建表结构
你说,娘那个腚的,并没有感觉代码量变少啊,呵呵, 孩子莫猴急,好戏在后面
Lazy ConnectingThe Engine,when first returned by create_engine(),has not actually tried to connect to the database yet; that happens only the first time it is asked to perform a task against the database.
除上面的创建之外,还有一种创建表的方式,虽不常用,但还是看看吧
sqlalchemy import Table,MetaData,Column,String,ForeignKey from sqlalchemy.orm import mapperuser = Table('user',Metadata,Column('id',primary_key=True),Column('name',String(50)),Column('fullname',Column('password',String(12))
)class User(object):
def init(self,name,fullname,password):
self.name = name
self.fullname = fullname
self.password = passwordmapper(User,user) #the table Metadata is created separately with the Table construct,then associated with the User class via the mapper() function
user = Table('user',Metadata,Column('id',primary_key=True),Column('name',String(50)),Column('fullname',Column('password',String(12))
)
class User(object):
def init(self,name,fullname,password):
self.name = name
self.fullname = fullname
self.password = password
mapper(User,user) #the table Metadata is created separately with the Table construct,then associated with the User class via the mapper() function
user = Table('user',Metadata,Column('id',primary_key=True),Column('name',String(50)),Column('fullname',Column('password',String(12))
)
class User(object):
def init(self,name,fullname,password):
self.name = name
self.fullname = fullname
self.password = password
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,注意,这里返回给session的是个class,不是实例 Session = Session_class() #生成session实例user_obj = User(name="alex",password="alex3714") #生成你要创建的数据对象
print(user_obj.name,user_obj.id) #此时还没创建对象呢,不信你打印一下id发现还是NoneSession.add(user_obj) #把要创建的数据对象添加到这个session里, 一会统一创建
print(user_obj.name,user_obj.id) #此时也依然还没创建Session.commit() #现此才统一提交,创建数据
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() #现此才统一提交,创建数据
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() #现此才统一提交,创建数据
我擦,写这么多代码才创建一条数据,你表示太tm的费劲了,正要转身离开,我拉住你的手不放开,高潮还没到。。
此时你看到的输出是这样的应该
我擦,这是什么?这就是你要的数据呀, 只不过sqlalchemy帮你把返回的数据映射成一个对象啦,这样你调用每个字段就可以跟调用对象属性一样啦,like this..
输出
1 alex alex3714
不过刚才上面的显示的内存对象对址你是没办法分清返回的是什么数据的,除非打印具体字段看一下,如果想让它变的可读,只需在定义表的类下面加上这样的代码
" % ( self.name,self.password)
my_user.name = "Alex Li"Session.commit()
Session.commit()
Session.commit()
回滚
fake_user = User(name='Rain',password='12345')
Session.add(fake_user)print(Session.query(User).filter(User.name.in_(['Jack','rain'])).all() ) #这时看session里有你刚添加和修改的数据
Session.rollback() #此时你rollback一下
print(Session.query(User).filter(User.name.in_(['Jack','rain'])).all() ) #再查就发现刚才添加的数据没有了。
Session
Session.commit()
print(Session.query(User).filter(User.name.in_(['Jack','rain'])).all() ) #这时看session里有你刚添加和修改的数据
Session.rollback() #此时你rollback一下
print(Session.query(User).filter(User.name.in_(['Jack','rain'])).all() ) #再查就发现刚才添加的数据没有了。
print(Session.query(User).filter(User.name.in_(['Jack','rain'])).all() ) #这时看session里有你刚添加和修改的数据
Session.rollback() #此时你rollback一下
print(Session.query(User).filter(User.name.in_(['Jack','rain'])).all() ) #再查就发现刚才添加的数据没有了。
获取所有数据
多条件查询
0).filter(User.id<7).all()
上面2个filter的关系相当于 user.id >1 AND user.id <7 的效果
统计和分组
分组
sqlalchemy import func print(Session.query(func.count(User.name),User.name).group_by(User.name).all() )
相当于原生sql为
输出为
[(1,'Jack'),(2,'Rain')]
我们创建一个addresses表,跟user表关联
sqlalchemy import ForeignKey from sqlalchemy.orm import relationshipclass 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
user = relationship("User",backref="addresses") #这个nb,允许你在user表里通过backref字段反向查出所有它在addresses表里的关联项
def __repr__(self):
return "<Address(email_address='%s')>" % self.email_address
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'))
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
The parameter is a newer version of a very common sqlAlchemy feature called. The parameter hasn’t gone anywhere and will always remain available! The is the same thing,except a little more verbose and easier to manipulate. For an overview of the entire topic,see the section sqlalchemy.org/en/latest/orm/backref.html#relationships-backref">Linking Relationships with Backref.
表创建好后,我们可以这样反查试试
addr_obj = Session.query(Address).first()
print(addr_obj.user.name) #在addr_obj里直接查关联的user表
创建关联对象
obj.addresses = [Address(email_address="r1@126.com"),#添加关联对象
Address(email_address="r2@126.com")]Session.commit()
Session.commit()
Session.commit()
Here’s a rundown of some of the most common operators used in filter():
equals:
query.filter(User.name == 'ed')
not equals:
query.filter(User.name != 'ed')
LIKE:
query.filter(User.name.like('%ed%'))
IN:
NOT IN: query.filter(~User.name.in_(['ed','wendy','jack']))
</li>
IS NULL:
</li>
IS NOT NULL:
</li>
AND: 2.1. ObjectRelationalTutorial 17
</li>
</ul>
</div>
<div class="section">
<div class="layoutArea">
<div class="column">
query.filter(User.name.in_(['ed','jack']))
# works with query objects too:
query.filter(User.name.in_( session.query(User.name).filter(User.name.like('%ed%'))
))
如果您也喜欢它,动动您的小指点个赞吧