存储数据库MYSQL-行政区划代码-5级12位-最终版

# CY3761 | 2021-11-20 23:52
import os
import time

import mysql.connector.errors
from mysql.connector import connect

joinStr = '\n'
fileSep = ','
fileDataEncoding = 'utf-8'


def timePlus():
    t = time.time()
    
    return '%s %.4f' % (time.strftime('%H:%M:%S', time.localtime(t)), t - int(t))


def getFileData(filePath):
    if os.path.exists(filePath):
        with open(filePath, 'r', encoding=fileDataEncoding) as r:
            fileData = r.readlines()
            
            # 这里最好一次循环解决
            newFileData = []
            for _ in fileData:
                if _:
                    _ = _.replace(joinStr, '').split(fileSep)
                    # 这里不能去末尾0 否则 10会变成 1 比较麻烦
                    _a = _[1]  # 名称
                    _b = _[2]  # 上级代码
                    _c = _[3]  # 当前代码
                    
                    newFileData.append((_a, _b, _c))
            
            return newFileData


dataDirPath = r'I:\AIoT智能物联网工程师\AIoT智能物联网\PY基础\玩儿转PY网络爬虫\学习代码\第9站-Linux环境编程基础\行政区划代码\2020\data'

walkItem = list(os.walk(dataDirPath))

items = []
i = 0
print('数据读取..')

""""""
for (a, b, c) in walkItem:
    for _ in c:
        _ = os.path.abspath(a + '/' + _)
        d = getFileData(_)
        
        if d:
            items.extend(d)
            i += len(d)
            print('\r已读取 %s 条数据 时间: %s' % (str(i).zfill(6), timePlus()), end='')
        
        # print(k, _)
        # print('\n'.join([fileSep.join(_) for _ in d]))

# print('\n'.join([fileSep.join(_) for _ in items]))

itemsLen = len(items)
saveFile = '行政区划代码-2020'

print('\n共读取 %s 条数据' % itemsLen)  # 可以遍历进行存储数据

# 存储(四) Mysql
# 本地MYSQL服务器帐户配置
connectCfg = {
    'host': 'localhost',
    'user': 'root',
    'password': '$23~45A78%90b',
    # 'database': 'test',  # 这里只进行登录不进行选择数据库
    'auth_plugin': 'mysql_native_password',  # MYSQL 8.0 需要
    'charset': 'utf8mb4'
}


class MysqlPlus:
    # 不能在这里设置连接数据库 否则文件运行就直接报错
    def __init__(self, database, table):
        self.conn = connect(**connectCfg)  # 关键字传参(字典注解)
        self.curs = self.conn.cursor()  # 获取游标对象
        
        self.database = database
        self.table = table
        
        self.charset = connectCfg.get('charset', 'utf8mb4')
        self.collate = 'utf8mb4_0900_ai_ci'
    
    def isExistsDatabase(self):
        return bool(self.findOne("SHOW DATABASES Like %s", (self.database,)))
    
    def createDatabase(self):
        if not self.isExistsDatabase():
            sql = f"CREATE DATABASE `{self.database}` character set '{self.charset}' collate '{self.collate}'"
            return bool(self.exec(sql))
        
        self.curs.execute(f"USE {self.database}")
        return False
    
    def isExistsTable(self):
        return bool(self.findOne("SHOW TABLE STATUS LIKE '%s'" % self.table))
    
    def createTable(self, struct, comment):
        if not self.isExistsTable():
            sql = [f"create table `{self.database}`.`{self.table}` ("]
            
            for _ in struct:
                sql.append(_.strip() + ',')
            sql[-1] = sql[-1][0:-1]
            
            sql.append(
                f") ENGINE = InnoDB CHARACTER SET = 'utf8mb4' COLLATE = 'utf8mb4_0900_ai_ci' COMMENT = '{comment}'")
            
            self.exec(''.join(sql))
            
            return self.isExistsTable()
        return False
    
    def find(self, sql, params):
        self.curs.execute(sql, params)
        
        # print('statement: ', self.curs.statement)
    
    def findOne(self, sql, params=()):
        self.find(sql, params)
        
        return self.curs.fetchone()
    
    def findAll(self, sql, params=()):
        find(sql, params)
        
        return self.curs.fetchall()
    
    def exec(self, sql, params=()):
        self.curs.execute(sql, params)
        self.conn.commit()
        
        # print('statement: ', self.curs.statement)
        
        # 返回获取影响函数
        return self.curs.rowcount


""""""


def main():
    try:
        o = MysqlPlus('csdn', 'stats')
    except (Exception, BaseException) as e:
        print(e)
    else:
        # 检查数据库是否存在
        print(o.isExistsDatabase())
        # 创建数据库
        print(o.createDatabase())
        # 检查数据表是否存在
        print(o.isExistsTable())
        # 创建数据表
        print(o.createTable([
            "`code` char(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' UNIQUE COMMENT '当前行政区划代码'",
            "`pcode` char(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '父级行政区划代码'",
            "`name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '当前行政区划名称'",
            "PRIMARY KEY (`code`)"
        ], '行政区划代码-2020'))
        
        for k, _ in enumerate(items):
            k = str(k).zfill(6)
            # 321
            code = _[2]
            pcode = _[1]
            name = _[0]
            
            # print(k, code, pcode, name)
            sql = f'REPLACE INTO `{o.database}`.`{o.table}` (`code`, `pcode`, `name`) VALUE (%s,%s,%s)'
            isExec = o.exec(sql, (code, pcode, name))
            print('\r%s 执行%s 时间: %s SQL: %s' % (k, '成功' if isExec else '失败', timePlus(), o.curs.statement), end='')
            
            # break
        print('执行完成')


if __name__ == '__main__':
    main()