Top

NSD Python2 DAY04

  1. 案例1:向表中添加数据
  2. 案例2:创建表
  3. 案例3:添加数据
  4. 案例4:操作数据

1 案例1:向表中添加数据

1.1 问题

  1. 向employees表插入数据
  2. 向salary表插入数据
  3. 插入的数据需要commit到数据库中

1.2 步骤

实现此案例需要按照如下步骤进行。

步骤一:PyMySQL安装

1) 安装gcc,有些软件包是C的源码

[root@localhost ~]# yum install -y gcc
已加载插件:fastestmirror, langpacks
dvd                                                      | 3.6 kB     00:00     
Loading mirror speeds from cached hostfile
匹配 gcc-4.8.5-16.el7.x86_64 的软件包已经安装。正在检查更新。
无须任何处理

2)为了加速下载,可以使用国内开源镜像站点

[root@localhost ~]# mkdir ~/.pip
[root@localhost ~]# vim ~/.pip/pip.conf
[global]
index-url = http://pypi.douban.com/simple/
[install]
trusted-host=pypi.douban.com

3)安装pymysql

[root@localhost ~]# pip3 install pymysql

步骤二:安装mariadb-server

[root@localhost ~]# yum install –y mariadb-server

....

已安装:
		mariadb-server.x86_64 1:5.5.56-2.el7

作为依赖被安装:
		mariadb.x86_64 1:5.5.56-2.el7
		perl-DBD-MySQL. x86_64 0:4.023-5.el7

完毕!
[root@localhost ~]# systemctl start mariadb
[root@localhost ~]# systemctl enable mariadb
[root@localhost ~]# mysqladmin password tedu.cn

步骤三:创建数据库

1)创建数据库

[root@localhost ~]# mysql -uroot -ptedu.cn	
MariaDB [(none)]> CREATE DATABASE tedu DEFAULT CHARSET 'utf8';
Query OK, 1 row affected (0.00 sec)

2)创建部门表

部门表字段:部门ID、部门名称

MariaDB [(none)]> USE tedu;
Database changed
MariaDB [tedu]> CREATE TABLE departments(dep_id INT PRIMARY KEY, dep_name VARCHAR(20));
Query OK, 0 rows affected (0.04 sec)

3)创建员工表

员工表字段:员工编号、姓名、出生日期、部门ID、电话号码、email、引用外键id

MariaDB [tedu]> CREATE TABLE employees (emp_id INT PRIMARY KEY, emp_name VARCHAR(20) NOT NULL, birth_date DATE, phone CHAR(11), email VARCHAR(50), dep_id INT, FOREIGN KEY(dep_id) REFERENCES departments(dep_id));
Query OK, 0 rows affected (0.05 sec)

4)创建工资表

工资表字段:auto_id、员工编号、日期、基本工资、奖金、工资总和

MariaDB [tedu]> CREATE TABLE salary(auto_id INT AUTO_INCREMENT PRIMARY KEY, date DATE, emp_id INT, basic INT, awards INT, FOREIGN KEY(emp_id) REFERENCES employees(emp_id));
Query OK, 0 rows affected (0.05 sec)

步骤四:向departments表插入数据

1)新建insert_data.py文件,编写代码如下:

[root@localhost day10]# vim insert_data.py

import pymysql
1)连接数据库
conn = pymysql.connect(
    host='127.0.0.1',		#连接ip
    port=3306,			#端口号
    user='root',			#数据库用户名
    passwd='tedu.cn',		#数据库密码
    db='tedu',			#数据库名
    charset='utf8'		#设置了数据库的字符集
)
2)创建游标
cursor = conn.cursor()
3)向部门表departments中插入数据
insert1 = "INSERT INTO departments(dep_id, dep_name) VALUES(%s, %s)"
result = cursor.execute(insert1, (1, '人事部'))		# execute执行insert语句
4)将更新提交到数据库
conn.commit()	
5)关闭游标
cursor.close()
6)关闭数据库连接
conn.close()

2)执行insert_data.py文件:

[root@localhost day10]# python3 insert_data.py

3)登录mariadb查看结果:

MariaDB [tedu]>> select * from departments;
+--------+-----------+
| dep_id | dep_name  |
+--------+-----------+
|      1  |  人事部    |
+--------+-----------+
1 row in set (0.00 sec) 

4) 向部门表departments中插入数据还可以用如下方法:

#以上insert_data.py文件第3步可用如下代码替换:

insert1 = "INSERT INTO departments(dep_id, dep_name) VALUES(%s, %s)"
data = [(2, '运维部'), (3, '开发部')]
cursor.executemany(insert1, data)

mariadb查看结果如下:

MariaDB [tedu]>> select * from departments;
+--------+-----------+
| dep_id | dep_name  |
+--------+-----------+
|      1  |  人事部    |
|      2  |  运维部    |
|      3  |  开发部    |
+--------+-----------+
3 rows in set (0.01 sec)

步骤五:向employees表插入数据

1)新建insert_emp.py文件,编写代码如下:

[root@localhost day10]# vim insert_emp.py

import pymysql
1)连接数据库
conn = pymysql.connect(
    host='127.0.0.1',		#连接ip
    port=3306,			#端口号
    user='root',			#数据库用户名
    passwd='tedu.cn',		#数据库密码
    db='tedu',			#数据库名
    charset='utf8'		#设置了数据库的字符集
)
2)创建游标
cursor = conn.cursor()
3)向部门表employees中插入数据
insert1 = "INSERT INTO employees(emp_id, emp_name, birth_date,phone, email, dep_id) VALUES(%s, %s, %s, %s, %s, %s)"
result = cursor.execute(insert1, (1, '王君', '2018-9-30',\
 '15678789090', 'wj@163.com', 3))		# execute执行insert语句
4)将更新提交到数据库
conn.commit()	
5)关闭游标
cursor.close()
6)关闭数据库连接
conn.close()

2)执行insert_emp.py文件:

[root@localhost day10]# python3 insert_emp.py

3)登录mariadb查看结果:

MariaDB [tedu]>> select * from employees;
+--------+----------+------------+-------------+------------+--------+
| emp_id | emp_name | birth_date |  phone       | email      | dep_id |
+--------+----------+------------+-------------+------------+--------+
|      1  |    王君   | 2018-09-30 | 15678789090 | wj@163.com |      3 |
+--------+----------+------------+-------------+------------+--------+
 1 row in set (0.00 sec) 

4) 向部门表employees中插入数据还可以用如下方法:

#以上insert_emp.py文件第3步可用如下代码替换:

insert1 = "INSERT INTO employees (dep_id, dep_name) VALUES(%s, %s)"
data = [(2, '运维部'), (3, '开发部')]
cursor.executemany(insert1, data)

mariadb查看结果如下:

MariaDB [tedu]>> select * from departments;
+--------+----------+------------+-------------+------------+--------+
| emp_id | emp_name | birth_date |  phone       | email      | dep_id |
+--------+----------+------------+-------------+------------+--------+
|      1  |   王君    | 2018-09-30 | 15678789090 | wj@163.com |      3 |
|      2  |   李雷    | 2018-09-30 | 15678789090 | wj@163.com |      2 |
|      3  |   张美    | 2018-09-30 | 15678789090 | zm@163.com |      1 |
+--------+----------+------------+-------------+------------+--------+
3 rows in set (0.00 sec)

步骤六:向salary表插入数据

1)新建insert_sal.py文件,编写代码如下:

[root@localhost day10]# vim insert_sal.py

import pymysql
1)连接数据库
conn = pymysql.connect(
    host='127.0.0.1',		#连接ip
    port=3306,			#端口号
    user='root',			#数据库用户名
    passwd='tedu.cn',		#数据库密码
    db='tedu',			#数据库名
    charset='utf8'		#设置了数据库的字符集
)
2)创建游标
cursor = conn.cursor()
3)向部门表salary中插入数据
insert2 = "INSERT INTO salary(date, emp_id,basic, awards) VALUES(%s, %s, %s, %s)"
data = [('2018-9-30', 2, 1000, 2000), ('2018-9-30', 3, 3000, 6000),('2018-9-30', 1, 8000, 9000)]
cursor.executemany(insert2, data)
4)将更新提交到数据库
conn.commit()	
5)关闭游标
cursor.close()
6)关闭数据库连接
conn.close()

2)执行insert_sal.py文件:

[root@localhost day10]# python3 insert_sal.py

3)登录mariadb查看结果:

MariaDB [tedu]>> select * from salary;
+---------+------------+--------+-------+--------+
| auto_id | date       | emp_id | basic | awards |
+---------+------------+--------+-------+--------+
|       1 | 2018-09-30 |      2  |  1000 |   2000 |
|       2 | 2018-09-30 |      3  |  3000 |   6000 |
|       3 | 2018-09-30 |      1  |  8000 |   9000 |
+---------+------------+--------+-------+--------+
3 rows in set (0.01 sec) 

2 案例2:创建表

2.1 问题

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

2.2 步骤

实现此案例需要按照如下步骤进行。

步骤一:SQLAlchemy安装

注意:sqlalchemy可以连接各种数据库

[root@serwang ~]# pip3 install sqlalchemy
Collecting sqlalchemy
  Downloading http://pypi.doubanio.com/packages/aa/cc/48eec885d81f7260b07d
961b3ececfc0aa82f7d4a8f45ff997e0d3f44ba/SQLAlchemy-1.2.11.tar.gz (5.6MB)
...
...
Installing collected packages: sqlalchemy
  Running setup.py install for sqlalchemy ... done
Successfully installed sqlalchemy-1.2.11
You are using pip version 9.0.1, however version 18.0 is available.
You should consider upgrading via the 'pip install --upgrade pip' command.

步骤二:为SQLAlchemy创建数据库

MariaDB [tedu]> CREATE DATABASE tarena DEFAULT CHARSET 'utf8';

步骤三:创建部门表,创建dbconn.py文件,编写如下代码:

1) 创建连接到数据库的引擎

[root@localhost day10]# vim dbconn.py

#!/usr/bin/env python3

from sqlalchemy import create_engine

# 创建连接到数据库的引擎
engine = create_engine(
		#指定数据库、用户名、密码、连接到哪台服务器、库名等信息
    'mysql+pymysql://root:tedu.cn@localhost/tarena?charset=utf8',
    encoding='utf8',
    echo=True	#终端输出
)		

2)创建ORM映射,生成ORM映射所需的基类

from sqlalchemy.ext.declarative import declarative_base 

Base = declarative_base()

3)自定义映射类,创建部门表

from sqlalchemy import Column, String, Integer

class Departments(Base):  # 必须继承于Base 
    __tablename__ = 'departments'  # 库中的表名
    # 每个属性都是表中的一个字段,是类属性
    dep_id = Column(Integer, primary_key=True)	#Integer整数类型,primary_key主键
    # String字符串类型,nullable非空约束,unique唯一性约束
	   dep_name = Column(String(20), nullable=False, unique=True)

    def __str__(self):
        return '[部门ID:%s, 部门名称:%s]' % (self.dep_id, self.dep_name)
if __name__ == '__main__':
    # 在数据库中创建表,如果库中已有同名的表,将不会创建
    Base.metadata.create_all(engine)

4)测试脚本执行,生成部门表

[root@localhost day10]# python3 dbconn.py	#成功生成部门表

5)进入数据库查看结果

#登录数据库
[root@localhost day10]# mysql -uroot -ptedu.cn	
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.56-MariaDB MariaDB Server

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

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
#查看数据库表
MariaDB [(none)]> use tarena;
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 [tarena]> show tables;
+------------------+
| Tables_in_tarena |
+------------------+
| departments      |
+------------------+
1 row in set (0.00 sec)


MariaDB [tarena]> show create table departments;
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                    |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| departments | CREATE TABLE `departments` (
  `dep_id` int(11) NOT NULL AUTO_INCREMENT,
  `dep_name` varchar(20) NOT NULL,
  PRIMARY KEY (`dep_id`),
  UNIQUE KEY `dep_name` (`dep_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

步骤四:创建员工表,在dbconn.py文件中添加如下数据:

1)创建员工表

from sqlalchemy import ForeignKey	导入外键

class Employees(Base):  # 必须继承于Base
    __tablename__ = 'employees'  # 库中的表名
	    # 每个属性都是表中的一个字段,是类属性
    emp_id = Column(Integer, primary_key=True) 	#Integer整数类型,primary_key主键
    name = Column(String(20), nullable=False) # String字符串类型,nullable非空约束
    gender = Column(String(6))
    phone = Column(String(11))
    email = Column(String(50))
    dep_id = Column(Integer, ForeignKey('departments.dep_id'))	#与departments中dep_id做外键关联

    def __str__(self):
        return '员工:%s' % self.name

4)测试脚本执行,生成员工表

[root@localhost day10]# python3 dbconn.py	#成功生成员工表

5)进入数据库查看结果

#登录数据库
[root@localhost day10]# mysql -uroot -ptedu.cn	
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.56-MariaDB MariaDB Server

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

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
#查看数据库表
MariaDB [(none)]> use tarena;
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 [tarena]> show tables;
+------------------+
| Tables_in_tarena |
+------------------+
| departments       |
| employees         |
+------------------+
2 rows in set (0.00 sec)

步骤五:创建工资表,在dbconn.py文件中添加如下数据:

1)创建工资表

from sqlalchemy import Date	导入外键

class Employees(Base):  # 必须继承于Base
    __tablename__ = 'employees'  # 库中的表名
	    # 每个属性都是表中的一个字段,是类属性
    emp_id = Column(Integer, primary_key=True) 	#Integer整数类型,primary_key主键
    name = Column(String(20), nullable=False) # String字符串类型,nullable非空约束
    gender = Column(String(6))
    phone = Column(String(11))
    email = Column(String(50))
    dep_id = Column(Integer, ForeignKey('departments.dep_id'))	#与departments中dep_id做外键关联

    def __str__(self):
        return '员工:%s' % self.name

class Salary(Base):  # 必须继承于Base
    __tablename__ = 'salary'  # 库中的表名
    auto_id = Column(Integer, primary_key=True) #Integer整数类型,primary_key主键
    date = Column(Date)	#导入日期
    emp_id = Column(Integer, ForeignKey('employees.emp_id')) #与employees中emp_id做外键关联
    basic = Column(Integer)		#基本工资
    awards = Column(Integer)	#奖金

4)测试脚本执行,生成员工表

[root@localhost day10]# python3 dbconn.py	#成功生成工资表

5)进入数据库查看结果

#登录数据库
[root@localhost day10]# mysql -uroot -ptedu.cn	
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.56-MariaDB MariaDB Server

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

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
#查看数据库表
MariaDB [(none)]> use tarena;
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 [tarena]> show tables;
+------------------+
| Tables_in_tarena |
+------------------+
| departments      |
| employees        |
| salary           |
+------------------+
3 rows in set (0.00 sec)

3 案例3:添加数据

3.1 问题

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

3.2 步骤

实现此案例需要按照如下步骤进行。

步骤一:向部门表添加数据,创建add_department.py文件,添加如下代码:

1)创建映射类的实例

[root@localhost day10]# vim add_department.py

from dbconn import Departments

hr = Departments(dep_id=1, dep_name='hr')
print(hr.dep_name)
print(hr.dep_id)

测试执行结果:

[root@localhost day10]# python3 add_department.py
hr
1

登录数据库查看,部门表中数据为空,此时,并不会真正在数据库表中添加记录

MariaDB [tarena]> select * from departments;
Empty set (0.00 sec)

2) 想在数据库中添加数据需完成如下操作,创建会话类

在dbconn.py文件中,添加如下代码:

通过将sessionmaker与数据库引擎绑定,创建会话类Session

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

3)添加新对象

在add_department.py文件中添加如下代码:

from dbconn import Departments, Session

session = Session()	#创建会话类实例
session.add(hr)	#向实例绑定的数据库添加数据
session.commit()	#将数据提交到实例对应数据库
session.close()	#关闭session类

测试执行结果:

[root@localhost day10]# python3 add_department.py

登录数据库查看部门表中数据

MariaDB [tarena]> select * from departments;
+--------+----------+
| dep_id | dep_name |
+--------+----------+
|      1 |  hr       |
+--------+----------+
1 row in set (0.01 sec)

数据成功添加

4)批量添加新数据

在add_department.py文件中添加如下代码:

ops = Departments(dep_id=2, dep_name='operations')
dev = Departments(dep_id=3, dep_name='development')
finance = Departments(dep_id=4, dep_name='财务部')
deps = [ops, dev]
session = Session()
session.add_all(deps)		
session.add(finance)
session.commit()
session.close()

此时注意:

添加过的数据不要再添加,即将session.add(hr)注释掉。

如果文件中有中文,注意在dbconn.py文件中,将engine的参数修改为如下代码:

engine=create_engine(
'mysql+pymysql://root:tedu.cn@localhost/tarena?charset=uU8',
	encoding='uU8',
	echo=True
)	

测试执行结果:

[root@localhost day10]# python3 add_department.py

登录数据库查看部门表中数据

MariaDB [tarena]> select * from departments;
+--------+-------------+
| dep_id | dep_name    |
+--------+-------------+
|      3 | development  |
|      1 | hr           |
|      2 | operations   |
|      4 | 财务部        |
+--------+-------------+
4 rows in set (0.00 sec)

数据成功添加

步骤二:向员工表批量添加数据,创建add_employees.py文件,添加如下代码:

from dbconn import Employees, Session

wj = Employees(
    emp_id=1,name='王俊',gender='男',phone='15678789090',email='wj@163.com',    dep_id=3
)
wwc = Employees(
    emp_id=2,name='吴伟超',gender='男',phone='13499887755',email='wwc@qq.com',    dep_id=3
)
dzj = Employees(
    emp_id=3, name='董枝俊', gender='男', phone='18900998877', email='dzj@163.com', dep_id=3
)
ltd = Employees(
		emp_id=4, name='李通达', gender='男', phone='13378904567', email='ltd@163.com', dep_id=2)
wxy = Employees(
		emp_id=5, name='王秀燕', gender='女', phone='15098765432', email='wxy@tedu.cn', dep_id=2)
gq = Employees(
		emp_id=6, name='高琦', gender='女', phone='15876543212', email='gq@tarena.com', dep_id=1)
wzf = Employees(
	 	emp_id=7, name='王召飞', gender='男', phone='15609871234', email='wzf@sohu.com', dep_id=1)
sy = Employees(
		emp_id=8, name='孙燕', gender='女', phone='18567895435', email='sy@163.com', dep_id=4)
gpf = Employees(
		emp_id=9, name='高鹏飞', gender='男', phone='13566889900', email='gpf@163.com', dep_id=2)
emps = [wj, wwc, dzj, ltd, wxy, gq, wzf, sy, gpf]
session = Session()
session.add_all(emps)
session.commit()
session.close()

测试执行结果:

[root@localhost day10]# python3 add_employees.py

登录数据库查看部门表中数据

MariaDB [tarena]> select * from employees;
+--------+-----------+--------+-------------+---------------+--------+
| emp_id | name      | gender | phone       | email         | dep_id |
+--------+-----------+--------+-------------+---------------+--------+
|      1  |    王俊    |   男   | 15678789090 | wj@163.com    |      3 |
|      2  |   吴伟超   |   男   | 13499887755 | wwc@qq.com    |      3 |
|      3  |   董枝俊   |   男   | 18900998877 | dzj@163.com   |      3 |
|      4  |   李通达   |   男   | 13378904567 | ltd@163.com   |      2 |
|      5  |   王秀燕   |   女   | 15098765432 | wxy@tedu.cn   |      2 |
|      6  |    高琦    |   女   | 15876543212 | gq@tarena.com |      1 |
|      7  |   王召飞   |   男   | 15609871234 | wzf@sohu.com  |      1 |
|      8  |    孙燕    |   女   | 18567895435 | sy@163.com    |      4 |
|      9  |   高鹏飞   |   男   | 13566889900 | gpf@163.com   |      2 |
+--------+-----------+--------+-------------+---------------+--------+
9 rows in set (0.00 sec)

数据成功添加

步骤三:向工资表添加数据,创建add_ salary.py文件,添加如下代码:

from dbconn import Salary, Session

jan2018_1 = Salary(date='2018-01-10', emp_id=1, basic=10000, awards=2000)
jan2018_2 = Salary(date='2018-01-10', emp_id=2, basic=11000, awards=1500)
jan2018_3 = Salary(date='2018-01-10', emp_id=3, basic=11000, awards=2200)
jan2018_4 = Salary(date='2018-01-10', emp_id=4, basic=11000, awards=3000)
jan2018_5 = Salary(date='2018-01-10', emp_id=1, basic=13000, awards=2000)
jan2018_6 = Salary(date='2018-01-10', emp_id=6, basic=15000, awards=3000)
jan2018_7 = Salary(date='2018-01-10', emp_id=7, basic=9000, awards=3000)
jan2018_8 = Salary(date='2018-01-10', emp_id=8, basic=13000, awards=2000)
jan2018_9 = Salary(date='2018-01-10', emp_id=9, basic=13000, awards=1500)
session = Session()
sals = [jan2018_1, jan2018_2, jan2018_3,jan2018_4, jan2018_5, jan2018_6, jan2018_7, jan2018_8, jan2018_9]
session.add_all(sals)
session.commit()
session.close()

测试执行结果:

[root@localhost day10]# python3 add_ salary.py

登录数据库查看部门表中数据

MariaDB [tarena]> select * from salary;
+---------+------------+--------+-------+--------+
| auto_id | date       | emp_id | basic | awards |
+---------+------------+--------+-------+--------+
|       1 | 2018-01-10 |      1 | 10000 |   2000 |
|       2 | 2018-01-10 |      2 | 11000 |   1500 |
|       3 | 2018-01-10 |      3 | 11000 |   2200 |
|       4 | 2018-01-10 |      4 | 11000 |   3000 |
|       5 | 2018-01-10 |      1 | 13000 |   2000 |
|       6 | 2018-01-10 |      6 | 15000 |   3000 |
|       7 | 2018-01-10 |      7 |  9000 |   3000 |
|       8 | 2018-01-10 |      8 | 13000 |   2000 |
|       9 | 2018-01-10 |      9 | 13000 |   1500 |
+---------+------------+--------+-------+--------+
9 rows in set (0.00 sec)

数据成功添加

4 案例4:操作数据

4.1 问题

  1. 修改部门表,将人事部改为人力资源部
  2. 如果存在设计部,将设计部删除
  3. 查询所有每个员工及其所在部门

4.2 步骤

实现此案例需要按照如下步骤进行。

步骤一:修改表中的记录,首先需要把记录找到

qset12 = session.query(Department).filter(Department.dep_name=='人事部')
hr = qset12.one()

步骤二:只要把映射的实体类对象重新赋值保存即可

hr.dep_name='人力资源部'
session.commit()

步骤三:删除记录与修改类似,先找到记录再执行删除

qset13 = session.query(Employee).filter(Department.dep_name=='设计部')
dep = qset13.one()
session.delete(dep)
session.commit()

步骤四:查询每个员工所在的部门涉及的是多表查询,因为员工表中只有部门ID,部门名称在部门表中存储

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