def table_column_ddl(columnObj, tableObj, engine, debug_level=-1): table_name = tableObj.name column_name = columnObj.name # xcolumn_name = str(columnObj.compile(dialect=engine.dialect)).strip() # print(xcolumn_name) # cname = columnObj.key # xcolumn_name = str(tableObj.c[cname].compile(dialect=engine.dialect)).strip() # print(xcolumn_name) column_type = str(columnObj.type.compile(dialect=engine.dialect)).strip() try: column_default = str( columnObj.default.compile(dialect=engine.dialect)).strip() except Exception as e: # print(e) column_default = '' try: column_default = str( columnObj.server_default.compile(dialect=engine.dialect)).strip() except Exception as e: # print(e) column_default = '' try: ddl_obj = ddl.CreateColumn(columnObj) ddl_string = str(ddl_obj.compile(dialect=engine.dialect)).strip() except Exception as e: # print(e) ddl_string = '' if not ddl_string: ddl_string = f"TABLE {table_name} COLUMN {column_name} {column_type} {column_default}" if int(debug_level) > 0: msg = f"table [[{tableObj.name}]] table_column_DDL: [{ddl_string}]" log_message(msg) return ddl_string
def alter_table_column_ddl(columnObj, tableObj, engine, debug_level=-1): table_name = tableObj.name # column_name = columnObj.name # xcolumn_name = str(columnObj.compile(dialect=engine.dialect)).strip() # column_type = str(columnObj.type.compile(dialect=engine.dialect)).strip() # try: # column_default = str(columnObj.default.compile(dialect=engine.dialect)).strip() # except Exception as e: # # if e.message.find('it has no name')<0: # # print(e) # column_default = '' # try: # column_default = str(columnObj.server_default.compile(dialect=engine.dialect)).strip() # except Exception as e: # # if e.message.find('it has no name')<0: # # print(e) # column_default = '' # ddl_string = f"ALTER TABLE {table_name} ALTER COLUMN {column_name} {column_type}" column_ddl_string = table_column_ddl(columnObj, tableObj, engine) #ddl_string = f"ALTER TABLE {table_name} ADD COLUMN {column_name} {column_type}" ddl_string = f"ALTER TABLE {table_name} ALTER COLUMN {column_ddl_string}" if int(debug_level) > 0: msg = f"table [[{tableObj.name}]] alter_table_column_DDL: [{ddl_string}]" log_message(msg) return ddl_string
def get_dbsession(**kwargs): xSession = sessionmaker(bind=engine) xsession = xSession() if 'debug' in kwargs.keys(): debug = kwargs.get('debug', -1) else: debug = kwargs.get('debug_level', -1) debug_level = get_debug_option_as_level(debug) try: session_id = get_session_id() dbsession = db_session_class(engine, xsession, db_schema_models, session_id, debug_level) except: dbsession = None msg = f"FAILED to create database session for [database ganimides]" log_message(msg, msgType='error', msgColor=thisApp.Fore.RED) #exit(0) if dbsession: process_id = kwargs.get('process_msgID', '') if debug_level >= 0: kwargs.update({'debug_level': debug_level}) if not kwargs.get('indent_method'): kwargs.update({'indent_method': 'CALL_LEVEL'}) msg = f"[session] [[{dbsession.session_id}]] [CREATED]" if process_id: msg = msg + '#C0# in #C0#' + process_id log_process_result_message('', 'session', msg, **kwargs) return dbsession
def create_table_ddl(tableObj, engine, debug_level=-1): ddl_obj = ddl.CreateTable(tableObj) ddl_string = str(ddl_obj.compile(dialect=engine.dialect)).strip() if int(debug_level) > 0: msg = f"table [[{tableObj.name}]] create_table_DDL: [{ddl_string}]" log_message(msg) return ddl_string
def drop_table_column_ddl(columnObj, tableObj, engine, debug_level=-1): table_name = tableObj.name column_name = columnObj.name ddl_string = f"ALTER TABLE {table_name} DROP COLUMN {column_name}" if int(debug_level) > 0: msg = f"table [[{tableObj.name}]] drop_table_column_DDL: [{ddl_string}]" log_message(msg) return ddl_string
def clear_table_ddl(tableObj, engine, debug_level=-1): ddl_obj = tableObj.delete() try: ddl_string = str(ddl_obj.compile(dialect=engine.dialect)).strip() except Exception as e: print(e) # ddl_string = '' if int(debug_level) > 0: msg = f"table [[{tableObj.name}]] clear_table_DDL: [{ddl_string}]" log_message(msg) return ddl_string
def drop_table_ddl(tableObj, engine, debug_level=-1): ddl_obj = DropTable(tableObj) try: ddl_string = str(ddl_obj.compile(dialect=engine.dialect)).strip() except Exception as e: # print(e) ddl_string = '' if int(debug_level) > 0: msg = f"table [[{tableObj.name}]] drop_table_DDL: [{ddl_string}]" log_message(msg) return ddl_string
def clear_table(tableInstance, debug=None): debug_level = get_table_debug_level(tableInstance, debug) engine = tableInstance.session.bind.engine tableObj = tableInstance.model.__table__ table_name = tableInstance.model.__tablename__ table_rows = tableInstance.rowCount() ddl_string = clear_table_ddl(tableObj, engine) engine.execute(ddl_string) if int(debug_level) > 0: msg = f"table [[{table_name}]] cleared with [{table_rows} rows]" log_message(msg)
def alter_column(tableInstance, columnObj, debug=None): debug_level = get_table_debug_level(tableInstance, debug) engine = tableInstance.session.bind.engine tableObj = tableInstance.model.__table__ table_name = tableInstance.model.__tablename__ ddl_string = alter_table_column_ddl(columnObj, tableObj, engine, debug_level - 1) # if int(debug_level) > 0: # msg = f"DDL:[{ddl_string}]" # log_message(msg) engine.execute(ddl_string) msg = f"table [[{table_name}]] [column {columnObj.name}] altered with DDL:[{ddl_string}]" if int(debug_level) > 0: log_message(msg)
def check_table(tableInstance, silent=None, debug=None, auto_synchronize=True, synchronization_method='drop-create,recreate,add-columns', copy_records=True): debug_level = get_table_debug_level(tableInstance, debug) engine = tableInstance.session.bind.engine tableObj = tableInstance.model.__table__ metadata = MetaData(bind=engine) table_name = tableInstance.model.__tablename__ table_rows = tableInstance.rowCount() if table_name == 'api_subscriptions': x = 1 compare_with_physical_table(tableInstance, debug_level - 1) if tableInstance.physical_table_is_synchronized: msg = f"table [[{table_name}]] loaded with [{table_rows} rows]" if int(debug_level) > 0 and not silent: log_message(msg) else: msg = f"table [[{table_name}]] with [{table_rows} rows]" if tableInstance.new_columns > 0: msg = msg + f", [[[[{tableInstance.new_columns} new]]]]" if tableInstance.unmapped_columns > 0: msg = msg + f", [[[{tableInstance.unmapped_columns} unmapped]]]" if tableInstance.changed_columns > 0: msg = msg + f", [[[[[{tableInstance.changed_columns} changed]]]]]" if tableInstance.new_columns + tableInstance.unmapped_columns + tableInstance.changed_columns > 1: x = 's' else: x = '' msg = msg + f" column{x}" if not auto_synchronize: msg = msg + " loaded [UnSynchronized]" if debug_level > 0 and not silent: log_message(msg) else: if tableInstance.new_columns == 0 and tableInstance.changed_columns == 0 and synchronization_method.upper( ).find('ADD') >= 0: msg = msg + " loaded [UnSynchronized]" if debug_level > 0 and not silent: log_message(msg) else: if synchronization_method.upper().find('DROP') >= 0: drop_and_create_table(tableInstance, copy_records, debug_level - 1) elif synchronization_method.upper().find('ADD') >= 0: add_columns_to_physical_table(tableInstance, debug_level - 1) elif synchronization_method.upper().find('RECREATE') >= 0: recreate_table(tableInstance, copy_records, debug_level - 1) compare_with_physical_table(tableInstance, debug_level - 1) if tableInstance.physical_table_is_synchronized: msg = msg + f" [Synchronized] (method used:[[[{synchronization_method}]]]) and loaded" else: msg = msg + f" [Synchronized] (method used:[[[{synchronization_method}]]]) and loaded but is [still UnSynchronized]" if int(debug_level) > 0: log_message(msg)
def recreate_tables(dbmodelBase, engine, debug_level=1): tables_before = get_tables_directory(engine) # engine = tableInstance.session.bind.engine # tableObj = tableInstance.model.__table__ # metadata = MetaData(bind=engine) # dbmodel.Base.metadata.create_all(bind=engine) dbmodelBase.metadata.create_all(bind=engine) tables_after = get_tables_directory(engine) ix = 0 for table_name in tables_after: if table_name not in tables_before: ix = ix + 1 if int(debug_level) > 0: msg = f'table [{table_name}] created' log_message(msg) if int(debug_level) > 0: msg = f'[{ix}] tables created in database [{engine}]' log_message(msg)
def drop_table_constraints_ddl(tableObj, engine, debug_level=-1): ddl_text = '' constraints = tableObj.constraints for constraint in constraints: ddl_obj = DropConstraint(constraint) try: ddl_string = str(ddl_obj.compile(dialect=engine.dialect)).strip() except Exception as e: # # if e. str(e.message).find('it has no name')<0: # print(e) ddl_string = '' if ddl_string: if not ddl_text: ddl_text = ddl_string else: ddl_text = ddl_text + '\n' + ddl_string ddl_string = ddl_text if int(debug_level) > 0: msg = f"table [[{tableObj.name}]] drop_table_constraints_DDL: [{ddl_string}]" log_message(msg) return ddl_string
def drop_table_indexes_ddl(tableObj, engine, debug_level=-1): ddl_text = '' indexes = sorted(list(tableObj.indexes), key=lambda k: k.name, reverse=False) for index in indexes: ddl_obj = DropIndex(index) try: ddl_string = str(ddl_obj.compile(dialect=engine.dialect)).strip() except Exception as e: # print(e) ddl_string = '' if ddl_string: if not ddl_text: ddl_text = ddl_string else: ddl_text = ddl_text + '\n' + ddl_string ddl_string = ddl_text if int(debug_level) > 0: msg = f"table [[{tableObj.name}]] drop_table_indexes_DDL: [{ddl_string}]" log_message(msg) return ddl_string
def recreate_table(tableInstance, copy_records=True, debug=None): debug_level = get_table_debug_level(tableInstance, debug) engine = tableInstance.session.bind.engine tableObj = tableInstance.model.__table__ metadata = MetaData(bind=engine) table_name = tableInstance.model.__tablename__ table_rows = tableInstance.rowCount() old_rows = table_rows if copy_records: backup_table_name = tableInstance.model.__tablename__ + '_backup' backup_table_name = copy_table(tableInstance, backup_table_name) if not backup_table_name: msg = '#ERROR#copy to backup failed#RESET#' log_message(msg) return backuptable = Table(backup_table_name, metadata, autoload=True) old_columns = backuptable.columns old_rows = engine.execute( f"select count(*) from {backup_table_name}").scalar() #drop and recreate with the new structure tableObj.drop(engine, checkfirst=True) tableObj.create(engine, checkfirst=True) new_rows = tableInstance.rowCount() if not copy_records: msg = f"table [[{tableInstance.model.__tablename__}]] [recreated] with [[[{new_rows}/{old_rows} rows copied]]]" else: columns = [c.copy() for c in tableObj.columns] #copy data from backup columns_str = '' for column in columns: if column.key in old_columns.keys(): if columns_str: columns_str = columns_str + ' , ' + column.name else: columns_str = column.name from_table = backup_table_name to_table = tableInstance.model.__tablename__ ddl_string = f"INSERT INTO {to_table} ({columns_str}) select {columns_str} from {from_table}" if int(debug_level) > 0: msg = f"table [[{tableInstance.model.__tablename__}]] copy records DDL: [{ddl_string}]" log_message(msg) try: engine.execute(ddl_string) except Exception as e: print(e) new_rows = tableInstance.rowCount() if new_rows == old_rows: backuptable.drop(engine, checkfirst=True) #kill garbages del backuptable # del BACKUPTABLE_TABLE msg = f"table [[{tableInstance.model.__tablename__}]] [recreated] with [[[{new_rows}/{old_rows} rows copied]]] from backup table {backup_table_name}" if int(debug_level) > 0: log_message(msg)
def copy_table(tableInstance, new_table_name, debug=None, overwrite=False): debug_level = get_table_debug_level(tableInstance, debug) engine = tableInstance.session.bind.engine tableObj = tableInstance.model.__table__ metadata = MetaData(bind=engine) table_name = tableInstance.model.__tablename__ table_rows = tableInstance.rowCount() physical_table = Table(table_name, metadata, autoload=True) physical_table.__tablename__ = table_name columns = [c.copy() for c in physical_table.columns] #temp_schema={} # PHYSICAL_TABLE_TABLE = db_table_class(physical_table, temp_schema, engine, tableInstance.session, debug_level - 1) # query_rows1 = PHYSICAL_TABLE_TABLE.rowCount() query_rows1 = engine.execute(f"select count(*) from {table_name}").scalar() # m = MetaData() # m.reflect(engine) # for table in m.tables.values(): # print(table.name) # # for column in table.c: # # print(column.name) if not overwrite: tables_list = get_tables_directory(engine) xnew_table_name = new_table_name ix = 0 while ix <= 99: ix = ix + 1 if xnew_table_name in tables_list: xnew_table_name = new_table_name + '_' + str(ix) else: break new_table_name = xnew_table_name new_table = Table(new_table_name, metadata, *columns) new_table.drop(engine, checkfirst=True) new_table.create(engine, checkfirst=True) new_table = Table(new_table_name, metadata, autoload=True) columns = [c.copy() for c in new_table.columns] # temp_schema={} # NEW_TABLE_TABLE = db_table_class(new_table, temp_schema, engine, tableInstance.session, debug_level - 1) columns_str = '' for column in columns: if columns_str: columns_str = columns_str + ' , ' + column.name else: columns_str = column.name from_table = tableInstance.model.__tablename__ to_table = new_table.name ddl_string = f"INSERT INTO {to_table} ({columns_str}) select {columns_str} from {from_table}" if int(debug_level) > 0: msg = f"table [[{tableInstance.model.__tablename__}]] copy records to [[{to_table}]] DDL: [[[{ddl_string}]]]" log_message(msg) try: engine.execute(ddl_string) except Exception as e: print(e) return False # query_rows2= NEW_TABLE_TABLE.rowCount() query_rows2 = engine.execute(f"select count(*) from {to_table}").scalar() #garbage kill del new_table del physical_table # del PHYSICAL_TABLE_TABLE # del NEW_TABLE_TABLE msg = f"table [[{from_table}]] [copied to ] [[{to_table}]] with [[[{query_rows2}/{query_rows1} rows]]]." if int(debug_level) > 0: log_message(msg) if query_rows1 == query_rows2: return to_table else: return None
APIS = db_table(db_schema, db_session,db_model.API,'apis','api') APPLICATIONS = db_table(db_schema, db_session,db_model.APPLICATION,'applications','application') REGISTERED_APIS = db_table(db_schema, db_session,db_model.APPLICATION_API,'registered apis','registered api') TOKENS = db_table(db_schema, db_session,db_model.TOKEN,'tokens','token') MERCHANTS = db_table(db_schema, db_session,db_model.MERCHANT,'merchants','merchant') POINTS_OF_SALES = db_table(db_schema, db_session,db_model.POINT_OF_SALE,'points_of_sale','point_of_sale') MERCHANT_EMPLOYEES = db_table(db_schema, db_session,db_model.MERCHANT_EMPLOYEE,'merchant_employees','employee') CONSUMERS = db_table(db_schema, db_session,db_model.CONSUMER,'consumers','consumer') BANKS = db_table(db_schema, db_session,db_model.BANK,'banks','bank') BANK_SUBSCRIPTIONS = db_table(db_schema, db_session,db_model.BANK_SUBSCRIPTION,'bank_subscriptions','bank_subscription') BANK_AUTHORIZATIONS = db_table(db_schema, db_session,db_model.BANK_AUTHORIZATION,'bank_authorizations','bank_authorization') BANK_ACCOUNTS = db_table(db_schema, db_session,db_model.BANK_ACCOUNT,'BANK_ACCOUNTS','bank_account') DEVICES = db_table(db_schema, db_session,db_model.DEVICE,'devices','device') DEVICE_USAGE = db_table(db_schema, db_session, db_model.DEVICE_USAGE, 'device_usage', 'device_usage') CLIENT_DEVICES = db_table(db_schema, db_session,db_model.CLIENT_DEVICE,'client_devices','client_device') INTERACTIONS = db_table(db_schema, db_session,db_model.INTERACTION,'interactions','interaction') INTERACTION_MESSAGES = db_table(db_schema, db_session, db_model.INTERACTION_MESSAGE, 'interaction_messages', 'interaction_message') # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # t = 0 r = 0 for table_alias in db_schema: tableObj = db_schema.get(table_alias) if tableObj: t = t + 1 r = r + tableObj.table_rows msg=f"database tables schema [ganimides] loaded with [[{t} tables]] and [{r} rows]" if thisApp.CONSOLE_ON: log_message(msg) else: log_message(msg) # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
'module_is_externally_configurable': False, } module_configuration = {} #:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: module_configuration = retrieve_module_configuration( __file__, module_identityDictionary, module_configuration, print_enabled=thisApp.DEBUG_ON, filelog_enabled=thisApp.FILELOG_ON, handle_as_init=False) #(print_enabled, filelog_enabled, log_file, errors_file,consolelog_enabled)=get_globals_from_configuration(module_configuration) #:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: msg = f"database [ganimides] [[[[engine created]]]]:[[{database_uri}]] with [{tables_modeled} tables]" if thisApp.get_module_debug_level(module_id): log_message(msg) # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 2. import the models # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # import _database_ganimides_model as dbmodel # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 3. create missing tables (use the Base of the model. Base must be declared only once) # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #