INSERT,UPDATE文作成

home
./csv
./csv/input.csv
./csv/output.csv
insert.py
insert2.py
update.py

insert.py

import pandas as pd

# CSVファイルのパス
csv_file_path = './csv/input.csv'

def create_insert_query(table_name, columns, values):
    # バルクインサートクエリの作成
    formatted_values = ', '.join(['(' + ', '.join([f"'{str(val)}'" for val in row]) + ')' for row in values])
    query = f"INSERT INTO {table_name} ({', '.join(columns)}) VALUES {formatted_values}"
    return query

def main():
    # CSVファイルからデータを読み取り
    data_frame = pd.read_csv(csv_file_path)

    # バルクインサートを実行するテーブル名
    table_name = 'your_table_name'

    # バルクインサート用のクエリを作成
    insert_query = create_insert_query(table_name, data_frame.columns, data_frame.values.tolist())

    # 作成したクエリを表示（確認用）
    print(insert_query)

if __name__ == "__main__":
    main()

insert2.py

import csv

tablename = 'table_name'

def process_csv_in_batches(file_path):
    with open(file_path, 'r', newline='', encoding='utf-8') as csvfile:
        data_rows = ""
        csv_reader = csv.reader(csvfile)
        header = next(csv_reader)  # ヘッダーを取得
        column = ",".join(header)
        insert = f"INSERT INTO {tablename} ({column}) VALUES "

        for row in csv_reader:
            data_row = ""
            for data in row:
                data_row += f",\'{data}\'"
            data_rows += f"({data_row[1:]}),"

    write_to_csv('./csv/output.csv', insert + data_rows[:-1])

def write_to_csv(output_path, data):
    f = open(output_path, 'w')
    f.write(data)
    f.close()

# CSVファイルのパスを指定して実行
csv_file_path = './csv/input.csv'  # あなたのCSVファイルの実際のパスに変更してください
process_csv_in_batches(csv_file_path)

update.py

import csv


INPUT_CSV_PATH= './csv/input.csv'
OUTPUT_CSV_PATH = './csv/output.csv'

tablename = 'table_name'
update = f"UPDATE {tablename} SET "
where_flag = False
where = "A = 1"

def process_csv_in_batches(file_path):
    with open(file_path, 'r', newline='', encoding='utf-8') as csvfile:
        csv_reader = csv.reader(csvfile)
        header = next(csv_reader)  # ヘッダーを取得
        data_rows = ""
        data_row = ""
        data = ""
        for rows in csv_reader:
            data_row = ""
            for i, row in enumerate(rows):
                data_row += f"{header[i]} = \'{row}\',"

            if where_flag:
                data += f"{update}{data_row[:-1]} WHERE {where};\r\n"
            else:
                data += f"{update}{data_row[:-1]};\r\n"

    data_rows += data
    write_to_csv(OUTPUT_CSV_PATH , data_rows[:-1])

def write_to_csv(output_path, data):
    f = open(output_path, 'w')
    f.write(data)
    f.close()

# CSVファイルのパスを指定して実行
process_csv_in_batches(INPUT_CSV_PATH)