时间戳

首页 > 技术文章 > mysqlalchemy

mysqlalchemy

Treelight 2019-06-28 16:29 原文

 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)

 

推荐阅读