def GetTables(host, database, user, password, schema='public'): connection_string = PG_CONN_STR.format(hostName=host, dbName=database, userName=user, password=password) engine = create_engine(connection_string, client_encoding='utf8') conn = engine.connect() metadata = MetaData(bind=engine, schema=schema) metadata.reflect(bind=engine) tables = [] for table in metadata.sorted_tables: # create table sql = str(CreateTable(table, bind=engine)).strip() + ';\n' # comment on table comment = conn.execute( text(SQL_TABLE_COMMENT.format(TableName=table.name))).fetchall() sql += (SQL_COMMENT_ON_TABLE.format( Schema=table.schema, TableName=table.name, Comment=comment[0][2]) + ';\n') if comment[0][2] else '' # create indexes sorted_indexes = sorted(table.indexes, key=lambda ind: ind.name) for index in sorted_indexes: sql += str(CreateIndex(index, bind=engine)) + ';\n' # create triggers triggers = conn.execute( text(SQL_TABLE_TRIGGERS.format(TableName=table.name))).fetchall() for trigger in triggers: sql += trigger[2] + ';\n' tables.append((table.schema, table.name, sql)) return tables
def GetTriggerFunctions(host, database, user, password): try: connection_string = PG_CONN_STR.format(hostName=host, dbName=database, userName=user, password=password ) conn = create_engine(connection_string, client_encoding='utf8').engine.connect() functions = conn.execute(text(SQL_TRIGGER_FUNCTIONS)).fetchall() return functions except Exception: raise finally: conn.close()
def GetViews(host, database, user, password): try: connection_string = PG_CONN_STR.format(hostName=host, dbName=database, userName=user, password=password ) conn = create_engine(connection_string, client_encoding='utf8').engine.connect() views = conn.execute(text(SQL_VIEWS)).fetchall() return views except Exception: raise finally: conn.close()
def GetTriggerFunctions(host, database, user, password): try: connection_string = PG_CONN_STR.format(hostName=host, dbName=database, userName=user, password=password) conn = create_engine(connection_string, client_encoding='utf8').engine.connect() functions = conn.execute(text(SQL_TRIGGER_FUNCTIONS)).fetchall() return functions except Exception: raise finally: conn.close()
def GetTables(host, database, user, password, schema='public'): connection_string = PG_CONN_STR.format(hostName=host, dbName=database, userName=user, password=password ) engine = create_engine(connection_string, client_encoding='utf8') conn = engine.connect() metadata = MetaData(bind=engine, schema=schema) metadata.reflect(bind=engine) tables = [] for table in metadata.sorted_tables: # create table sql = str(CreateTable(table, bind=engine)).strip() + ';\n' # comment on table comment = conn.execute(text(SQL_TABLE_COMMENT.format(TableName=table.name))).fetchall() sql += (SQL_COMMENT_ON_TABLE.format(Schema=table.schema,TableName=table.name,Comment=comment[0][2]) + ';\n') if comment[0][2] else '' # create indexes sorted_indexes = sorted(table.indexes, key=lambda ind: ind.name) for index in sorted_indexes: sql += str(CreateIndex(index, bind=engine)) + ';\n' # create triggers triggers = conn.execute(text(SQL_TABLE_TRIGGERS.format(TableName=table.name))).fetchall() for trigger in triggers: sql += trigger[2] + ';\n' tables.append((table.schema, table.name,sql)) return tables
def GetTypes(host, database, user, password): def GetType(row): TYPE_COMPOSITE = "c" TYPE_ENUM = "e" TYPE_RANGE = "r" #TYPE_EXTERNAL='' ALIGNS = {'c': 'char', 's': 'int2', 'i': 'int4', 'd': 'double'} STORAGES = { 'p': 'PLAIN', 'e': 'EXTERNAL', 'm': 'MAIN', 'x': 'EXTENDED' } typtype = row['typtype'] typinput = row['typinput'] typoutput = row['typoutput'] typreceive = row['typreceive'] typsend = row['typsend'] typmodin = row['typmodin'] typmodout = row['typmodout'] typanalyze = row['typanalyze'] typispreferred = row['typispreferred'] typbyval = row['typbyval'] typdefault = row['typdefault'] element = row['element'] typdelim = row['typdelim'] typlen = row['typlen'] typcategory = row['typcategory'] typalign = ALIGNS[ row['typalign']] if row['typalign'] in ALIGNS else 'unknown' typstorage = STORAGES[ row['typstorage']] if row['typstorage'] in ALIGNS else 'unknown' schemaname = row['schemaname'] typeName = row['alias'] isCollatable = row['typcollation'] == 100 attrs = row['attrs'] sql = 'CREATE TYPE {Schema}."{TypeName}"'.format(Schema=schemaname, TypeName=typeName) if typtype == TYPE_COMPOSITE: sql += " AS\n (" + attrs elif typtype == TYPE_ENUM: raise Exception('TYPE_ENUM not implemented') # sql +=" AS ENUM\n (" elif typtype == TYPE_RANGE: raise Exception('TYPE_RANGE not implemented') # sql += " AS RANGE\n (SUBTYPE=" + rngsubtypestr else: sql += "\n (INPUT=" + typinput + ",\n OUTPUT=" + typoutput if typreceive: sql += ",\n RECEIVE=" + typreceive if typsend: sql += ",\n SEND=" + typsend if typmodin: sql += ",\n TYPMOD_IN=" + typmodin if typmodout: sql += ",\n TYPMOD_OUT=" + typmodout if typanalyze: sql += ",\n ANALYZE=" + typanalyze sql += ",\n CATEGORY=" + typcategory if typispreferred: sql += ",\n PREFERRED=true" if typbyval: sql += ",\n PASSEDBYVALUE" if typdefault: sql += ", DEFAULT=" + typdefault if element: sql += ",\n ELEMENT=" + element + ", DELIMITER='" + typdelim + "'" sql += ",\n INTERNALLENGTH=" + str( typlen) + ", ALIGNMENT=" + typalign + ", STORAGE=" + typstorage if isCollatable: sql += ",\n COLLATABLE=true" sql += ");\n" return (schemaname, typeName.replace('"', ''), sql) try: connection_string = PG_CONN_STR.format(hostName=host, dbName=database, userName=user, password=password) conn = create_engine(connection_string, client_encoding='utf8').engine.connect() rows = conn.execute(text(SQL_TYPES)).fetchall() except Exception: raise finally: conn.close() types = [] for row in rows: types.append(GetType(row)) return types
def GetTypes(host, database, user, password): def GetType(row): TYPE_COMPOSITE="c" TYPE_ENUM="e" TYPE_RANGE="r" #TYPE_EXTERNAL='' ALIGNS = {'c': 'char','s': 'int2','i': 'int4','d': 'double'} STORAGES = {'p': 'PLAIN','e': 'EXTERNAL','m': 'MAIN','x': 'EXTENDED'} typtype = row['typtype'] typinput = row['typinput'] typoutput = row['typoutput'] typreceive = row['typreceive'] typsend = row['typsend'] typmodin = row['typmodin'] typmodout = row['typmodout'] typanalyze = row['typanalyze'] typispreferred = row['typispreferred'] typbyval = row['typbyval'] typdefault = row['typdefault'] element = row['element'] typdelim = row['typdelim'] typlen = row['typlen'] typcategory = row['typcategory'] typalign = ALIGNS[row['typalign']] if row['typalign'] in ALIGNS else 'unknown' typstorage = STORAGES[row['typstorage']] if row['typstorage'] in ALIGNS else 'unknown' schemaname = row['schemaname'] typeName = row['alias'] isCollatable = row['typcollation'] == 100 attrs = row['attrs'] sql = 'CREATE TYPE {Schema}."{TypeName}"'.format(Schema=schemaname, TypeName=typeName) if typtype == TYPE_COMPOSITE: sql += " AS\n (" + attrs elif typtype == TYPE_ENUM: raise Exception('TYPE_ENUM not implemented') # sql +=" AS ENUM\n (" elif typtype == TYPE_RANGE: raise Exception('TYPE_RANGE not implemented') # sql += " AS RANGE\n (SUBTYPE=" + rngsubtypestr else: sql += "\n (INPUT=" + typinput + ",\n OUTPUT=" + typoutput if typreceive: sql += ",\n RECEIVE=" + typreceive if typsend: sql += ",\n SEND=" + typsend if typmodin: sql += ",\n TYPMOD_IN=" + typmodin if typmodout: sql += ",\n TYPMOD_OUT=" + typmodout if typanalyze: sql += ",\n ANALYZE=" + typanalyze sql += ",\n CATEGORY=" + typcategory if typispreferred: sql += ",\n PREFERRED=true" if typbyval: sql += ",\n PASSEDBYVALUE" if typdefault: sql += ", DEFAULT=" + typdefault if element: sql += ",\n ELEMENT=" + element + ", DELIMITER='" + typdelim + "'" sql += ",\n INTERNALLENGTH=" + str(typlen) + ", ALIGNMENT=" + typalign + ", STORAGE=" + typstorage if isCollatable: sql += ",\n COLLATABLE=true" sql +=");\n" return (schemaname, typeName.replace('"','') ,sql) try: connection_string = PG_CONN_STR.format(hostName=host, dbName=database, userName=user, password=password ) conn = create_engine(connection_string, client_encoding='utf8').engine.connect() rows = conn.execute(text(SQL_TYPES)).fetchall() except Exception: raise finally: conn.close() types = [] for row in rows: types.append(GetType(row)) return types