关于mysql:Python中MySQL用法

查看零碎版本：
- arch 命令，查看零碎是 64 位还是 32 位
- 应用 cat /etc/system-release 查看内核版本
留神装置 MySQL 的版本
- 企业版：付费
- 社区版：收费
- MariaDB
留神装置之后防止 yum 自动更新版本
留神数据库的安全性

(一)、字符集设置

字符集分类：
- 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 语言：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：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 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()

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

关于mysql:Python中MySQL用法

Python 中 MySQL 用法

一、注意事项

二、字符集设置及 mysql 命令

(一)、字符集设置

(二)、mysql 常用命令

三、Python 连贯 MySQL 的 API

四、pymysql 操作

(一)、查问数据

(二)、插入数据

(三)、更新数据

(四)、删除数据

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

五、sqlalchemy 操作

(一)、sqlalchemy core 的形式创立数据表

(二)、sqlalchemy orm 的形式创立表

(三)、增删改查操作

六、SQL 基础知识