def save(association_types, db_url=None, connection_func=None): """ persists a list of one (or more) association_type, to the warehouse database Keyword Parameters: association_types -- list of types for association db_url -- String, representing a SQLAlchemy connection (Required, if parameter 'connection' is not provided. connection_func -- function returning SQLAlchemy connections (Optional, if provided, will override db_url) Exceptions: ExecMissingArguments -- raised if neither connection or db_url parameter is specified. ValidateException -- raised when a problem is encountered validating a dto >>> import datetime >>> empty_list = [] >>> save( []) Traceback (most recent call last): ... api.resources.source.warehouse.support.dto_util.ExecMissingArgument """ insert_statement = ('INSERT INTO {schema}.{table} (' ' type_name' ' ,description' ') VALUES (' ' %(name)s' ' ,%(description)s' ')').format(schema=dto_util.SCHEMA, table=TABLE) dto_util.exec_base(association_types, validate, insert_statement, db_url=db_url, connection_func=connection_func)
def save(types, db_url=None, connection_func=None): """ persists a list of one (or more) table types, to the warehouse database Keyword Parameters: types -- list of table types db_url -- String, representing a SQLAlchemy connection (Required, if parameter 'connection' is not provided. connection_func -- function returning SQLAlchemy connections (Optional, if provided, will override db_url) Exceptions: ExecMissingArguments -- raised if neither connection or db_url parameter is specified. ValidateTypeError -- raised when table_type is not a String ValidateUnexpectedValue -- raised when table_type is malformed >>> empty_list = [] >>> save( []) Traceback (most recent call last): ... api.resources.source.warehouse.support.dto_util.ExecMissingArgument """ # write out the types -- first, prepare a statement template = 'INSERT into {schema}.{table} (type_name) values (%s);' insert_statement = template.format(schema=dto_util.SCHEMA, table=TABLE) # next compose a one list of bind parameters, for each row to be inserted insert_binds = [[t] for t in types] # insert! dto_util.exec_base(insert_binds, validate, insert_statement, db_url=db_url, connection_func=connection_func)
def save(variables, db_url=None, connection_func=None): """ persists a list of one (or more) variables, to the warehouse database Keyword Parameters: variables -- list of variables db_url -- String, representing a SQLAlchemy connection (Required, if parameter 'connection' is not provided. connection_func -- function returning SQLAlchemy connections (Optional, if provided, will override db_url) Exceptions: ExecMissingArguments -- raised if neither connection or db_url parameter is specified. ValidateException -- raised when a problem is encountered validating a dto >>> import datetime >>> empty_list = [] >>> save( []) Traceback (most recent call last): ... api.resources.source.warehouse.support.dto_util.ExecMissingArgument """ # prepare statement, to write out the variable metadata insert_statement = ( 'INSERT into {schema}.{variable_table} ( \n' ' column_name \n' ' ,title \n' ' ,table_id \n' ' ,variable_python_type_id \n' ' ,column_type \n' ' ,units \n' ' ,max_length \n' ' ,precision \n' ' ,allowed_values \n' ') values ( \n' ' %(column)s --use the same key name, as the variable dictionary \n' ' ,%(title)s --again, use name of the relevant variable key \n' ' ,(SELECT table_id \n' ' FROM {schema}.{table_table} \n' ' WHERE name=%(table)s ) \n' ' ,(SELECT variable_python_type_id \n' ' FROM {schema}.{python_type_table} \n' ' WHERE constructor_name=%(python_type)s ) \n' ' ,%(physical_type)s \n' ' ,%(units)s \n' ' ,%(max_length)s \n' ' ,%(precision)s \n' ' ,%(allowed_values)s \n' ') \n').format(schema=dto_util.SCHEMA, variable_table=TABLE, table_table=table.TABLE, python_type_table=variable_python_type.TABLE) # insert! -- directly use tables dictionaries for binds dto_util.exec_base(variables, validate, insert_statement, db_url=db_url, connection_func=connection_func)
def save(associations, db_url=None, connection_func=None): """ persists a list of one (or more) table associations, to the warehouse db Keyword Parameters: associations -- list of associations db_url -- String, representing a SQLAlchemy connection (Required, if parameter 'connection' is not provided. connection_func -- function returning SQLAlchemy connections (Optional, if provided, will override db_url) Exceptions: ExecMissingArguments -- raised if neither connection or db_url parameter is specified. ValidateException -- raised when a problem is encountered validating a dto >>> import datetime >>> empty_list = [] >>> save( []) Traceback (most recent call last): ... api.resources.source.warehouse.support.dto_util.ExecMissingArgument """ # prepare statement, to write out the table metadata insert_statement = ( 'INSERT into {schema}.{association_table} ( \n' ' table_id \n' ' ,table_col \n' ' ,parent_table_id \n' ' ,parent_table_col \n' ' ,table_relation_type_id \n' ') values ( \n' ' (SELECT table_id \n' ' FROM {schema}.{table_table} \n' ' WHERE name=%(table)s ) --use association object key names,as the value bind targets \n' ' ,%(column)s --again, use name of relevant key \n' ' ,(SELECT table_id \n' ' FROM {schema}.{table_table} \n' ' WHERE name=%(parent)s ) \n' ' ,%(parent_column)s \n' ' ,(SELECT table_relation_type_id \n' ' FROM {schema}.{association_type_table} \n' ' WHERE type_name=%(type)s ) \n' ')').format(schema=dto_util.SCHEMA, association_table=TABLE, table_table=table.TABLE, association_type_table=association_type.TABLE) # insert! -- directly use associations dictionaries for binds dto_util.exec_base(associations, validate, insert_statement, db_url=db_url, connection_func=connection_func)
def save(authorizations, db_url=None, connection_func=None): """ persists a list of one (or more) authorizations, to the warehouse db Keyword Parameters: authorizations -- list of authorizations db_url -- String, representing a SQLAlchemy connection (Required, if parameter 'connection' is not provided. connection_func -- function returning SQLAlchemy connections (Optional, if provided, will override db_url) Exceptions: ExecMissingArguments -- raised if neither connection or db_url parameter is specified. ValidateException -- raised when a problem is encountered validating a dto >>> import datetime >>> empty_list = [] >>> save([]) Traceback (most recent call last): ... api.resources.source.warehouse.support.dto_util.ExecMissingArgument """ # prepare statement, to write out the authorizations template = """INSERT into {auth_schema}.{authorization_table} ( user_id ,table_id ,nda_signed_date ) values ( %(user_id)s --use the same key name, as the type dictionary ,(SELECT project_id FROM {support_schema}.{project_table} WHERE name=%(project)s ) ,(SELECT contact_id FROM {support_schema}.{table_table} WHERE name=%(table)s AND project_id=(SELECT project_id FROM {support_schema}.{project_table} WHERE name=%(project)s )) ,'1970-01-01' )""" insert_statement = template.format(auth_schema=SCHEMA, authorization_table=TABLE ,support_schema=dto_util.SCHEMA ,project_table=project.TABLE ,table_table=table.TABLE ) # insert! -- directly use authorization dictionaries for binds dto_util.exec_base(authorizations, validate, insert_statement, db_url=db_url ,connection_func=connection_func)
def delete(associations, db_url=None, connection_func=None): """ removes a list of one (or more) table associations, from the warehouse db Keyword Parameters: associations -- list of associations db_url -- String, representing a SQLAlchemy connection (Required, if parameter 'connection' is not provided. connection_func -- function returning SQLAlchemy connections (Optional, if provided, will override db_url) Exceptions: ExecMissingArguments -- raised if neither connection or db_url parameter is specified. ValidateException -- raised when a problem is encountered validating a dto >>> import datetime >>> empty_list = [] >>> delete( []) Traceback (most recent call last): ... api.resources.source.warehouse.support.dto_util.ExecMissingArgument """ # prepare statement, to remove table relation metadata delete_statement = ( 'DELETE FROM {schema}.{association_table} \n' 'WHERE \n' ' table_id = (SELECT table_id \n' ' FROM {schema}.{table_table} \n' ' WHERE name=%(table)s ) \n' ' AND table_col = %(column)s \n' ' AND parent_table_id = (SELECT table_id \n' ' FROM {schema}.{table_table} \n' ' WHERE name=%(parent)s ) \n' ' AND parent_table_col = %(parent_column)s \n' ' AND table_relation_type_id = (SELECT table_relation_type_id \n' ' FROM {schema}.{association_type_table} \n' ' WHERE type_name=%(type)s ) \n' ).format(schema=dto_util.SCHEMA, association_table=TABLE, table_table=table.TABLE, association_type_table=association_type.TABLE) # delete! -- directly use associations dictionaries for binds dto_util.exec_base(associations, validate, delete_statement, db_url=db_url, connection_func=connection_func)
def save(projects, db_url=None, connection_func=None): """ persists a list of one (or more) projects, to the warehouse database Keyword Parameters: projects -- list of projects db_url -- String, representing a SQLAlchemy connection (Required, if parameter 'connection' is not provided. connection_func -- function returning SQLAlchemy connections (Optional, if provided, will override db_url) Exceptions: ExecMissingArguments -- raised if neither connection or db_url parameter is specified. ValidateException -- raised when a problem is encountered validating a dto >>> import datetime >>> empty_list = [] >>> save( []) Traceback (most recent call last): ... api.resources.source.warehouse.support.dto_util.ExecMissingArgument """ # prepare statement, to write out the project metadata template = ('INSERT into {schema}.{table} ( \n' ' name \n' ' ,title \n' ' ,inport_data_set_id \n' ' ,csw_uuid \n' ' ) values ( \n' ' %(name)s --use the same key name, as the type dictionary \n' ' ,%(title)s \n' ' ,%(inport_id)s \n' ' ,%(uuid)s \n' ' )') insert_statement = template.format(schema=dto_util.SCHEMA, table=TABLE) # insert! -- directly use projects dictionaries for binds dto_util.exec_base(projects, validate, insert_statement, db_url=db_url, connection_func=connection_func)
def update_by_table_column(table_name, column, variable, db_url=None, connection_func=None): """ updates a variable (identified by name) in the warehouse database Keyword Parameters: table_name -- String, table which the variable to update belongs to column -- String, representing the column of the variable to update variable -- Dict, representing the new variable values (May change the table and/or column name!) db_url -- String, representing a SQLAlchemy connection (Required, if parameter 'connection' is not provided. connection_func -- function returning SQLAlchemy connections (Optional, if provided, will override db_url) Exceptions: ExecMissingArguments -- raised if neither connection or db_url parameter is specified. ValidateException -- raised when a problem is encountered validating a dto >>> var1 = { 'column':'frob_hz', 'title':'Frobniz Resonance (Hz)' ... ,'python_type': 'float', 'physical_type': 'numeric' ... ,'table': 'some_table', 'max_length': 147456 ... ,'units': 'hertz', 'precision': 16383 ... ,'description': 'A useful value!' ... ,'allowed_values': '1+e-16383 - 9e147456'} >>> update_by_table_column('some_table', 'frob_hz', var1) Traceback (most recent call last): ... api.resources.source.warehouse.support.dto_util.ExecMissingArgument """ # prepare statement, to update table metadata template = """UPDATE {schema}.{table} SET column_name = %(column)s ,title = %(title)s ,description = %(desecription)s ,table_id = (SELECT table_id FROM {schema}.{table_table} WHERE name=%(table)s ) ,variable_python_type_id = ( SELECT variable_python_type_id FROM {schema}.{python_type_table} WHERE constructor_name=%(python_type)s ) ,column_type = %(physical_type)s ,units = %(units)s ,max_length = %(max_length)s ,precision = %(precision)s ,allowed_values = %(allowed_values)s WHERE column_name = %(arg_column)s AND table_id = (SELECT table_id FROM {schema}.{table_table} WHERE name=%(arg_table_name)s )""" update_statement = template.format( schema=dto_util.SCHEMA, table=TABLE, table_table=table.TABLE, python_type_table=variable_python_type.TABLE) # update! -- directly use tables dictionary for binds dto_util.exec_base([variable], validate, update_statement, db_url=db_url, connection_func=connection_func, additional_binds={ 'arg_table_name': table_name, 'arg_column': column })
def update_by_table_column(table_name, column_name, association, db_url=None, connection_func=None): """ updates one association,referenced by the table & its lookup field Keyword Parameters: table_name -- String, representing the name of the association Table column_name -- String, representing name of table_name's association Column association -- DWSupport association Data Transfer Object defining the new association values (may *change* table_name+column_name) db_url -- String, representing a SQLAlchemy connection (Required, if parameter 'connection' is not provided. connection_func -- function returning SQLAlchemy connections (Optional, if provided, will override db_url) Exceptions: ExecMissingArguments -- raised if neither connection or db_url parameter is specified. ValidateException -- raised when a problem is encountered validating a dto >>> import datetime >>> assoc1 = {'parent': 'depth_dim', 'parent_column': 'depth_whid', \ 'table': 'catch_fact', 'column': 'depth_whid', 'type':'fact dimension'} >>> update_by_table_column('any_table', 'any_column', assoc1) Traceback (most recent call last): ... api.resources.source.warehouse.support.dto_util.ExecMissingArgument """ # prepare statement, to update the table metadata update_statement = ( 'UPDATE {schema}.{association_table} SET \n' ' table_id = (SELECT table_id --use name of relevant key \n' ' FROM {schema}.{table_table} \n' ' WHERE name=%(table)s ) \n' ' ,table_col = %(column)s \n' ' ,parent_table_id = (SELECT table_id \n' ' FROM {schema}.{table_table} \n' ' WHERE name=%(parent)s ) \n' ' ,parent_table_col = %(parent_column)s\n' ' ,table_relation_type_id = (SELECT table_relation_type_id \n' ' FROM {schema}.{association_type_table} \n' ' WHERE type_name=%(type)s ) \n' ' ,aud_beg_dtm = current_timestamp \n' 'WHERE \n' ' table_id = (SELECT table_id --use name of relevant key \n' ' FROM {schema}.{table_table} \n' ' WHERE name=%(arg_table_name)s ) \n' ' AND table_col = %(arg_column_name)s \n').format( schema=dto_util.SCHEMA, association_table=TABLE, table_table=table.TABLE, association_type_table=association_type.TABLE) # update! -- directly use association dictionarie for binds dto_util.exec_base([association], validate, update_statement, db_url=db_url, connection_func=connection_func, additional_binds={ 'arg_table_name': table_name, 'arg_column_name': column_name })
def update_by_name(name, table, db_url=None, connection_func=None): """ updates a table (identified by name) in the warehouse database Keyword Parameters: name -- String, name of the table to update table -- Dict, representing the new table values (May change the name!) db_url -- String, representing a SQLAlchemy connection (Required, if parameter 'connection' is not provided. connection_func -- function returning SQLAlchemy connections (Optional, if provided, will override db_url) Exceptions: ExecMissingArguments -- raised if neither connection or db_url parameter is specified. ValidateException -- raised when a problem is encountered validating a dto >>> import datetime >>> tab1 = { 'name':'catch_fact', 'type':'fact' ... ,'updated': datetime.datetime(2016, 1, 1) ... ,'rows': 999, 'project': 'FRAM Trawl Survey' ... ,'contact': 'Name: Awesome Pat <*****@*****.**>' ... ,'years': '1971-1972, 1989, 2016', 'selectable': True ... ,'inport_id': None, 'description': None, 'uuid': None ... ,'inport_replacement_project_id': None ... ,'title': 'Survey Catch', 'update_frequency': 'continually' ... ,'restriction': 'otherRestrictions', 'usage_notice': None ... ,'keywords': None, 'bounds': '80.0, -110.5, 60.06, -134' ... ,'confidential': False} >>> update_by_name('any_table', tab1) Traceback (most recent call last): ... api.resources.source.warehouse.support.dto_util.ExecMissingArgument """ # prepare statement, to update table metadata template = """UPDATE {schema}.{table} SET name = %(name)s ,data_years = %(years)s ,table_type_id = (SELECT table_type_id FROM {schema}.{type_table} WHERE type_name=%(type)s ) ,num_rows = %(rows)s ,project_id = (SELECT project_id FROM {schema}.{project_table} WHERE name=%(project)s ) ,contact_id = (SELECT contact_id FROM {schema}.{contact_table} WHERE info=%(contact)s ) ,aud_beg_dtm = CURRENT_TIMESTAMP ,is_selectable = %(selectable)s ,inport_entity_id = %(inport_id)s ,inport_replacement_project_id = %(inport_replacement_project_id)s ,description = %(description)s ,title = %(title)s ,csw_uuid = %(uuid)s ,table_update_frequency_id = ( SELECT table_update_frequency_id FROM {schema}.table_update_frequency WHERE iso_maintenance_update_code=%(update_frequency)s ) ,table_use_constraint_id = (SELECT table_use_constraint_id FROM {schema}.table_use_constraint WHERE gmd_code=%(restriction)s ) ,usage_notice = %(usage_notice)s ,keywords = %(keywords)s ,north_bound = %(north_bound)s ,east_bound = %(east_bound)s ,south_bound = %(south_bound)s ,west_bound = %(west_bound)s WHERE name = %(arg_table_name)s """ update_statement = template.format(schema=dto_util.SCHEMA, table=TABLE ,type_table=table_type.TABLE ,project_table=project.TABLE ,contact_table=contact.TABLE ) # update! -- directly use tables dictionary for binds dto_util.exec_base( [table], _validate_and_split_bounds, update_statement, db_url=db_url ,connection_func=connection_func ,additional_binds={'arg_table_name': name})
def save(tables, db_url=None, connection_func=None): """ persists a list of one (or more) tables, to the warehouse database Keyword Parameters: tables -- list of tables db_url -- String, representing a SQLAlchemy connection (Required, if parameter 'connection' is not provided. connection_func -- function returning SQLAlchemy connections (Optional, if provided, will override db_url) Exceptions: ExecMissingArguments -- raised if neither connection or db_url parameter is specified. ValidateException -- raised when a problem is encountered validating a dto >>> import datetime >>> empty_list = [] >>> save( []) Traceback (most recent call last): ... api.resources.source.warehouse.support.dto_util.ExecMissingArgument """ # prepare statement, to write out the table metadata template = """INSERT into {schema}.{table} ( name ,project_id ,contact_id ,data_years ,table_type_id ,aud_beg_dtm ,is_selectable ,inport_entity_id ,inport_replacement_project_id ,description ,num_rows ,title ,csw_uuid ,table_update_frequency_id ,table_use_constraint_id ,usage_notice ,keywords ,north_bound ,east_bound ,south_bound ,west_bound ,is_sensitive ) values ( %(name)s --use the same key name, as the type dictionary ,(SELECT project_id FROM {schema}.{project_table} WHERE name=%(project)s ) ,(SELECT contact_id FROM {schema}.{contact_table} WHERE info=%(contact)s ) ,%(years)s ,(SELECT table_type_id FROM {schema}.{type_table} WHERE type_name=%(type)s ) --again, use name of relevant key ,%(updated)s ,%(selectable)s ,%(inport_id)s ,%(inport_replacement_project_id)s ,%(description)s ,%(rows)s ,%(title)s ,%(uuid)s ,(SELECT table_update_frequency_id FROM {schema}.table_update_frequency WHERE iso_maintenance_update_code=%(update_frequency)s ) ,(SELECT table_use_constraint_id FROM {schema}.table_use_constraint WHERE gmd_code=%(restriction)s ) ,%(usage_notice)s ,%(keywords)s ,%(north_bound)s ,%(east_bound)s ,%(south_bound)s ,%(west_bound)s ,%(confidential)s )""" insert_statement = template.format(schema=dto_util.SCHEMA, table=TABLE ,type_table=table_type.TABLE ,project_table=project.TABLE ,contact_table=contact.TABLE ) # postgresql can't insert NULL into non-null fields (even with defaults) for t in tables: if t['rows'] is None: t['rows'] = -1 # rows: Table might not even exist, yet if t['updated'] is None: t['updated'] = datetime.now() #multiple insert statements is too hard if t['project'] is None: t['project'] = 'warehouse' # DB default, is to use the warehouse if t['contact'] is None: t['contact'] = 'Name: FRAM Data Team <*****@*****.**>' if t['update_frequency'] is None: t['update_frequency'] = 'continual' # insert! -- directly use tables dictionaries for binds dto_util.exec_base( tables, _validate_and_split_bounds, insert_statement, db_url=db_url ,connection_func=connection_func)