关于pymysql增删改查的基础模板，拿走直接套用

关注

原创最新推荐文章于 2026-08-07 09:26:58 发布 1.5k 阅读 AI 写同款· GEO 优化 ›

【全栈开发】----用pymysql库连接MySQL，批量存入 PyMySQL` 是一个纯 Python 实现的 MySQL 客户端库，允许开发者通过 Python 程序与 MySQL 数据库进行交互。它遵循 PEP 249（Python 数据库 API 规范 v2.0），提供了与 MySQL 数据库连接、查询和管理的功能。`PyMySQL` 的一大优势在于其不依赖外部 C 库或 MySQL 客户端库，这使得安装和部署更加简便，同时也增强了跨平台兼容性。该库支持多种字符集和编码，并且能够处理复杂的 SQL 操作，如事务、游标和参数化查询等。阅读详情

# encoding: utf-8
"""
@time: 2021/12/15 15:57
"""
import pymysql

db = pymysql.connect(host="localhost", user="root", port=3306, db="spiders")
cursor = db.cursor()


def create_data():
    cursor.execute("SELECT VERSION()")
    data = cursor.fetchone()
    print("data", data)
    cursor.execute("CREATE DATABASE spiders DEFAULT CHARACTER set utf8mb4")
    db.close()


def create_table():
    sql = 'create table if not exists students (id varchar(255) not null,name varchar(255) not null,age int not null, primary key (id))'
    cursor.execute(sql)
    print("crate table")
    db.close()


data = {
    'id': '20120001',
    'name': 'Bob',
    'age': 21
}


def insert_data():
    table = "students"
    keys = ",".join(data.keys())
    values = ','.join(['%s'] * len(data))
    sql = "insert into {table}({keys}) values ({values})".format(table=table, keys=keys, values=values)
    try:
        if cursor.execute(sql, tuple(data.values())):
            print("ok")
            db.commit()
    except:
        print("no")
        db.rollback()
    finally:
        db.close()


def update_data():
    table = "students"
    keys = ",".join(data.keys())
    values = ','.join(['%s'] * len(data))
    sql = "update students set age = %s where name = %s"
    try:
        if cursor.execute(sql, (25, "Bob")):
            print("ok")
            db.commit()
    except Exception as e:
        print("no", e)
        db.rollback()
    finally:
        db.close()


def insert_or_update_data():
    """
    1：ON DUPLICATE KEY UPDATE需要有在INSERT语句中有存在主键或者唯一索引的列，并且对应的数据已经在表中才会执行更新操作。
    而且如果要更新的字段是主键或者唯一索引，不能和表中已有的数据重复，否则插入更新都失败。

    2：不管是更新还是增加语句都不允许将主键或者唯一索引的对应字段的数据变成表中已经存在的数据。
    :return:
    """
    table = "students"
    keys = ",".join(data.keys())
    values = ','.join(['%s'] * len(data))
    insert_sql = "insert into {table}({keys}) value ({values}) on duplicate key update ".format(table=table, keys=keys,
                                                                                                values=values)
    update_sql = ",".join(["{key} = %s".format(key=key) for key in data])
    sql = insert_sql + update_sql
    # insert into students(id,name,age) value (%s,%s,%s) on duplicate key update id = %s,name = %s,age = %s
    try:
        if cursor.execute(sql, tuple(data.values()) * 2):
            print("ok")
            db.commit()
    except Exception as e:
        print("no", e)
        db.rollback()
    finally:
        db.close()


def delete_data():
    table = "students"
    condition = "age > 20"

    sql = "delete from {table} where {condition}".format(table=table, condition=condition)
    try:
        if cursor.execute(sql):
            print("ok")
            db.commit()
    except Exception as e:
        print("no", e)
        db.rollback()
    finally:
        db.close()


def query_data():
    table = "students"
    condition = "age >= 20"

    sql = "select * from {table} where {condition}".format(table=table, condition=condition)
    try:
        if cursor.execute(sql):
            print("ok")
            print('Count:', cursor.rowcount)
            one = cursor.fetchone()  # 查询会导致指针偏移
            print("one", one)
            results = cursor.fetchall()
            print("results", results)
            for row in results:
                print("row", row)
    except Exception as e:
        print("no", e)
    finally:
        db.close()


if __name__ == '__main__':
    # create_data()
    # create_table()
    # insert_data()
    # update_data()
    # insert_or_update_data()
    # delete_data()
    query_data()
    pass