import csv
import psycopg2
import os
import re

data_dir = "path/to/files"
data_files = [
    ('table1', 'table1.csv'),
    ('table2', 'table2.csv')
]

with psycopg2.connect(dbname="postgres", user="postgres", password="postgres", host="localhost") as conn:
    with conn.cursor() as cur:
        for table_name, file_name in data_files:
            # Loop through each CSV File
            csv_file = os.path.join(data_dir, file_name)

            with open(csv_file, newline='') as f:
                # Read in CSV Data to get headers:
                csv_data = csv.DictReader(f)

                # Format headers: Convert spaces to underscores, lowercase and remove anything not alpha-numeric or underscore:
                fieldnames = [re.sub(r"[^a-z0-9_]", "", i.strip().lower().replace(" ", "_")) for i in
                              csv_data.fieldnames]

                # Reset file position
                f.seek(0, 0)

                # Drop previous table
                cur.execute(f"drop table if exists {table_name};")

                # Recreate the table with current field names
                cur.execute("create table {table_name}({field_names} text)".format(
                    table_name=table_name,
                    field_names=" text,".join(fieldnames)))

                # Add the data
                cur.copy_expert(f"COPY {table_name} FROM STDIN WITH CSV HEADER", f)