python操作mysql的基础知识和一些实用性方案

目录

前言

基础知识

基本操作

实用方案

获得字段名与对应值的绑定关系

获取自增序列

根据时间拿取所需元素

mysql长连接问题

---

前言

        本来想多总结些服务器环境安装方面知识的，但现在我的主要业务与服务器这方面的联系已经没这么急迫了（像是Window/Linux的bitwarden、oracle、seafile、wordpress、流媒体服务器等安装及服务器扩容这些、亦或是云服务器/域名/备案等相关问题），之后遇到了再往上传吧。

        话不多说，本文中将给出一些python操作mysql的基础知识和一些实用性方案。

基础知识

        像我这样比较基础的程序猿来说，对数据库的操作无非是增删改查和存储过程调用啥的，也不会考虑做什么触发器、事务、存储过程这玩意儿。毕竟给厨子的食材就那么多点儿，总不能一边让我做饭，一边还让我自己找锅碗瓢盆吧！￥ヾ(≧へ≦)〃 ￥

基本操作

先用cmd用以下命令安装pymysql或pycharm里直接拉

pip install pymysql

 这里我已经装好了，所以再次输入是这样的，表示已经安装

 这里给出一个建立mysql连接的语句

# 导入pymysql包
import pymysql

# 建立mysql连接
# host：主机地址，port：端口号（一般默认是3306，注意这里一定是int类型）
# user：用户名，password：密码
# database：要访问的数据库，charset：字符集（给utf8就行了）
db = pymysql.connect(host="localhost", port=3306, user="root", password="root",
                                 database='fucker', charset='utf8')

# 创建游标（读取文件的句柄），可以理解为工具人（sql执行者，交互的中间人）
cursor = db.cursor()

# 执行sql语句
sql = 'insert into player values (null ,"鸡与篮球","2.5",999999,now())'
cursor.execute(sql)

# 提交事务，执行insert、delete、update时必须提交事务否则将不会入库
db.commit()

# 游标关闭
cursor.close()

# 数据库关闭
db.close()

报错出现这玩意儿表示你服务器参数输错了，请检查连接数据库的ip、端口、用户名、密码

出现这个表示数据库名输错了，也就是database不存在

 

实用方案

        老生常谈的sql语句就不讲了，不懂sql的建议先去自学一下，这里只给出几个比较实用的知识。

获得字段名与对应值的绑定关系

        若查询语句包含较多的字段，就不方便用下标来弄了，这里给的方案是将所有 字段名和值对应关系的字典 塞入二维数组中

import pymysql

db = pymysql.connect(host="localhost", port=3306, user="root", password="root",
                                 database='fucker', charset='utf8')
cursor = db.cursor()

# 执行sql语句
sql = 'select * from player'
cursor.execute(sql)

# 查询全部
results = cursor.fetchall()
print(results)

# 获取表结构
description = cursor.description
print(description)

# 获取字段名列表
fields = [field[0] for field in cursor.description]
print(fields)

# 序列化 将字段名和值的对应关系构建为zip对象后转为字典
# 再将所有 字段名和值对应关系的字典 塞入二维数组中
res = [dict(zip(fields, result)) for result in results]
print(res)

# 这里给出返回示例
# [{'id': 1, 'name': 'father', 'age': 188, 'money': Decimal('98761354.55'), 'create_time': datetime.datetime(2022, 11, 17, 10, 18, 27)}]

cursor.close()
db.close()

获取自增序列

        本人在建表时还是偏向设置id为自增，而不是采取时间戳（虽然概率很小，但我总感觉用时间戳还是有撞车的嫌疑）。那么问题就来了，怎么拿到这条新建数据的id呢？这里不废话直接上代码

import pymysql

db = pymysql.connect(host="localhost", port=3306, user="root", password="root",
                                 database='fucker', charset='utf8')
cursor = db.cursor()

# 执行sql语句
sql = 'insert into player values (null ,"小红","18",999999,now())'
cursor.execute(sql)

# 拿取自增序列
sql = 'select last_insert_id();'
cursor.execute(sql)

# 拿到的这个index就是上面insert插入的null（id）
index = cursor.fetchall()[0][0]
print(index)

# 提交上面的insert事务，注意必须在拿到自增序列后才能提交，否则自增序列将失效并返回0
db.commit()

cursor.close()
db.close()

        

根据时间拿取所需元素

        时间比较的话，与oracle相比，mysql其中的一点好处是可以直接用str类型的时间直接和datetime类型进行比较，这里只给一些sql语句

# 这里给出mysql sql语句中的date_format()方法中的格式
# %Y-%m-%d %H:%i:%S    年-月-日 时:分:秒
# 请注意python time.strftime()里面的 %M 指代的是分钟
# 而mysql date_format()里面的 %M 却表示的是 月名


# 查询前五分钟内的事件
select * from events where create_time >= SUBDATE(now(),interval 5 minute);

# 查询某日的事件
select * from events where DATE_FORMAT(create_time,'%Y-%m-%d') = '2022-12-11';

# 查询某月的事件
select * from events where DATE_FORMAT(create_time,'%Y-%m') = '2022-12';

# 查询 某日某点某分 到 某日某点某分 的事件
select * from events where create_time >= '2022-12-09 08:00' and create_time < '2022-12-11 16:00';

# 查询 某月内 各种类 的事件统计数量
select kind,count(*) from events where DATE_FORMAT(create_time,'%Y-%m') = '2022-12' group by kind;

# 查询 某月内 最后10条事件
select * from events where DATE_FORMAT(create_time,'%Y-%m') = '2022-12' order by create_time desc limit 10;

# 查询 某月内 各种类 最后一条事件
select * from events a where id in (select max(b.id) as id from events b where DATE_FORMAT(b.create_time,'%Y-%m') = '2022-12' group by b.kind);

mysql长连接问题

        python的数据库长连接问题带给我的感受是非常头疼的，因为没有学习到任何关于python与数据库长连接的知识，也没人教。所以根据网上的只言片语，我只得到了可以使用 异常+db.ping() 的办法来验证数据库连接是否存活，但是一用到生产环境上就会出现各种奇怪的问题！目前鄙人django都还是用的是短链接。。也不知道怎么解决

import time
import pymysql

# 写一个长连接保持方法，若断连将再次连接
def conn_keep(db, cursor):
    if db:
        try:
            db.ping()
            return db, cursor
        except:
            pass

    db = pymysql.connect(host="localhost", port=3306, user="root", password="root",
                         database='snms', charset='utf8')
    cursor = db.cursor()
    return db, cursor

if __name__ == '__main__':
    db = ''
    cursor = ''
    while True:
        db,cursor = conn_keep(db,cursor)

        sql = 'select * from events'
        cursor.execute(sql)
        results = cursor.fetchall()

        # 数据库没断的话，results将永远为第一次采集到的值
        print(results)

        # 解决办法也很简单，提交一次事务就能更新
        db.commit()

        time.sleep(30)

这个鬼办法其实也行，但是我总感觉每commit一下就多一分风险，如果有大佬不小心瞥到鄙文时，麻烦私信教一下，嘿嘿 (￣▽￣)" 三克油