共计 11496 个字符，预计需要花费 29 分钟才能阅读完成。
Python 中 MySQL 用法
一、注意事项
-
查看零碎版本：
- arch 命令，查看零碎是 64 位还是 32 位
- 应用 cat /etc/system-release 查看内核版本
-
留神装置 MySQL 的版本
- 企业版：付费
- 社区版：收费
- MariaDB
- 留神装置之后防止 yum 自动更新版本
- 留神数据库的安全性
二、字符集设置及 mysql 命令
(一)、字符集设置
-
字符集分类：
- ASCII：占用一个字节
- LATIN：对 ASCII 做了扩大
- GBK：占用一个或两个字节，windows 默认的字符集
- utf8：占用 3 个字节，像 emoje 等占用四个字节的数据无奈存储
- UTF-8：占用四个字节，在 mysql 中写法位 utf8mb4
- 查看字符集：show variables like‘%character%’;
mysql> show variables like '%character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+- 查看校对规定：show variables like‘collation_%’;
mysql> show variables like 'collation_%';
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database | utf8mb4_0900_ai_ci |
| collation_server | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
3 rows in set (0.01 sec)_ci 示意大小写不敏感，_cs 示意大小写敏感
-
配置文件批改 mysql 字符集
- 命令：vim /etc/my.cnf
[client-server] default_character_set = utf8mb4 [mysql] default_character_set = utf8mb4 #interactive_timeout = 28800 #针对交互连贯的超时工夫 #wait_timeout = 28800 #针对非交互连贯的超时工夫 #max_connections = 1000 #MySQL 的最大连接数 #character_set_server = utf8mb4 #MySQL 的字符集设置 #init_connect = 'SET NAMES utf8mb4' # 服务器为每个连贯的客户端执行的字符串 #character_set_client_handshake = False #collation_server = utf8mb4_unicode_ci
(二)、mysql 常用命令
- 启动服务：systemctl start mysqld.service
- 查看服务状态：systemctl status mysqld.service
- 敞开服务：systemctl stop mysqld.service
- 重启服务：systemctl restart mysqld.service
- 登陆 mysql：mysql -u 用户 -p 明码
- 更改用户明码：ALTER USER‘用户名’@‘localhost’IDENTIFIED BY‘新密码’
三、Python 连贯 MySQL 的 API
-
概念：
- Python 语言：Python Database API 或者 DB-API
- 其余语言：连接器、绑定、binding
-
分类：
- MySQLdb：Python2 的包，实用于 MySQL5.5 和 Python2.7
-
Python3 连贯 MySQL：
-
mysqlclient:
- Python3 装置 MySQLdb 的包叫做 mysqlclient，但加载的仍然是 MySQLdb
- 装置：pip3 install mysqlclient
- 导入：import MySQLdb
- pymysql：pip install pymysql (风行度最高)
- Mysql-connector-python：pip install mysql-connector-python (MySQL 官网)，举荐应用
-
-
应用 ORM(对原始的 DB-API 的扩大)：
-
sqlalchemy
- pip install sqlalchemy
- Django 框架 Model 模型
-
四、pymysql 操作
(一)、查问数据
- 导入 pymysql：import pymysql
-
应用 pymysql 中的 connect 办法创立连贯对象：参数如下
- host
- user
- password
- db
留神：参数都是关键字参数，如果不是关键字参数，会报如下谬误：TypeError: __init__() takes 1 positional argument but 5 were given
- 通过调用连贯创立游标对象 cursor
- 调用 cursor.execute 办法执行 sql 语句
- cursor.fetchone() 获取一条数据，fetchall() 获取多条数据
#!/usr/bin/env python
import pymysql
# 关上数据库连贯
db = pymysql.connect(host="47.98.222.6", user="liquanhui01", password="liqh930215", db="testdb")
try:
# 应用 cursor 办法创立一个游标对象
with db.cursor() as cursor:
sql = 'SELECT * FROM book'
cursor.execute(sql)
books = cursor.fetchall()
for book in books:
print(book)
db.commit()
except Exception as e:
print(f'fetch error {e}')
finally:
db.close()
print(f'Database version : { result}')
(二)、插入数据
- 插入单行数据：value = (数据字读内容)，执行办法是：execute(sql, value)
- 插入多行数据：values = ((数据字段内容 1), (数据字段内容 2), (数据字段内容 3)……)，执行办法是：executemany(sql, values)
#!/usr/bin/env python
import pymysql
# 关上数据库连贯
db = pymysql.connect(host="47.98.222.6", user="liquanhui01", password="liqh930215", db="testdb")
try:
# 应用 cursor 办法创立一个游标对象
with db.cursor() as cursor:
sql = '''INSERT INTO book (id, name, price) VALUES (%s, %s, %s)''' # 全副用 s，无论字段类型
value = (1, "平庸的世界", 23.0) # 传入的值
cursor.execute(sql, value)
db.commit() # 在 pymysql 中必须应用 commit()
except Exception as e:
print(f'fetch error {e}')
finally:
db.close()
print(cursor.rowcount) # rowcount 写入的数据的行数 (作用的行数，不是表中的总行数)(三)、更新数据
#!/usr/bin/env python
import pymysql
# 关上数据库连贯
db = pymysql.connect(host="47.98.222.6", user="liquanhui01", password="liqh930215", db="testdb")
try:
# 应用 cursor 办法创立一个游标对象
with db.cursor() as cursor:
sql = '''UPDATE book SET name = %s WHERE id = %s'''
value = ("巴黎圣母院", 1)
cursor.execute(sql, value)
db.commit()
except Exception as e:
print(f'fetch error {e}')
finally:
db.close()
print(cursor.rowcount)(四)、删除数据
#!/usr/bin/env python
import pymysql
# 关上数据库连贯
db = pymysql.connect(host="47.98.222.6", user="liquanhui01", password="liqh930215", db="testdb")
try:
# 应用 cursor 办法创立一个游标对象
with db.cursor() as cursor:
sql = '''DELETE FROM book WHERE name = %s'''
value = ("巴黎圣母院")
cursor.execute(sql, value)
db.commit()
except Exception as e:
print(f'fetch error {e}')
finally:
db.close()
print(cursor.rowcount)
(五)、pymysql.connect(参数) 参数的传入形式
- 形式一：间接应用关键字参数，传入对应的内容
-
形式二：把参数写入一个配置文件中，创立办法读取配置文件中的对应内容，返回字典格局，再把字典格局的数据作为参数传入
- 配置文件
# config.ini 文件
[mysql]
host = 47.98.222.6
user = liquanhui01
password = liqh930215
database = testdb
# 留神：init 文件中 key 对应的值不能增加引号 # dbconfig.py 文件，该文件中的办法用于读取并返回字典格局的配置参数
from configparser import ConfigParser
from pathlib import Path
p = Path(__file__)
cur_path = p.resolve().parent
file_path = cur_path.joinpath("config.ini")
def read_db_config(filename=file_path, section="mysql"):
# section 规定传入哪一部分的内容
# 实例化配置文件解析类，调用该类的 read 办法获取 section 对应的文件内容
parser = ConfigParser()
parser.read(filename)
if parser.has_section(section):
items = parser.items(section)
print(items)
else:
raise Exception('{0} not found in the {1} file'.format(section, filename))
return dict(items)
if __name__ == "__amin__":
read_db_config()
# pymysql_cnn.py
#!/usr/bin/env python
import pymysql
from example.dbconfig import read_db_config
dbserver = read_db_config()
print(dbserver)
# 关上数据库连贯
db = pymysql.connect(**dbserver)
..... 省略 五、sqlalchemy 操作
(一)、sqlalchemy core 的形式创立数据表
- 导入 pymysql
- 从 sqlalchemy 中导入 create_engine，Table, Column 以及其余的字段信息
- 应用 create_engine 创立引擎，办法外部为字符串。格局为：
engine = create_engine("mysql+pymysql:// 用户名: 明码 @域名: 端口 / 数据库", echo=True)
# echo=True 开启终端打印模式，在生产模式下须要敞开 - 创立元数据：元数据是对数据库的形容信息，metadata = MetaData(engine)
- 创立表：变量名 = Table(‘表名’, metadata, 字段信息)
- 执行 metadata.create_all() 执行创立表的命令，留神设置异样解决
#!/usr/bin/env python
import pymysql
from sqlalchemy import create_engine, Table, Column, Integer, String, ForeignKey, Float, MetaData, DateTime
# 创立引擎
engine = create_engine("mysql+pymysql://liquanhui01:liqh930215@47.98.222.6:3306/testdb", echo=True)
# 创立元数据
metadata = MetaData(engine)
# 创立表
book_table = Table('book', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(100), nullable=False),
Column('desc', String(255), nullable=True),
Column('price', Float, nullable=False)
)
author_table = Table('author', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(30), nullable=False),
Column('book_id', ForeignKey('book.id'), None)
)
try:
metadata.create_all()
except Exception as e:
print(f'create error {e}')(二)、sqlalchemy orm 的形式创立表
-
应用 orm 形式的四个前提条件：
- Base 必须继承自 declarative_base()
- 应用类创立数据表的时候必须应用属性__tablename__=“表名”的形式设置表名, tablename 是双下办法，md 文件自动隐藏了下滑线
- 必须蕴含一个或者多个 Column 字段（属性）
- 必须蕴含一个主键
-
导入模块
- import pymysql
- from sqlalchemy import create_engine, Table, Column, 字段类型
- from sqlalchemy.ext.declarative import declarative_base
- 创立 Base：Base = declarative_base()
- 创立类，继承自 Base，在类中增加__basename__和其余 Column 属性
- 创立 dbUrl，格局如下：
dbUrl = "mysql+pymysql://liquanhui01:liqh930215@47.98.222.6:3306/testdb?charset=utf8mb4"- 创立引擎：engine = create_engine(dbUrl, echo=True, encoding=“uff-8”)
- 创立数据表：Base.metadata.create_all(engine)
#!/usr/bin/env python
import pymysql
from sqlalchemy import create_engine, Table, Column, Integer, String, Float, DateTime, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime
Base = declarative_base()
class Category(Base):
__tablename__ = "category_rom"
id = Column(Integer(), primary_key=True, autoincrement=True)
category_name = Column(String(50), index=True)
class Products(Base):
__tablename__ = "product_rom"
id = Column(Integer(), primary_key=True, autoincrement=True)
product_name = Column(String(50), index=True)
category = Column(Integer, ForeignKey('category_rom.id', ondelete="SET NULL"))
create_on = Column(DateTime(), default=datetime.now)
update_on = Column(DateTime(), default=datetime.now, onupdate=datetime.now)
dburl = "mysql+pymysql://liquanhui01:liqh930215@47.98.222.6:3306/testdb?charset=utf8mb4"
engine = create_engine(dburl, echo=True, encoding="utf-8")
Base.metadata.create_all(engine)(三)、增删改查操作
-
创立 session 对象
- 增删改查操作都是基于 session 进行的
- 导入：from sqlalchemy.orm import sessionmaker
- 创立 session:
# 创立 session
from sqlalchemy.orm import sessionmaker
SessionClass = sessionmaker(bind=engine)
session = SessionClass()-
减少操作
- class_demo = ClassName(field=“值”, …)
- session.add(class_demo)
- session.commit()
# 创立 session
SessionClass = sessionmaker(bind=engine)
session = SessionClass()
# 增加老师数据
teacher_demo = Teacher(name="李贞贞")
session.add(teacher_demo)
# 增加学生数据
students = ["拂晓", "麻织", "杨志", "冉阿让"]
for student in students:
student_demo = Students(name=student)
session.add(student_demo)
session.commit()-
查问操作
-
获取数据集：
- 查问数据表中的全副字段：应用 query = session.query(ClassName) 获取数据集，相当于 select *
- 查问一部分字段：应用 query = session.query(ClassName. 字段名, ClassName. 字段名, …)
-
应用聚合函数获取查问集：
- 导入 func：from sqlalchemy import func
- query = session(func. 聚合函数名 (ClassName. 字段名))
-
对查问的数据做排序操作：
- 升序：query.order_by(ClassName. 字段名)
-
降序：
- 导入 desc 办法：from sqlalchemy import desc
- query.order_by(desc(ClassName. 字段名))
-
对查问的数据集做过滤操作
- 间接写字段条件：query.filter(ClassName. 字段 + 条件, ClassName. 字段 + 条件, …)
-
应用与、或、非
- 导入：from sqlalchemy import and_, or_, not_, 留神 and、or 和 not 后都有一个下滑线，md 文件暗藏了
- query.filter(or_/and_/not_(ClassName. 字段名 + 条件, ClassName. 字段名 + 条件, ClassName. 字段名 + 条件, ClassName. 字段名 + 条件….))
- 获取全副数据：query.all()，再通过循环取出
- 获取单个数据：query.first() 获取，或者通过 query.one()、query.scale() 获取。举荐应用 first()
- session.commit()
-
data = session.query(Teacher).all()
data = session.query(Students).first()
for result in session.query(Students):
print(result.all())
data = session.query(Students.id, Students.name).order_by(desc(Students.id))
for student in data:
print(student)
query = session.query(Students.id, Students.name).order_by(desc(Students.id)).limit(3)
print([student.name for student in query])
query = session.query(func.count(Students.name)).first()
query = session.query(Students).filter(Students.id > 2, Students.id < 5)
print([[student.id, student.name] for student in query])
query = session.query(Students).filter(or_(Students.id.between(2, 4),
Students.name.contains("黎")
))
print([student.name for student in query])
session.commit-
更新数据
- 获取指定数据的数据集：query = session.query(ClassName).filter(ClassName. 字段 == 值)
- 更新数据：query.update({ClassName. 字段名: value, …})
- session.commit()
query = session.query(Students).filter(Students.id == 3)
query.update({Students.name: "管仲"})
print(query.first())
session.commit()-
删除数据
- 获取指定数据的数据集：query = session.query(ClassName).filter(ClassName. 字段 == 值)
-
删除数据：
- 形式一：session.delete(query.one())
- 形式二：query.delete()
- session.commit()
六、SQL 基础知识
- select 查问时关键字的书写程序：SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT
须要留神的是：
- 生产环境下因为列数很多，个别禁用 SELECT *
- WHERE 字段为防止全表扫描，个别须要减少索引
- select 查问时的执行程序：
SELECT DISTINCT player_id, player_name, count(*) as num -- 第五步：从虚构表中查问出 player_id, player_name, count(*) 数据
FROM player JOIN team ON player.team_id = team.team_id -- 第一步：从两个表中查问出 player.team_id = team.team_id 的数据生成一张新的虚构表
WHERE height > 1.80 -- 第二步：从虚构表中查问出 height>1.80 的数据生成新的虚构表
GROUP BY player.team_id -- 第三步：以 player.team_id 为条件进行分组
HAVING num > 2 -- 第四步：筛选 num > 2 的数据
ORDER BY num DESC -- 第六步：依据 num 的值进行降序排列，升序是 asc，降序是 desc
LIMIT 2 -- 第七步：取出前两条数据，查问步骤完结
/*
WHERE 作用于字段，即每一行数据
HAVING 作用于 GROUP BY，所有在 GROUP BY 分组之后如果还要再进行筛选，必须应用 HAVING，不能应用 WHERE
*/-
SQL 函数：
- 算数函数
- 字符串函数
- 日期函数
- 转换函数
-
聚合函数
- COUNT() 行数
- MAX() 最大值
- MIN() 最小值
- SUM() 求和
- AVG() 平均值
留神：聚合函数疏忽空行
-
子查问
- 概念：须要从查问后果中集中再次进行查问，才能够失去想要的后果，一次无奈失去后果
-
分类
-
关联子查问：简单，嵌套查问，须要应用 EXIST 或 IN 关键字
- 当内层查问表为小表，外层查问表为大表时应用 IN 关键字
SELECT * FROM TABLE_A WHERE condition IN (SELECT condition FROM TABLE_B) -- A 为大表，B 为小表，以小表作为优先限度条件能够缩小查问的数据量，晋升查问的效率
-
- 当内层查问表为大表，外层查问表为小表时应用 EXIST 关键字
```sql
SELECT * FROM TABLE_A WHERE EXIST (SELECT condition FROM TABLE_B WHERE B.condition = A.condition)
-- B 为大表，A 为小表，以小表作为优先限度条件能够缩小查问的数据量，晋升查问的效率
```
- 非关联子查问：简略，内层的语句只执行一次
```sql
SELECT COUNT(*), n_star FROM t1 GROUP BY n_star HAVING n_star > (SEECT avg(n_star) FROM t1) ORDER BY n_star DESC;
```
-
罕用的连贯 (JOIN)
- 天然连贯：inner join 外部连贯，获取两个表的公共局部
- ON 连贯
- USING 连贯
-
外连贯
- 左外连贯：leftjoin—> 只获取左表中两个表的公共局部数据
- 右外连贯：right join —> 只获取右表中两个表的公共局部数据
- 全外连贯 (MySQL 不反对)：获取两个表中的全副数据，因为在 MySQL 中的不反对，能够应用 union 来代替
-
事务
- 执行同步，要么全执行，要么不执行
-
事务的个性 —— ACID
- A：原子性 (Atomicity)
- C：一致性 (Consistency)
- I：隔离性 (Idolation)
- D：持久性 (Durability)
-
事务的隔离级别
- 读未提交：容许读到未提交的数据，级别最低，无奈用在高并发场景
- 读已提交：只能读到曾经提交的数据
- 可重复性：同一事务在雷同的查问条件下两次查问的后果统一，默认事务级别
- 可串行化：事务进行串行化，排队执行，就义了并发性能，级别最高
> MySQL 中默认主动提交
- 查问主动提交设置：show variables like“autocommit”;
-
敞开与开启主动提交：
- 敞开：set autocommit = 0
- 开启：set autocommit = 1
- 开启事务：BEGIN
- 提交：COMMIT
- 回滚：RALLBACK
- 回滚至某一个保留点：ROLLBACK TO
正文完
