Python 灵活使用pymysql写数据库脚本

Python 灵活使用pymsql写数据库脚本

1.在配置目录下创建一个mysql.py 名字自由取名

mysql.py 代码如下

# coding=UTF-8

import pymysql
import contextlib


# 生产库
mysql_prod_config = {
    'host': '127.0.0.1',
    'port': 3306,
    'user': 'root',
    'password': '123456',
    'database': 'luffy',
    'charset': 'utf8',
    'cursorclass': pymysql.cursors.SSDictCursor,
}


# 测试库
mysql_dev_config = {
    'host': '127.0.0.1',
    'port': 3306,
    'user': 'root',
    'password': '123456',
    'database': 'luffy',
    'charset': 'utf8',
    'cursorclass': pymysql.cursors.SSDictCursor,
}


# 定义上下文管理器，连接后自动关闭连接
@contextlib.contextmanager
def MySQL(debug):
    assert debug in (True, False)

    if debug:
        cf = mysql_dev_config
    else:
        cf = mysql_prod_config

    conn = pymysql.connect(**cf)
    cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
    try:
        yield cursor, conn
    finally:
        conn.commit()
        cursor.close()
        conn.close()

2.此时我们就可以自由的写python脚本了

例如，我删除数据库中满足条件的某些记录

# coding=utf-8

import traceback
from mysql import MySQL

class OperateData(object):
    def __init__(self):
        """环境开关
        True: 测试环境
        False: 生产环境
        """
        self.debug = True
        self.src_taskid = [219]

    def operate(self):
        with MySQL(self.debug) as (cursor, conn):
            try:

                sql = "DELETE from student WHERE id in %s"
                cursor.execute(sql, (self.src_taskid, ))
                values = cursor.fetchall()
                print(values)

            except Exception as err:
                print(traceback.format_exc())
                return


if __name__ == '__main__':
    od = OperateData()
    od.operate()