python SQLAlchemy数据库工具_python 数据库管理工具

一、sqlalchemy概述与架构

1.SQLAlchemy

SQLAlchemy是Python编程语言下的一款开源软件。提供SQL包以及对象关联映射（ORM）工具，使用MIT许可证

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

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

SQLAlchemy目标是提供兼容众多数据库，如SQLite，Mysql，SQLServer，Oracal

安装数据库模块 pip3 install sqlalchemy

 

2.SQLAlchemy架构图

SQLAlchemy分为ORM（对象管理映射工具）和Core（核心模块），还有DBAPI数据库接口

Core分为Engine核心，SQl语句支持，Connection Pooling连接池

 

3.ORM模型

ORM即对象关系映射

数据库是一个二维表，包含多行多列，把一个表的内容用Pthon的数据结构表示的话，可以有一个列表list表示,list每一个元素都是tuple，表示一行记录

[

 ('1','c')

 ('2','b') 

('3','c')

]

用列表表示一行很难看出表的结构。如果把一个列表用一个类来表示，就很容易看出结构

class User(object):

  def __init__(self,id,name)

    self.id=id

    self.name=name

u=User(1,'libai')            #实例化对象

 

二、sqlalchemy连接数据库操作与定义映射关系类

1.连接mysql

通过create_engine实现数据库的链接

1. #!/usr/bin/env python3
2.  
3. from sqlalchemy import create_engine
4.  
5. # 创建连接到数据库的引擎
6. engine = create_engine(
7. #指定数据库、用户名、密码、连接到哪台服务器、库名等信息
8. 'mysql+pymysql://用户名:密码@localhost/库名?charset=utf8',
9. encoding='utf8',
10. echo=True    #终端输出
11. )   

 

2.声明映射--ORM表映射基类

当时用ORM的时候，配置过程从描述数据库表开始

通过自定义类映射相应的表

通过声明系统实现类的映射，定义基类

1. from sqlalchemy.ext.declarative import declarative_base
2. Base=declarative_base()

 

3.创建映射类--数据库表结构

一旦创建了基类，就可以创建自定义映射类了，映射类相当于将表结构转换为类结构

1. from sqlalchemy import Column,Integer,String
2. class Departments(Base):                                                                           # 必须继承于Base
3.       __tablename__ = 'departments'                                                            # 库中的表名
4.      dep_id = Column(Integer, primary_key=True)                                       #Integer整数类型,primary_key主键
5.      dep_name = Column(String(20), nullable=False, unique=True)           #nullable非空约束，unique唯一性约束
6.  
7.      def __str__(self):
8.           return '[部门ID：%s, 部门名称：%s]' % (self.dep_id, self.dep_name)

 

4.创建架构--创建表

类构建完成后，表的信息将被写入到表的元数据（metadata），综上Departments类。可以看到表的结构信息

>>> Departments.__table__
Table('departments', MetaData(bind=None), Column('dep_id', Integer(), table=<departments>, primary_key=True, nullable=False), Column('dep_name', String(length=20), table=<departments>, nullable=False), schema=None)

>>> Base.metadata.create_add(engine)          #在数据库中创建表

 

三、sqlalchemy的增删改查

1.创建会话类Session

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

1. >>>from sqlalchemy.orm import sessionmaker             #导入模块
2. >>>Session=sessionmaker(bind=engine)                     #如果创建session时engine存在
3. >>>Session=sessionmaker()                                         #创建session时engine不存在，可以后期绑定
4. >>>Session.configure(bin=engine)

 

2.Session添加新对象--数据库插入操作

会话类的实例对象用于绑定到数据库，当实例初次使用时，它将从Engine维护的连接池中获得一个连接，当所有的事务均被commit或会话对象关闭时，连接结束

1. >>>session=Session()                                                   #创建对话
2. >>>dep_dev=Departments(dep_name="财务部")         #实例化对象
3. >>>session.add(dep_dev)                                             #向session中插入一条字段
4. >>>session.commit()                                                      #提交
5. >>>session.close()                                                         #关闭会话
6. >>>ops = Departments(dep_id=2, dep_name='人事部')               #一次提交多个字段
7. >>>dev = Departments(dep_id=3, dep_name='财务部')
8. >>>deps = [ops, dev]
9. >>>session = Session()
10. >>>session.add_all(deps)        
11. >>>session.commit()
12. >>>session.close()

 

3.创建映射类--创建表结构--外键约束

ORM映射关系也可用于表间创建外键约束

1. class Employees(Base):                                                               # 必须继承于Base
2.   __tablename__ = 'employees'                                                      # 库中的表名
3.    # 每个属性都是表中的一个字段，是类属性
4.   emp_id = Column(Integer, primary_key=True)                             #Integer整数类型,primary_key主键
5.   name = Column(String(20), nullable=False)                                 # String字符串类型，nullable非空约束
6.   gender = Column(String(6))
7.   phone = Column(String(11))
8.   email = Column(String(50))
9.   dep_id = Column(Integer, ForeignKey('departments.dep_id'))     #与departments中dep_id做外键关联
10.  
11. def __str__(self):
12.   return '员工：%s' % self.name

 

4.SQLAlchemy查询操作

通过session的query()函数创建查询对象

query()函数可以接受多种参数

1）查询所有字段

1. >>>session=Session()
2. >>>qresult=session.query(Departments).order_by(Departments.dep_id)      #利用query函数查出记录存放到变量里
3. >>>for i in qresult:
4. >>>   print(i)

2）查询部分字段

1. >>>session=Session()
2. >>>for id,name in session.query(Departments.id,Departments.name):
3. >>>    print('id:%s , name: %s' % (id,name))

3)查询并排序

 按照Departments.id排序显示

1. >>>session=Session()
2. >>>for id,name in session.query(Departments).order_by(Departments.id):
3. >>>    print('id:%s , name: %s' % (id,name))

4)切片查询

切片查询一般与排序查询组合使用，常用于分页操作

1. >>>session=Session()
2. >>>for id,name in session.query(Departments).order_by(Departments.id)[2:10]:
3. >>>   print('id:%s , name: %s' % (id,name))

5）条件查询（过滤查询）

过滤查询可以单独用也可以叠加使用,也可以使用or_ 或者and_

1. >>>session=Session()
2. >>>for id,name in session.query(Departments).filter(Departments.id==2):
3. >>>    print('id:%s , name: %s' % (id,name))
4. #过滤查询可以单独用也可以叠加使用
5. >>>for id,name in session.query(Departments).filter(Departments.a>=100).filter(Departments.b>=100):
6. >>>   print('id:%s , name: %s' % (id,name))
7. #也可以通过and_符号使用，代表即满足a又满足b
8. >>>for id,name in session.query(Departments).filter(and_(Departments.a>=100，(Departments.b>=100)):
9. >>>   print('id:%s , name: %s' % (id,name))
10. #也可以通过or_符号使用，代表即满足a或b的任意一个
11. >>>for id,name in session.query(Departments).filter(or_(Departments.a>=100，(Departments.b>=100)):
12. >>>    print('id:%s , name: %s' % (id,name))

常用过滤符：

相等：query.filter(Departments.name == ''libai")

不相等：query.filter(Departments.name != ''libai")

模糊查询：query.filter(Departments.name.like(''%li"))

in：query.filter(Departments.name.in_(['libai'，'zhangsan'])               #查询libai或zhangsan

not in：query.filter(~Departments.name.in_(['libai'，'zhangsan'])       #查询除libai或zhangsan

字段为空：query.filter(~Departments.name.is_(None)                       #查询为空的Departments.name字段字段不为空：query.filter(~Departments.name.isnot(None)                 #查询不为空的Departments.name字段

或查询：query(Departments).filter(and_(Departments.a>=100，(Departments.b>=100))

且查询：query(Departments).filter(or_(Departments.a>=100，(Departments.b>=100))

 

6）多表查询

通过join()方法实现多表查询

1. >>>session=Session()
2. >>>for id,name in session.query(Departments.name,Employees.name).join(Departments):
3. >>>   print('id:%s , name: %s' % (id,name))

 

5.更新数据

通过session的update()方法更新

1. >>>session=Session()
2. >>>q1=session.query(Departments).filter(Departments.id==1)       #通过变量保留查询结果
3. >>>q1=session.query(Departments).get(1)                                     #如果主键是int类型可以直接获取
4. >>>q.update({Departments.name:'人事部'})                                     #更新值
5. #也可以一步到位
6. >>>session.query(Departments).filter(Departments.id==1).update({Departments.name:'人事部'}) 
7. >>>q.commit()
8. >>>q.close()

 

6.删除记录

通过session的delete()方法更新

1. >>>session=Session()
2. >>>q1=session.query(Departments).filter(Departments.id==1)       #通过变量保留查询结果
3. >>>q1=session.query(Departments).get(1)                                     #如果主键是int类型可以直接获取
4. >>>q.delete(q1)                                                                                #更新值
5. >>>q.commit()
6. >>>q.close()

 

四、实验1--使用SQLAlchemy创建表

要求：

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

 

步骤一：SQLAlchemy安装

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

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

 

步骤二：为SQLAlchemy创建数据库

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

 

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

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

1. [root@localhost day10]# vim dbconn.py
2.  
3. #!/usr/bin/env python3
4.  
5. from sqlalchemy import create_engine
6.  
7. # 创建连接到数据库的引擎
8. engine = create_engine(
9. #指定数据库、用户名、密码、连接到哪台服务器、库名等信息
10. 'mysql+pymysql://root:tedu.cn@localhost/tarena?charset=utf8',
11. encoding='utf8',
12. echo=True    #终端输出
13. )        

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

1. from sqlalchemy.ext.declarative import declarative_base
2. Base = declarative_base()

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

1. from sqlalchemy import Column, String, Integer
2.  
3. class Departments(Base):                                                                  # 必须继承于Base
4.   __tablename__ = 'departments'                                                         # 库中的表名
5.   dep_id = Column(Integer, primary_key=True)                                    #Integer整数类型,primary_key主键
6.   dep_name = Column(String(20), nullable=False, unique=True)        #nullable非空约束，unique唯一性约束
7.  
8.   def __str__(self):
9.       return '[部门ID：%s, 部门名称：%s]' % (self.dep_id, self.dep_name)
10. if __name__ == '__main__':
11.   Base.metadata.create_all(engine)                        # 在数据库中创建表，如果库中已有同名的表，将不会创建

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

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

5)进入数据库查看结果

1. #登录数据库
2. [root@localhost day10]# mysql -uroot -ptedu.cn    
3. #查看数据库表
4. MariaDB [(none)]> use tarena;
5. Reading table information for completion of table and column names
6. You can turn off this feature to get a quicker startup with -A
7.  
8. Database changed
9. MariaDB [tarena]> show tables;
10. +------------------+
11. | Tables_in_tarena |
12. +------------------+
13. | departments |
14. +------------------+
15. 1 row in set (0.00 sec)

 

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

1)创建员工表

1. from sqlalchemy import ForeignKey                                            #导入外键
2.  
3. class Employees(Base):                                                              # 必须继承于Base
4.   __tablename__ = 'employees' # 库中的表名
5.   emp_id = Column(Integer, primary_key=True)                          #Integer整数类型,primary_key主键
6.   name = Column(String(20), nullable=False)                             # String字符串类型，nullable非空约束
7.   gender = Column(String(6))
8.   phone = Column(String(11))
9.   email = Column(String(50))
10.   dep_id = Column(Integer, ForeignKey('departments.dep_id'))    #与departments中dep_id做外键关联
11.  
12. def __str__(self):
13.   return '员工：%s' % self.name

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

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

5)进入数据库查看结果

1. #登录数据库
2. [root@localhost day10]# mysql -uroot -ptedu.cn    
3. #查看数据库表
4. MariaDB [(none)]> use tarena;
5. Reading table information for completion of table and column names
6. You can turn off this feature to get a quicker startup with -A
7.  
8. Database changed
9. MariaDB [tarena]> show tables;
10. +------------------+
11. | Tables_in_tarena |
12. +------------------+
13. | departments |
14. | employees |
15. +------------------+
16. 2 rows in set (0.00 sec)

 

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

1)创建工资表

1. from sqlalchemy import Date                                               #导入外键
2.  
3. class Employees(Base):                                                       # 必须继承于Base
4.   __tablename__ = 'employees'                                           #库中的表名
5.   emp_id = Column(Integer, primary_key=True)                  #Integer整数类型,primary_key主键
6.   name = Column(String(20), nullable=False)                      # String字符串类型，nullable非空约束
7.   gender = Column(String(6))
8.   phone = Column(String(11))
9.   email = Column(String(50))
10.   dep_id = Column(Integer, ForeignKey('departments.dep_id'))    #与departments中dep_id做外键关联
11.  
12. def __str__(self):
13.   return '员工：%s' % self.name
14.  
15. class Salary(Base):                                                                       # 必须继承于Base
16.   __tablename__ = 'salary'                                                            # 库中的表名
17.   auto_id = Column(Integer, primary_key=True)                           #Integer整数类型,primary_key主键
18.   date = Column(Date)                                                                  #导入日期
19.   emp_id = Column(Integer, ForeignKey('employees.emp_id'))   #与employees中emp_id做外键关联
20.   basic = Column(Integer)                                                             #基本工资
21.   awards = Column(Integer)                                                          #奖金

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

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

5)进入数据库查看结果

1. #登录数据库
2. [root@localhost day10]# mysql -uroot -ptedu.cn    
3. #查看数据库表
4. MariaDB [(none)]> use tarena;
5. Reading table information for completion of table and column names
6. You can turn off this feature to get a quicker startup with -A
7.  
8. Database changed
9. MariaDB [tarena]> show tables;
10. +------------------+
11. | Tables_in_tarena |
12. +------------------+
13. | departments |
14. | employees |
15. | salary |
16. +------------------+
17. 3 rows in set (0.00 sec)

 

五、实验2--使用SQLAlchemy做增删改查操作

要求：

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

 

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

1)创建映射类的实例

1. [root@localhost day10]# vim add_department.py
2.  
3. from dbconn import Departments
4.  
5. hr = Departments(dep_id=1, dep_name='hr')
6. print(hr.dep_name)
7. print(hr.dep_id)

测试执行结果：

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

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

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

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

在dbconn.py文件中，添加如下代码：

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

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

3)添加新对象

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

1. from dbconn import Departments, Session
2.  
3. session = Session()                              #创建会话类实例
4. session.add(hr)                                    #向实例绑定的数据库添加数据
5. session.commit()                                  #将数据提交到实例对应数据库
6. session.close()                                     #关闭session类

测试执行结果：

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

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

1. MariaDB [tarena]> select * from departments;
2. +--------+----------+
3. | dep_id | dep_name |
4. +--------+----------+
5. | 1 | hr |
6. +--------+----------+
7. 1 row in set (0.01 sec)

数据成功添加

4)批量添加新数据

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

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

此时注意：

添加过的数据不要再添加，即将session.add（hr）注释掉。

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

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

测试执行结果：

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

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

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

数据成功添加

 

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

1. from dbconn import Employees, Session
2.  
3. wj = Employees(
4. emp_id=1,name='王俊',gender='男',phone='15678789090',email='wj@163.com', dep_id=3
5. )
6. wwc = Employees(
7. emp_id=2,name='吴伟超',gender='男',phone='13499887755',email='wwc@qq.com', dep_id=3
8. )
9. dzj = Employees(
10. emp_id=3, name='董枝俊', gender='男', phone='18900998877', email='dzj@163.com', dep_id=3
11. )
12. ltd = Employees(
13.         emp_id=4, name='李通达', gender='男', phone='13378904567', email='ltd@163.com', dep_id=2)
14. wxy = Employees(
15.         emp_id=5, name='王秀燕', gender='女', phone='15098765432', email='wxy@tedu.cn', dep_id=2)
16. gq = Employees(
17.         emp_id=6, name='高琦', gender='女', phone='15876543212', email='gq@tarena.com', dep_id=1)
18. wzf = Employees(
19.          emp_id=7, name='王召飞', gender='男', phone='15609871234', email='wzf@sohu.com', dep_id=1)
20. sy = Employees(
21.         emp_id=8, name='孙燕', gender='女', phone='18567895435', email='sy@163.com', dep_id=4)
22. gpf = Employees(
23.         emp_id=9, name='高鹏飞', gender='男', phone='13566889900', email='gpf@163.com', dep_id=2)
24. emps = [wj, wwc, dzj, ltd, wxy, gq, wzf, sy, gpf]
25. session = Session()
26. session.add_all(emps)
27. session.commit()
28. session.close()

测试执行结果：

1. [root@localhost day10]# python3 add_employees.py

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

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

数据成功添加

 

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

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

测试执行结果：

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

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

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

数据成功添加

步骤四：修改表中的记录，首先需要把记录找到

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

 

步骤五：删除记录与修改类似，先找到记录再执行删除

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

 

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

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