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的扩展)：sqlalchemypip 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来代替
事务执行同步，要么全执行，要么不执行
事务的特性 —— ACIDA：原子性(Atomicity)
C：一致性(Consistency)
I：隔离性(Idolation)
D：持久性(Durability)
事务的隔离级别读未提交：允许读到未提交的数据，级别最低，无法用在高并发场景
读已提交：只能读到已经提交的数据
可重复性：同一事务在相同的查询条件下两次查询的结果一致， 默认事务级别
可串行化：事务进行串行化，排队执行，牺牲了并发性能，级别最高> MySQL中默认自动提交查询自动提交设置：show variables like “autocommit”;
关闭与开启自动提交：关闭：set autocommit = 0
开启：set autocommit = 1
开启事务：BEGIN
提交：COMMIT
回滚：RALLBACK
回滚至某一个保存点：ROLLBACK TO
