YAML for SQL table names

The following YAML for the SQL insert resides at OpenFootprint/impacts/exibase/US-source
Yaml-2-SQL table python from From ChatGPT which resides in the textarea below.
A version of the following resides in our generate_import_factors.py which we are replacing with a direct pull from the Exiobase API.

The previous Exiobase CSV file exports pulled by the US EPA code reside in impacts/exiobase/US-source. Initilly the following was created to use yaml to rename CSV columns when pulling into Supabase. We are aiming to avoid using the US EPA csv files with a new version of the Exiobase pull that works for all countries. import yaml import pandas as pd import sqlalchemy from sqlalchemy import create_engine, Table, MetaData # Load the YAML file with open('create-database.yaml', 'r') as file: config = yaml.safe_load(file) # Create SQL Alchemy Engine engine = create_engine('sqlite:///example.db') metadata = MetaData() # Function to convert column names to CamelCase without underscores def to_camel_case(snake_str): components = snake_str.split('_') return components[0].capitalize() + ''.join(x.title() for x in components[1:]) # Process each table in the YAML configuration for table_name, table_config in config.items(): csv_file = table_config['source'] df = pd.read_csv(csv_file) columns_map = table_config.get('columns', {}) omit_columns = table_config.get('omit', []) # Drop omitted columns df.drop(columns=omit_columns, errors='ignore', inplace=True) # Rename columns as per the YAML configuration df.rename(columns=columns_map, inplace=True) # Generate new column names for remaining columns new_columns = {} for col in df.columns: if col not in columns_map.values(): new_columns[col] = to_camel_case(col) df.rename(columns=new_columns, inplace=True) # Exclude 'Year' column if not explicitly included in columns_map if 'Year' not in columns_map.values() and 'Year' in df.columns: df.drop(columns=['Year'], inplace=True) # Append "US" to the table name table_name = table_name + "US" # Insert/Update the data into the database if engine.dialect.has_table(engine, table_name): # Update existing table temp_table_name = table_name + "_temp" df.to_sql(temp_table_name, engine, if_exists='replace', index=False) with engine.connect() as conn: conn.execute(f""" INSERT OR REPLACE INTO {table_name} SELECT * FROM {temp_table_name} """) conn.execute(f"DROP TABLE {temp_table_name}") else: # Create and insert new table df.to_sql(table_name, engine, if_exists='replace', index=False)

To reduce the size of our resulting SQL databases, the large FlowUUID values will be replaced with FactorID, a 1-based index relating the Factor table to 4 or more tables.

Our Exiobase To Github Pipeline pushes to Github using these steps...