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
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。