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
from _database_ganimides_api import dbapi_customer_service_assistant from _database_ganimides_api import dbapi_device from _database_ganimides_api import dbapi_device_log from _database_ganimides_api import dbapi_device_register_unregister from _database_ganimides_api import dbapi_device_usage from _database_ganimides_api import dbapi_get_bank_account_id from _database_ganimides_api import dbapi_get_bank_code from _database_ganimides_api import dbapi_interaction from _database_ganimides_api import dbapi_interaction_accept from _database_ganimides_api import dbapi_interaction_finish from _database_ganimides_api import dbapi_interaction_message from _database_ganimides_api import dbapi_interaction_message_add from _database_ganimides_api import dbapi_interaction_start from _database_ganimides_api import dbapi_merchant from _database_ganimides_api import dbapi_merchant_bankaccount_register from _database_ganimides_api import dbapi_merchant_get_bankaccounts from _database_ganimides_api import dbapi_pointofsale from _database_ganimides_api import dbapi_pointofsale_bankaccount_add from _database_ganimides_api import dbapi_pointofsale_bankaccount_remove from _database_ganimides_api import dbapi_pointofsale_credit_info from _database_ganimides_api import dbapi_retail_store from _database_ganimides_api import dbapi_service_point from _database_ganimides_api import dbapi_subscription from _database_ganimides_api import dbapi_token from _database_ganimides_api import dbapi_token_get_access_token from _database_ganimides_api import dbapi_token_is_valid from _database_ganimides_api import dbapi_user msg = f'module [{module_id}] [[version {module_version}]] loaded.' log_message(msg)
# 1: create the engine # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # database_provider = f"sqlite:///" database_folder_path = f"C:\\Users\\User\\Documents\\my Projects\\Systems_Development\\Development_Environment" database_file_name = f"ganimides.db" database_uri = f"{database_provider}{database_folder_path}\\{database_file_name}" engine = create_engine(database_uri, echo=database_commands_echo) inspector = inspect(engine) table_names = inspector.get_table_names() tables_modeled = len(table_names) msg = f"database [ganimides] [[[[engine created]]]]:[[{database_uri}]] with [{tables_modeled} tables]" if thisApp.CONSOLE_ON: log_message(msg) else: 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) # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #