3.1 问题
分别在部门表、员工表和工资表中加入数据
通过SQLAlchemy代码实现
分别练习每次加入一行数据和每次可加入多行数据
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与数据库引擎绑定，创建会话类Sessionfrom 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)
数据成功添加