Python连接数据库
安装python包
- python包管理,使用pip / pip3
- python官方包管理:https://pypi.org
# 配置pip使用国内镜像
[root@localhost ~]# mkdir ~/.pip
[root@localhost ~]# vim ~/.pip/pip.conf
[global]
index-url = http://mirrors.aliyun.com/pypi/simple/
[install]
trusted-host = mirrors.aliyun.com
# 在线安装pymysql
[root@localhost ~]# pip3 install pymysql
数据库
- 如果你正在为一个小型公司编写软件,这个过程中需要用到一个数据库
- 数据库需要记录的信息包括员工的基本信息以及发工资的情况
- 数据库需要记录的字段:员工编号、姓名、生日、性别、联系方式、部门、工资日、基本工资、奖金、工资总额
- 关系型数据库应该尽量避免数据冗余。可以通过把字段放到不同的表来实现消除冗余。
- 数据库范式。关系型数据库至少要满足第三范式
- 所谓第一范式(1NF)是指在关系模型中,对于添加的一个规范要求,所有的域都应该是原子性的,即数据库表的每一列都是不可分割的原子数据项。联系方式可以再分,如家庭住址、手机号、email等
- 第二范式(2NF)是在第一范式(1NF)的基础上建立起来的,即满足第二范式(2NF)必须先满足第一范式(1NF)。第二范式(2NF)要求数据库表中的每个实例或记录必须可以被唯一地区分。简单来说,就是每张表需要有一个主键字段。
- 第三范式(3NF)是第二范式(2NF)的一个子集,即满足第三范式(3NF)必须满足第二范式(2NF)。简而言之,非主信息不能依赖于其他非主信息。工资总额由基本工资和奖金计算出来,不应该出现在数据库中。
- 最终的三张表:
- 员工表:员工ID、姓名、生日、email、部门id
- 部门表:部门ID、部门名
- 工资表:id、工资日、员工ID、基本工资、奖金
pymysql
- 用于操纵mysql数据库
[root@localhost ~]# yum install -y mariadb-server
[root@localhost ~]# systemctl start mariadb
[root@localhost ~]# systemctl enable mariadb
[root@localhost ~]# mysqladmin password tedu.cn
[root@localhost ~]# mysql -uroot -ptedu.cn
MariaDB [(none)]> CREATE DATABASE nsd1911 DEFAULT CHARSET utf8; #必须指定字符集为utf8
利用python操作mysql数据库,完成增删改查等操作:
- 利用python创建表
import pymysql
#建立到数据库的连接
conn = pymysql.connect( #固定格式
host = '127.0.0.1',port = 3306,
user='root',passwd='tedu.cn',
db='nsd1911',charset='utf8'
)
#创建游标,游标类似文件对象类似,通过文件对象操作文件。
cur = conn.cursor()
#编写SQL语句
create_dep = """create table departments (
dep_id int,dep_name varchar(30),
PRIMARY KEY (dep_id)
)"""
cur.execute(create_dep) #执行定义的SQL语句
#确认
conn.commit()
#关闭
cur.close()
conn.close()
执行后查看是否成功创建表:
[root@python untitled]# mysql -uroot -ptedu.cn
MariaDB [(none)]> use nsd1911
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 [nsd1911]> show tables;
+-------------------+
| Tables_in_nsd1911 |
+-------------------+
| departments |
+-------------------+
1 row in set (0.00 sec)
MariaDB [nsd1911]> desc departments;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| dep_id | int(11) | NO | PRI | 0 | |
| dep_name | varchar(30) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
利用python添加表数据:
只需更改SQL语句部分,其余不动
#编写SQL语句
insert1 = "insert into departments values (%s %s)"
hr = (1,'人事部')
ops = (2,'运维部')
dev = (3,'开发部')
qa = (4,'测试部')
sales = (5,'销售部')
market = (6,'市场部')
cur.execute(insert1,hr) #增加一个部门
cur.executemany(insert1,[ops,dev,qa,sales,market]) #增加多个部门
验证:
MariaDB [nsd1911]> select * from departments;
+--------+-----------+
| dep_id | dep_name |
+--------+-----------+
| 1 | 人事部 |
| 2 | 运维部 |
| 3 | 开发部 |
| 4 | 测试部 |
| 5 | 销售部 |
| 6 | 市场部 |
+--------+-----------+
6 rows in set (0.00 sec)
利用python查询表数据:
只需更改SQL语句部分,其余不动
#查询并取值
select1 = "select * from departments ORDER BY %s"
cur.execute(select1,('dep_id',)) #定义查询语句,查寻dep_id字段的值
result1 = cur.fetchone() #取出第一条记录
print(result1)
print('*' * 30)
result2 = cur.fetchmany(2) #继续读取后两条记录
print(result2)
print('*' * 30)
result3 = cur.fetchall() #继续取出全部剩余记录
print(result3)
测试运行:
[root@python python]# python3 wenhao.py
(1, '人事部')
******************************
((2, '运维部'), (3, '开发部'))
******************************
((4, '测试部'), (5, '销售部'), (6, '市场部'))
利用python修改表数据:
只需更改SQL语句部分,其余不动
update1 = "update departments set dep_name=%s where dep_name=%s"
cur.execute(update1,('人力资源部','人事部'))
测试运行 :
MariaDB [nsd1911]> select * from departments;
+--------+-----------------+
| dep_id | dep_name |
+--------+-----------------+
| 1 | 人力资源部 |
| 2 | 运维部 |
| 3 | 开发部 |
| 4 | 测试部 |
| 5 | 销售部 |
| 6 | 市场部 |
+--------+-----------------+
利用python删除表数据:
只需更改SQL语句部分,其余不动
drop1 = 'delete from departments WHERE dep_id=%s'
cur.execute(drop1,(6,))
测试运行 :
MariaDB [nsd1911]> select * from departments;
+--------+-----------------+
| dep_id | dep_name |
+--------+-----------------+
| 1 | 人力资源部 |
| 2 | 运维部 |
| 3 | 开发部 |
| 4 | 测试部 |
| 5 | 销售部 |
+--------+-----------------+
sqlalchemy
https://yiyibooks.cn -> 翻译的中文文档
https://yiyibooks.cn/wizard/sqlalchemy_11/orm_tutorial.html
ORM
- Object:对象
- Relationship:关系
- Mapper:映射
- 在sqlalchemy中,数据库的每张表映射为一个class
- class中的类变量与数据库的字段映射
- 数据库的字段类型,sqlalchemy都提前声明了相应的class
- 数据库表的一行记录,在sqlalchemy中映射为一个实例
MariaDB [nsd1911]> CREATE DATABASE cloud1911 DEFAULT CHARSET utf8; #先创建库指定字符集
[root@localhost ~]# pip3 install sqlalchemy #安装sqlalchemy
使用sqlalchemy创建库:
from sqlalchemy import create_engine, Column, Integer, String, Date, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
# 创建到数据连接的引擎
engine = create_engine(
# mysql+pymysql://用户名:密码@服务器/数据库?参数
'mysql+pymysql://root:tedu.cn@127.0.0.1/cloud1911?charset=utf8',
encoding='utf8',
# echo=True
)
# 创建连接数据库的会话类
Session = sessionmaker(bind=engine)
# 创建实体类的基类
Base = declarative_base()
#####################以上为固定格式###################
# 声明实体类
class Department(Base):
__tablename__ = 'departments'
dep_id = Column(Integer, primary_key=True) #Integer表示int类型
dep_name = Column(String(20), unique=True) #string表示字符类型
if __name__ == '__main__':
# 在数据库中创建表,如果表已存在,则跳过
Base.metadata.create_all(engine)
测试运行:
MariaDB [nsd1911]> use cloud1911;
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 [cloud1911]> show tables;
+---------------------+
| Tables_in_cloud1911 |
+---------------------+
| departments |
+---------------------+
1 row in set (0.00 sec)
MariaDB [cloud1911]> desc departments;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| dep_id | int(11) | NO | PRI | NULL | auto_increment |
| dep_name | varchar(20) | YES | UNI | NULL | |
+----------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
创建另外两个表:
class Employee(Base):
__tablename__ = 'employees'
emp_id = Column(Integer,primary_key=True)
emp_name = Column(String(50))
birth_date = Column(Date())
email = Column(String(50))
dep_id = Column(Integer,ForeignKey('departments.dep_id')) #设置外键
class Salary(Base): #创建工资表
__tablename__ = 'Salary'
id = Column(Integer,primary_key=True)
date = Column(Date())
emp_id = Column(Integer,ForeignKey('employees.emp_id'))
basic = Column(Integer)
awards = Column(Integer)
```
验证:
```python
MariaDB [cloud1911]> desc employees;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| emp_id | int(11) | NO | PRI | NULL | auto_increment |
| emp_name | varchar(50) | YES | | NULL | |
| birth_date | date | YES | | NULL | |
| email | varchar(50) | YES | | NULL | |
| dep_id | int(11) | YES | MUL | NULL | |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
MariaDB [cloud1911]> desc Salary;
+--------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| date | date | YES | | NULL | |
| emp_id | int(11) | YES | MUL | NULL | |
| basic | int(11) | YES | | NULL | |
| awards | int(11) | YES | | NULL | |
+--------+---------+------+-----+---------+----------------+
新建一个项目,导入之前模块
# create / retrive / update / delete 增删改查
from wenhao import Session,Department, Employee
#创建实例会话,用于创建链接数据库的会话
session= Session()
#执行SQL语句
hr = Department(dep_id=1,dep_name='人事')
ops = Department(dep_id=2,dep_name='运维')
dev = Department(dep_id=3,dep_name='开发')
qa = Department(dep_id=4,dep_name='测试')
sales = Department(dep_id=5,dep_name='销售')
market = Department(dep_id=6,dep_name='市场')
session.add(hr) #添加一个部门
session.add_all([ops,dev,qa,sales,market]) #添加多个部门
# 创建员工
fjx = Employee(
emp_id=1, emp_name='fjx',
birth_date='1999-1-1', email='fjx@tedu.cn', dep_id=1
)
zrt = Employee(
emp_id=2, emp_name='zrt',
birth_date='1998-5-4', email='zrt@qq.com', dep_id=1
)
hp = Employee(
emp_id=3, emp_name='hp',
birth_date='2000-10-1', email='gp@163.com', dep_id=2
)
mc = Employee(
emp_id=4, emp_name='mc',
birth_date='1997-8-1', email='mc@tedu.cn', dep_id=3
)
hl = Employee(
emp_id=5, emp_name='hl',
birth_date='1999-7-6', email='hl@qq.com', dep_id=4
)
session.add_all([fjx, zrt, hp, mc, hl])
查询时,将类作为参数,返回的是实例集合
qset1 = session.query(Department)
print(qset1) # 此时qset1只是一条sql语句
for dep in qset1:
print(dep.dep_id, dep.dep_name)
# 查询时,将类变量作为参数,返回的是类变量构成的元组列表
qset2 = session.query(Employee.emp_name, Employee.email)
for data in qset2:
print(data)
# 排序
qset3 = session.query(Department).order_by(Department.dep_id)
for dep in qset3:
print(dep.dep_id, dep.dep_name)
# 使用where条件
qset4 = session.query(Department).filter(Department.dep_id>1)
print(qset4)
for dep in qset4:
print(dep.dep_id, dep.dep_name)
#
# filter可以支持多次调用
qset5 = session.query(Department).filter(Department.dep_id>1).filter(Department.dep_id<4)
print(qset5)
for dep in qset5:
print(dep.dep_id, dep.dep_name)
# #################################################3
qset6 = session.query(Employee).filter(Employee.email.like('%tedu.cn'))
for emp in qset6:
print(emp.emp_name, emp.email)
qset7 = session.query(Department).filter(Department.dep_id.in_([1, 3, 5]))
for dep in qset7:
print(dep.dep_id, dep.dep_name)
# 找到部门ID不是1、3、5的部门
qset8 = session.query(Department).filter(~Department.dep_id.in_([1, 3, 5]))
for dep in qset8:
print(dep.dep_id, dep.dep_name)
#
# 取出的结果,可以不使用for循环,通过all方法也能取出全部数据
qset9 = session.query(Department.dep_id, Department.dep_name).filter(~Department.dep_id.in_([1, 3, 5]))
print(qset9.all())
first方法返回all结果中的第一项
qset9 = session.query(Department.dep_id, Department.dep_name).filter(~Department.dep_id.in_([1, 3, 5]))
print(qset9.first())
#
多表查询,query中先写Employee.emp_name,join时写Department
多表查询,query中先写Department.dep_name,join时写Employee
qset10 = session.query(Employee.emp_name, Department.dep_name).join(Department)
print(qset10.all())
#
update时,只要找到相应的实例,重新赋值即可
qset10 = session.query(Department).filter(Department.dep_name=='人事部')
hr = qset10.first()
hr.dep_name = '人力资源部'
#
# 删除时,先找到实例,再删除
qset11 = session.query(Department).filter(Department.dep_id==6)
market = qset11.first()
session.delete(market)
#确认
session.commit()
#关闭会话
session.close()
验证:
MariaDB [cloud1911]> select * from departments;
+--------+----------+
| dep_id | dep_name |
+--------+----------+
| 1 | 人事 |
| 6 | 市场 |
| 3 | 开发 |
| 4 | 测试 |
| 2 | 运维 |
| 5 | 销售 |
+--------+----------+
MariaDB [cloud1911]> select * from employees;
+--------+----------+------------+-------------+--------+
| emp_id | emp_name | birth_date | email | dep_id |
+--------+----------+------------+-------------+--------+
| 1 | fjx | 1999-01-01 | fjx@tedu.cn | 1 |
| 2 | zrt | 1998-05-04 | zrt@qq.com | 1 |
| 3 | hp | 2000-10-01 | gp@163.com | 2 |
| 4 | mc | 1997-08-01 | mc@tedu.cn | 3 |
| 5 | hl | 1999-07-06 | hl@qq.com | 4 |
+--------+----------+------------+-------------+--------+