import psycopg2
import csv

# Connect to PostgreSQL database
conn = psycopg2.connect(
    host="localhost",
    database="your_database",
    user="your_username",
    password="your_password"
)
cur = conn.cursor()

# Open CSV file
with open('your_file.csv', 'r') as f:
    reader = csv.reader(f)
    next(reader)  # Skip header row

    # Iterate over rows and insert into database
    for row in reader:
        cur.execute(
            "INSERT INTO your_table (column1, column2, column3) VALUES (%s, %s, %s)",
            row
        )

# Commit changes and close connection
conn.commit()
cur.close()
conn.close()

pip install psycopg2-binary pandas

import psycopg2
import pandas as pd
from io import StringIO

# Replace these with your own values
db_name = "your_database_name"
db_user = "your_username"
db_password = "your_password"
db_host = "your_host"
db_port = "your_port"
table_name = "your_table_name"
csv_file_path = "your_csv_file_path.csv"

# Connect to the PostgreSQL database
conn = psycopg2.connect(
    dbname=db_name,
    user=db_user,
    password=db_password,
    host=db_host,
    port=db_port
)

# Read the CSV file using pandas
data = pd.read_csv(csv_file_path)

# Normalize column names to lowercase and replace spaces with underscores
data.columns = [col.lower().replace(" ", "_") for col in data.columns]

# Create a cursor object
cursor = conn.cursor()

# Create a table using the column names from the CSV file
create_table_query = f"CREATE TABLE IF NOT EXISTS {table_name} ({', '.join([f'{col} TEXT' for col in data.columns])});"
cursor.execute(create_table_query)
conn.commit()

# Save the DataFrame to a string buffer in CSV format
buffer = StringIO()
data.to_csv(buffer, index=False, header=False)
buffer.seek(0)

# Copy the data from the buffer to the PostgreSQL table
cursor.copy_expert(f"COPY {table_name} FROM STDIN WITH (FORMAT CSV)", buffer)
conn.commit()

# Close the cursor and connection
cursor.close()
conn.close()