Altering Columns in a PostGIS enabled Postgres DB with Python

rename-columns.py

#!/usr/bin/env python3

import psycopg2
import json

# The db-config follows this schema.
#
# {
#  "database": {
#    "host": "TARGET_HOSTNAME",
#    "port": TARGET_PORT,
#    "user": "YOUR_ADMIN USER",
#    "password": "YOUR_PASSWORD",
#    "databaseName": "YOUR_DATABASE_NAME"
#  }
#}


def load_database_config():
    with open("./db-config.json") as config_file:
        config = json.load(config_file)
        return config

def create_connection():
    conf = load_database_config();
    try:
        connection = psycopg2.connect(
            host=conf["database"]["host"],
            port=conf["database"]["port"],
            user=conf["database"]["user"],
            password=conf["database"]["password"],
            dbname=conf["database"]["databaseName"]
        )
        return connection
    except Exception as error:
        print("Error connecting to PostgreSQL database:", error)
        return None

def execute_query(connection, query):
    results = None
    try:
        cursor = connection.cursor()
        cursor.execute(query)
        results = cursor.fetchall()
        cursor.close()
    except Exception as error:
        print("Error executing the query:", error)
    return results

def close_connection(connection):
    if connection:
        connection.close()

def get_tables_and_columns(connection):
    query = (
            "SELECT table_name, column_name "
            "FROM information_schema.columns "
            "WHERE table_schema = 'public' "
            "AND table_name NOT IN ("
                "'geometry_columns', "
                "'geography_columns', "
                "'spatial_ref_sys') "
            "ORDER BY table_name;"
    )

    return execute_query(connection, query);

def lower_column_name(column_name, table_name, connection):
    query = (
            f"ALTER TABLE {table_name} "
            f"RENAME COLUMN \"{column_name}\" "
            f"TO {column_name.lower()};"
        );
    
    execute_query(connection, query);
    connection.commit();

def rename_column(new_column_name, old_column_name, table_name, connection):
    query = (
            f"ALTER TABLE {table_name} "
            f"RENAME COLUMN \"{old_column_name}\" TO {new_column_name};"
    );
    execute_query(connection, query);
    connection.commit();

def set_data_type_to_serial(new_column_name, table_name, connection):
    query = (
            f"ALTER TABLE {table_name} "
            f"ALTER COLUMN \"{column_name}\" SET DATA TYPE SERIAL;"
    );
    execute_query(connection, query);
    connection.commit();

def get_data_type(column_name, table_name, connection):
    query = (
        "SELECT data_type "
        "FROM information_schema.columns "
        f"WHERE table_name = '{table_name}' " 
        f"AND column_name = '{column_name}';"
    );
    return execute_query(connection, query);

if __name__ == "__main__":
    connection = create_connection();
    if connection:

        tables_and_columns = get_tables_and_columns(connection);

        for table_and_column in tables_and_columns:
            table_name = table_and_column[0];
            column_name = table_and_column[1];

            # print(f"Information for {table_name} -> {column_name}");

            if not column_name.islower():
                lower_column_name(column_name, table_name, connection);

            if column_name == f"{table_name}_id":
                new_column_name = 'id'
                rename_column(new_column_name, column_name, table_name, connection);
            
            if column_name == f"{table_name}_id" or column_name == "id":
                set_data_type_to_serial(column_name, table_name, connection);
                print(f"{table_name} | {column_name}");
                print(get_data_type(column_name, table_name, connection));    

    close_connection(connection)

Converting data_type to SERIAL cannot work as SERIAL are not "true types."

This is explained in the SERIAL section of the Data Types article within the PostgreSQL Documentation