import sqlalchemy
import pandas as pd
from copy import deepcopy

def _generate_pool_commands(prefix, data):
    commands = []
    for attributes in data:
        attrs = ""
        for key, value in attributes.items():
            attrs += f' {key}="{value}"'
        
        commands.append(prefix + attrs)
    return commands

def read_meta(engine_dp_1):
    sites = pd.read_sql("SELECT * FROM site;", engine_dp_1).to_dict(orient="records")
    sources = pd.read_sql("SELECT * FROM source;", engine_dp_1).to_dict(orient="records")
    source_types = pd.read_sql("SELECT * FROM source_type;", engine_dp_1).to_dict(orient="records")
    parameters = pd.read_sql("SELECT * FROM parameter;", engine_dp_1).to_dict(orient="records")
    return sites, sources, source_types, parameters

def read_pictures(engine_dp_1):
    pictures = pd.read_sql("SELECT * FROM picture;", engine_dp_1).to_dict(orient="records")
    return pictures

def _map_entities(entities, _id):
    _map={}
    for entity in entities:
        _map[entity[_id]] = entity["name"]
    return _map

def fix_sites(sites):
    new_sites = []
    for site in deepcopy(sites):
        del site["site_id"]
        #renaming fields
        site["x"] = site.pop("coord_x")
        site["y"] = site.pop("coord_y")
        site["z"] = site.pop("coord_z")
        site["country"] = "Switzerland"
        new_sites.append(site)
    return new_sites
        
def fix_parameters(parameters):
    new_parameters = []
    for parameter in deepcopy(parameters):
        del parameter["parameter_id"]
        new_parameters.append(parameter)
    return new_parameters

def fix_source_types(sources, source_types):
    new_source_types = []
    mapped_manufacturer = {}
    for source in sources:
        mapped_manufacturer[source["source_type_id"]] = source["manufacturer"]
    
    for source_type in deepcopy(source_types):
        source_type["manufacturer"] = mapped_manufacturer[source_type["source_type_id"]]
        del source_type["source_type_id"]
        new_source_types.append(source_type)
    return new_source_types
        
def fix_sources(sources, source_types, project_map):
    new_sources = []
    mapped_source_types = _map_entities(source_types, "source_type_id")
    for source in deepcopy(sources):
        for project, _sources in project_map.items():
            if source["name"] in _sources:
                project_name = project
        
        source["project"] = project_name
        source["source_type"] = mapped_source_types[source["source_type_id"]]
        del source["source_id"]
        del source["source_type_id"]
        del source["manufacturer"]
        del source["manufacturing_date"]
        
        new_sources.append(source)
    return new_sources

Specify your database connections

*_1 -> old version
*_2 -> new version

connection_datapool_1 = dict(
    host = "HOST-ADDRESS-OLD",
    user = "datapool",
    database = "datapool",
    password = "YOUR-VERY-SECRET-PASSWORD"
)

connection_datapool_2 = dict(
    host = "HOST-ADDRESS-NEW",
    user = "datapool",
    database = "datapool",
    password = "YOUR-VERY-SECRET-PASSWORD"
)

engine_dp_1 = sqlalchemy.create_engine(
    'postgresql+psycopg2://{user}:{password}@{host}/{database}'.format(
        **connection_datapool_1
    )
)
engine_dp_2 = sqlalchemy.create_engine(
    'postgresql+psycopg2://{user}:{password}@{host}/{database}'.format(
        **connection_datapool_2
    )
)

Script settings

dlz = "dlz"

projects = [
    {
        "title": "KLARA - SBR", 
        "description":"Kleinkläranlagen have a lot of potential." 
    },
    {
        "title": "PVGreen",
        "description":"How do plants influence the efficiency of solar panels"
    }
]

# please fill in your sources, respective to the project!!
project_map = {
    "KLARA - SBR": [
        'source_1',
        'source_2',
        'source_3'
    ],
    "PVGreen": [
        'source_4',
        'source_5',
        'source_6'
    ]
}

Yaml Generation

Reading Data from Datapool1

sites, sources, source_types, parameters = read_meta(engine_dp_1)

pics = read_pictures(engine_dp_1)

Fix up old meta to new fields

dlz = "dlz"

new_sites = fix_sites(sites)

site_commands = _generate_pool_commands(
    f"pool add site {dlz}",
    new_sites
)
print("\n\n".join(site_commands))

new_parameters = fix_parameters(parameters)

parameter_commands = _generate_pool_commands(
    f"pool add parameter {dlz}",
    new_parameters
)
print("\n\n".join(parameter_commands))

new_source_types = fix_source_types(sources, source_types)

source_type_commands = _generate_pool_commands(
    f"pool add source_type {dlz}",
    new_source_types
)
print("\n\n".join(source_type_commands))

new_sources = fix_sources(sources, source_types, project_map)

source_commands = _generate_pool_commands(
    f"pool add source {dlz}",
    new_sources
)
print("\n\n".join(source_commands))