pymysql进行数据库操作

类块
引用了这些

https://www.cnblogs.com/zhml/p/12524403.html这里是引用

# -*- coding =utf-8 -*-
# @Time : 2021/2/2 18:29
# @Author :Mr
# @File :MysqlRead.py
# @Software :PyCharm
import pymysql
import logging
import sys
from db_config import db_config

# 加入日志
# 获取logger实例
logger = logging.getLogger("baseSpider")
# 指定输出格式
formatter = logging.Formatter('%(asctime)s\
              %(levelname)-8s:%(message)s')
# 文件日志
file_handler = logging.FileHandler("operation_database.log")
file_handler.setFormatter(formatter)
# 控制台日志
console_handler = logging.StreamHandler(sys.stdout)
console_handler.setFormatter(formatter)

# 为logge添加具体的日志处理器
logger.addHandler(file_handler)
logger.addHandler(console_handler)

logger.setLevel(logging.INFO)


class MySQL:
    # 构造函数,初始化数据库连接
    def __init__(self, configfile):
        self.db_config = db_config(configfile)
        # self.sql = sql
        # self.params = params
        self.conn = None
        self.cur = None

    # 初始化数据库连接
    def connDB(self):
        try:
            self.conn = pymysql.connect(host=self.db_config['host'],
                                        port=3306,
                                        user=self.db_config['user'],
                                        passwd=self.db_config['pswd'],
                                        db=self.db_config['db'],
                                        charset=self.db_config['charset'])

        except Exception as err:
            logger.error(err)
        # 创建游标
        self.cur = self.conn.cursor()
        return True

    # 关闭数据库
    def closeDB(self):
        # 如果数据打开，则关闭；否则没有操作
        if self.conn and self.cur:
            self.cur.close()
            self.conn.close()
        return True

    # 执行数据库的sq语句,主要用来做插入操作
    def execute(self, sql, params=None):
        self.connDB()
        try:
            if self.conn and self.cur:
                # 正常逻辑，执行sql，提交操作
                self.cur.execute(sql, params)
                self.conn.commit()
        except:
            logger.error("execute failed: " + sql)
            logger.error("params: " + params)
            self.closeDB()
            return False
        return True

    # 用来查询表数据
    def select(self, sql, params=None):
        self.connDB()
        self.cur.execute(sql, params)
        result = [list(i)for i in self.cur.fetchall()]
        # print(result)
        return result

    # 查询表的字段信息
    def select_cols(self, sql, params=None):
        self.connDB()
        # self.cur
        self.cur.execute(sql, params)
        cols = [list(i) for i in self.cur.description]
        # print(cols)
        return cols在这里插入代码片

db_config.py

import json

def db_config(configfile):
    with open(configfile) as json_file:
        dbSettings = json.load(json_file)
    return dbSettings

onfigfile = “db_settings.txt”
db_settings.txt的内容如下：
{ ‘host’:‘127.0.0.1’,
‘port’:3306,
‘username’:‘root’,
‘password’:‘admin’,
‘database’:‘DataBase’,
‘charset’:‘utf8’
}
测试程序

# -*- coding =utf-8 -*-
# @Time : 2021/2/3 10:44
# @Author :Mr
# @File :测试.py
# @Software :PyCharm
import MysqlRead


def main():
    configfile = "db_settings.txt"
    # 连接 mysql，获取连接的对象
    sql = r"select table_name from information_schema.tables where table_schema='数据库名'"
    db = MysqlRead.MySQL(configfile)
    rows = db.select(sql)
    # 依次遍历结果集，发现每个元素，就是表中的一条记录，用一个元组来显示
    for row in rows:
        print(row)


if __name__ == '__main__':
    main()