用pymysql简单操作数据库

用pymysql简单操作数据库

程序说明
待续

import time
import pymysql
import gevent
from gevent import monkey
from datetime import datetime as dt
from dbutils.pooled_db import PooledDB

monkey.patch_all()

db_conn= {
    "host": "*",
    "port": 0,
    "hostin": "*",
    "portin": *,
    "user": '*',
    "pwd": '*'
}

class DB(object):
    ''' dbconnection'''

    def __init__(self, dbconn, database=None, netout=True):
        '''数据库基本链接信息，数据库，是否为外网'''
        self.__database = database
        self.__pool = self._get_pool(dbconn, database, netout)
        # self.s=dbconn.get("port")

    def _get_pool(self, dbconn, database, tag=True):
        try:
            if tag:
                pool = PooledDB(
                    creator=pymysql,
                    maxconnections=15,
                    host=dbconn.get("host"),
                    user=dbconn.get("user"),
                    passwd=dbconn.get("pwd"),
                    db=database,
                    port=dbconn.get("port"),
                )
            else:
                pool = PooledDB(
                    creator=pymysql,
                    host=dbconn.get("hostin"),
                    user=dbconn.get("user"),
                    passwd=dbconn.get("pwd"),
                    db=database,
                    port=dbconn.get("portin"),
                )
        except Exception as e:
            pool = None
            print(dt.now(), "log==>", e)

        return pool

    def _get_connection_and_cursor(self):
        '''获取游标链接 conn, cursor'''
        conn = self.__pool.connection()
        cursor = conn.cursor()
        # cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        return conn, cursor

    def _close_all(self, *args):
        '''关闭链接'''
        try:
            for i in args:
                i.close()
        except:
            print("参数错误是否为conn, cursor")

    def get_tables(self) -> list:
        '''获取数据库的表 返回为列表'''
        conn, cursor = self._get_connection_and_cursor()
        sql = f'show tables from {self.__database}'
        tables = []
        try:
            rows = cursor.execute(sql)  # 返回执行成功的结果条数
            print(f'{self.__database} 一共有 {rows} 张表')
            for d in cursor.fetchall():
                for k, v in d.items():
                    tables.append(v)
        except Exception as e:
            print('\033[1;31m', dt.now(), 'errorlog==》', sql, e, '\033[0m')
        self._close_all(conn, cursor)

        return tables

    def get_table_file(self, table: str) -> list:
        '''获取表的字段名'''
        conn, cursor = self._get_connection_and_cursor()
        # sql = f'desc {table}'
        sql = f"SELECT COLUMN_NAME,DATA_TYPE,COLUMN_COMMENT  FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = '{table}'"
        try:
            cursor.execute(sql)
            res = cursor.fetchall()
            # print(res)
            files = [(x['COLUMN_NAME'], x['COLUMN_COMMENT']) for x in res]
        except Exception as e:
            files = None
            print('\033[1;31m', dt.now(), 'errorlog==》', sql, e, '\033[0m')
        self._close_all(conn, cursor)
        return files

    def get_index_file(self, table: str) -> list:
        '''取得索引'''
        conn, cursor = self._get_connection_and_cursor()
        sql = f"SHOW INDEX FROM {table}"
        try:
            cursor.execute(sql)
            res = cursor.fetchall()
            # pprint(res)
            files = [x['Column_name'] for x in res]
        except Exception as e:
            files = None
            print('\033[1;31m', dt.now(), 'errorlog==》', sql, e, '\033[0m')
        self._close_all(conn, cursor)
        return files

    def query_by_sql(self, sql):
        '''查询语句'''
        print(dt.now(), sql)
        conn, cursor = self._get_connection_and_cursor()
        try:
            cursor.execute(sql)
            res = cursor.fetchall()
        except Exception as e:
            res = None
            print('\033[1;31m', dt.now(), 'errorlog==》', sql, e, '\033[0m')
        self._close_all(conn, cursor)
        return res

    def exec_by_sql(self, sql):
        '''执行语句添加，更新，删除（单）'''
        conn, cursor = self._get_connection_and_cursor()
        print(dt.now(), sql)
        try:
            cursor.execute(sql)
            conn.commit()
        except Exception as e:
            print('\033[1;31m', dt.now(), 'errorlog==》', sql, e, '\033[0m')
            conn.rollback()
        self._close_all(conn, cursor)
        
    def exec_by_sql(self, sql, data_list):
    	'''执行语句添加，更新，删除（多）'''
    	pass

    def get_count_by_index(self, table):
        '''统计表数量通过索引'''
        file = self.get_index_file(table)
        fiirst_index = file[0]
        sql = f"select count({fiirst_index}) from {table}"
        conn, cursor = self._get_connection_and_cursor()
        try:
            key = f"count({fiirst_index})"
            cursor.execute(sql)
            rse_count = [table, cursor.fetchall()[0][str(key)]]
        except Exception as e:
            rse_count = None
            print('\033[1;31m', dt.now(), 'errorlog==》', sql, e, '\033[0m')
        self._close_all(conn, cursor)
        # print(rse_count)
        return rse_count

    def get_count(self, table):
        '''统计表数量'''
        # print(table)
        file = self.get_index_file(table)
        sql = f"select count(*) from {table}"
        conn, cursor = self._get_connection_and_cursor()
        try:
            cursor.execute(sql)
            rse_count = [table, cursor.fetchall()[0]]
        except Exception as e:
            rse_count = None
            print('\033[1;31m', dt.now(), 'errorlog==》', sql, e, '\033[0m')
        self._close_all(conn, cursor)
        # print(rse_count)
        return rse_count


class CoroutinesGevent(DB):
    ''' 协程操作'''
    def process_item(self, table_list: list):
        '''协程 统计库所有表数据'''
        geventlist = [gevent.spawn(self.get_count, x) for x in table_list]
        gevent.joinall(geventlist)
        return [x.value for x in geventlist]


class FindAllDatabase:

    def __init__(self, database):
        self.db = pymysql.Connection(host=database["host"], 
                                     user=database["user"], 
                                     passwd=database["pwd"],
                                     port=database["port"])
        self.cursor = self.db.cursor()

    def get_databases(self):
        '''获取所有的数据库名字'''
        try:
            self.cursor.execute("show databases")
            res = self.cursor.fetchall()
            databases = [r[0] for r in res]
        except Exception as e:
            databases = []
            print('\033[1;31m', dt.now(), 'errorlog==》', e, '\033[0m')
        self.cursor.close()
        self.db.close()
        return databases