python解析.sql文件并执行

最新推荐文章于 2024-05-15 15:23:38 发布

高过蓝天的云

最新推荐文章于 2024-05-15 15:23:38 发布

阅读量669

点赞数

文章标签： python 数据库 sql

本文链接：https://blog.csdn.net/CSDN1csdn1/article/details/131038575

版权

该文章描述了一个Python类DBInit，用于读取SQL文件并执行初始化数据库的操作。它包含了创建数据库、表以及索引的SQL语句，并通过pymysql连接执行这些DDL语句。DBInit类的方法包括解析SQL文件、执行SQL以及创建数据库的方法。

摘要由CSDN通过智能技术生成

SQL文件：

-- 创库
CREATE DATABASE IF NOT EXISTS flaskttt CHARACTER SET UTF8;
-- 进入数据库
USE flasktest;
-- 创表
CREATE TABLE IF NOT EXISTS userinfo (
    uid INT(8) NOT NULL AUTO_INCREMENT,
    uname VARCHAR(16) DEFAULT NULL,
    tel INT(11),
    PRIMARY KEY (uid));
-- 创建普通索引
CREATE INDEX uname_index ON userinfo(uname);

解析SQL文件：

class DBInit:
    """初始化数据库"""

    def __init__(self, host, port, username, passwd, database=None):
        self.host = host
        self.port = port
        self.username = username
        self.password = passwd
        self.database = database

    @staticmethod
    def _parse_sql_file(sql_path=None):
        """sql文件解析"""
        sql_path = "dbsql.sql" if not sql_path else sql_path
        sql_context, sql_list = "", []
        with open(sql_path, "r", encoding="utf8") as sqls:
            handle_sql_list = [i.replace("\n", "").replace("\r", "") for i in sqls.readlines() if
                               not i.startswith("--") and i not in [';\n', '\n']]
            for sql in handle_sql_list:
                if sql.endswith(";") and not sql_context:
                    yield sql
                    sql_context = ""
                else:
                    sql_context += sql
                    if sql_context.endswith(";"):
                        yield sql_context
                        sql_context = ""

    def _exec_sql(self):
        """数据库执行sql"""
        connect = pymysql.connect(host=self.host, port=self.port, user=self.username, password=self.password)
        with connect as conn:
            with conn.cursor() as cursor:
                for sql in self._parse_sql_file():
                    print(f"exec sql: {sql}")
                    cursor.execute(sql)

    def create_db(self):
        print(">>>>>> DDL start <<<<<<")
        try:
            self._parse_sql_file()
            self._exec_sql()
            print(">>>>>> DDL successful! <<<<<< ")
        except Exception as e:
            print(f">>>>>> DDL sql failed, detail is {e} <<<<<<")


dbinit = DBInit(BaseConfig.HOST, BaseConfig.PORT, BaseConfig.USERNAME, BaseConfig.PASSWORD)
dbinit.create_db()