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