import json import os from dataclasses import dataclass, field from functools import cached_property from pathlib import Path import pyodbc from sqlalchemy import Engine, create_engine @dataclass class DsnConfig: user: str = "sa" password: str = "Mffu3011#" server: str = "LOCALHOST\\GLOBALCUBE" database: str = "CARLO" driver: str = "mssql" schema: str = "import" def conn_ini(self, db_type: str) -> str: return "\n".join( [ f'{db_type}_SERVER="{self.server}"', f'{db_type}_USER="{self.user}"', f'{db_type}_PASSWORD="{self.password}"', f'{db_type}_DATABASE="{self.database}"', ] ) @dataclass class DbCreateConfig: name: str = "CARLO" csv_file: str = "CARLO.csv" clients: dict[str, str] = field(default_factory=lambda: {"1": "M und S Fahrzeughandel GmbH"}) filter: list[str] = (["2018-01-01T00:00:00", "2022-01-01T00:00:00"],) source_dsn: DsnConfig = None dest_dsn: DsnConfig = None temp_db: str = "CARLOX" stage_dir: str = "..\\temp" batch_dir: str = "..\\batch" logs_dir: str = "..\\logs" scripts_dir: str = "C:\\GlobalCube\\Tasks\\scripts" def conn_ini(self) -> str: return "\n".join( [ f'SQL_TEMP="{self.stage_dir}"', f'SQL_BATCH="{self.batch_dir}"', f'SQL_LOGS="{self.logs_dir}"', ] ) class DatabaseInspect: _cursor: pyodbc.Cursor = None _sqlalchemy_engine: Engine = None def __init__(self, dsn: DsnConfig, source=False): self.dsn = dsn self.type = "SOURCE" if source else "DEST" @property def conn_string(self) -> str: if self.dsn.driver == "mssql": return ";".join( [ "Driver={SQL Server Native Client 11.0}", f"Server={self.dsn.server}", f"Database={self.dsn.database}", f"Uid={self.dsn.user}", f"Pwd={self.dsn.password}", ] ) if self.dsn.driver == "mysql": return f"mysql+pymysql://{self.dsn.user}:{self.dsn.password}@{self.dsn.server}/{self.dsn.database}?charset=utf8mb4" return ";".join( [ "Driver={PostgreSQL Unicode}", f"Server={self.dsn.server}", "Port=5432", f"Database={self.dsn.database}", f"Uid={self.dsn.user}", f"Pwd={self.dsn.password}", ] ) # f"DSN={self.dsn.server};UID={self.dsn.user};PWD={self.dsn.password}" @property def conn_string_sqlalchemy(self) -> str: if self.dsn.driver == "mssql": return ( f"mssql+pyodbc://{self.dsn.user}:{self.dsn.password}@{self.dsn.server}/{self.dsn.database}?" "driver=SQL+Server+Native+Client+11.0" ) if self.dsn.driver == "mysql": return f"mysql+pymysql://{self.dsn.user}:{self.dsn.password}@{self.dsn.server}/{self.dsn.database}?charset=utf8mb4" return f"pyodbc://{self.dsn.user}:{self.dsn.password}@{self.dsn.server}/{self.dsn.database}?driver={self.dsn.driver}" @property def bcp_conn_params(self) -> str: return f"-S {self.dsn.server} -d {self.dsn.database} -U {self.dsn.user} -P {self.dsn.password}" @property def cursor(self) -> pyodbc.Cursor: if not self._cursor: self._cursor = self.connect() return self._cursor @property def sqlalchemy_engine(self) -> Engine: if not self._sqlalchemy_engine: self._sqlalchemy_engine = create_engine(self.conn_string_sqlalchemy) return self._sqlalchemy_engine def connect(self) -> pyodbc.Cursor: c = pyodbc.connect(self.conn_string) return c.cursor() @cached_property def tables_list(self) -> list[str]: tables = [x[2] for x in self.cursor.tables(tableType="TABLE")] views = [x[2] for x in self.cursor.tables(tableType="VIEW")] return tables + views def get_prefix(self) -> dict[str, str]: source_tables_prefix = dict( enumerate(sorted(list(set([t.split("$")[0] for t in self.tables_list if "$" in t]))), 1) ) if len(source_tables_prefix) == 0: q = self.cursor.execute("select name FROM sys.databases") source_tables_prefix = [x[0] for x in q.fetchall()] return source_tables_prefix def get_columns(self, table: str) -> list[str]: source_insp_cols = [col.column_name for col in self.cursor.columns(table=table)] if len(source_insp_cols) == 0: q = self.cursor.execute( "SELECT COLUMN_NAME as column_name FROM information_schema.columns " + f"WHERE TABLE_NAME = '{self.convert_table(table)}'" ) source_insp_cols = [col[0] for col in q.fetchall()] return source_insp_cols def get_columns_is_typeof_str(self, table: str) -> list[str]: source_insp_cols = [ col.data_type in [pyodbc.SQL_CHAR, pyodbc.SQL_VARCHAR] for col in self.cursor.columns(table=table) ] if len(source_insp_cols) == 0: q = self.cursor.execute( "SELECT COLLATION_NAME as column_collation FROM information_schema.columns " + f"WHERE TABLE_NAME = '{self.convert_table(table)}'" ) source_insp_cols = [len(col[0]) > 0 for col in q.fetchall()] return source_insp_cols def get_pkey(self, table: str, catalog: str) -> list[str]: source_insp_cols = [col.column_name for col in self.cursor.primaryKeys(table=table, catalog=catalog)] if len(source_insp_cols) == 0: self.cursor.execute(f"USE {catalog}") q = self.cursor.execute( "SELECT COLUMN_NAME " "FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE " "WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1 " f"AND TABLE_NAME = '{self.convert_table(table)}' " # AND TABLE_SCHEMA = 'dbo'" ) source_insp_cols = [col[0] for col in q.fetchall()] self.cursor.execute(f"USE {self.dsn.database}") return source_insp_cols def convert_table(self, table: str) -> str: if "." in table: table = table.split(".")[-1] if "[" in table: table = table[1:-1] return table def load_config(config_file: str) -> DbCreateConfig: cfg_import = json.load(open(config_file, "r", encoding="latin-1")) base_dir = Path(config_file).resolve().parent cfg_import["name"] = Path(config_file).stem if "logs_dir" not in cfg_import: cfg_import["logs_dir"] = "..\\logs" if "scripts_dir" not in cfg_import: cfg_import["scripts_dir"] = "C:\\GlobalCube\\Tasks\\scripts" if "target_dsn" in cfg_import: cfg_import["dest_dsn"] = cfg_import["target_dsn"] del cfg_import["target_dsn"] for folder in ["stage_dir", "batch_dir", "logs_dir", "scripts_dir"]: if cfg_import[folder].startswith(".."): cfg_import[folder] = str(base_dir.joinpath(cfg_import[folder]).resolve()) os.makedirs(cfg_import[folder], exist_ok=True) for folder in ["source", "dest", "diff"]: os.makedirs(cfg_import["stage_dir"] + "\\" + folder, exist_ok=True) cfg_import["source_dsn"] = DsnConfig(**cfg_import["source_dsn"]) cfg_import["dest_dsn"] = DsnConfig(**cfg_import["dest_dsn"]) return DbCreateConfig(**cfg_import) def create_db_ini(cfg: DbCreateConfig) -> None: with open(cfg.scripts_dir + "/../DB.ini", "w", encoding="cp850") as fwh: fwh.write(cfg.conn_ini()) fwh.write("\n\n") fwh.write(cfg.source_dsn.conn_ini("SOURCE")) fwh.write("\n\n") fwh.write(cfg.dest_dsn.conn_ini("DEST")) fwh.write("\n")