PyMySQL模块, SQLAlchemy基础, SQLAlchemy进阶

PyMySQL模块

PyMySQL安装

使用pypi

• pypi即python package index

• 是python语言的软件仓库

• 官方站点为https://pypi.python.org

使用国内镜像站点

• 为了实现安装加速，可以配置pip安装时采用国内镜 像站点 
[root@localhost ~]# mkdir ~/.pip/ 
[root@localhost ~]# vim ~/.pip/pip.conf
[global]
index-url=http://pypi.douban.com/simple/
[install]
trusted-host=pypi.douban.com

通过pip安装PyMySQL模块

• 安装依赖包
[root@localhost ~]# yum install -y  gcc
• 本地安装
[root@localhost ~]# pip3 install PyMySQL-0.8.0.tar.gz
• 离线安装
[root@localhost ~]# cd /root/python_bao/zzg_pypkgs/pymysql_pkgs
[root@localhost pymysql_pkgs]# pip3 install *
• 在线安装
[root@localhost packages]#  pip3 install pymysql

配置mysql或mariadb

  1. 安装
  2. 启动
  3. 修改密码
  4. 创建数据库
[root@localhost ~]# yum install -y mariadb-server
[root@localhost ~]# systemctl start mariadb
[root@localhost ~]# mysql
MariaDB [(none)]> create database nqe default charset utf8;

PyMySQL应用

数据库

为一个小型企业编写数据库，能够记录员工信息，记录发工资情况。

经过调查，需要这些字段：姓名、出生日期、联系方式、部门、工资日、基本工资、奖金、总工资。

关系型数据库，应该尽量减少数据冗余（重复的数据）

姓名 生日 联系方式 部门 工资日 基本工资 奖金 总工资
张三 19950221 13242356 运维 20190510 10000 2000 12000
张三 19950221 13242356 运维部 20190610 10000 2000 12000

为了减少数据冗余，可以将字段存放到不同的表中：员工表、部门表、工资表。

员工表：

姓名 生日 联系方式 部门ID
张三 19950221 13242356 2

部门表：

部门ID 部门名称
2 运维

工资表：

姓名 工资日 基本工资 奖金 总工资
张三 20190510 10000 2000 12000

虽然各张表已经分开了，但是字段并不符合关系型数据库的要求。

关系型数据库字段需要满足数据库范式：

1. 所谓第一范式（1NF）是指在关系模型中，所有的域都应该是原子性的。联系方式不满足1NF，因为它包括家庭住址、电话号码、email等，所以要把联系方式拆分成更小的项目。
2. 2NF在1NF的基础上，非码属性必须完全依赖于码。简单来说就是表需要一个主键。根据2NF，最好为员工表加上员工ID作为主键；工资表应该记录的是员工ID，而不是员工姓名，但是员工ID也不能成为主键，因为每个月都要发工资，用现有的任何字段作为主键都不合适，干脆强加一个主键。
3. 第三范式（3NF）任何非主属性不得传递依赖于主属性，非主属性不能依赖其他非主属性。工资表中的总工资依赖于基本工资和奖金，它不应该出现在表中。

最终确定了三张表：
员工表：员工ID、姓名、email、部门ID
部门表：部门ID、部门名称
工资表：工资日、员工ID、基本工资、奖金

配置mysql或mariadb

  1. 安装
  2. 启动
  3. 修改密码
  4. 创建数据库
[root@localhost ~]# yum install -y mariadb-server
[root@localhost ~]# systemctl start mariadb
[root@localhost ~]# mysql
MariaDB [(none)]> create database nqe default charset utf8;

# 练习
[root@localhost ~]# vim py_mysql.py
#! /usr/local/python3
import pymysql

conn = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    passwd = '123',
    db = 'nqe',
    charset = 'utf8'
)

cursor = conn.cursor()
[root@localhost ~]# python3 py_mysql.py
[root@localhost ~]# vim py_mysql.py
#! /usr/local/python3
import pymysql

conn = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    passwd = '123',
    db = 'nqe',
    charset = 'utf8'
)

cursor = conn.cursor()
create_dep = '''CREATE TABLE departments(
dep_id INT, dep_name VARCHAR(50),
PRIMARY KEY(dep_id)
)'''
create_emp = '''CREATE TABLE employees(
emp_id INT, emp_name VARCHAR(50), email VARCHAR(50), dep_id INT,
PRIMARY KEY(emp_id), FOREIGN KEY(dep_id) REFERENCES departments(dep_id)
)'''
create_sal = '''CREATE TABLE salary(
id INT, date DATE, emp_id INT, basic INT, awards INT,
PRIMARY KEY(id), FOREIGN KEY(emp_id) REFERENCES employees(emp_id)
)'''

cursor.execute(create_dep)
cursor.execute(create_emp)
cursor.execute(create_sal)

conn.commit()       # 提交改动
cursor.close()      # 关闭游标
conn.close()        # 关闭连接
[root@localhost ~]# python3 py_mysql.py

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| nqe                |
| performance_schema |
| test               |
+--------------------+
7 rows in set (0.00 sec)

MariaDB [(none)]> use nqe;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [nqe]> show tables;
+---------------+
| Tables_in_nqe |
+---------------+
| departments   |
| employees     |
| salary        |
+---------------+
3 rows in set (0.00 sec)

# > drop tables 删除表

import pymysql
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    passwd='123',
    db='nqe',
    charset='utf8'
)

cursor = conn.cursor()          # 游标

...
# 插入语句
insert_dep = 'INSERT INTO departments VALUES(%s, %s)'
cursor.executemany(insert_dep, [(1, '人事部')])
deps = [(2, '财务部'), (3, '运维部'), (4, '开发部'), (5, '测试部'), (6, '市场部')]
cursor.executemany(insert_dep, deps)
#############################################


conn.commit()           # 提交改动
cursor.close()          # 关闭游标
conn.close()            # 关闭连接

MariaDB [nqe]> select * from departments;
+--------+-----------+
| dep_id | dep_name  |
+--------+-----------+
|      1 | 人事部    |
|      2 | 财务部    |
|      3 | 运维部    |
|      4 | 开发部    |
|      5 | 测试部    |
|      6 | 市场部    |
+--------+-----------+
6 rows in set (0.00 sec)
# > delete from  departments;

import pymysql
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    passwd='123',
    db='nqe',
    charset='utf8'
)

cursor = conn.cursor()          # 游标

...
# 基础查询
select1 = 'SELECT * FROM departments'
cursor.execute(select1)
print(cursor.fetchone())
print('*' * 20)
print(cursor.fetchmany(2))
print('*' * 20)
print(cursor.fetchall())
#############################################

conn.commit()           # 提交改动
cursor.close()          # 关闭游标
conn.close()            # 关闭连接

[root@localhost ~]# python3 py_mysql.py 
(1, '人事部')
********************
((2, '财务部'), (3, '运维部'))
********************
((4, '开发部'), (5, '测试部'), (6, '市场部'))

import pymysql
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    passwd='123',
    db='nqe',
    charset='utf8'
)

cursor = conn.cursor()          # 游标

...
# 基础查询
select1 = 'SELECT * FROM departments'
cursor.execute(select1)
print(cursor.fetchone())
print('*' * 20)
print(cursor.fetchmany(2))
print('*' * 20)
print(cursor.fetchall())
#############################################

conn.commit()           # 提交改动
cursor.close()          # 关闭游标
conn.close()            # 关闭连接

MariaDB [nqe]> select * from departments order by dep_id;

[root@localhost ~]# python3 py_mysql.py 
(3, '运维部')
********************
(1, '人事部')

import pymysql
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    passwd='123',
    db='nqe',
    charset='utf8'
)

cursor = conn.cursor()          # 游标

...
# 修改
update1 = 'UPDATE departments set dep_name=%s WHERE dep_name=%s'
cursor.execute(update1, ('人力资源部', '人事部'))
#############################################

conn.commit()           # 提交改动
cursor.close()          # 关闭游标
conn.close()            # 关闭连接

[root@localhost ~]# python3 py_mysql.py

MariaDB [nqe]> select * from departments order by dep_id;
+--------+-----------------+
| dep_id | dep_name        |
+--------+-----------------+
|      1 | 人力资源部      |
|      2 | 财务部          |
|      3 | 运维部          |
|      4 | 开发部          |
|      5 | 测试部          |
|      6 | 市场部          |
+--------+-----------------+
6 rows in set (0.00 sec)

import pymysql
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    passwd='123',
    db='nqe',
    charset='utf8'
)

cursor = conn.cursor()          # 游标

...
# 删除
delete1 = 'DELETE FROM departments WHERE dep_name=%s'
cursor.execute(delete1, ('市场部',))

conn.commit()           # 提交改动
cursor.close()          # 关闭游标
conn.close()            # 关闭连接

[root@localhost ~]# python3 py_mysql.py

MariaDB [nqe]> select * from departments order by dep_id;
+--------+-----------------+
| dep_id | dep_name        |
+--------+-----------------+
|      1 | 人力资源部      |
|      2 | 财务部          |
|      3 | 运维部          |
|      4 | 开发部          |
|      5 | 测试部          |
+--------+-----------------+
5 rows in set (0.00 sec)

连接数据库

• 创建连接是访问数据库的第一步
conn = pymysql.connect(
    host='127.0.0.1',   
    port=3306,  
    user='root',
    passwd='tedu.cn',
    db=nsd_cloud',  
    charset='utf8')

游标

• 游标（cursor）就是游动的标识 
• 通俗的说，一条sql取出对应n条结果资源的接口/句柄，就 是游标，沿着游标可以一次取出一行
cursor = conn.cursor()

插入数据

• 对数据库表做修改操作，必须要commit
sql1 = "insert into departments(dep_name) values(%s)"
result = cur.execute(sql1, ('development',))

sql2 = "insert into departments(dep_name) values(%s)"
data = [('hr',), ('op',)]
result = cur.executemany(sql2, data)

sql3 = "insert into departments(dep_name) values(%s)"
data = [('行政',), ('财务',), ('运营',)]
result = cur.executemany(sql3, data)

conn.commit()

查询数据

• 可以取出表中一条、多条或全部记录
sql4 = "select * from departments"
cur.execute(sql4)
result = cur.fetchone()
print(result)

result2 = cur.fetchmany(2)
print(result2)

result3 = cur.fetchall()
print(result3)

移动游标

• 如果希望不是从头取数据，可以先移动游标
cur.scroll(1, mode="ralative")
cur.scroll(2, mode="absolute")

sql5 = "select * from departments"
cur.execute(sql5)
cur.scroll(3, mode='absolute')
result4 = cur.fetchmany(2)s
print(result4)

修改数据

• 通过update修改某一字段的值
sql6 = "update departments set dep_name=%s where dep_name=%s"
result = cur.execute(sql6, ('operations', 'op'))
print(result)
conn.commit()

删除记录

• 通过delete删除记录
sql7 = "delete from departments where dep_id=%s"
result = cur.execute(sql7, (6,))
print(result)
conn.commit()

向表中添加数据

1. 通过pymysql模块创建数据库的表
2. 向employees表插入数据
3. 向salary表插入数据
4. 插入的数据需要commit到数据库中

SQLAlchemy基础

SQLAlchemy概述

安装

• SQLAlchemy由官方收录，可以直接安装 
[root@localhost ~]# cd python_bao/zzg_pypkgs/sqlalchemy_pkgs/
[root@localhost sqlalchemy_pkgs]# pip3 install *
[root@localhost sqlalchemy_pkgs]# pip3 install SQLAlchemy-1.2.14.tar.gz

简介

• SQLAlchemy是Python编程语下的一款开源软件。提供 SQL 具包及对象关系映射(ORM) 工具，使用MIT许可证发

• SQLAlchemy“采用简单的Python语言，为高效和高性能的数据库访问设计，实现了完整的企业级持久模型”

• SQLAlchemy的理念是，SQL数据库的量级和性能重要于对象集合；而对象集合的抽象又重要于表和行

• 目标是提供能兼容众多数据库（如 SQLite、MySQL、Postgresql、Oracle、MS-SQL、SQLServer 和 Firebird）的企业级持久性模型

架构

SQL Alchemy ORM
            Object Relational Mapper (ORM)

SQL Alchemy Core
Schema / Types      SQL Expression Language     Engine
                    Connection Pooling          Dialect
                                         DBAPI

ORM模型

• ORM即对象关系映射 
• 数据库表是一个二维表，包含多行多列。把一个表的内容用Python的数据结构表示出来的话，可以用一个list表示多行，list的每一个元素是tuple，表示一行记录
[
    ('1', 'Michael'),
    ('2', 'Bob'),
    ('3', 'Adam')
]
    - 对象：指OOP编程的方式
    - 关系：关系型数据库
    - 将python中的class映射到数据库的表
    - class中的类变量映射到数据库表中的每个字段
    - class的每个实例映射到数据库表中的每行记录

ORM模型（续1）

• 用tuple表示一行很难看出表的结构。如果把一个tuple用class实例来表示，就可以更容易地看出表的结构来
class User(object):
def __init__(self, id, name):
self.id = id
self.name = name
[
User('1', 'Michael'),
User('2', 'Bob'),
User('3', 'Adam')
]

数据库对象管理

连接mysql

• 通过create_engine实现数据库的连接 
[root@bogon bin]# mysql -uroot -ptedu.cn
MariaDB [(none)]> create database tarena default char set utf8;

>>> from sqlalchemy import create_engine
>>> engine = create_engine( 'mysql+pymysql://root:tedu.cn@localhost/tarena?charset=utf8', encoding='utf8',
echo=True
)
//echo=True表示将日志输出到终端屏幕，默认为False

声明映射

• 当使用ORM的时候，配置过程从描述数据库表开始 
• 通过自定义类映射相应的表 
• 通过声明系统实现类映射 
• 首先通过声明系统，定义基类
>>> from sqlalchemy.ext.declarative import declarative_base
>>> Base = declarative_base()

创建映射类

• 一旦创建了基类，就可以创建自定义映射类了
>>> from sqlalchemy import Column, Integer, String
>>> class Departments(Base):
... __tablename__ = 'departments'
... dep_id = Column(Integer, primary_key=True)
... dep_name =  Column(String(20))
... def __repr__(self):
...     return "<Department(dep_name='%s')>" % self.dep_name
//__repr__是可选项

创建架构

• 类构建完成后，表的信息将被写入到表的元数据 （metadata）
>>> Departments.__table__
Table('departments', MetaData(bind=None), Column('dep_id', Integer(),   
table=<departments>, primary_key=True, nullable=False), olumn('dep_name', String(), table=<departments>), schema=None)

创建架构（续1）

• 通过表的映射类，在数据库中创建表
>>> Base.metadata.create_all(engine)

创建映射类的实例

• 创建实例时，并不会真正在表中添加记录

dep_dev = Departments(dep_name='developments')
print(dep_dev.dep_name)
print(str(dep_dev.dep_id))

创建会话类

• ORM访问数据库的句柄被称作Session

>>> from sqlalchemy.orm import sessionmaker
>>> Session = sessionmaker(bind=engine)

如果在创建session前还未创建engine，操作如下
>>> Session = sessionmaker()
>>> Session.configure(bind=engine) //创建engine后执行

添加新对象

• 会话类的实例对象用于绑定到数据库 
• 实例化类的对象，并不打开任何连接 
• 当实例初次使用，它将从Engine维护的连接池中获 得一个连接 
• 当所有的事务均被commit或会话对象被关闭时，连接结束
>>> session = Session()
>>> session.add(dep_dev)
>>> session.commit()
>>> print(str(dep_dev.dep_id))
>>> session.close()

添加新对象（续1）

• 可以创建多个实例，批量添加记录

dep_hr = Departments(dep_name='hr')
dep_op = Departments(dep_name='operations')
dep_finance = Departments(dep_name='财务')
dep_xz = Departments(dep_name='行政’)
Session = sessionmaker(engine)
session = Session()
session.add_all([dep_hr, dep_op, dep_finance, dep_xz])
session.commit()
session.close()

外键约束

• ORM映射关系也可用于表间创建外键约束
class Employees(Base):
    __tablename__ = 'employees'
    emp_id = Column(Integer, primary_key=True)
    name = Column(String(20))
    genda = Column(String(10))
    phone = Column(String(11))
    dep_id = Column(Integer,    ForeignKey('departments.dep_id'))
    def __repr__(self):
        return "<Employees(name='%s')>" % self.name

新建数据库

MariaDB [(none)]> create database mqe default charset utf8;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use mqe;
Database changed

dbconn.py

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

engine = create_engine(
    # mysql+pymymysql://用户名:密码@服务器/数据库?参数
    'mysql+pymysql://root:123@127.0.0.1/mqe?charset=utf8',
    encoding='utf8',
    # echo=True  # 在屏幕上输出日志，生产环境中不要使用
)
# 创建ORM的基类
Base = declarative_base()

class Department(Base):
    __tablename__ = 'departments'  # 定义库中的表名
    dep_id = Column(Integer, primary_key=True)
    dep_name = Column(String(50), unique=True, nullable=False)

if __name__ == '__main__':
    # 如果库中没有相关的表则创建，有的话不会创建
    Base.metadata.create_all(engine)

[root@localhost ~]# python3 dbconn.py

MariaDB [(none)]> use mqe;
Database changed
MariaDB [mqe]> show tables;
Empty set (0.01 sec)

MariaDB [mqe]> show tables;
+---------------+
| Tables_in_mqe |
+---------------+
| departments   |
+---------------+
1 row in set (0.00 sec)
# > drop tables 删除表

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

engine = create_engine(
    # mysql+pymymysql://用户名:密码@服务器/数据库?参数
    'mysql+pymysql://root:tedu.cn@127.0.0.1/mqe?charset=utf8',
    encoding='utf8',
    # echo=True  # 在屏幕上输出日志，生产环境中不要使用
)
# 创建ORM的基类
Base = declarative_base()

class Department(Base):...

class Employee(Base):
    __tablename__ = 'employees'
    emp_id = Column(Integer, primary_key=True)
    emp_name = Column(String(50), nullable=False)
    email = Column(String(50), unique=True, nullable=False)
    dep_id = Column(Integer, ForeignKey('departments.dep_id'))

    
if __name__ == '__main__':
    # 如果库中没有相关的表则创建，有的话不会创建
    Base.metadata.create_all(engine)

[root@localhost ~]# python3 dbconn.py

MariaDB [mqe]> show tables;
+---------------+
| Tables_in_mqe |
+---------------+
| departments   |
| employees     |
+---------------+
2 rows in set (0.00 sec)

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, Date
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine(
    # mysql+pymymysql://用户名:密码@服务器/数据库?参数
    'mysql+pymysql://root:tedu.cn@127.0.0.1/tedu1812?charset=utf8',
    encoding='utf8',
    # echo=True  # 在屏幕上输出日志，生产环境中不要使用
)
# 创建ORM的基类
Base = declarative_base()

class Department(Base):...

class Employee(Base):...

class Salary(Base):
    __tablename__ = 'salary'
    id = Column(Integer, primary_key=True)
    date = Column(Date, nullable=False)
    emp_id = Column(Integer, ForeignKey('employees.emp_id'))
    basic = Column(Integer)
    awards = Column(Integer)

if __name__ == '__main__':
    # 如果库中没有相关的表则创建，有的话不会创建
    Base.metadata.create_all(engine)

[root@localhost ~]# python3 dbconn.py

MariaDB [mqe]> show tables;
+---------------+
| Tables_in_mqe |
+---------------+
| departments   |
| employees     |
| salary        |
+---------------+
3 rows in set (0.00 sec)

....
from sqlalchemy.orm import sessionmaker
....
# 创建ORM的基类
Base = declarative_base()
Session = sessionmaker(bind=engine)
....

创建表

1. 创建employees表
2. 创建部门表
3. 创建salary表
4. 表间创建恰当的关系

添加数据

1. 分别在部门表、员工表和工资表中加入数据
2. 通过SQLAlchemy代码实现
3. 分别练习每次加入一行数据和每次可加入多行数据

SQLAlchemy进阶

查询操作

基本查询

• 通过作用于session的query()函数创建查询对象 
• query()函数可以接收多种参数
    from myorm import Session, Departments
    
    session = Session()
    
    for instance in 
    session.query(Departments).order_by(Departments.dep_id):
        print(instance.dep_id, instance.dep_name)

使用ORM描述符进行查询

• 使用ORM描述符进行查询 
• 返回值是元组
    from myorm import Employees, Session

    session = Session()

    for name, phone in session.query(Employees.name,Employees.phone):
    print(name, phone)

排序

• 通这order_by()函数可以实现按指定字段排序
    from myorm import Session, Departments

    session = Session()

    for instance in 
    session.query(Departments).order_by(Departments.dep_id):
        print(instance.dep_id,instance.dep_name)

提取部分数据

• 通过“切片”的方式，实现部分数据的提取
    from myorm import Session, Departments

    session = Session()

    for row in session.query(Departments, Departments.dep_name)[2:5]:
        print(row.Departments,  row.dep_name)

结果过滤

• 通过filter()函数实现结果过滤
    from myorm import Session, Departments

    session = Session()

    for row in  
    session.query(Departments.dep_name).filter(Departments.dep_id==2):
        print(row.dep_name)

结果过滤（续1）

• filter()函数可以叠加使用
    from myorm import Session, Salary

    session = Session()

    for row in session.query(Salary.emp_id, Salary.base,    Salary.award)\
  
.filter(Salary.award>2000).filter(Salary.base>10000):
            print(row.emp_id)

常用过滤操作符

• 相等
    query.filter(Employees.name=='john')
• 不相等
    query.filter(Employees.name!='john')
• 模糊查询
    query.filter(Employees.name.like('%j'))

常用过滤操作符（续1）

• in
    query.filter(new_emp.name.in_(['bob', 'john'])
• not in
    query.filter(~new_emp.name.in_(['bob', 'john']) 
• 字段为空
    query.filter(new_emp.name.is_(None)) 
• 字段不为空
    query.filter(new_emp.name.isnot(None))

查询对象返回值

• all()返回列表 
• first()返回结果中的第一条记录

修改操作

多表查询

• 通过join()方法实现多表查询
q = session.query(
Employees.name, Departments.dep_name).join(Departments)
print(q.all())

更新数据

• 通过会话的update()方法更新
from myorm import Session, Departments
session = Session()
q1 = session.query(Departments).filter(Departments.dep_id==6)
q1.update({Departments.dep_name: '运维部'})
session.commit()
session.close()

更新数据（续1）

• 通过会话的字段赋值更新
from myorm import Session, Departments
session = Session()
q2 = session.query(Departments).get(1) # get(1)查询主键是1的记录
q2.dep_name = '开发部'
session.commit()
session.close()

删除记录

• 通过会话的delete()方法进行记录删除
from myorm import Session, Departments
session = Session()
q1 = session.query(Departments).get(7)
session.delete(q1)
session.commit()
session.close()

crud.py

from dbconn import Session, Department, Employee, Salary

session = Session()
########################
hr = Department(dep_id=1, dep_name='人事部')
finance = Department(dep_id=2, dep_name='财务部')
ops = Department(dep_id=3, dep_name='运维部')
dev = Department(dep_id=4, dep_name='开发部')
qa = Department(dep_id=5, dep_name='测试部')
session.add_all([hr, finance, ops, dev, qa])

session.commit()
session.close()

[root@localhost ~]# python3 crud.py

MariaDB [mqe]> select * from departments;
+--------+-----------+
| dep_id | dep_name  |
+--------+-----------+
|      1 | 人事部    |
|      4 | 开发部    |
|      5 | 测试部    |
|      2 | 财务部    |
|      3 | 运维部    |
+--------+-----------+
5 rows in set (0.00 sec)

from dbconn import Session, Department, Employee, Salary

session = Session()

...
#########################
wt = Employee(
    emp_id=1,
    emp_name='王涛',
    email='wangtao@qq.com',
    dep_id=3
)
zj = Employee(
    emp_id=2,
    emp_name='张钧',
    email='zhangjun@163.com',
    dep_id=3
)
sy = Employee(
    emp_id=3,
    emp_name='苏艳',
    email='suyan@qq.com',
    dep_id=1
)
wjy = Employee(
    emp_id=4,
    emp_name='吴计印',
    email='wujiying@126.com',
    dep_id=4
)
kzw = Employee(
    emp_id=5,
    emp_name='康志文',
    email='kangzhiwen@qq.com',
    dep_id=4
)
hzq = Employee(
    emp_id=6,
    emp_name='胡志强',
    email='huzhiqiang@163.com',
    dep_id=5
)
lh = Employee(
    emp_id=7,
    emp_name='李浩',
    email='lihao@126.com',
    dep_id=2
)
session.add_all([wt, zj, sy, wjy, kzw, hzq, lh])

session.commit()
session.close()

[root@localhost ~]# python3 crud.py

MariaDB [mqe]> select * from employees;
+--------+-----------+--------------------+--------+
| emp_id | emp_name  | email              | dep_id |
+--------+-----------+--------------------+--------+
|      1 | 王涛      | wangtao@qq.com     |      3 |
|      2 | 张钧      | zhangjun@163.com   |      3 |
|      3 | 苏艳      | suyan@qq.com       |      1 |
|      4 | 吴计印    | wujiying@126.com   |      4 |
|      5 | 康志文    | kangzhiwen@qq.com  |      4 |
|      6 | 胡志强    | huzhiqiang@163.com |      5 |
|      7 | 李浩      | lihao@126.com      |      2 |
+--------+-----------+--------------------+--------+
7 rows in set (0.00 sec)

from dbconn import Session, Department, Employee, Salary

session = Session()
...
#######################
qset1 = session.query(Department)
print(qset1)  # qset1只是个sql语句，当取具体值的时候，才真正查数据库
# qset1.all()取出全部的部门，因为查询的是类名，所以返回所有的实例组成的列表
print('*' * 30)
print(qset1.all())
print('*' * 30)
for dep in qset1:  # 遍历实例列表中的每个实例
    print('%s: %s' % (dep.dep_id, dep.dep_name))

session.commit()
session.close()

[root@localhost ~]# python3 crud.py 
SELECT departments.dep_id AS departments_dep_id, departments.dep_name AS departments_dep_name 
FROM departments
******************************
[<dbconn.Department object at 0x7f5369838c10>, <dbconn.Department object at 0x7f5369838c90>, <dbconn.Department object at 0x7f5369838d50>, <dbconn.Department object at 0x7f5369838dd0>, <dbconn.Department object at 0x7f5369838e50>]
******************************
1: 人事部
4: 开发部
5: 测试部
2: 财务部
3: 运维部

from dbconn import Session, Department, Employee, Salary

session = Session()
...
#######################
qset3 = session.query(Employee.emp_name, Employee.email)
# 查询的参数是字段，返回的结果是元组
for item in qset3:
    print(item)
print('*' * 30)
for name, email in qset3:
    print('%s: %s' % (name, email))

session.commit()
session.close()

[root@localhost ~]# python3 crud.py 
('王涛', 'wangtao@qq.com')
('张钧', 'zhangjun@163.com')
('苏艳', 'suyan@qq.com')
('吴计印', 'wujiying@126.com')
('康志文', 'kangzhiwen@qq.com')
('胡志强', 'huzhiqiang@163.com')
('李浩', 'lihao@126.com')
******************************
王涛: wangtao@qq.com
张钧: zhangjun@163.com
苏艳: suyan@qq.com
吴计印: wujiying@126.com
康志文: kangzhiwen@qq.com
胡志强: huzhiqiang@163.com
李浩: lihao@126.com

from dbconn import Session, Department, Employee, Salary

session = Session()
...
#########################
qset4 = session.query(Department).order_by(Department.dep_id)[1:4]
for dep in qset4:  # 遍历实例列表中的每个实例
    print('%s: %s' % (dep.dep_id, dep.dep_name))

session.commit()
session.close()

[root@localhost ~]# python3 crud.py 
2: 财务部
3: 运维部
4: 开发部

from dbconn import Session, Department, Employee, Salary

session = Session()
...
#######################
qset5 = session.query(Department).filter(Department.dep_id==2)
print(qset5)
print(qset5.all())  # all()返回列表
dep = qset5.one()  # 返回一个实例，如果返回值不是一个，将报错
print(dep.dep_id, dep.dep_name)

session.commit()
session.close()

[root@localhost ~]# python3 crud.py 
SELECT departments.dep_id AS departments_dep_id, departments.dep_name AS departments_dep_name 
FROM departments 
WHERE departments.dep_id = %(dep_id_1)s
[<dbconn.Department object at 0x7f5471b02e50>]
2 财务部

from dbconn import Session, Department, Employee, Salary

session = Session()
...
#######################
qset6 = session.query(Department).filter(Department.dep_id>1).filter(Department.dep_id<4)
for dep in qset6:
    print(dep.dep_id, dep.dep_name, sep=', ')

session.commit()
session.close()

[root@localhost ~]# python3 crud.py 
2, 财务部
3, 运维部

from dbconn import Session, Department, Employee, Salary

session = Session()
...
#######################
qset7 = session.query(Employee).filter(Employee.email.like('%@qq.com'))
for emp in qset7:
    print(emp.emp_name, emp.email)

session.commit()
session.close()

[root@localhost ~]# python3 crud.py 
王涛 wangtao@qq.com
苏艳 suyan@qq.com
康志文 kangzhiwen@qq.com

from dbconn import Session, Department, Employee, Salary

session = Session()
...
#######################
qset8 = session.query(Department).filter(Department.dep_id.in_([3, 4]))
for dep in qset8:
    print(dep.dep_id, dep.dep_name)

session.commit()
session.close()

[root@localhost ~]# python3 crud.py 
4 开发部
3 运维部

from dbconn import Session, Department, Employee, Salary

session = Session()
...
#######################
qset9 = session.query(Department).filter(Department.dep_name.isnot(None))
for dep in qset9:
    print(dep.dep_id, dep.dep_name)

session.commit()
session.close()

[root@localhost ~]# python3 crud.py 
1 人事部
4 开发部
5 测试部
2 财务部
3 运维部

from dbconn import Session, Department, Employee, Salary

session = Session()
...
#######################
# query中先写的是Employee，join中要写Department
qset10 = session.query(Employee.emp_name, Department.dep_name).join(Department)
for row in qset10:
    print(row)
    
session.commit()
session.close()

[root@localhost ~]# python3 crud.py 
('王涛', '运维部')
('张钧', '运维部')
('苏艳', '人事部')
('吴计印', '开发部')
('康志文', '开发部')
('胡志强', '测试部')
('李浩', '财务部')

from dbconn import Session, Department, Employee, Salary

session = Session()
...
#######################
qset11 = session.query(Department.dep_name, Employee.emp_name).join(Employee)
for row in qset11:
    print(row)

session.commit()
session.close()

[root@localhost ~]# python3 crud.py 
('运维部', '王涛')
('运维部', '张钧')
('人事部', '苏艳')
('开发部', '吴计印')
('开发部', '康志文')
('测试部', '胡志强')
('财务部', '李浩')

from dbconn import Session, Department, Employee, Salary

session = Session()
...
#######################
# 修改数据，先找到实例，再给实例的属性重新赋值
qset12 = session.query(Department).filter(Department.dep_name=='人事部')
hr = qset12.one()
hr.dep_name='人力资源部'

session.commit()
session.close()

[root@localhost ~]# python3 crud.py

MariaDB [mqe]> select * from departments;
+--------+-----------------+
| dep_id | dep_name        |
+--------+-----------------+
|      1 | 人力资源部      |
|      4 | 开发部          |
|      5 | 测试部          |
|      2 | 财务部          |
|      3 | 运维部          |
+--------+-----------------+
5 rows in set (0.00 sec)

from dbconn import Session, Department, Employee, Salary

session = Session()
...
#######################
# 删除，只要找到实例，然后删除即可
qset13 = session.query(Employee).filter(Employee.emp_id==6)
emp = qset13.one()
session.delete(emp)

session.commit()
session.close()

MariaDB [mqe]> select * from employees;
+--------+-----------+-------------------+--------+
| emp_id | emp_name  | email             | dep_id |
+--------+-----------+-------------------+--------+
|      1 | 王涛      | wangtao@qq.com    |      3 |
|      2 | 张钧      | zhangjun@163.com  |      3 |
|      3 | 苏艳      | suyan@qq.com      |      1 |
|      4 | 吴计印    | wujiying@126.com  |      4 |
|      5 | 康志文    | kangzhiwen@qq.com |      4 |
|      7 | 李浩      | lihao@126.com     |      2 |
+--------+-----------+-------------------+--------+
6 rows in set (0.00 sec)

操作数据

1. 修改部门表，将人事部改为人力资源部
2. 如果存在设计部，将设计部删除
3. 查询所有每个员工及其所在部门
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 205,236评论 6赞 478
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 87,867评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 151,715评论 0赞 340
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,899评论 1赞 278
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,895评论 5赞 368
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,733评论 1赞 283
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,085评论 3赞 399
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,722评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 43,025评论 1赞 300
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,696评论 2赞 323
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,816评论 1赞 333
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,447评论 4赞 322
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,057评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,009评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,254评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,204评论 2赞 352
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,561评论 2赞 343

推荐阅读更多精彩内容