ORM简介
见链接：
http://www.cnblogs.com/alex3714/articles/5978329.html
利用orm创建一个数据表
步骤：
1、连接
2、创建一个orm基类
3、定义一个数据库的类，继承的是orm基类
4、调用orm基类中的metadata的create_all创建数据表
1 import sqlalchemy 2 from sqlalchemy.ext.declarative import declarative_base 3 from sqlalchemy import Integer, String, Column 4 # 相当于创建连接实例 5 engine = sqlalchemy.create_engine('mysql+pymysql://root:dczx_5501@10.62.36.58/DCZXdb?charset=utf8', 6 encoding='utf-8', echo=True) # echo是把执行时调用的sql语句显示出来 charset表示支持中文 7 Base = declarative_base() # 生成orm基类 8 9 10 class User(Base): 11 """表格类""" 12 # 表格名的变量必须是__tablename__ 13 __tablename__ = 'user' 14 # 创建字段 15 id = Column(Integer, primary_key=True) 16 name = Column(String(32)) # 如需默认值，可添加参数server_default = 'name', 必须有双引号 17 password = Column(String(64)) 18 19 20 # 开始创建数据 21 Base.metadata.create_all(engine)
支持utf-8方法
1、直接在mysql客户端创建时支持
2、定义表结构时定义，如下图
插入数据
步骤
1、连接
2、定义一个orm的基类
3、定义一个数据表
4、创建一个会话，类似于游标
5、创建一个数据表的实例
6、利用会话的add(obj)的方法，添加到一条数据数据库，session.add_all([obj1,obj2,obj3...])，可添加多条记录到数据库
7、提交操作
import sqlalchemy from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Integer, String, Column from sqlalchemy.orm import sessionmaker # 连接 engine = sqlalchemy.create_engine('mysql+pymysql://Treelight:dczx_5501@192.168.0.50/ehomedb', encoding='utf-8', echo=True) # 实例化一个基于orm的类 Base = declarative_base() 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 = Session_class() # 创建一个实例，用于添加到数据库 user_obj = User(name='Alex', password='E-Home') # 添加实例 session.add(user_obj) # 由于默认开始事务，所以需要提交 session.commit()
查询操作
使用步骤：
1、创建会话实例session，绑定连接实例
2、data = session.query(<table_name>).filter(clause).all()：获取所有记录，返回对象列表
data = session.query(<table_name>).filter(clause).first()：获取第一条记录，返回对象
import sqlalchemy from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Integer, String, Column from sqlalchemy.orm import sessionmaker from sqlalchemy import text # 连接 engine = sqlalchemy.create_engine('mysql+pymysql://Treelight:dczx_5501@192.168.0.50/ehomedb', encoding='utf-8', echo=False) # 实例化一个基于orm的类 Base = declarative_base() class User(Base): """创建一个表""" __tablename__ = 'user' id = Column(Integer, primary_key=True) name = Column(String(32)) password = Column(String(64)) def __repr__(self): """重构此函数是为了不用在主程序中用for循环显示输入""" return '<id:%s name:%s>' % (self.id, self.name) Session_class = sessionmaker(bind=engine) # 创建与数据库会话的类，注意这里返回的是类，不是实例 session = Session_class() data = session.query(User).filter(User.id < 4).all() # 获取所有的数据，并返回对象列表，有时候可以用filter_by
data = session.query(User).filter(User.id > 1).filter(User.id < 4).all() # 多条件查询，使用多个filter
print(data)
查询所有的数据：print(session.query(User.id, User.name, User.password).all())
删除
1 import sqlalchemy 2 from sqlalchemy.ext.declarative import declarative_base 3 from sqlalchemy import Integer, String, Column 4 from sqlalchemy.orm import sessionmaker 5 from sqlalchemy import func 6 # 连接 7 engine = sqlalchemy.create_engine('mysql+pymysql://Treelight:dczx_5501@192.168.0.50/ehomedb', 8 encoding='utf-8', 9 echo=False) 10 # 实例化一个基于orm的类 11 Base = declarative_base() 12 13 14 class User(Base): 15 """创建一个表""" 16 __tablename__ = 'user' 17 id = Column(Integer, primary_key=True) 18 name = Column(String(32)) 19 password = Column(String(64)) 20 21 def __repr__(self): 22 """重构此函数是为了不用在主程序中用for循环显示输入""" 23 return '<id:%s name:%s password:%s>' % (self.id, self.name, self.password) 24 25 26 Session_class = sessionmaker(bind=engine) # 创建与数据库会话的类，注意这里返回的是类，不是实例 27 session = Session_class() 28 print(session.query(User).filter(User.name == 'Alex').delete()) 29 print(session.query(User).all())
修改
步骤：
1、先查询需要修改的数据
2、对这些数据的属性进行赋值
1 import sqlalchemy 2 from sqlalchemy.ext.declarative import declarative_base 3 from sqlalchemy import Integer, String, Column 4 from sqlalchemy.orm import sessionmaker 5 from sqlalchemy import text 6 # 连接 7 engine = sqlalchemy.create_engine('mysql+pymysql://Treelight:dczx_5501@192.168.0.50/ehomedb', 8 encoding='utf-8', 9 echo=False) 10 # 实例化一个基于orm的类 11 Base = declarative_base() 12 13 14 class User(Base): 15 """创建一个表""" 16 __tablename__ = 'user' 17 id = Column(Integer, primary_key=True) 18 name = Column(String(32)) 19 password = Column(String(64)) 20 21 def __repr__(self): 22 """重构此函数是为了不用在主程序中用for循环显示输入""" 23 return '<id:%s name:%s password:%s>' % (self.id, self.name, self.password) 24 25 26 Session_class = sessionmaker(bind=engine) # 创建与数据库会话的类，注意这里返回的是类，不是实例 27 session = Session_class() 28 data = session.query(User).filter(User.id == 1).first() 29 data.name = 'LiangShuHui' 30 data.password = 'dczx_84905501' 31 print(data)
也可以：把29、30行的改为类似于：session.query(User).filter(User.name == 'LiangShuHui').update({'name': 'Treelight'})
回滚
session.rollback()
1 import sqlalchemy 2 from sqlalchemy.ext.declarative import declarative_base 3 from sqlalchemy import Integer, String, Column 4 from sqlalchemy.orm import sessionmaker 5 from sqlalchemy import text 6 # 连接 7 engine = sqlalchemy.create_engine('mysql+pymysql://Treelight:dczx_5501@192.168.0.50/ehomedb', 8 encoding='utf-8', 9 echo=False) 10 # 实例化一个基于orm的类 11 Base = declarative_base() 12 13 14 class User(Base): 15 """创建一个表""" 16 __tablename__ = 'user' 17 id = Column(Integer, primary_key=True) 18 name = Column(String(32)) 19 password = Column(String(64)) 20 21 def __repr__(self): 22 """重构此函数是为了不用在主程序中用for循环显示输入""" 23 return '<id:%s name:%s password:%s>' % (self.id, self.name, self.password) 24 25 26 Session_class = sessionmaker(bind=engine) # 创建与数据库会话的类，注意这里返回的是类，不是实例 27 session = Session_class() 28 data = session.query(User).filter(User.id == 1).first() 29 data.name = 'LiangShuHui' 30 data.password = 'dczx_84905501' 31 print(data) 32 session.rollback() 33 print('After rollback') 34 print(data)
统计
统计'alex'这个名称的次数
1 import sqlalchemy 2 from sqlalchemy.ext.declarative import declarative_base 3 from sqlalchemy import Integer, String, Column 4 from sqlalchemy.orm import sessionmaker 5 from sqlalchemy import func 6 # 连接 7 engine = sqlalchemy.create_engine('mysql+pymysql://Treelight:dczx_5501@192.168.0.50/ehomedb', 8 encoding='utf-8', 9 echo=False) 10 # 实例化一个基于orm的类 11 Base = declarative_base() 12 13 14 class User(Base): 15 """创建一个表""" 16 __tablename__ = 'user' 17 id = Column(Integer, primary_key=True) 18 name = Column(String(32)) 19 password = Column(String(64)) 20 21 def __repr__(self): 22 """重构此函数是为了不用在主程序中用for循环显示输入""" 23 return '<id:%s name:%s password:%s>' % (self.id, self.name, self.password) 24 25 26 Session_class = sessionmaker(bind=engine) # 创建与数据库会话的类，注意这里返回的是类，不是实例 27 session = Session_class() 28 print(session.query(User).filter(User.name == 'Alex').count())
分组：
1 import sqlalchemy 2 from sqlalchemy.ext.declarative import declarative_base 3 from sqlalchemy import Integer, String, Column 4 from sqlalchemy.orm import sessionmaker 5 from sqlalchemy import func 6 # 连接 7 engine = sqlalchemy.create_engine('mysql+pymysql://Treelight:dczx_5501@192.168.0.50/ehomedb', 8 encoding='utf-8', 9 echo=False) 10 # 实例化一个基于orm的类 11 Base = declarative_base() 12 13 14 class User(Base): 15 """创建一个表""" 16 __tablename__ = 'user' 17 id = Column(Integer, primary_key=True) 18 name = Column(String(32)) 19 password = Column(String(64)) 20 21 def __repr__(self): 22 """重构此函数是为了不用在主程序中用for循环显示输入""" 23 return '<id:%s name:%s password:%s>' % (self.id, self.name, self.password) 24 25 26 Session_class = sessionmaker(bind=engine) # 创建与数据库会话的类，注意这里返回的是类，不是实例 27 session = Session_class() 28 print(session.query(User.name, func.count(User.name)).group_by(User.name).all())
连接
1 import sqlalchemy 2 from sqlalchemy.ext.declarative import declarative_base 3 from sqlalchemy import Integer, String, Column, DATE, Enum 4 from sqlalchemy.orm import sessionmaker 5 # 连接 6 engine = sqlalchemy.create_engine('mysql+pymysql://Treelight:dczx_5501@192.168.0.50/ehomedb', 7 encoding='utf-8', 8 echo=True) 9 # 实例化一个基于orm的类 10 Base = declarative_base() 11 12 13 class Student(Base): 14 """创建一个表""" 15 __tablename__ = 'student' 16 id = Column(Integer, primary_key=True) 17 name = Column(String(32), nullable=False) 18 register_date = Column(DATE, nullable=False) 19 gender = Column(String(32), nullable=False) 20 21 def __repr__(self): 22 return '<id:%s name:%s register_date:%s gender:%s' % (self.id, 23 self.name, 24 self.register_date, 25 self.gender) 26 27 28 class User(Base): 29 """创建一个表""" 30 __tablename__ = 'user' 31 id = Column(Integer, primary_key=True) 32 name = Column(String(32)) 33 password = Column(String(64)) 34 35 def __repr__(self): 36 """重构此函数是为了不用在主程序中用for循环显示输入""" 37 return '<id:%s name:%s password:%s>' % (self.id, self.name, self.password) 38 39 40 Session_Class = sessionmaker(bind=engine) 41 session = Session_Class() 42 ret = session.query(User, Student).filter(User.id == Student.id).all() 43 print(ret)
有外键的表不能随便创建数据，必须关联的表创建了才能创建数据
外键关联
在定义表结构时加上，如
Column(Integer,ForeignKey('<tablename>.<col_name>')
查询附表中的相应数据，例子如下：
1 import sqlalchemy 2 from sqlalchemy.ext.declarative import declarative_base 3 from sqlalchemy import Integer, Column, String, ForeignKey, DATE 4 from sqlalchemy.orm import relationship 5 engine = sqlalchemy.create_engine( 6 'mysql+pymysql://Treelight:dczx_5501@10.62.36.58/dczxdb' 7 ) 8 Base = declarative_base() 9 10 11 class Student(Base): 12 """定义一个学生表格""" 13 __tablename__ = 'student' 14 id = Column(Integer, primary_key=True) 15 name = Column(String(32), nullable=False) 16 register_date = Column(DATE, nullable=False) 17 18 def __repr__(self): 19 return '<id:%s name:%s regiester_date:%s>' % (self.id, self.name, self.register_date) 20 21 22 class StudyRecord(Base): 23 """定义一个学习记录类""" 24 __tablename__ = 'studyrecord' 25 id = Column(Integer, primary_key=True) 26 day = Column(Integer, nullable=False) 27 status = Column(String(32), nullable=False) 28 stu_id = Column(Integer, ForeignKey('student.id')) 29 # 相当于在Student中创建了一个my_study_record属性，然后通过StudyRecord类中的外键stu_id与Student类关联此属性可查studyrecord的表记录 30 student = relationship('Student', backref='my_study_record') 31 32 def __repr__(self): 33 return '<name:%s day:%s status:%s stu_id:%s>' % (self.student.name, 34 self.day, 35 self.status, 36 self.stu_id) 37 38 39 from sqlalchemy.orm import sessionmaker 40 # from orm_basic import engine, Student 41 Session_Class = sessionmaker(bind=engine) 42 session = Session_Class() 43 student_obj = session.query(Student).filter(Student.name == 'Treelight').first() 44 print(student_obj.my_study_record)
一对多关联
建立表格
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import relationship engine = create_engine('mysql+pymysql://Treelight:dczx_5501@10.62.36.58/test') Base = declarative_base() class Customer(Base): __tablename__ = 'customer' id = Column(Integer, primary_key=True) name = Column(String(64), nullable=False) 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(64), nullable=False) city = Column(String(64), nullable=False) state = Column(String(64), nullable=False)
插入数据
from sqlalchemy.orm import sessionmaker from orm_basic import engine, Address, Customer Session_class = sessionmaker(bind=engine) session = Session_class() addr1 = Address(street='Tiantongyuan', city='ChangPing', state='BJ') addr2 = Address(street='Wudaokou', city='Haidian', state='BJ') addr3 = Address(street='Yanjiao', city='LangFang', state='HB') c1 = Customer(name='Treelight', billing_address=addr1, shipping_address=addr2) c2 = Customer(name='Alex', billing_address=addr3, shipping_address=addr3) session.add_all([addr1, addr2, addr3]) session.add_all([c1, c2]) session.commit()
多对多关联
使用场景：就比如一本书，可有多个作者，而一个作者，可出版多本书，这就是多对多的关系
如果按以下表格建立数据库会出问题：
解决办法：引入中间数据表！此数据表关系book表和author表，如下图
一、表格创建
1 import sqlalchemy 2 from sqlalchemy.ext.declarative import declarative_base 3 from sqlalchemy import Integer, Column, String, DATE, Table, ForeignKey 4 from sqlalchemy.orm import relationship 5 engine = sqlalchemy.create_engine( 6 'mysql+pymysql://Treelight:dczx_5501@10.62.36.58/dczxdb?charset=utf8' 7 ) 8 Base = declarative_base() 9 # 创建一个中间表格，由于这个表格由orm自动维护，所以这个表格不用以类的方式创建 10 book_m2m_author = Table('book_m2m_author', Base.metadata, 11 Column('book_id', Integer, ForeignKey('books.id')), 12 Column('author_id', Integer, ForeignKey('authors.id'))) 13 14 15 class Author(Base): 16 """作者表""" 17 __tablename__ = 'authors' 18 id = Column(Integer, primary_key=True) 19 name = Column(String(32), nullable=ForeignKey) 20 21 22 class Book(Base): 23 """书本表""" 24 __tablename__ = 'books' 25 id = Column(Integer, primary_key=True) 26 name = Column(String(64), nullable=False) 27 pub_date = Column(DATE, nullable=False) 28 authors = relationship('Author', secondary='book_m2m_author', backref='books') # secondary表明中间表的名称。
29 Base.metadata.create_all(engine)
插入数据
1 from sqlalchemy.orm import sessionmaker 2 from orm_basic import engine, Book, Author 3 Session_Class = sessionmaker(bind=engine) 4 session = Session_Class() 5 b1 = Book(name='跟Treelight学信息技术', pub_date='2012-09-10') 6 b2 = Book(name='跟Treelight学机器人', pub_date='2016-05-01') 7 b3 = Book(name='跟Treelight学3D打印', pub_date='2013-06-07') 8 b4 = Book(name='跟Treelight学Python', pub_date='2015-06-21') 9 author1 = Author(name='Treelight') 10 author2 = Author(name='Alex') 11 author3 = Author(name='Syvia') 12 author4 = Author(name='HeMinLing') 13 b1.authors = [author1, author4] # 注意！！ 14 b2.authors = [author1, author2] 15 b3.authors = [author2, author3] 16 b4.authors = [author4,] 17 session.add_all([b1, b2, b3, b4, 18 author1, author2, author3, author4]) 19 session.commit()
查询
from sqlalchemy.orm import sessionmaker from orm_basic import engine, Book, Author Session_class = sessionmaker(bind=engine) session = Session_class() book_obj = session.query(Book).filter(Book.name == '跟Treelight学信息技术').first() # 正查 print(book_obj, book_obj.authors) author_obj = session.query(Author).filter(Author.name == 'Treelight').first() # 反查 print(author_obj, author_obj.books)
扫一扫
1187
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
