International Trade Flow

Overview
State Models
IO Javascript
IO Charts

Profile Dev
IO Template
Profile YAML

YAML for SQL table names

The following YAML for the SQL insert resides at profile/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...