关于mysql:Python中MySQL用法

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 pythonimport 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 pythonimport 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 pythonimport 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 pythonimport 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.6user = liquanhui01password = liqh930215database = testdb# 留神：init文件中key对应的值不能增加引号

# dbconfig.py文件，该文件中的办法用于读取并返回字典格局的配置参数from configparser import ConfigParserfrom pathlib import Pathp = Path(__file__)cur_path = p.resolve().parentfile_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 pythonimport pymysqlfrom example.dbconfig import read_db_configdbserver = 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 pythonimport pymysqlfrom 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 pythonimport pymysqlfrom sqlalchemy import create_engine, Table, Column, Integer, String, Float, DateTime, ForeignKeyfrom sqlalchemy.ext.declarative import declarative_basefrom datetime import datetimeBase = 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:

# 创立sessionfrom sqlalchemy.orm import sessionmakerSessionClass = sessionmaker(bind=engine)session = SessionClass()

减少操作
- class_demo = ClassName(field=“值”, ...)
- session.add(class_demo)
- session.commit()

# 创立sessionSessionClass = 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，降序是descLIMIT 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为小表，以小表作为优先限度条件能够缩小查问的数据量，晋升查问的效率  ```- 非关联子查问：简略，内层的语句只执行一次```sqlSELECT 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