def load_engine_tech_specs(vals: Dict[str, str], connector: DbConnector):
"""This function loads the engine-tech-spec details of a vessel into its respective table in the DB
:param vals: the row-tuple containing all the values
:param connector: the class to connect to the DB
"""
connection = connector.get_connection()
ins = EngineTechSpecs.insert().values(**vals)
result = connection.execute(ins)
def load_owner_data(vals: Dict[str, str], connector: DbConnector):
"""This function loads the owner details of a vessel into its respective table in the DB
:param vals: the row-tuple containing all the values
:param connector: the class to connect to the DB
"""
connection = connector.get_connection()
ins = ShipOwner.insert().values(**vals)
result = connection.execute(ins)
class TestPageParser(unittest.TestCase):
def setUp(self) -> None:
self.db_conn = DbConnector()
def test_load_postion_data(self):
test_query = """SELECT COUNT(*)
FROM position_data;"""
total_count = self.db_conn.get_connection().execute(
text(test_query)).fetchall()[0][0]
true_count = 43247
self.assertEqual(total_count, true_count,
f"{true_count-total_count} row(s) missing")
def test_load_ship_engines_data(self):
test_query = """SELECT engine1_id
FROM ship_engines
WHERE SHIP_ID = 'Ship_337' ;"""
test_output = self.db_conn.get_connection().execute(
text(test_query)).fetchall()[0][0]
true_output = "RNX0003702"
self.assertEqual(test_output, true_output)
def test_load_ship_owners_data(self):
test_query = """SELECT COUNT(DISTINCT SHIP_ID)
FROM ship_owner
WHERE owner = 'viking'
GROUP BY owner;"""
test_output = self.db_conn.get_connection().execute(
text(test_query)).fetchall()[0][0]
true_output = 4
self.assertEqual(test_output, true_output)
def load_postion_data(vals: Dict[str, Union[str, int, float]],
connector: DbConnector):
"""This function loads the position details of a vessel into its respective table in the DB
:param vals: the row-tuple containing all the values
:param connector: the class to connect to the DB
"""
connection = connector.get_connection()
upds = PositionData.update().where(
and_(PositionData.c.SHIP_ID == vals["SHIP_ID"],
PositionData.c.TIMESTAMP == vals["TIMESTAMP"])).values(**vals)
result = connection.execute(upds)
if result.rowcount == 0:
ins = PositionData.insert().values(**vals)
result = connection.execute(ins)
def load_engine_data(vals: Dict[str, Union[str, int, float]],
connector: DbConnector):
"""This function loads the engine details of a vessel into its respective table in the DB
:param vals: the row-tuple containing all the values
:param connector: the class to connect to the DB
"""
connection = connector.get_connection()
upds = ShipEngines.update().where(
and_(ShipEngines.c.SHIP_ID == vals["SHIP_ID"],
ShipEngines.c.MMSI == vals["MMSI"])).values(**vals)
result = connection.execute(upds)
if result.rowcount == 0:
ins = ShipEngines.insert().values(**vals)
result = connection.execute(ins)
def setUp(self) -> None:
self.db_conn = DbConnector()
default=False,
help="forces the script to create/replace tables")
return arg_parser.parse_args()
MMSI_IDS = ["269057500", "269057489"]
if __name__ == "__main__":
args = get_args()
logging.info("====Starting pipeline====")
# initialize the connection to the DB and create all the tables
logging.info("Initialising DB")
db_conn = DbConnector()
if args.create_or_replace_tables:
create_all_tables(engine=db_conn.get_engine())
# EXTRACT - from marinetraffic.com API
logging.info(
"Starting extraction jobs for position data from third-party API")
for mmsi in MMSI_IDS:
config = {
"API_KEY": os.getenv("API_KEY"),
"PERIOD": "hourly",
"DAYS": 2,
"FROM_DATE": "2021-03-01 00:00:00",
"TO_DATE": "2021-03-02 23:59:59",
"MMSI": mmsi
}