photon ↔ icms

.gitignore

*.pyc
/.venv/

main.py

import argparse
import json
from typing import Set, Tuple

from sqlalchemy import create_engine, engine
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column

OWNER_KEYS = ('americas', 'chailease', 'demo', 'plus', 'thailand', 'thingnario', 'tsc')
Relation = Set[Tuple[str, str]]


class Base(DeclarativeBase):
    pass


class DeviceLogger(Base):
    __tablename__ = 'DeviceLogger'

    id:       Mapped[int] = mapped_column(primary_key=True)
    plantNo:  Mapped[str]
    loggerNo: Mapped[str]


class PlantsLoggers(Base):
    __tablename__ = 'plants_loggers'

    id:         Mapped[int] = mapped_column(primary_key=True)
    plant_no:   Mapped[str]
    logger_uid: Mapped[str]


def main():
    args = get_args()
    photon_relation = set(collect_photon_relation(args.photon_url))
    print('phtn:', len(photon_relation))
    icms_relation = set(query_icms_relation(args.icms_url))
    print('icms:', len(icms_relation))

    same, only_photon, only_icms = compare_relation_pairs(photon_relation, icms_relation)
    print('same:', len(same))
    print('in phtn only:', len(only_photon))
    dump(only_photon)
    print('in icms only:', len(only_icms))
    dump(only_icms)


def get_args():
    args = argparse.ArgumentParser()
    args.add_argument(
        '--photon-url',
        required=True,
        type=engine.make_url,
    )
    args.add_argument(
        '--icms-url',
        required=True,
        type=engine.make_url,
    )
    return args.parse_args()


def collect_photon_relation(url: engine.URL):
    for owner_key in OWNER_KEYS:
        yield from query_photon_relation(url, owner_key)


def query_photon_relation(url: engine.URL, owner_key: str):
    engine = create_engine(url.set(database=f'solar_PV_{owner_key}'))
    with Session(engine) as session:
        for row in session.query(DeviceLogger).all():
            for plant_no in json.loads(row.plantNo):
                yield plant_no, row.loggerNo


def query_icms_relation(url: engine.URL):
    engine = create_engine(url)
    with Session(engine) as session:
        for row in session.query(PlantsLoggers).all():
            yield row.plant_no, row.logger_uid


def compare_relation_pairs(lhs: Relation, rhs: Relation):
    same = lhs & rhs
    return same, lhs - same, rhs - same


def dump(pairs: Relation):
    for plant_no, logger_id in sorted(pairs):
        print('-', plant_no, logger_id)


if __name__ == '__main__':
    main()

requirements.txt

PyMySQL==1.1.1
SQLAlchemy==2.0.35

result.txt

phtn: 8238
icms: 8239
same: 8227
in phtn only: 11
- DEMO-02-E41 DEMO-02-LOGGER-04
- DEMO-02-E45 DEMO-02-LOGGER-04
- DEMO-02-F51 DEMO-02-LOGGER-05
- DEMO-02-F54 DEMO-02-LOGGER-05
- TH-001 TH-001-LOG-01
- TH001-TW TH001-LOG-01
- TH004-TW TH004-LOG-01
- TH017-TW TH017-TW-LOG
- YCH005 YCH005-LOG
- YCH006 YCH005-LOG
- rd001 rd001-LOG
in icms only: 12
- CY-110070802 CY-110070802-LOG
- CY-110070803 CY-110070802-LOG
- DELTA005 DELTA005-LOG
- ETK017 ETK016-LOG
- ICMS-TEST-01 ICMS-TEST-01-LOG-03
- ICMS-TEST-01 ICMS-TEST-01-LOG-04
- NFC-PT006 NFC-PT006-LOG
- S00013-2-2 S00013-1-1-1-LOG
- S00013-2-2 S00013-1-3-1-LOG
- TP001 TH002-LOG-01
- YCH005 YCH005-LOG-01
- YCH006 YCH005-LOG-01

setup.cfg

[isort]
line_length = 100

[pycodestyle]
max_line_length = 100