Python_Day13_ORM sqlalchemy

orm英文全称object relational mapping,就是对象映射关系程序，简单来说我们类似python这种面向对象的程序来说一切皆对象，但是我们使用的数据库却都是关系型的，为了保证一致的使用习惯，通过orm将编程语言的对象模型和数据库的关系模型建立映射关系，这样我们在使用编程语言对数据库进行操作的时候可以直接使用编程语言的对象模型进行操作就可以了，而不用直接使用sql语言

优点：

• 隐藏了数据访问细节，“封闭”的通用数据库交互，ORM的核心。他使得我们的通用数据库交互变得简单易行，并且完全不用考虑该死的SQL语句。快速开发，由此而来
• ORM使我们构造固化数据结构变得简单易行

缺点：

• 无可避免的，自动化意味着映射和关联管理，代价是牺牲性能（早期，这是所有不喜欢ORM人的共同点）。现在的各种ORM框架都在尝试使用各种方法来减轻这块（LazyLoad，Cache），效果还是很显著的

 

二、SQLAlchemy

SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果。

 

在Python中，最有名的ORM框架是SQLAlchemy。用户包括openstack＼Dropbox等知名公司或应用

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

注：支持连接MySQL、Oracles数据库

安装：

pip install SQLAlchemy pip install pymysql  #由于mysqldb依然不支持py3，所以这里我们用pymysql与sqlalchemy交互    

步骤一：

使用 Engine/ConnectionPooling/Dialect 进行数据库操作，Engine使用ConnectionPooling连接数据库，然后再通过Dialect执行SQL语句。

#!/usr/bin/env python # -*- coding:utf-8 -*-   from sqlalchemy import create_engine     engine = create_engine("mysql+mysqldb://root:123@127.0.0.1:3306/s11", max_overflow=5)   engine.execute(     "INSERT INTO ts_test (a, b) VALUES (‘2‘, ‘v1‘)" )   engine.execute(      "INSERT INTO ts_test (a, b) VALUES (%s, %s)",     ((555, "v1"),(666, "v1"),) ) engine.execute(     "INSERT INTO ts_test (a, b) VALUES (%(id)s, %(name)s)",     id=999, name="v1" )   result = engine.execute(‘select * from ts_test‘) result.fetchall()   事务操作：

<span style="color: #008000">#<span style="color: #008000">!/usr/bin/env python<span style="color: #008000">
#<span style="color: #008000"> -*- coding:utf-8 -*-
<span style="color: #0000ff">from sqlalchemy <span style="color: #0000ff">import<span style="color: #000000"> create_engine
engine = create_engine(<span style="color: #800000">"<span style="color: #800000">mysql+mysqldb://root:123@127.0.0.1:3306/s11<span style="color: #800000">", max_overflow=5<span style="color: #000000">)
<span style="color: #008000">#<span style="color: #008000"> 事务操作
<span style="color: #000000">with engine.begin() as conn:
    conn.execute(<span style="color: #800000">"<span style="color: #800000">insert into table (x, y, z) values (1, 2, 3)<span style="color: #800000">"<span style="color: #000000">)
    conn.execute(<span style="color: #800000">"<span style="color: #800000">my_special_procedure(5)<span style="color: #800000">"<span style="color: #000000">)
    
    
conn =<span style="color: #000000"> engine.connect()
<span style="color: #008000">#<span style="color: #008000"> 事务操作 
<span style="color: #000000">with conn.begin():
       conn.execute(<span style="color: #800000">"<span style="color: #800000">some statement<span style="color: #800000">", {<span style="color: #800000">‘<span style="color: #800000">x<span style="color: #800000">‘:5, <span style="color: #800000">‘<span style="color: #800000">y<span style="color: #800000">‘:10})<br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

注：查看数据库连接：show status like ‘Threads%‘;

步骤二：

使用 Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect 进行数据库操作。Engine使用Schema Type创建一个特定的结构对象，之后通过SQL Expression Language将该对象转换成SQL语句，然后通过 ConnectionPooling 连接数据库，再然后通过 Dialect 执行SQL，并获取结果。

#!/usr/bin/env python # -*- coding:utf-8 -*-   from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKey   metadata = MetaData()   user = Table(‘user‘, metadata,     Column(‘id‘, Integer, primary_key=True),     Column(‘name‘, String(20)), )   color = Table(‘color‘, metadata,     Column(‘id‘, Integer, primary_key=True),     Column(‘name‘, String(20)), ) engine = create_engine("mysql+mysqldb://root:123@127.0.0.1:3306/s11", max_overflow=5)   metadata.create_all(engine) # metadata.clear() # metadata.remove()  

<span style="color: #008000">#<span style="color: #008000">!/usr/bin/env python<span style="color: #008000">
#<span style="color: #008000"> -*- coding:utf-8 -*-
<span style="color: #0000ff">from sqlalchemy <span style="color: #0000ff">import<span style="color: #000000"> create_engine, Table, Column, Integer, String, MetaData, ForeignKey
metadata =<span style="color: #000000"> MetaData()
user = Table(<span style="color: #800000">‘<span style="color: #800000">user<span style="color: #800000">‘<span style="color: #000000">, metadata,
    Column(<span style="color: #800000">‘<span style="color: #800000">id<span style="color: #800000">‘, Integer, primary_key=<span style="color: #000000">True),
    Column(<span style="color: #800000">‘<span style="color: #800000">name<span style="color: #800000">‘, String(20<span style="color: #000000">)),
)
color = Table(<span style="color: #800000">‘<span style="color: #800000">color<span style="color: #800000">‘<span style="color: #000000">, metadata,
    Column(<span style="color: #800000">‘<span style="color: #800000">id<span style="color: #800000">‘, Integer, primary_key=<span style="color: #000000">True),
    Column(<span style="color: #800000">‘<span style="color: #800000">name<span style="color: #800000">‘, String(20<span style="color: #000000">)),
)
engine = create_engine(<span style="color: #800000">"<span style="color: #800000">mysql+mysqldb://root:123@127.0.0.1:3306/s11<span style="color: #800000">", max_overflow=5<span style="color: #000000">)
conn =<span style="color: #000000"> engine.connect()
<span style="color: #008000">#<span style="color: #008000"> 创建SQL语句，INSERT INTO "user" (id, name) VALUES (:id, :name)
conn.execute(user.insert(),{<span style="color: #800000">‘<span style="color: #800000">id<span style="color: #800000">‘:7,<span style="color: #800000">‘<span style="color: #800000">name<span style="color: #800000">‘:<span style="color: #800000">‘<span style="color: #800000">seven<span style="color: #800000">‘<span style="color: #000000">})
conn.close()
<span style="color: #008000">#<span style="color: #008000"> sql = user.insert().values(id=123, name=‘wu‘)<span style="color: #008000">
#<span style="color: #008000"> conn.execute(sql)<span style="color: #008000">
#<span style="color: #008000"> conn.close()
<span style="color: #008000">#<span style="color: #008000"> sql = user.delete().where(user.c.id > 1)
<span style="color: #008000">#<span style="color: #008000"> sql = user.update().values(fullname=user.c.name)<span style="color: #008000">
#<span style="color: #008000"> sql = user.update().where(user.c.name == ‘jack‘).values(name=‘ed‘)
<span style="color: #008000">#<span style="color: #008000"> sql = select([user, ])<span style="color: #008000">
#<span style="color: #008000"> sql = select([user.c.id, ])<span style="color: #008000">
#<span style="color: #008000"> sql = select([user.c.name, color.c.name]).where(user.c.id==color.c.id)<span style="color: #008000">
#<span style="color: #008000"> sql = select([user.c.name]).order_by(user.c.name)<span style="color: #008000">
#<span style="color: #008000"> sql = select([user]).group_by(user.c.name)
<span style="color: #008000">#<span style="color: #008000"> result = conn.execute(sql)<span style="color: #008000">
#<span style="color: #008000"> print result.fetchall()<span style="color: #008000">
#<span style="color: #008000"> conn.close()</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span><br><br>

注：SQLAlchemy无法修改表结构，如果需要可以使用SQLAlchemy开发者开源的另外一个软件Alembic来完成。

步骤三：

使用 ORM/Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect 所有组件对数据进行操作。根据类创建对象，对象转换成SQL，执行SQL。

#!/usr/bin/env python # -*- coding:utf-8 -*-   from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String from sqlalchemy.orm import sessionmaker from sqlalchemy import create_engine   engine = create_engine("mysql+mysqldb://root:123@127.0.0.1:3306/s11", max_overflow=5)   Base = declarative_base()     class User(Base):     __tablename__ = ‘users‘     id = Column(Integer, primary_key=True)     name = Column(String(50))   # 寻找Base的所有子类，按照子类的结构在数据库中生成对应的数据表信息 # Base.metadata.create_all(engine)   Session = sessionmaker(bind=engine) session = Session()     # ########## 增 ########## # u = User(id=2, name=‘sb‘) # session.add(u) # session.add_all([ #     User(id=3, name=‘sb‘), #     User(id=4, name=‘sb‘) # ]) # session.commit()   # ########## 删除 ########## # session.query(User).filter(User.id > 2).delete() # session.commit()   # ########## 修改 ########## # session.query(User).filter(User.id > 2).update({‘cluster_id‘ : 0}) # session.commit() # ########## 查 ########## # ret = session.query(User).filter_by(name=‘sb‘).first()   # ret = session.query(User).filter_by(name=‘sb‘).all() # print ret   # ret = session.query(User).filter(User.name.in_([‘sb‘,‘bb‘])).all() # print ret   # ret = session.query(User.name.label(‘name_label‘)).all() # print ret,type(ret)   # ret = session.query(User).order_by(User.id).all() # print ret   # ret = session.query(User).order_by(User.id)[1:3] # print ret # session.commit()

 

 

1、基本使用

 

CREATE TABLE user (     id INTEGER NOT NULL AUTO_INCREMENT,     name VARCHAR(32),     password VARCHAR(64),     PRIMARY KEY (id) ) 这只是最简单的sql表，如果再加上外键关联什么的，一般程序员的脑容量是记不住那些sql语句的，于是有了orm,实现上面同样的功能，代码如下： # 创建表结构   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:zyw@123@192.168.20.219/lzl",                        encoding="utf-8",echo=True)      #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)    #创建表结构   # 打印输出 # 2016-10-26 08:42:02,619 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE ‘sql_mode‘ # 2016-10-26 08:42:02,619 INFO sqlalchemy.engine.base.Engine {} # 2016-10-26 08:42:02,622 INFO sqlalchemy.engine.base.Engine SELECT DATABASE() # 2016-10-26 08:42:02,622 INFO sqlalchemy.engine.base.Engine {} # 2016-10-26 08:42:02,624 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = ‘utf8‘ and `Collation` = ‘utf8_bin‘ # 2016-10-26 08:42:02,624 INFO sqlalchemy.engine.base.Engine {} # 2016-10-26 08:42:02,649 INFO sqlalchemy.engine.base.Engine SELECT CAST(‘test plain returns‘ AS CHAR(60)) AS anon_1 # 2016-10-26 08:42:02,649 INFO sqlalchemy.engine.base.Engine {} # 2016-10-26 08:42:02,651 INFO sqlalchemy.engine.base.Engine SELECT CAST(‘test unicode returns‘ AS CHAR(60)) AS anon_1 # 2016-10-26 08:42:02,651 INFO sqlalchemy.engine.base.Engine {} # 2016-10-26 08:42:02,652 INFO sqlalchemy.engine.base.Engine SELECT CAST(‘test collated returns‘ AS CHAR CHARACTER SET utf8) # COLLATE utf8_bin AS anon_1 # 2016-10-26 08:42:02,652 INFO sqlalchemy.engine.base.Engine {} # 2016-10-26 08:42:02,655 INFO sqlalchemy.engine.base.Engine DESCRIBE `user` # 2016-10-26 08:42:02,655 INFO sqlalchemy.engine.base.Engine {} # 2016-10-26 08:42:02,657 INFO sqlalchemy.engine.base.Engine ROLLBACK # 2016-10-26 08:42:02,660 INFO sqlalchemy.engine.base.Engine # CREATE TABLE user ( #   id INTEGER NOT NULL AUTO_INCREMENT, #   name VARCHAR(32), #   password VARCHAR(64), #   PRIMARY KEY (id) # ) # # # 2016-10-26 08:42:02,660 INFO sqlalchemy.engine.base.Engine {} # 2016-10-26 08:42:02,904 INFO sqlalchemy.engine.base.Engine COMMIT mysql> desc user; +----------+-------------+------+-----+---------+----------------+ | Field    | Type        | Null | Key | Default | Extra          | +----------+-------------+------+-----+---------+----------------+ | id       | int(11)     | NO   | PRI | NULL    | auto_increment | | name     | varchar(32) | YES  |     | NULL    |                | | password | varchar(64) | YES  |     | NULL    |                | +----------+-------------+------+-----+---------+----------------+ 3 rows in set (0.01 sec)
 

2、创建数据

最基本的表我们创建好了，那我们开始用orm创建一条数据试试

 

# 创建表数据   from sqlalchemy import  create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import String,Integer,Column from sqlalchemy.orm import sessionmaker   engine = create_engine("mysql+pymysql://root:zyw@123@192.168.20.219/lzl",                        encoding="utf-8")   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)    #创建表结构   Session_class = sessionmaker(bind=engine)   #Session_class现在不是实例，而是类 Session = Session_class()   #生成Session实例   user_obj = User(name="lzl",password="123456")   #生成你要创建的数据对象 print(user_obj.name,user_obj.id)  #此时还没创建对象呢，不信你打印一下id发现还是None   Session.add(user_obj) #把要创建的数据对象添加到这个session里， 一会统一创建 print(user_obj.name,user_obj.id) #此时也依然还没创建   Session.commit()    #现此才统一提交，创建数据   3、查询数据 # 查询   from sqlalchemy import  create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import String,Integer,Column from sqlalchemy.orm import sessionmaker   engine = create_engine("mysql+pymysql://root:zyw@123@192.168.20.219/lzl",                        encoding="utf-8")   Base = declarative_base()   #生成orm基类   class User(Base):     __tablename__ = "user"      #表名     id = Column(Integer,primary_key=True)     name = Column(String(32))     password = Column(String(64))   Session_class = sessionmaker(bind=engine)   #Session_class现在不是实例，而是类 Session = Session_class()   #生成Session实例   my_user = Session.query(User).filter_by(name="lzl").first() print(my_user)          #my_user此时是一个对象 #<__main__.User object at 0x03EFC6D0>   print(my_user.id,my_user.name,my_user.password) 多条件查询： from sqlalchemy import  create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import String,Integer,Column from sqlalchemy.orm import sessionmaker from sqlalchemy import func   engine = create_engine("mysql+pymysql://root:zyw@123@192.168.20.219/lzl",                        encoding="utf-8")   Base = declarative_base()   #生成orm基类   class User(Base):     __tablename__ = "user"      #表名     id = Column(Integer,primary_key=True)     name = Column(String(32))     password = Column(String(64))       def __repr__(self):         return "<User(name=‘%s‘,  password=‘%s‘)>" % (         self.name, self.password)   Session_class = sessionmaker(bind=engine)   #Session_class现在不是实例，而是类 Session = Session_class()   #生成Session实例   objs = Session.query(User).filter(User.id>3).filter(User.id<8).all() print(objs) 统计： # 统计   from sqlalchemy import  create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import String,Integer,Column from sqlalchemy.orm import sessionmaker from sqlalchemy import func   engine = create_engine("mysql+pymysql://root:zyw@123@192.168.20.219/lzl",                        encoding="utf-8")   Base = declarative_base()   #生成orm基类   class User(Base):     __tablename__ = "user"      #表名     id = Column(Integer,primary_key=True)     name = Column(String(32))     password = Column(String(64))       def __repr__(self):         return "<User(name=‘%s‘,  password=‘%s‘)>" % (         self.name, self.password)   Session_class = sessionmaker(bind=engine)   #Session_class现在不是实例，而是类 Session = Session_class()   #生成Session实例   分组： # 分组   from sqlalchemy import  create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import String,Integer,Column from sqlalchemy.orm import sessionmaker from sqlalchemy import func   engine = create_engine("mysql+pymysql://root:zyw@123@192.168.20.219/lzl",                        encoding="utf-8")   Base = declarative_base()   #生成orm基类   class User(Base):     __tablename__ = "user"      #表名     id = Column(Integer,primary_key=True)     name = Column(String(32))     password = Column(String(64))       def __repr__(self):         return "<User(name=‘%s‘,  password=‘%s‘)>" % (         self.name, self.password)   Session_class = sessionmaker(bind=engine)   #Session_class现在不是实例，而是类 Session = Session_class()   #生成Session实例   print(Session.query(func.count(User.name),User.name).group_by(User.name).all()) 4、修改   # 修改   from sqlalchemy import  create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import String,Integer,Column from sqlalchemy.orm import sessionmaker   engine = create_engine("mysql+pymysql://root:zyw@123@192.168.20.219/lzl",                        encoding="utf-8")   Base = declarative_base()   #生成orm基类   class User(Base):     __tablename__ = "user"      #表名     id = Column(Integer,primary_key=True)     name = Column(String(32))     password = Column(String(64))       def __str__(self):         return "<User(name=‘%s‘,  password=‘%s‘)>" % (         self.name, self.password)   Session_class = sessionmaker(bind=engine)   #Session_class现在不是实例，而是类

• < 上一篇

  ORACLE查询语句

• 下一篇 >

  postgresql常用命令