【Python】数据库初始化类SQLite3Init.py

---

一、SQLite3Init.py

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import sqlite3
from TzqUtils.CommonTool import *


class SQLite3Init:
    """
    SQLite3Init数据库初始化类
    新建表及刷数

    """

    def __init__(self):
        pass

    # 创建表的函数
    @staticmethod
    def create_table_f(i_database_name: str, i_create_table_scripts):
        global cursor, conn
        try:
            conn = sqlite3.connect(i_database_name)
            cursor = conn.cursor()
            cursor.executescript(i_create_table_scripts)
            cursor.close()
            conn.close()
        except sqlite3.OperationalError as oe:
            print(f"发生了 sqlite3.OperationalError 异常: {oe}")
            cursor.close()
            conn.close()
        except Exception as e:
            # 捕获其他所有类型的异常
            print(f"发生了异常: {e}")
            cursor.close()
            conn.close()
        else:
            print(f"表创建成功！")
        finally:
            pass


if __name__ == '__main__':
    database_name = "tzq.db"
    table_script = CommonTool.get_file_content_f("01_sqlite3_table_tzq_880808.sql")
    print(table_script)
    obj = SQLite3Init()
    obj.create_table_f(database_name, table_script)

二、01_sqlite3_table_tzq_880808.sql

/* tzq - log_number 和 log_id 的 mapping 关系表 */
-- create sequence seq_tzq_info_mapping_t;

drop table if exists tzq_info_mapping_t;
CREATE TABLE tzq_info_mapping_t (
  id integer PRIMARY KEY AUTOINCREMENT,
  log_number text(1000) NOT NULL,
  log_type text(100) NOT NULL,
  log_id integer NOT NULL,
  creation_date TIMESTAMP not null default CURRENT_TIMESTAMP
);
CREATE UNIQUE index uk_tzq_info_mapping_1 on tzq_info_mapping_t (log_number, log_type);
CREATE UNIQUE index uk_tzq_info_mapping_2 on tzq_info_mapping_t (log_id);
delete from tzq_info_mapping_t;
vacuum;
-- delete from tzq_info_mapping_t;
-- select log_id from tzq_info_mapping_t where log_number = 'Z168006'

insert into tzq_info_mapping_t (log_number, log_type, log_id) select 'T168008', 'TZQ', 80008088;
insert into tzq_info_mapping_t (log_number, log_type, log_id) select 'Z168009', 'IS', 80008089;
insert into tzq_info_mapping_t (log_number, log_type, log_id) select 'Q168018', 'GOD', 80008090;