def add_multiple_columns(db, schema, table, attrs, types): """ Add new column to a specific table. Parameters ---------- name : str column_name : str column_type : str Example ------- add_multiple_columns(db, ['nyanya', some_integer'], ['text', integer']) """ statements_add = [] attrs_types = zip(attrs, types) for i, j in attrs_types: statements_add.append(' '.join(['ADD COLUMN IF NOT EXISTS', i, j])) statements_merged = ', '.join(statements_add) cmd = "ALTER TABLE IF EXISTS %s.%s %s;" % (schema, table.lower(), statements_merged) for i, j in zip(attrs, types): logger.warn("Adding column %s (%s) to company %s." % (i, j, table.lower())) try: db.execute_cmd(cmd) except Exception as ex: logger.error('[TABLE] %s when executing command %s.' % (ex, cmd))
def handle_failed_type_update(self, failed): if failed is not None: for tuf in failed: name_pg = tuf[0] name_mdb = [ attr for attr in self.attr_details if self.attr_details[attr]["name_cm"] == name_pg][0] type_orig = tuf[1].lower() type_new = self.attr_details[name_mdb]["type_cm"].lower() self.attr_details[name_mdb]["type_cm"] = type_orig logger.warn(""" %s Type conversion is not possible for column '%s'. Skipping conversion %s -> %s.""" % ( CURR_FILE, name_pg, type_orig, type_new))
def check(db, colls_requested): """ Checks if requested collections exist in the database. Gets all collection names from MongoDB (colls_name) and creates a new list which contains only the existing collection names. Parameters ---------- db : pymongo.database.Database Database connection and name colls_requested : list Contains the list of requested collection names. Returns ------- colls_existing : list Contains only existing collection names. Raises ------ ValueError when a requested collection does not exist in the database (MongoDB) Example ------- check(db, ['Car']) [] check(db, ['Region', 'Customer'] ['Region', 'Customer'] """ colls_name = db.collection_names(include_system_collections=False) colls_existing = [] logger.info('[COLLECTION] Checking collection names...') try: for coll in colls_requested: try: colls_name.index(coll) colls_existing.append(coll) except ValueError: logger.warn(""" [COLLECTION] '%s' is not in the Mongo database. Skipping data transfer""" % coll) except Exception as ex: logger.error("[COLLECTION] Checking collection names failed: %s" % ex) return colls_existing
def get_types(docs): types = {} for doc in docs: fields = [] for k, v in doc.items(): name_column = tc.snake_case(k) if name_column in fields: logger.warn("Column %s cannot appear twice. Skipping..." % k, CURR_FILE) continue else: fields.append(name_column) if k not in types.keys(): types[k] = {} value_new, type_pg = tc.get_type_pg(v) if type_pg in types[k].keys(): types[k][type_pg] += 1 else: types[k][type_pg] = 1 return types
def remove_column(db, schema, table, column_name): """ Remove a column from a table. Parameters ---------- db: obj schema: str table: str column_name: str Example ------- remove_column(pg, 'public', 'user', age') """ cmd = "ALTER TABLE IF EXISTS %s.%s DROP COLUMN IF EXISTS %s;" % ( schema, table.lower(), column_name) try: logger.warn(""" [TABLE] Removing column '%s' from table '%s.%s' if exists. """ % (column_name, schema, table.lower())) db.execute_cmd(cmd) except Exception as ex: logger.error('[TABLE] %s when executing command %s.' % (ex, cmd))
def column_change_type(db, schema, table, column_name, column_type): """ Add new column to a specific table. Parameters ---------- name : str column_name : str column_type : str Example ------- column_change_type(pg.db, 'some_integer', 'integer') """ expression = '' if column_type == 'jsonb': expression = 'to_json(%s)' % column_name elif column_type == 'double precision': expression = 'CAST(%s as double precision)' % column_name if len(expression) == 0: cmd = "ALTER TABLE %s.%s ALTER COLUMN %s TYPE %s;" % ( schema, table.lower(), column_name, column_type) else: cmd = "ALTER TABLE %s.%s ALTER COLUMN %s TYPE %s USING %s;" % ( schema, table.lower(), column_name, column_type, expression) logger.warn(""" [TABLE] ALTER TABLE %s, changing type of column '%s' to '%s' """ % (table.lower(), column_name, column_type)) try: db.execute_cmd(cmd) except psycopg2.ProgrammingError as ex: logger.error(""" [TABLE] ProgrammingError: %s when executing command %s. """ % (ex, cmd)) except Exception as ex: logger.error('[TABLE] %s when executing command %s.' % (ex, cmd))
def add_column(db, schema, table, column_name, column_type): """ Add new column to a specific table. Parameters ---------- table : str : name of table to alter column_name : str : name of new column column_type : str : type of new column Example ------- add_column(db, 'some_integer', 'integer') """ cmd = "ALTER TABLE IF EXISTS %s.%s ADD COLUMN IF NOT EXISTS %s %s;" % ( schema, table.lower(), column_name, column_type) logger.warn(""" [TABLE] Adding new column to table: %s, column: %s, type: %s """ % (table.lower(), column_name, column_type)) try: db.execute_cmd(cmd) except Exception as ex: logger.error('[TABLE] %s when executing command %s.' % (ex, cmd))