Zenn
🦑

CSVファイルをアップロードしてsqlite3に書き込む

に公開
sqlite3
CSV
tech

sqlite3のテーブル情報を取得して、フロントに表示

  • 今回sqlite3のデータベースのパスは"DB_PATH = "data.db"としています。
  • この"data.db"内のテーブルのテーブル名とカラム情報（カラム名や型など）を取得します

サーバー側コード

import pandas as pd
import sqlite3

DB_PATH = "data.db"

@app.get("/tables")
async def get_tables():
    """ テーブル名と、テーブルのカラム名及び型を取得します。"""
    with sqlite3.connect(DB_PATH) as conn:
        df = pd.read_sql_query("SELECT name FROM sqlite_master WHERE type='table'", conn)

        tables = []
        for table_name in df["name"]:
            df = pd.read_sql_query(f"PRAGMA table_info({table_name})", conn)
            df = df.to_dict(orient="records")
            tables.append({
                "table_name": table_name,
                "columns": df
            })

        return tables
tablesの形
[
  {
    "table_name": "table1",
    "columns": [
      {
        "cid": 0,
        "name": "title",
        "type": "TEXT",
        "notnull": 0,
        "dflt_value": null,
        "pk": 0
      },
      {
        "cid": 1,
        "name": "rating",
        "type": "REAL",
        "notnull": 0,
        "dflt_value": null,
        "pk": 0
      },
      {
        "cid": 2,
        "name": "is_released",
        "type": "INTEGER",
        "notnull": 0,
        "dflt_value": null,
        "pk": 0
      }
    ]
  },
  {
    "table_name": "サンプル",
    "columns": [
      {
        "cid": 0,
        "name": "title",
        "type": "TEXT",
        "notnull": 0,
        "dflt_value": null,
        "pk": 0
      },
      {
        "cid": 1,
        "name": "views",
        "type": "INTEGER",
        "notnull": 0,
        "dflt_value": null,
        "pk": 0
      },
      {
        "cid": 2,
        "name": "rating",
        "type": "REAL",
        "notnull": 0,
        "dflt_value": null,
        "pk": 0
      },
      {
        "cid": 3,
        "name": "is_released",
        "type": "INTEGER",
        "notnull": 0,
        "dflt_value": null,
        "pk": 0
      },
      {
        "cid": 4,
        "name": "release_date",
        "type": "TEXT",
        "notnull": 0,
        "dflt_value": null,
        "pk": 0
      }
    ]
  }
]

フロント側

const hostUrl = "http://localhost:8080";

interface TableInfo {
    table_name: string;
    columns: {
        cid: number;
        name: string;
        type: string;
        notnull: number;
    }[];
}

export const TableInfo = () => {

    const [tableData, setTableData] = useState<TableInfo[]>([]);

    useEffect(() => {
        fetch(`${hostUrl}/tables`)
        .then(response => response.json())
        .then((data: TableInfo[]) => {
            setTableData(data);
        });
    }, []);

    return (
        <div>
            <h4>テーブル情報</h4>

            <ul className="grid grid-cols-2 gap-4">

                {tableData.map((table, idx) => (
                    <li key={idx} className="border w-fit p-2">
                        <table className=" w-32 table table-sm">
                            <caption className="font-bold">
                                {table.table_name}
                            </caption>

                            <thead>
                                <tr>
                                    <th>cid</th>
                                    <th>name</th>
                                    <th>type</th>
                                    <th>notnull</th>
                                </tr>
                            </thead>

                            <tbody>
                                {table.columns.map((column, idx) => (
                                    <tr key={idx}>
                                        <td>{idx + 1}</td>
                                        <td>{column.name}</td>
                                        <td>{column.type}</td>
                                        <td>{column.notnull}</td>
                                    </tr>
                                ))}
                            </tbody>
                        </table>
                    </li>
                ))}
            </ul>
        </div>
    );
}

フロント側でCSVファイルをアップロードし、Sqliteに保存

こんな感じの画面です。

フロント側

  • ファイルをドラッグ&ドロップで読み込みさせるには、Reactライブラリである"react-dropzone"を使うのが良いです。
    // ライブラリのインポート
    const { acceptedFiles, getRootProps, getInputProps } = useDropzone({
        accept: {
            'text/csv': ['.csv'],
            'text/tsv': ['.tsv'],
        },
        multiple: false,
    });

    // ファイルが登録された時のアクションを設定
    useEffect(() => {
      
    }, [acceptedFiles]);

    return (
        <div className='border w-full min-h-24' {...getRootProps()}>
          <span className="text-gray-500">CSVまたはTSVファイルをドラッグ&ドロップするか、クリックしてファイルを選択してください</span>
          <input {...getInputProps()}  />
        </div>
    )
  • サーバー側にファイルデータを送るには、FormDataを使うのが良いです。
  • 下の例では、FormDataの中にファイルとテーブル名（文字列）をセットしています。
    const submit = async() => {
        if (acceptedFiles.length === 0) return;

        const formData = new FormData();

        const file = acceptedFiles[0];
        const table_name = file.name.split('.')[0];

        formData.append("file", file);
        formData.append("table_name", table_name); // 任意のテーブル名を指定

        const response = await fetch("http://localhost:8080/upload", {
            method: "POST",
            body: formData,
        });

        const data = await response.json();
        console.log(data);
    }
サンプルコード全体
export const TableFileUploader = ({ onFilesSelected }: { onFilesSelected: (files: File[]) => void }) => {
    const { acceptedFiles, getRootProps, getInputProps } = useDropzone({
        accept: {
            'text/csv': ['.csv'],
            'text/tsv': ['.tsv'],
        },
        multiple: false,
    });

    const [tableData, setTableData] = useState<string[][]>([]);
    const [tableName, setTableName] = useState<string>('');
  
    useEffect(() => {
        if (!acceptedFiles || acceptedFiles.length === 0) return;
  
        const supportedTypes = ['text/csv', 'text/tsv'];
        const tableFiles = acceptedFiles.filter(file => supportedTypes.includes(file.type) || file.name.endsWith('.csv') || file.name.endsWith('.tsv'));
  
        const file = tableFiles[0];

        const reader = new FileReader();
        reader.onload = (e) => {
            const text = e.target?.result as string;
            // 区切り文字を判定
            const delimiter = file.type === 'text/tsv' || file.name.endsWith('.tsv') ? '\t' : ',';
            const rows = text.split(/\r?\n/).filter(row => row.trim() !== '');
            const data = rows.map(row => row.split(delimiter));
            setTableData(data);
            setTableName(file.name.split('.')[0]);
        };

        reader.readAsText(file);
      
    }, [acceptedFiles]);


    const submit = async() => {
        if (acceptedFiles.length === 0) return;

        const formData = new FormData();

        const file = acceptedFiles[0];
        const table_name = file.name.split('.')[0];

        formData.append("file", file);
        formData.append("table_name", table_name); // 任意のテーブル名を指定

        const response = await fetch("http://localhost:8080/upload", {
            method: "POST",
            body: formData,
        });

        const data = await response.json();
        console.log(data);
    }
  
    return (
      <div className="container border-2 border-gray-300 rounded-md p-4">

        <h2 className='text-2xl font-bold'>CSV/TSVファイルアップロード</h2>

        <div className='border w-full min-h-24' {...getRootProps()}>
          <span className="text-gray-500">CSVまたはTSVファイルをドラッグ&ドロップするか、クリックしてファイルを選択してください</span>
          <input {...getInputProps()}  />
        </div>
  
        <div className="mt-4">
          <h4>ファイル情報</h4>
          {acceptedFiles.length > 0 && (
            <ul>
              <li>ファイル名: {acceptedFiles[0].name}</li>
              <li>ファイルサイズ: {acceptedFiles[0].size} bytes</li>
            </ul>
          )}

        </div>
  
        <div className="mt-4">
          <h4>テーブルプレビュー</h4>
          {tableData.length > 0 ? (
            <table className="table table-bordered table-striped">
              <thead>
                <tr>
                  {tableData[0].map((cell, idx) => (
                    <th key={idx}>{cell}</th>
                  ))}
                </tr>
              </thead>
              <tbody>
                {tableData.slice(1).map((row, rowIdx) => (
                  <tr key={rowIdx}>
                    {row.map((cell, cellIdx) => (
                      <td key={cellIdx}>{cell}</td>
                    ))}
                  </tr>
                ))}
              </tbody>
            </table>
          ) : (
            <div className="text-gray-400">プレビューできるデータがありません</div>
          )}
        </div>

        <div className="mt-4">
            <h4>テーブル名</h4>
            <input type="text" className="input " value={tableName} onChange={(e) => setTableName(e.target.value)} />
                  
            <button className=" btn btn-primary" onClick={submit}>送信</button>
        </div>

      </div>
    );
  };

サーバー側

  • フロント側からFormDataを受け取ります。
# データを読んで、カラムの型を推定する
def infer_sqlite_type(series: pd.Series) -> str:
    sample = series.dropna().head(10)
    
    # datetime
    for val in sample:
        if isinstance(val, str):
            try:
                pd.to_datetime(val)
            except Exception:
                break
        else:
            break
    else:
        return "TEXT"  # SQLiteにdatetime型はないためTEXTにする

    # bool
    if sample.map(lambda v: str(v).lower()).isin(["true", "false", "0", "1"]).all():
        return "BOOLEAN"

    # int
    if pd.api.types.is_integer_dtype(sample):
        return "INTEGER"
    
    # float
    if pd.api.types.is_float_dtype(sample):
        return "REAL"
    
    # fallback
    return "TEXT"


# テーブル作成
def update_table_from_df(df: pd.DataFrame, table_name: str, conn: sqlite3.Connection):
    inferred_types = {col: infer_sqlite_type(df[col]) for col in df.columns}

    index_col = df.columns[0]

    columns_def = []
    for col, dtype in inferred_types.items():
        if col == index_col:
            columns_def.append(f'"{col}" {dtype} PRIMARY KEY')
        else:
            columns_def.append(f'"{col}" {dtype}')
    columns_def_str = ", ".join(columns_def)

    create_stmt = f'CREATE TABLE IF NOT EXISTS "{table_name}" ({columns_def_str});'

    cursor = conn.cursor()
    cursor.execute(create_stmt)

    # データ挿入
    df.to_sql(table_name, conn, if_exists='replace', index=False)
    conn.commit()


@app.post("/upload")
async def set_data_to_table(
    file: UploadFile = File(...), 
    table_name: str = Form(...), 
):
    """
    CSVファイルをアップロードしてテーブルにデータを挿入します。

    テーブルがあれば、既存テーブルにデータを挿入します。
    テーブルがなければ、新規テーブルを作成します。
    """
    print(table_name)
    contents = await file.read()
    df = pd.read_csv(StringIO(contents.decode('utf-8')))

    with sqlite3.connect(DB_PATH) as conn:
        update_table_from_df(df, table_name, conn)

    return {"status": "success", "rows": len(df)}

Discussion