将CSV文件数据导入PostgreSQL表格自动化

import pandas as pd
import psycopg2
from psycopg2 import sql

# 读取CSV文件
csv_file_path = 'your_csv_file.csv'
data = pd.read_csv(csv_file_path)

# 连接到PostgreSQL数据库
conn = psycopg2.connect(
    dbname="your_database_name",
    user="your_user",
    password="your_password",
    host="your_host",
    port="your_port"
)

# 创建表格（如果不存在）
create_table_query = '''
CREATE TABLE IF NOT EXISTS your_table_name (
    column1 datatype1,
    column2 datatype2,
    ...
);
'''

conn.autocommit = True
cursor = conn.cursor()
cursor.execute(create_table_query)
conn.autocommit = False

# 将CSV数据插入到PostgreSQL表格中
for index, row in data.iterrows():
    insert_query = sql.SQL("INSERT INTO your_table_name ({}) VALUES ({})").format(
        sql.SQL(', ').join(map(sql.Identifier, data.columns)),
        sql.SQL(', ').join(sql.Placeholder() * len(data.columns))
    )
    cursor.execute(insert_query, tuple(row))

# 提交更改并关闭连接
conn.commit()
cursor.close()
conn.close()