139 lines
4.1 KiB
Python
139 lines
4.1 KiB
Python
#!/usr/bin/env python3
|
|
|
|
import os
|
|
from pymongo import MongoClient
|
|
import yaml
|
|
import json
|
|
import psycopg2
|
|
|
|
class ColumnMeta:
|
|
def __init__(self, name, type, description=None):
|
|
self.name = name
|
|
self.type = type
|
|
self.description = description
|
|
|
|
def from_dict(value: dict):
|
|
return ColumnMeta(name=value['name'], type=value['type'], description=value.get('description'))
|
|
|
|
|
|
class TableMeta:
|
|
def __init__(self, name, columns, description=None):
|
|
self.name = name
|
|
self.columns = columns
|
|
self.description = description
|
|
|
|
|
|
def from_dict(value):
|
|
columns = [ColumnMeta.from_dict(v) for v in value['columns']]
|
|
return TableMeta(name=value['name'], columns=columns, description=value.get('description'))
|
|
|
|
def from_file(path):
|
|
file = open(path, 'r')
|
|
yaml_to_dict = yaml.safe_load(file.read())
|
|
try:
|
|
file.close()
|
|
except Exception:
|
|
pass
|
|
return TableMeta.from_dict(yaml_to_dict)
|
|
|
|
|
|
try:
|
|
connection_string = (
|
|
(
|
|
'postgres://postgres:Optimus0329@192.168.128.7:5432/media_dbsync'
|
|
'?options=-csearch_path%3Ddbo,public,updates'
|
|
)
|
|
)
|
|
pgclient = psycopg2.connect(connection_string)
|
|
pgcursor = pgclient.cursor()
|
|
print(pgclient.get_dsn_parameters(), "\n")
|
|
print("Connected to postgresql database server.")
|
|
pgcursor.execute("SELECT version();")
|
|
record = pgcursor.fetchone()
|
|
print("You are connected to - ", record, "\n")
|
|
except Exception as e:
|
|
print(f"Unable to connect to postgresql database server:{e}\nExitting!")
|
|
exit()
|
|
|
|
|
|
host = '192.168.128.8'
|
|
port = 27017
|
|
print(f"Connecting to mongodb at {host}:{port}")
|
|
|
|
try:
|
|
connection_string = (
|
|
f"mongodb://{host}:{port}"
|
|
)
|
|
mgclient = MongoClient(connection_string)
|
|
mgdb = mgclient['test2']
|
|
except BaseException as e:
|
|
print(f"Unable to connect to mongodb.{e}\nExitting!")
|
|
exit()
|
|
else:
|
|
mgseries = mgdb["series"]
|
|
mgseries.create_index('id', unique=True)
|
|
mgepisodes = mgdb["episodes"]
|
|
mgepisodes.create_index('id', unique=True)
|
|
mgactors = mgdb["actors"]
|
|
mgactors.create_index('id', unique=True)
|
|
mgcharacters = mgdb["characters"]
|
|
mgcharacters.create_index('id', unique=True)
|
|
mgcrew = mgdb["crew"]
|
|
mgcrew.create_index('id', unique=True)
|
|
print(
|
|
f"Connection to mongodb at {host}:"
|
|
f"{port} successful."
|
|
)
|
|
os.chdir('C:\\Projects\\new_dbsync\\db\\ddl')
|
|
# Create the database and tables in PostgreSQL
|
|
# Check if the database exists, if not create it
|
|
# Check if the tables exist, if not create them
|
|
# possibly add , 'genre_ddl.yaml', 'language_ddl.yaml' down the road
|
|
ddl_list = [
|
|
# 'seriesdata_ddl.yaml',
|
|
# 'epdata_ddl.yaml',
|
|
# 'actordata_ddl.yaml',
|
|
# 'castdata_ddl.yaml',
|
|
# 'crewdata_ddl.yaml',
|
|
# 'countrydata_ddl.yaml',
|
|
# 'networkdata_ddl.yaml',
|
|
# 'webchanneldata_ddl.yaml',
|
|
# 'temp_seriesdata_ddl.yaml',
|
|
# 'temp_epdata_ddl.yaml',
|
|
# 'temp_actordata_ddl.yaml',
|
|
# 'temp_castdata_ddl.yaml',
|
|
# 'temp_crewdata_ddl.yaml',
|
|
# 'temp_countrydata_ddl.yaml',
|
|
# 'temp_networkdata_ddl.yaml',
|
|
# 'temp_webchanneldata_ddl.yaml',
|
|
# 'tvupdates_ddl.yaml'
|
|
'db_schema.yaml'
|
|
]
|
|
for ddl in ddl_list:
|
|
path = os.path.join(os.getcwd(), ddl)
|
|
table_meta = TableMeta.from_file(path)
|
|
|
|
columnlist = []
|
|
for column in table_meta.columns:
|
|
columnlist.append(f"{column.name} {column.type}")
|
|
table_meta.columns = ', '.join(columnlist)
|
|
#print(column.name, column.type)
|
|
|
|
sql_command = f"DROP TABLE IF EXISTS {table_meta.name};CREATE TABLE IF NOT EXISTS {table_meta.name} ({table_meta.columns})"
|
|
#sql_command = f"CREATE TABLE IF NOT EXISTS {table_meta.name} ({table_meta.columns})"
|
|
|
|
try:
|
|
pgcursor.execute(sql_command)
|
|
pgclient.commit()
|
|
except Exception as e:
|
|
print(f"Unable to create table {table_meta.name} in postgresql database server:{e}\nExitting!")
|
|
exit()
|
|
#print(sql_command)
|
|
|
|
pgcursor.close()
|
|
pgclient.close()
|
|
print("PostgreSQL connection is closed")
|
|
mgclient.close()
|
|
print("MongoDB connection is closed")
|
|
exit()
|