MySQL（七）——Python操作MySQL基础使用-Toy模板网

这篇具有很好参考价值的文章主要介绍了MySQL（七）——Python操作MySQL基础使用。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

上期文章

MySQL（六）——事务

数据库介绍

数据库是指数据存储的库，作用就是组织数据并存储数据

按照库->表->数据三个层级进行组织

数据库（软件）提供数据组织存储的能力

SQL语句则是操作数据、数据库的工作语言

MySQL入门使用

打开命令提示符程序，输入mysql -u root -p

show databases 查看有哪些数据库
use 数据库名使用某个数据库
show tables 查看数据库内有哪些表
exit 退出MySQL的命令行环境

SQL基础

SQL语言分类

SQL：Structured Query Language，结构化查询语言，用于访问和处理数据库的标准的计算机语言

数据定义 DDL Data Definition Language：库的创建删除、表的创建删除等
数据操纵 DML Data Manipulation Language：新增数据、删除数据、修改数据等
数据控制 DCL Data Control Language：新增用户、删除用户、密码修改、权限管理等
数据查询 DQL Data Query Language：基于需求查询和计算数据

SQL语法特征

SQL语言大小写不敏感
SQL可以单行或多行书写，最后以;号结束
单行注释：-- 注释内容（–后面一定要有一个空格）
单行注释：# 注释内容（#后面可以不加空格，推荐加上）
多行注释：/* 注释内容 */

DDL

-- 查看数据库
SHOW DATABASES;
-- 使用数据库
USE 数据库名称;
-- 创建数据库 字符集UTF8
CREATE DATABASE 数据库名称 [CHARSET UTF8];
-- 删除数据库
DROP DATABASE 数据库名称;
-- 查看当前使用的数据库
SELECT DATABASE();
-- 查看有哪些表
SHOW TABLES;
-- 删除表
DROP TABLE 表名称;
DROP TABLE IF EXISTS 表名称;
-- 创建表
CREATE TABLE 表名称(
	列名称 列类型,
	列名称 列类型,
	......
);

列类型有
int：整数
float：浮点数
varchar(长度)：文本，长度为数字，做最大长度限制
date：日期类型
timestamp：时间戳类型

DML

/*数据添加*/
INSERT INTO 表[(列1, 列2, ..., 列N)] VALUES(值1, 值2,..., 值N)[,(值1, 值2,..., 值N), ..., (值1, 值2,..., 值N)]
/*数据删除*/
DELETE FROM 表名称 [WHERE 条件判断];
/*数据更新*/
UPDATE 表名 SET 列=值 [WHERE 条件判断];

DQL

/*基础数据查询*/
SELECT 字段列表|* FROM 表;
/*过滤查询的语法*/
SELECT 字段列表|* FROM 表 WHERE 条件判断;
/*分组聚合*/
SELECT 字段|聚合函数 FROM 表 [WHERE 条件] GROUP BY 列
/*结果排序*/
SELECT 列|聚合函数|* FROM 表
WHERE ...
GROUP BY ...
ORDER BY ...[ASC|DESC]
/*结果分页限制*/
SELECT 列|聚合函数|* FROM 表
WHERE ...
GROUP BY ...
ORDER BY ...[ASC|DESC]
LIMIT n[, m]

聚合函数：

SUM 列求和
AVG 列求平均值
MIN 列求最小值
MAX 列求最大值
COUNT 列|* 求数量

注意：GROUP BY出现哪个列，哪个列才能出现在SELECT中的非聚合中

执行顺序：FROM->WHERE->GROUP BY和聚合函数->SELECT->ORDER BY->LIMIT

Python & MySQL

基础使用

创建到MySQL的数据库链接

from pymysql import Connection
# 获取到MySQL数据库的链接对象
conn = Connection(
    host='localhost',   # 主机名/IP地址
    port=3306,  # 端口，默认3306
    user='root',    # 账户名
    password='SYQ1338751!'  # 密码
)
# 打印MySQL数据库软件信息
# print(conn.get_server_info())

# 获取游标对象
cursor = conn.cursor()

# 先选择数据库
conn.select_db('test') 
 
# 使用游标对象，执行非查询性质的sql语句
cursor.execute('create table test_pymysql(id int, info varchar(255))')

# 使用游标对象，执行查询性质的sql语句
cursor.execute('select * from student')
# 获取查询结果
results: tuple = cursor.fetchall()
for r in results:\
    print(r)

# 关闭到数据库的链接
conn.close()

总结

1、如何获取链接对象？

from pymysql import Connection 导包
Connection(主机,端口,账户,密码)即可得到链接对象
链接对象.close()关闭和MySQL数据库的连接

2、如何执行SQL查询？

通过连接对象调用cursor()方法，得到游标对象

游标对象.execute()执行SQL语句
游标对象.fetchall()得到全部的查询结果封装入元组内

数据插入

pymysql在执行数据插入或其他产生数据更改的SQL语句时，默认是需要提交更改的，即需要通过代码“确认”这种更改行为

手动commit

通过链接对象.commit()即可确认此行为

from pymysql import Connection
# 获取到MySQL数据库的链接对象
conn = Connection(
    host='localhost',   # 主机名/IP地址
    port=3306,  # 端口，默认3306
    user='root',    # 账户名
    password='SYQ1338751!'  # 密码
)
# 打印MySQL数据库软件信息
# print(conn.get_server_info())

# 获取游标对象
cursor = conn.cursor()

# 先选择数据库
conn.select_db('itheima')  

# 执行sql
cursor.execute("insert into account values (3, '王五', 2000)")

# 通过commit提交确认
conn.commit()

# 关闭到数据库的链接
conn.close()

自动commit

conn = Connection(
    host='localhost',   # 主机名/IP地址
    port=3306,  # 端口，默认3306
    user='root',    # 账户名
    password='SYQ1338751!',  # 密码
    autocommit=True  # 设置自动提交
)

总结

1、什么是commit提交？
pymysql库在执行对数据库所有修改操作的行为时，是需要通过链接对象的commit成员方法来进行确认的

只有确认的修改，才能生效

2、如何自动提交呢？文章来源地址https://www.toymoban.com/news/detail-814286.html

conn = Connection(
    host='localhost',   # 主机名/IP地址
    port=3306,  # 端口，默认3306
    user='root',    # 账户名
    password='SYQ1338751!',  # 密码
    autocommit=True  # 设置自动提交
)

综合案例

from file_define import *
from data_define import *
from pyecharts.charts import Bar
from pyecharts.options import *
from pyecharts.globals import ThemeType
from pyecharts.faker import Faker
from pymysql import Connection

text_file_reader = TextFileReader('2011年1月销售数据.txt')
json_file_reader = JsonFileReader('2011年2月销售数据JSON.txt')

jan_data: list[Record] = text_file_reader.read_data()
feb_data: list[Record] = json_file_reader.read_data()
# 两个月份的数据合并为一个list存储
all_data: list[Record] = jan_data + feb_data

# 获取到MySQL数据库的链接对象
conn = Connection(
    host='localhost',   # 主机名/IP地址
    port=3306,  # 端口，默认3306
    user='root',    # 账户名
    password='SYQ1338751!',  # 密码
    autocommit=True  # 设置自动提交
)

# 获取游标对象
cursor = conn.cursor()
# 先选择数据库
conn.select_db('py_sql')
# 组织SQL语句
for record in all_data:
    sql = f"insert into orders(order_date, order_id, money, province) values ('{record.date}','{record.order_id}', {record.money}, '{record.province}')"
    # 执行SQl语句
    cursor.execute(sql)

# 关闭MySQL链接对象
conn.close()

import decimal

from file_define import *
from data_define import *
from pyecharts.charts import Bar
from pyecharts.options import *
from pyecharts.globals import ThemeType
from pyecharts.faker import Faker
from pymysql import Connection
import json

from datetime import date, datetime

from datetime import date, datetime

class ComplexEncoder(json.JSONEncoder):
    def default(self, obj):
        if isinstance(obj, datetime):
            return obj.strftime('%Y-%m-%d %H:%M:%S')
        elif isinstance(obj, date):
            return obj.strftime('%Y-%m-%d')
        else:
            return json.JSONEncoder.default(self, obj)


text_file_reader = TextFileReader('2011年1月销售数据.txt')
json_file_reader = JsonFileReader('2011年2月销售数据JSON.txt')

jan_data: list[Record] = text_file_reader.read_data()
feb_data: list[Record] = json_file_reader.read_data()
# 两个月份的数据合并为一个list存储
all_data: list[Record] = jan_data + feb_data

# 获取到MySQL数据库的链接对象
conn = Connection(
    host='localhost',   # 主机名/IP地址
    port=3306,  # 端口，默认3306
    user='root',    # 账户名
    password='SYQ1338751!',  # 密码
    autocommit=True  # 设置自动提交
)

# 获取游标对象
cursor = conn.cursor()
# 先选择数据库
conn.select_db('py_sql')
# 组织SQL语句
# for record in all_data:
#     sql = f"insert into orders(order_date, order_id, money, province) values ('{record.date}','{record.order_id}', {record.money}, '{record.province}')"
#     # 执行SQl语句
#     cursor.execute(sql)

# 使用游标对象，执行查询性质的sql语句
cursor.execute('select * from orders')
# 获取查询结果
results: tuple = cursor.fetchall()
results_list = list(results)
print(results_list)

with open('example.json.txt', 'w') as f:
    for item in results_list:
        a = {}
        a['date'] = item[0]
        a['order_id'] = item[1]
        a['money'] = item[2]
        a['province'] = item[3]

        r_json = json.dumps(a, cls=ComplexEncoder, ensure_ascii=False)
        f.write(r_json+'\n')


# 关闭MySQL链接对象
conn.close()