Python ORM

本章内容

1. ORM介绍
2. sqlalchemy安装
3. sqlalchemy基本使用
4. 多外键关联
5. 多对多关系
6. 表结构设计作业

ORM介绍

　　如果写程序用pymysql和程序交互，那是不是要写原生sql语句。如果进行复杂的查询，那sql语句就要进行一点一点拼接，而且不太有重用性，扩展不方便。而且写的sql语句可能不高效，导致程序运行也变慢。
为了避免把sql语句写死在代码里，有没有一种方法直接把原生sql封装好了并且以你熟悉的方式操作，像面向对象那样？

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

　　ORM 相当于把数据库也给你实例化了，在代码操作MySQL中级又加了orm这一层。

　　

　　ORM的优点：

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

　　缺点：

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

sqlalchemy安装

　　在python中，最有名的ORM框架是SQLAlchemy。用户包括OpenStack＼Dropbox等知名公司或应用，主要用户列表http://www.sqlalchemy.org/organizations.html#openstack

　　安装sqlalchemy

pip install SQLAlchemy
pip install pymysql
# 由于mysqldb依然不支持py3，所以这里我们用pymysql与sqlalchemy交互
# 虽然大家可能在python2.x中用习惯了mysqldb，但是在python3.x中已经不支持那个组件了。取而代之的是：pymysql

sqlalchemy基本使用

　　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...]

　　基本的增、删、改、查

import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker
 
 
Base = declarative_base()
 
 class User(Base):
     __tablename__ = 'user'                         #表名
     id = Column(Integer, primary_key=True)
     username = Column(String(100))
     password = Column(String(100))

    #定义函数的输出方式，不然会以对象的形式输出，<__main__.User object at 0x105b4ba90>
　　 def __repr__(self):              

   　   return  'name:%s,password:%s'%(self.username,self.password)

 #创建实例并连接数据库
 engine = create_engine("mysql+mysqldb://woniu:1234@59.110.12.72:3306/wangjin?charset=utf8",echo=True)  #echo 输出日志的信息
 # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
 db_session = sessionmaker(bind=engine)          # 实例和engine绑定
 session = db_session()                          # 生成session实例，相当于游标
 
 #add user
 user_obj = User(username='hello',password='python')                 # 生成你要创建的数据对象
 session.add(user_obj)                           # 把要创建的数据对象添加到这个session里
 session.commit()
 print user_obj.username
 print user_obj.password         #现此才统一提交，创建数据
 
 
 #query
 my_user = session.query(User).filter_by(username='hello').first()     #根据条件去查询
 
 print my_user.username,my_user.password
 
 
 #edit
 edit_user = session.query(User).filter_by(username='hello').all()
 edit_user.username = 'woniu'                                            #赋值
 edit_user.password = 'hehe'
 
 session.commit()
 print my_user.username,my_user.password
 
 #del   
 del_user = session.query(User).filter_by(username='hello').all()
 for i in del_user:
     session.delete(i)
 session.commit()

 #rallback
 my_user = Session.query(User).filter_by(id=1).first()
 my_user.name = "Jack"
  
 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()

　  filter_by与filter的区别

my_user2 = Session.query(User).filter_by(id=27).all() # filter_by相等用‘=’ 　　

my_user3 = Session.query(User).filter(User.id==27).all() # filter相等用‘==’

用法不同而已，filter 可以像写 sql 的 where 条件那样写 > < 等条件，但引用列名时，需要通过 类名.属性名 的方式。 filter_by 可以使用 python 的正常参数传递方法传递条件，指定列名时，不需要额外指定类名。，参数名对应名类中的属性名

　  多条件查询

　  objs = Session.query(User).filter(User.id>0).filter(User.id<7).all()

　  上面2个filter的关系相当于 user.id >1 AND user.id <7 的效果

　　      from sqlalchemy import and_, or_ # 且和or的关系

　　      ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all() # 条件以and方式排列

　　      ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'eric')).all() # 条件以or方式排列

 　　分组统计　

from sqlalchemy import func
print(Session.query(func.count(User.name),User.name).group_by(User.name).all() )

Session.query(User).filter(User.name.like("Ra%")).count().all()

#输出结果
4
[(1, 'alex'), (1, 'chongyang'), (4, 'liming')

 外键关联

import sqlalchemy

from sqlalchemy import create_engine,func,ForeignKey
from sqlalchemy.ext.declarative import  declarative_base
from sqlalchemy import Column,Integer,String
from sqlalchemy.orm import sessionmaker,relationship

engine = create_engine("mysql+pymysql://woniu:123456@59.110.12.72/txadmin",encoding='utf-8')

Base = declarative_base()

class Student(Base):
    __tablename__ = 'student'
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    password = Column(String(64))

    def __repr__(self):
        return  'name:%s,password:%s'%(self.name,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('student.id'))

#允许你在student表里通过backref字段反向查出所有它在addresses表里的关联项
    student = relationship('Student',backref='addresses')

    def __repr__(self):
        return  'Address(email_address="%s")'%self.email_address


Base.metadata.create_all(engine)

db_session = sessionmaker(bind=engine)
session = db_session()

#add user
# user_obj = Student(name = 'alex',password='1234')
# session.add(user_obj)
# session.commit()
# print(user_obj.name)
# print(user_obj.password)
# 如果有多个对象需要添加，可以如下使用
  session.add_all([obj1,obj2,obj3])
#add_address
# add_obj = Address(email_address = 'hebeisheng',user_id = '2')
# session.add(add_obj)
# session.commit()
# #query
# # my_user = session.query(Student).filter_by(name='liming').all()
# res = session.query(func.count(Student.name),Student.name).group_by(Student.name).all()
#
# res1 = session.query(Student).filter(Student.name.like('li%')).count()
# print(res1)
# print(res)

# obj = session.query(Student).all()
#
#
# for i in obj:
#     print(i.addresses)

#在addr_obj里直接插关联的student表
# addr_obj = session.query(Address).all()
# for i in addr_obj:
#     print(i.student.name)

#通过student对象方差关联的address的记录
obj =  session.query(Student).filter_by(name='liming').all()[2]
print(obj.addresses) 

#添加关联对象
obj.addresses = [Address(email_address="r1@126.com"),Address(email_address="8888888.com")]
session.commit()
print(obj.addresses)

#运行结果
[]
[Address(email_address="r1@126.com"), Address(email_address="8888888.com")]

#首先 第三个李明是没有设置对应的地址的，所以为空，设置完毕后就可以看到对应的值了

 多外键关联

　　customer 表中的 billing_address_id 关联 address表的ID

　　customer 表中的 shipping_address_id 关联 address表的ID

 import sqlalchemy
from sqlalchemy import Integer,Column,String,ForeignKey,create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import  relationship,sessionmaker

engine = create_engine("mysql+pymysql://woniu:123456@59.110.12.72/txadmin",encoding='utf-8')
Base = declarative_base()

class Customer(Base):
    __tablename__ = 'customer'
    id = Column(Integer,primary_key=True)
    name = Column(String(32))

    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 '{name:%s,billing_addres:%s,shipping_address:%s}'%(self.name,self.billing_address,self.shipping_address)


class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer,primary_key=True)
    street = Column(String(32))
    city = Column(String(32))
    state = Column(String(32))

    def __repr__(self):
        return self.city

Base.metadata.create_all(engine)

db_session = sessionmaker(bind=engine)
session = db_session()

# obj2 = Address(street = 'shipping_address',city='test',state='eee')
# obj3 = Address(street = 'billing_address',city='test',state='eee')
# session.add(obj2)
# session.add(obj3)
#
# session.commit()
#
#
# obj = Customer(name = 'nima',billing_address = obj2,shipping_address = obj3)
# session.add(obj)
# session.commit()

res = session.query(Customer).filter(Customer.name == 'nima').first()
print(res.name,dir(res))
print(res.billing_address,res.shipping_address)          #可以关联得到外键，通过customer表，查看address表

mysql> desc address;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| street | varchar(32) | YES  |     | NULL    |                |
| city   | varchar(32) | YES  |     | NULL    |                |
| state  | varchar(32) | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> desc customer;
+---------------------+-------------+------+-----+---------+----------------+
| Field               | Type        | Null | Key | Default | Extra          |
+---------------------+-------------+------+-----+---------+----------------+
| id                  | int(11)     | NO   | PRI | NULL    | auto_increment |
| name                | varchar(32) | YES  |     | NULL    |                |
| billing_address_id  | int(11)     | YES  | MUL | NULL    |                |
| shipping_address_id | int(11)     | YES  | MUL | NULL    |                |
+---------------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> select * from customer;
+----+---------+--------------------+---------------------+
| id | name    | billing_address_id | shipping_address_id |
+----+---------+--------------------+---------------------+
|  6 | liming  |                  6 |                   7 |
|  7 | xiaohua |                  4 |                   5 |
|  8 | xiaohua |                  4 |                   5 |
|  9 | xiaohua |                 14 |                  15 |
| 10 | nima    |                 16 |                  17 |
+----+---------+--------------------+---------------------+
5 rows in set (0.01 sec)

mysql> select * from address;
+----+------------------+------+-------+
| id | street           | city | state |
+----+------------------+------+-------+
|  3 | uuu              | rrr  | eee   |
|  4 | shipping_address | rrr  | eee   |
|  5 | shipping_address | rrr  | eee   |
|  6 | shipping_address | rrr  | eee   |
|  7 | billing_address  | rrr  | eee   |
|  8 | shipping_address | qqq  | eee   |
|  9 | billing_address  | www  | eee   |
| 10 | shipping_address | qqq  | eee   |
| 11 | billing_address  | www  | eee   |
| 12 | shipping_address | qqq  | eee   |
| 13 | billing_address  | www  | eee   |
| 14 | shipping_address | test | eee   |
| 15 | billing_address  | test | eee   |
| 16 | shipping_address | test | eee   |
| 17 | billing_address  | test | eee   |
+----+------------------+------+-------+
15 rows in set (0.00 sec)

mysql> 

返回mysql目录