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

# 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