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

Python中的MySQL用法

Python中的MySQL用法

一、注意事项

二、字符集设置及mysql命令

(一)、字符集设置

(二)、mysql常用命令

三、Python连接MySQL的API

四、pymysql操作

(一)、查询数据

(二)、插入数据

(三)、更新数据

(四)、删除数据

(五)、pymysql.connect(参数)参数的传入方式

五、sqlalchemy操作

(一)、sqlalchemy core的方式创建数据表

(二)、sqlalchemy orm的方式创建表

(三)、增删改查操作

六、SQL基础知识

苦心僧

引用和评论

Redis系列之SDS

如何实现一个楼中楼的评论系统

在Java程序中监听mysql的binlog

将 Python 和 Rust 融合在一起，为 pyQuil® 4.0 带来和谐

Mysql数据库sql优化案例

Ubuntu20.04 从源代码编译安装 python3.10

MySQL 亿级数据平滑迁移实战