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/ |
+--------------------------+----------------------------+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)配置文件修改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概念：
Python语言：Python Database API 或者DB-API其他语言：连接器、绑定、binding分类：
MySQLdb：Python2的包，适用于MySQL5.5和Python2.7Python3连接MySQL：
mysqlclient:
Python3安装MySQLdb的包叫做mysqlclient，但加载的依然是MySQLdb安装：pip3 install mysqlclient导入：import MySQLdbpymysql：pip install pymysql (流行度最高)Mysql-connector-python：pip install mysql-connector-python (MySQL官方)，推荐使用使用ORM(对原始的DB-API的扩展)：
sqlalchemy
pip install sqlalchemyDjango框架Model模型四、pymysql操作(一)、查询数据导入pymysql：import pymysql使用pymysql中的connect方法创建连接对象：参数如下
hostuserpassworddb注意：参数都是关键字参数，如果不是关键字参数，会报如下错误：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 }')
#!/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)
方式二：把参数写入一个配置文件中，创建方法读取配置文件中的对应内容，返回字典格式，再把字典格式的数据作为参数传入
配置文件# 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)
.....省略engine = create_engine("mysql+pymysql://用户名:密码@域名:端口/数据库", echo=True)
# echo=True开启终端打印模式，在生产模式下需要关闭#!/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}')使用orm方式的四个前提条件：
Base必须继承自declarative_base()使用类创建数据表的时候必须使用属性__tablename__=“表名”的方式设置表名, tablename是双下方法，md文件自动隐藏了下滑线必须包含一个或者多个Column字段（属性）必须包含一个主键导入模块
import pymysqlfrom 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"#!/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 funcquery = session(func.聚合函数名(ClassName.字段名))对查询的数据做排序操作：
升序：query.order_by(ClassName.字段名)降序：
导入desc方法：from sqlalchemy import descquery.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中默认自动提交
关闭与开启自动提交：
关闭：set autocommit = 0开启：set autocommit = 1开启事务：BEGIN提交：COMMIT回滚：RALLBACK回滚至某一个保存点：ROLLBACK TO 特别申明：本文内容来源网络，版权归原作者所有，如有侵权请立即与我们联系（cy198701067573@163.com），我们将及时处理。
上一篇： MySQL问题
下一篇： Mysql存储引擎选择
Tags 标签
加个好友，技术交流
