PYTHON3.7调用MSQL数据库，检查时间字段用INT类型来存储

import pymysql

class ls_CheckDateIsInt:

    def __init__(self):
        self.host = ''
        self.port = 3306
        self.db = ''
        self.user = ''
        self.password = ''


    def get_connection(self):
        conn = pymysql.connect(host=self.host, port=self.port, db=self.db, user=self.user, password=self.password)
        return conn

    # 查询表的所有字段和字段对应的类型
    def list_col_type(self, tabls_name):
        conn = self.get_connection()
        cursor = conn.cursor()
        cursor.execute("desc %s" % tabls_name)
        data = cursor.fetchall()
        conn.close()
        return data

    # # 查询所有字段
    # def list_col(self, tabls_name):
    #     conn = self.get_connection()
    #     cursor = conn.cursor()
    #     cursor.execute("select * from %s" % tabls_name)
    #     col_name_list = [tuple[1] for tuple in cursor.description]
    #     conn.close()
    #     return col_name_list

    # 列出所有的表
    def list_table(self):
        conn = self.get_connection()
        cursor = conn.cursor()
        cursor.execute("show tables")
        table_list = [tuple[0] for tuple in cursor.fetchall()]
        conn.close()
        return table_list

    # 检查字段是INT类型，且是21开始的表和字段
    def search_COLUMN_IS_INT_AND_STARTWITH_21(self, needshow=True):
        # 存储测试结果 21打头的
        resultlist21 = list()
        resultlistNone = list()

        # 获取数据库所有的表
        db_tables = self.list_table()

        conn = self.get_connection()
        cursor = conn.cursor(pymysql.cursors.DictCursor)

        # 遍历表
        for db_table in db_tables:
            # 获得字段名称，字段类型
            datalist = lscd.list_col_type(db_table)
            # 遍历字段
            for data in datalist:
                # 如果类型是INT类型
                if data[1] == 'int(11)':
                        # 用MAX获得最大的值，判断是不是21打头的
                        cursor.execute(
                            "select max(`%s`) from %s" % (data[0], db_table))
                        maxdata = cursor.fetchall()

                        if maxdata[0]['max(`%s`)' % (data[0])] is None:
                            resultlistNone.append((db_table, data[0], data[1], maxdata[0]))
                            # 是否立即显示数据
                            if needshow:
                                print(db_table, data[0], data[1], maxdata[0]['max(`%s`)' % (data[0])])

                        elif maxdata[0]['max(`%s`)' % (data[0])] >= 2100000000:
                            resultlist21.append((db_table, data[0], data[1], maxdata[0]))
                            # 是否立即显示数据
                            if needshow:
                                print(db_table, data[0], data[1], maxdata[0]['max(`%s`)' % (data[0])])

        return resultlist21, resultlistNone

    # 检查字段是INT类型，且名字像时间的表和字段
    def search_COLUMN_IS_INT_AND_COLUMNNAME_LIKE_DATE(self, likedatelist, needshow=True):


        # 存储测试结果
        resultlist = list()

        conn = self.get_connection()
        cursor = conn.cursor(pymysql.cursors.DictCursor)

        # 获取数据库所有的表
        db_tables = self.list_table()



        # 遍历表
        for db_table in db_tables:
            # 获得字段名称，字段类型
            datalist = lscd.list_col_type(db_table)
            # print(datalist)
            # 遍历字段
            for data in datalist:
                # 如果类型是INT类型
                if data[1] == 'int(11)':

                    # 遍历像是时间列表
                    for check_item in likedatelist:
                        # 都是小写进行比对
                        if check_item.lower() in data[0].lower():
                            cursor.execute(
                                "select max(`%s`) from %s" % (data[0], db_table))
                            maxdata = cursor.fetchall()
                            resultlist.append((db_table, data[0], data[1], maxdata[0]['max(`%s`)' % (data[0])]))
                            # 是否立即显示数据
                            if needshow:
                                print(db_table, data[0], data[1], maxdata[0]['max(`%s`)' % (data[0])])
        return resultlist




if __name__ == '__main__':
    lscd = ls_CheckDateIsInt()

    likedatelist = ['create', 'update', 'delete', 'time', 'data', 'last']

    print('### 关键字排查法 ###')
    COLUMN_IS_INT_AND_COLUMNNAME_LIKE_DATE_RESULT = lscd.search_COLUMN_IS_INT_AND_COLUMNNAME_LIKE_DATE(likedatelist)
    print('#########')

    resultlist21, resultlistNone = lscd.search_COLUMN_IS_INT_AND_STARTWITH_21(needshow=False)
    print('### 大于等于2100000000的 ###')
    for item in resultlist21:
        print(item)
    print('#########')


    print('### 跑完了 ###')




############################################
windows环境python2.7安装MySQLdb
http://www.lfd.uci.edu/~gohlke/pythonlibs/#mysql-python

下载对应版本我的是

MySQL_python‑1.2.5‑cp27‑none‑win_amd64.whl

在下载目录进入cmd，执行

pip install MySQL_python‑1.2.5‑cp27‑none‑win_amd64.whl

############################################
为啥要用PY2.7来连接MYSQL数据，因为PY3.7的MYSQL库只支持高版本的MYSQL，连低版本会报错，只能用PY2.7的库来连接MYSQL了。