SQLite自动建库建表

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()