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)
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
