使用Python操作Mysql数据库（进阶）

# -*- coding: utf-8 -*-


import logging
import pymysql
from rest_framework.response import Response

logger = logging.getLogger(__name__)


# 连接数据库
def get_sql():
    try:
        conn = pymysql.connect(host='localhost',
                               port=3306,
                               user='root',
                               passwd='12345678',
                               database='django_backend_master',
                               charset='utf8')
        return conn
    except Exception as error:
        print(error)
        logger.error("===error==>{0}".format(error))


# 单条数据查询
def select_sql(sql_str, *parameter):
    conn = get_sql()
    cur = conn.cursor()
    try:
        cur.execute(sql_str, *parameter)
        results = cur.fetchall()
        return results
    except Exception as e:
        print(e)
        logger.error("===error==>{0}".format(e))


# 单条数据更新
def commit_sql(sql_str, *parameter):
    """

    :param sql_str: sql语句
    :param parameter: 参数
    :return:
    """
    conn = get_sql()
    cur = conn.cursor()
    conn.begin()
    try:
        cur.execute(sql_str, *parameter)
    except Exception as e:
        print(e)
        logger.error("===error==>{0}".format(e))
        print('事务回滚')
        conn.rollback()
    else:
        print('事务提交')
        conn.commit()
    cur.close()
    conn.close()
    return 200


# 批量数据更新
def sql_executemany(sql_str, *parameter):
    """
    正确保存sql语句
    :param sql_str:
    :param sql_info:
    :return:
    """
    conn = get_sql()
    cur = conn.cursor()
    conn.begin()
    # with get_sql() as conn:
    #     with conn.cursor() as cur:
    try:
        cur.executemany(sql_str, *parameter)
    except Exception as e:
        print(e)
        logger.error("===error==>{0}".format(e))
        print('事务回滚')
        conn.rollback()
    else:
        print('事务提交')
        conn.commit()
    cur.close()
    conn.close()
    return 200


if __name__ == '__main__':
    import datetime
    now = datetime.datetime.now()
    ######################################################
    """新增"""
    sql_tup = (str(now), str(now), 'PHP', 0)
    # cur.execute(sql_str)一个参数的写法
    insert_sql = f"""
                insert into blog_tag (update_datetime, create_datetime, tag_name, articles_count)
                values {sql_tup}
            """
    commit_sql(insert_sql)
    # ============================= #
    # cur.execute(sql_str, *parameter) 两个参数的写法
    sql_tup = (str(now), str(now), 'PHP1', 0)
    insert_sql = f"""
                insert into blog_tag (update_datetime, create_datetime, tag_name, articles_count)
                values (%s,%s,%s,%s)
            """
    commit_sql(insert_sql, sql_tup)
    # ============================= #
    # 批量新增  列表里面是元组，每个元组是新增的数据
    sql_list = [(str(now), str(now), 'Django', 0)]
    insert_sql = f"""
                    insert into blog_tag (update_datetime, create_datetime, tag_name, articles_count)
                    values (%s,%s,%s,%s)
                """
    sql_executemany(insert_sql, sql_list)
    ######################################################
    """更新"""
    update_list = ['vue333', 3]
    update_sql = f"""
        update blog_tag set tag_name=%s where id=%s
        """
    commit_sql(update_sql, update_list)
    # ============================= #
    update_list = [('vue222', 3)]
    update_sql = f"""
        update blog_tag set tag_name=(%s) where id=(%s)
    """
    sql_executemany(update_sql, update_list)
    ######################################################
    """删除"""
    delete_tup = ('PHP1', 23)
    delete_sql = f"""
        delete from blog_tag where tag_name=%s and id=%s
    """
    commit_sql(delete_sql, delete_tup)
    delete_list = [('Go', 21)]
    delete_sql = f"""
        delete from blog_tag where tag_name=%s and id=%s 
    """
    sql_executemany(delete_sql, delete_list)