SQLite自动建库建表

古城微笑少年丶 2024-04-01 13:29 15阅读 0赞

1.  SpringBoot版本
    
        package com.ciih.facebarrier.config;
        
        import Commom.osSelect;
        import cn.hutool.db.Entity;
        import cn.hutool.db.handler.EntityListHandler;
        import cn.hutool.db.sql.SqlExecutor;
        import com.ciih.facebarrier.model.SysConfig;
        import com.ciih.facebarrier.service.SysConfigService;
        import org.springframework.stereotype.Component;
        
        import javax.annotation.PostConstruct;
        import javax.annotation.Resource;
        import javax.sql.DataSource;
        import java.io.IOException;
        import java.sql.Connection;
        import java.sql.SQLException;
        import java.util.List;
        import java.util.Map;
        import java.util.stream.Collectors;
        
        /**
         * 数据库配置
         */
        @Component
        public class DbConfig {
            @Resource
            DataSource dataSource;
            @Resource
            SysConfigService sysConfigService;
        
            /**
             * 初始化数据库
             */
            private static void createDatabase() throws IOException {
                if (osSelect.isLinux()) {
                    String[] cmd = new String[]{"/bin/sh", "-c", "cd /home/webapps&&cd /home/webapps&& if [ ! -f \"barrier.db\" ];then touch barrier.db; fi;"};
                    Runtime.getRuntime().exec(cmd);
                } else if (osSelect.isWindows()) {
                    //创建sqlite3数据库文件
                    Runtime.getRuntime().exec("cmd /c if not exist fast.db type nul>barrier.db");
                }
            }
        
            /**
             * 初始化表
             */
            private void createTables() throws SQLException {
                Connection connection = dataSource.getConnection();
                List<Entity> list = SqlExecutor.query(connection, "SELECT name FROM sqlite_master ", new EntityListHandler());
                List<String> tables = list.stream().map(x -> x.get("name").toString()).collect(Collectors.toList());
        
                if (!tables.contains("device")) {
                    SqlExecutor.execute(connection, "CREATE TABLE \"device\" (\n" +
                            "  \"id\" integer NOT NULL PRIMARY KEY AUTOINCREMENT,\n" +
                            "  \"ip\" TEXT NOT NULL,\n" +
                            "  \"port\" TEXT NOT NULL,\n" +
                            "  \"username\" TEXT NOT NULL,\n" +
                            "  \"password\" TEXT NOT NULL,\n" +
                            "  \"serial_number\" text,\n" +
                            "  \"position\" text,\n" +
                            "  \"direction\" TEXT\n" +
                            ");");
                    SqlExecutor.execute(connection, "INSERT INTO \"device\"(\"ip\", \"port\", \"username\", \"password\", \"serial_number\", \"position\", \"direction\") VALUES ('192.168.88.186', '8000', 'admin', 'zj123456', 'G59394420', '安特磁材厂区南门', '进');");
                    SqlExecutor.execute(connection, "INSERT INTO \"device\"(\"ip\", \"port\", \"username\", \"password\", \"serial_number\", \"position\", \"direction\") VALUES ('192.168.88.187', '8000', 'admin', 'zj123456', 'G59394482', '安特磁材厂区南门', '出');");
        
                }
                if (!tables.contains("sys_config")) {
                    SqlExecutor.execute(connection, "CREATE TABLE \"sys_config\" ( \"id\" integer NOT NULL PRIMARY KEY AUTOINCREMENT, \"business_key\" text ( 255 ) NOT NULL, \"business_value\" text NOT NULL, \"business_description\" text NOT NULL );\n" +
                            "CREATE UNIQUE INDEX \"sys_config_business_key\" ON \"sys_config\" ( \"business_key\" ASC );");
                    //初始化参数：
                    SqlExecutor.execute(connection, "INSERT INTO \"sys_config\"( \"business_key\", \"business_value\", \"business_description\") VALUES ( 'AlarmPushAddress', 'http://atcc-workshoptest.ciih.net/java/auth/login', '收到报警后回调外网地址');");
                    SqlExecutor.execute(connection, "INSERT INTO \"sys_config\"( \"business_key\", \"business_value\", \"business_description\") VALUES ( 'AlarmPushAddressToken', 'Authorization=eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJsb2dpblR5cGUiOiJ1c2VyIiwibG9naW5JZCI6IjE1MjY3Mzk4MTMxMTk2NiIsImRldmljZSI6ImRlZmF1bHQtZGV2aWNlIiwiZWZmIjoxNjYyNzk5MDE3OTYxfQ.xPQNsuAfV_g7t5XoQ01MNaF44TQYUSP8WbMuApenDQQ', '访问凭证');");
                    SqlExecutor.execute(connection, "INSERT INTO \"sys_config\"( \"business_key\", \"business_value\", \"business_description\") VALUES ( 'AlarmLoginAddress', 'http://atcc-workshoptest.ciih.net/java/auth/getToken', '获取访问凭证地址');");
                    SqlExecutor.execute(connection, "INSERT INTO \"sys_config\"( \"business_key\", \"business_value\", \"business_description\") VALUES ( 'AlarmLoginUserName', '15267398131', '账号');");
                    SqlExecutor.execute(connection, "INSERT INTO \"sys_config\"( \"business_key\", \"business_value\", \"business_description\") VALUES ( 'AlarmLoginPassword', 'admin@123', '密码');");
                }
            }
        
            @PostConstruct
            public void init() throws SQLException, IOException {
                //初始化数据库
                createDatabase();
                //初始化数据库表
                createTables();
            }
        }
2.  FastAPI版本

"""
    SQLite数据库初始化：
    1.建库
    2.建表
    """
    import os
    import sqlite3
    
    
    # 执行SQL-sqlite3
    def execute_sqlite3(sql: str):
        conn = sqlite3.connect('./db/fast.db')
        cur = conn.cursor()
        cur.execute(sql)
        data = cur.fetchall()
        conn.close()
        return data
    
    
    # 建库（不存在则创建）
    def __createDatabase(db_name: str):
        # win
        if os.sep == "\\":
            os.popen(f"cmd /c if not exist ./db/{db_name}.db type nul>./db/{db_name}.db")
        else:  # linux
            os.popen(f'/bin/sh&&-c&&cd /home/webapps&& if [ ! -f "./db/{db_name}.db" ];then touch ./db/{db_name}.db; fi;')
    
    
    # 建表（不存在则创建）
    def __createTables():
        sql_select_tables = "SELECT name FROM sqlite_master"
        tables = execute_sqlite3(sql_select_tables)
        table_names = [i[0] for i in tables]
    
        if not table_names.__contains__("operation_record"):
            sql_create_table = 'CREATE TABLE "operation_record" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "ip" text NOT NULL,  "create_time" text NOT NULL,"update_time" text NOT NULL);'
            execute_sqlite3(sql_create_table)
    
    
    def init():
        __createDatabase('fast')
        __createTables()