sqlalchemy ORM解惑
关于ORM框架介绍
orm英文全称object relational mapping,就是对象关系映射，简单来说我们类似python这种面向对象的程序来说一切皆对象，但是我们使用一些关系型数据库却都是关系型的，为了保证一致的使用习惯，通过orm将编程语言的对象模型和数据库的关系模型建立映射关系，这样我们在使用编程语言对数据库进行操作的时候可以直接使用编程语言的对象模型进行操作就可以了，而不用直接使用sql语言。
pyalchemy的安装与使用
在Python中，最有名的ORM框架是SQLAlchemy，而且是各大互联网公司常用的ORM框架，用户包括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
如果在python2.x上用pyalchemy的话应选择mysql+mysqldb，而如果在python3.x上用pyalchemy的话只能用mysql+pymysql，因为在python3.x上无法使用mysqldb，只能装pymysql来代替mysqldb。
下面就简单的来个实例，创建一个简单表并向表里插入一条记录：
# !/usr/bin/env python
# -*- coding:utf-8 -*-
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
engine = create_engine("mysql+mysqldb://root:123@localhost/oldboydb",
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的是个class,不是实例
Session = Session_class() #生成session实例
user_obj = User(name="goser",password="12345") #生成你要创建的数据对象
print(user_obj.name,user_obj.id) #此时还没创建对象呢，不信你打印一下id发现还是None
Session.add(user_obj) #把要创建的数据对象添加到这个session里， 一会统一创建
print(user_obj.name,user_obj.id) #此时也依然还没创建
Session.commit() #现此才统一提交，创建数据
现在通过直接操作数据库来验证是否成功：
mysql> select * from user; +----+-------+----------+ | id | name | password | +----+-------+----------+ | 1 | goser | 12345 | +----+-------+----------+ 1 row in set (0.00 sec)
查询操作：
# !/usr/bin/env python
# -*- coding:utf-8 -*-
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
engine = create_engine("mysql+mysqldb://root:123@localhost/oldboydb",
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)
Base.metadata.create_all(engine) #创建表结构
Session_class = sessionmaker(bind=engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
Session = Session_class() #生成session实例
# user_obj = User(name="rain",password="123456") #生成你要创建的数据对象
# print(user_obj.name,user_obj.id) #此时还没创建对象呢，不信你打印一下id发现还是None
#
# Session.add(user_obj) #把要创建的数据对象添加到这个session里， 一会统一创建
# print(user_obj.name,user_obj.id) #此时也依然还没创建
#first()查询记录第一条记录，如果是all()表示所以记录，count()表示查询结果有多少记录
user_obj = Session.query(User).filter(User.name == 'rain').first()
print user_obj
Session.commit() #现此才统一提交，创建数据
小的注意事项：
上面的查询操作的 print user_obj在没加def __repr__(self)的话输出的结果为对象，类似于这种<__main__.User object at 0x105b4ba90>。
不过刚才上面的显示的内存对象对址你是没办法分清返回的是什么数据的，除非打印具体字段看一下，如果想让它变的可读，只需在定义表的类下面才加上这样的代码：
def __repr__(self):
return "<User(name='%s', password='%s')>" % (
self.name, self.password)
修改操作：
my_user = Session.query(User).filter_by(name="goser").first() my_user.name = "James" Session.commit()
回滚操作：
my_user = Session.query(User).filter_by(id=1).first() my_user.name = "jodan" fake_user = User(name='tomath', password='12345') Session.add(fake_user) print(Session.query(User).filter(User.name.in_(['jodan','tomath'])).all() ) #这时看session里有你刚添加和修改的数据 Session.rollback() #此时你rollback一下 print(Session.query(User).filter(User.name.in_(['jodan','tomath'])).all() ) #再查就发现刚才添加的数据没有了。
多条件查询：
objs = Session.query(User).filter(User.id>0).filter(User.id<7).all()
分组：
from sqlalchemy import func print(Session.query(func.count(User.name),User.name).group_by(User.name).all() ) 输出为 [(1, 'goser'), (2, 'rain')]
外键关联操作：
先添加一个地址表，和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
创建好后就可以使用正向和反向查找:
#反向查找
obj = Session.query(User).first()
for i in obj.addresses: #通过user对象反查关联的addresses记录
print(i)
#正向查找
addr_obj = Session.query(Address).first()
print(addr_obj.user.name) #在addr_obj里直接查关联的user表
#输出的记过为：
<Address(email_address='shanghai')>
<Address(email_address='beijing')>
goser
多外键关联：
from sqlalchemy import Integer, ForeignKey, String, Column
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
Base = declarative_base()
class Customer(Base):
__tablename__ = 'customer'
id = Column(Integer, primary_key=True)
name = Column(String)
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])
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, primary_key=True)
street = Column(String)
city = Column(String)
state = Column(String)
多对多关系：
多对多关系，要生成一个中间表，这个中间表来关联多个表，存储多个表关联对应的外键。
这个中间表需要自己创建，不像Django可以在创建完多对多表后会自动生成。
#一本书可以有多个作者，一个作者又可以出版多本书
from sqlalchemy import Table, Column, Integer,String,DATE, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
book_m2m_author = Table('book_m2m_author', Base.metadata,
Column('book_id',Integer,ForeignKey('books.id')),
Column('author_id',Integer,ForeignKey('authors.id')),
)
class Book(Base):
__tablename__ = 'books'
id = Column(Integer,primary_key=True)
name = Column(String(64))
pub_date = Column(DATE)
authors = relationship('Author',secondary=book_m2m_author,backref='books')
def __repr__(self):
return self.name
class Author(Base):
__tablename__ = 'authors'
id = Column(Integer, primary_key=True)
name = Column(String(32))
def __repr__(self):
return self.name
接下来创建几本书和几个作者：
Session_class = sessionmaker(bind=engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例 s = Session_class() #生成session实例 b1 = Book(name="python basic") b2 = Book(name="javaSE") b3 = Book(name="PHP") b4 = Book(name="c++") a1 = Author(name="goser") a2 = Author(name="Jack") a3 = Author(name="Rain") b1.authors = [a1,a2] b2.authors = [a1,a2,a3] s.add_all([b1,b2,b3,b4,a1,a2,a3]) s.commit()
此时，我们去用orm查一下数据
print('--------通过书表查关联的作者---------')
book_obj = s.query(Book).filter_by(name="javaSE").first()
print(book_obj.name, book_obj.authors)
print('--------通过作者表查关联的书---------')
author_obj =s.query(Author).filter_by(name="goser").first()
print(author_obj.name , author_obj.books)
s.commit()
关于删除：
通过书删除作者
author_obj =s.query(Author).filter_by(name="Jack").first() book_obj = s.query(Book).filter_by(name="python basic").first() book_obj.authors.remove(author_obj) #从一本书里删除一个作者 s.commit()
直接删除作者
删除作者时，会把这个作者跟所有书的关联关系数据也自动删除
author_obj =s.query(Author).filter_by(name="goser").first() # print(author_obj.name , author_obj.books) s.delete(author_obj) s.commit()
处理中文：
sqlalchemy设置编码字符集一定要在数据库访问的URL上增加charset=utf8，否则数据库的连接就不是utf8的编码格式
engine = create_engine("mysql+mysqldb://root:123@localhost/oldboydb?charset=utf8",
encoding='utf-8', echo=True)
浙公网安备 33010602011771号