def test_add_sdm(self): # Test non existing raster null_path = os.path.join(NIAMOTO_HOME, "NULL.tif") self.assertRaises(FileNotFoundError, SSDMManager.add_sdm, 1038, null_path, tile_dimension=(200, 200)) # Test wrong taxon id self.assertRaises( NoRecordFoundError, SSDMManager.add_sdm, -1, TEST_SDM_1038, ) # Test existing raster SSDMManager.add_sdm( 1038, TEST_SDM_1038, ) df = SSDMManager.get_raster_list() self.assertEqual(len(df), 1) self.assertEqual(df['name'].iloc[0], 'species_{}'.format(1038)) engine = Connector.get_engine() inspector = Inspector.from_engine(engine) self.assertIn( 'species_{}'.format(1038), inspector.get_table_names(schema=settings.NIAMOTO_SSDM_SCHEMA), )
def truncate(self, cascade=False, connection=None): """ Truncate an existing dimension (i.e. drop every row) :param connection: If not None, use an existing connection. :param cascade: If True, TRUNCATE CASCADE. """ LOGGER.debug("Start Truncate {}".format(self)) close_after = False if connection is None: connection = Connector.get_engine().connect() close_after = True if not self.is_created(connection): m = "The dimension {} does not exists in database." \ " Truncate will be aborded" LOGGER.warning(m.format(self.name)) return with connection.begin(): sql = "TRUNCATE {}".format("{}.{}".format( settings.NIAMOTO_DIMENSIONS_SCHEMA, self.name)) if cascade: sql += " CASCADE" connection.execute(sql) if close_after: connection.close() LOGGER.debug("{} successfully truncated".format(self))
def create_dimension(self, connection=None): """ Create the dimension in database. :param connection: If not None, use an existing connection. """ LOGGER.debug("Creating {}".format(self)) close_after = False if connection is None: connection = Connector.get_engine().connect() close_after = True if self.is_created(connection): m = "The dimension {} already exists in database. Creation will " \ "be skipped." LOGGER.warning(m.format(self.name)) return with connection.begin(): self.table.create(connection) ins = meta.dimension_registry.insert().values({ 'name': self.name, 'dimension_type_key': self.get_key(), 'label_column': self.label_col, 'date_create': datetime.now(), 'properties': self.properties, }) connection.execute(ins) if close_after: connection.close() LOGGER.debug("{} successfully created".format(self))
def test_update_raster(self): # Add raster test_raster = os.path.join(NIAMOTO_HOME, "data", "raster", "rainfall_wgs84.tif") RasterManager.add_raster( "rainfall", test_raster, tile_dimension=(200, 200), ) # Update raster RasterManager.update_raster( "rainfall", test_raster, new_name="rainfall_new", tile_dimension=(100, 100), ) df = RasterManager.get_raster_list() engine = Connector.get_engine() inspector = Inspector.from_engine(engine) self.assertIn( 'rainfall_new', inspector.get_table_names(schema=settings.NIAMOTO_RASTER_SCHEMA), ) self.assertNotIn( 'rainfall', inspector.get_table_names(schema=settings.NIAMOTO_RASTER_SCHEMA), ) # Update raster, only properties RasterManager.update_raster("rainfall_new", properties={'test': 10})
def populate(self, dataframe): """ Populates the fact table. Assume that the input dataframe had been correctly formatted to fit the fact table columns. All the null measure are set to 0 before populating. :param dataframe: The dataframe to populate from. """ LOGGER.debug("Populating {}".format(self)) cols = [c.name for c in self.columns] s = io.StringIO() dataframe[cols].fillna(value=0).to_csv(s, columns=cols, index=False) s.seek(0) sql_copy = \ """ COPY {}.{} ({}) FROM STDIN CSV HEADER DELIMITER ','; """.format( settings.NIAMOTO_FACT_TABLES_SCHEMA, self.name, ','.join(cols) ) raw_connection = Connector.get_engine().raw_connection() cur = raw_connection.cursor() cur.copy_expert(sql_copy, s) cur.close() raw_connection.commit() raw_connection.close() LOGGER.debug("{} successfully populated".format(self))
def test_add_vector(self): # Test non existing raster null_path = os.path.join(NIAMOTO_HOME, "NULL.shp") self.assertRaises( FileNotFoundError, VectorManager.add_vector, "null_vector", null_path, ) VectorManager.add_vector("ncl_adm1", SHP_TEST) self.assertRaises( RecordAlreadyExistsError, VectorManager.add_vector, "ncl_adm1", SHP_TEST, ) df = VectorManager.get_vector_list() self.assertEqual(len(df), 1) self.assertEqual(df['name'].iloc[0], 'ncl_adm1') engine = Connector.get_engine() inspector = Inspector.from_engine(engine) self.assertIn( 'ncl_adm1', inspector.get_table_names(schema=settings.NIAMOTO_VECTOR_SCHEMA), )
def create_fact_table(self, connection=None): """ Create the fact table in database. :param connection: If not None, use an existing connection. """ close_after = False if connection is None: connection = Connector.get_engine().connect() close_after = True if self.is_created(connection): m = "The fact table {} already exists in database. Creation " \ "will be skipped." LOGGER.warning(m.format(self.name)) return with connection.begin(): self.table.create(connection) ins = meta.fact_table_registry.insert().values({ 'name': self.name, 'date_create': datetime.now(), 'properties': self.properties, }) connection.execute(ins) if close_after: connection.close()
def load_data_provider(name, *args, connection=None, **kwargs): BaseDataProvider.assert_data_provider_exists(name) sel = select([ data_provider.c.id, data_provider.c.name, data_provider.c.provider_type_key.label('provider_type'), ]).where(data_provider.c.name == name) # Look for args that must be set None none_values = [ None, 'none', 'None', '0', 'n', 'N', ] nargs = [None if i in none_values else i for i in args] close_after = False if connection is None: close_after = True connection = Connector.get_engine().connect() r = connection.execute(sel) record = r.fetchone() name = record.name type_key = record.provider_type provider = PROVIDER_REGISTRY[type_key]['class'](name, *nargs, **kwargs) if close_after: connection.close() return provider
def _unregister_unique_synonym_key_constraint(cls, synonym_key, bind=None): synonym_col = meta.taxon.c.synonyms[synonym_key] index = Index("{}_unique_synonym_key".format(synonym_key), synonym_col, unique=True, postgresql_where=(synonym_col != 'null')) meta.taxon.indexes.remove(index) if bind is None: bind = Connector.get_engine() index.drop(bind) LOGGER.debug("{}_unique_synonym_key unregistered.".format(synonym_key))
def test_delete_vector(self): VectorManager.add_vector('ncl_adm1', SHP_TEST) VectorManager.delete_vector('ncl_adm1') df = VectorManager.get_vector_list() self.assertNotIn('ncl_adm1', list(df['name'])) engine = Connector.get_engine() inspector = Inspector.from_engine(engine) self.assertNotIn( 'ncl_adm1', inspector.get_table_names(schema=settings.NIAMOTO_VECTOR_SCHEMA), ) self.assertRaises(NoRecordFoundError, VectorManager.delete_vector, 'ncl_adm1')
def test_delete_raster(self): SSDMManager.add_sdm( 1038, TEST_SDM_1038, ) self.assertEqual(len(SSDMManager.get_sdm_list()), 1) SSDMManager.delete_sdm(1038) self.assertEqual(len(SSDMManager.get_sdm_list()), 0) engine = Connector.get_engine() inspector = Inspector.from_engine(engine) self.assertNotIn( 'species_{}'.format(1038), inspector.get_table_names(schema=settings.NIAMOTO_SSDM_SCHEMA), )
def is_created(self, connection=None): """ :param connection: If not None, use an existing connection. :return: True if the fact table exists in database. """ close_after = False if connection is None: connection = Connector.get_engine().connect() close_after = True inspector = Inspector.from_engine(connection) tables = inspector.get_table_names( schema=settings.NIAMOTO_FACT_TABLES_SCHEMA) if close_after: connection.close() return self.name in tables
def _register_unique_synonym_key_constraint(cls, synonym_key, bind=None): """ :param bind: If passed, use an existing connection or engine instead of creating a new one. """ synonym_col = meta.taxon.c.synonyms[synonym_key] index = Index("{}_unique_synonym_key".format(synonym_key), synonym_col, unique=True, postgresql_where=(synonym_col != 'null')) if bind is None: bind = Connector.get_engine() index.create(bind) meta.taxon.indexes.remove(index) LOGGER.debug("{}_unique_synonym_key registered.".format(synonym_key))
def setUpClass(cls): engine = Connector.get_engine() meta.metadata.create_all(engine, tables=[ meta.occurrence, meta.plot, meta.plot_occurrence, meta.data_provider, meta.taxon, meta.synonym_key_registry, meta.raster_registry, meta.vector_registry, meta.dimension_registry, meta.fact_table_registry, meta.sdm_registry, ])
def unregister_all_synonym_keys(cls, exclude=[], bind=None): """ Unregister all the synonym keys from database. :param exclude: A list of synonym keys to exclude. :param bind: If passed, use and existing engine or connection. """ close_after = False if bind is None: close_after = True bind = Connector.get_engine().connect() identity = cls.IDENTITY_SYNONYM_KEY for synonym_key in cls.get_synonym_keys()['name']: if synonym_key not in exclude and synonym_key != identity: cls.unregister_synonym_key(synonym_key, bind=bind) LOGGER.debug("All synonym_key records unregistered.") if close_after: bind.close()
def delete_raster(cls, name, connection=None): """ Delete an existing raster. :param name: The name of the raster. :param connection: If provided, use an existing connection. """ cls.assert_raster_exists(name) close_after = False if connection is None: close_after = True connection = Connector.get_engine().connect() with connection.begin(): connection.execute("DROP TABLE IF EXISTS {};".format( "{}.{}".format(cls.DB_SCHEMA, name))) del_stmt = cls.REGISTRY_TABLE.delete().where( cls.REGISTRY_TABLE.c.name == name) connection.execute(del_stmt) if close_after: connection.close()
def truncate(self, connection=None): """ Truncate an existing fact table (i.e. drop every row). :param connection: If not None, use an existing connection. """ LOGGER.debug("Start Truncate {}".format(self)) close_after = False if connection is None: connection = Connector.get_engine().connect() close_after = True if not self.is_created(connection): m = "The fact table {} does not exists in database." \ " Truncate will be aborded" LOGGER.warning(m.format(self.name)) return with connection.begin(): connection.execute("TRUNCATE {}".format("{}.{}".format( settings.NIAMOTO_FACT_TABLES_SCHEMA, self.name))) if close_after: connection.close() LOGGER.debug("{} successfully truncated".format(self))
def delete_vector(cls, name, connection=None): """ Delete an existing vector. :param name: The name of the vector. :param connection: If provided, use an existing connection. """ LOGGER.debug("VectorManager.delete_vector(connection={})".format( str(connection))) cls.assert_vector_exists(name) close_after = False if connection is None: close_after = True connection = Connector.get_engine().connect() with connection.begin(): connection.execute("DROP TABLE IF EXISTS {};".format( "{}.{}".format(settings.NIAMOTO_VECTOR_SCHEMA, name))) del_stmt = meta.vector_registry.delete().where( meta.vector_registry.c.name == name) connection.execute(del_stmt) if close_after: connection.close()
def populate(self, dataframe, append_ns_row=True): """ Populates the dimension. Assume that the input dataframe had been correctly formatted to fit the dimension columns. All the null values are set to the corresponding type NS before populating. :param dataframe: The dataframe to populate from. :param append_ns_row: If True, append a NS row to the dimension. """ LOGGER.debug("Populating {}".format(self)) cols = [c.name for c in self.columns] s = io.StringIO() ns = {} for c in self.columns: if type(c.type) in self.NS_VALUES: ns[c.name] = self.NS_VALUES[type(c.type)] else: ns[c.name] = self.DEFAULT_NS_VALUE dataframe[cols].fillna(value=ns).to_csv(s, columns=cols) if append_ns_row: idx = 0 if len(dataframe.index) > 0: idx = dataframe.index.max() + 1 ns_row = pd.DataFrame(ns, index=[idx]) ns_row[cols].to_csv(s, columns=cols, header=False) s.seek(0) sql_copy = \ """ COPY {}.{} ({}) FROM STDIN CSV HEADER DELIMITER ','; """.format( settings.NIAMOTO_DIMENSIONS_SCHEMA, self.name, ','.join([self.PK_COLUMN_NAME] + cols) ) raw_connection = Connector.get_engine().raw_connection() cur = raw_connection.cursor() cur.copy_expert(sql_copy, s) cur.close() raw_connection.commit() raw_connection.close() LOGGER.debug("{} successfully populated".format(self))
def test_update_vector(self): # Add raster VectorManager.add_vector('ncl_adm1', SHP_TEST) # Update raster VectorManager.update_vector('ncl_adm1', SHP_TEST, new_name='ncl') VectorManager.update_vector('ncl', SHP_TEST) self.assertRaises( NoRecordFoundError, VectorManager.update_vector, 'ncl_adm1', SHP_TEST, ) null_path = os.path.join(NIAMOTO_HOME, "NULL.shp") self.assertRaises( FileNotFoundError, VectorManager.add_vector, "ncl_bis", null_path, ) VectorManager.add_vector('ncl_adm', SHP_TEST) self.assertRaises(RecordAlreadyExistsError, VectorManager.update_vector, 'ncl', SHP_TEST, new_name='ncl_adm') df = VectorManager.get_vector_list() self.assertIn('ncl', list(df['name'])) engine = Connector.get_engine() inspector = Inspector.from_engine(engine) self.assertIn( 'ncl', inspector.get_table_names(schema=settings.NIAMOTO_VECTOR_SCHEMA), ) self.assertNotIn( 'ncl_adm1', inspector.get_table_names(schema=settings.NIAMOTO_VECTOR_SCHEMA), ) VectorManager.update_vector('ncl', )
def drop_dimension(self, connection=None): """ Drop an existing dimension. :param connection: If not None, use an existing connection. """ LOGGER.debug("Dropping {}".format(self)) close_after = False if connection is None: connection = Connector.get_engine().connect() close_after = True if not self.is_created(connection): m = "The dimension {} does not exists in database. Drop will " \ "be skipped" LOGGER.warning(m.format(self.name)) return with connection.begin(): self.table.drop(connection) delete = meta.dimension_registry.delete().where( meta.dimension_registry.c.name == self.name) connection.execute(delete) if close_after: connection.close() LOGGER.debug("{} successfully dropped".format(self))
def tearDownClass(cls): engine = Connector.get_engine() meta.metadata.drop_all(engine) with Connector.get_connection() as connection: inspector = Inspector.from_engine(connection) # Drop vectors tables = inspector.get_table_names( schema=settings.NIAMOTO_VECTOR_SCHEMA) for tb in tables: connection.execute("DROP TABLE IF EXISTS {} CASCADE;".format( "{}.{}".format(settings.NIAMOTO_VECTOR_SCHEMA, tb))) # Drop rasters tables = inspector.get_table_names( schema=settings.NIAMOTO_RASTER_SCHEMA) for tb in tables: connection.execute("DROP TABLE IF EXISTS {} CASCADE;".format( "{}.{}".format(settings.NIAMOTO_RASTER_SCHEMA, tb))) # Drop fact tables tables = inspector.get_table_names( schema=settings.NIAMOTO_FACT_TABLES_SCHEMA) for tb in tables: connection.execute("DROP TABLE IF EXISTS {} CASCADE;".format( "{}.{}".format(settings.NIAMOTO_FACT_TABLES_SCHEMA, tb))) # Drop dimensions tables = inspector.get_table_names( schema=settings.NIAMOTO_DIMENSIONS_SCHEMA) for tb in tables: connection.execute("DROP TABLE IF EXISTS {} CASCADE;".format( "{}.{}".format(settings.NIAMOTO_DIMENSIONS_SCHEMA, tb))) # Drop SDMs tables = inspector.get_table_names( schema=settings.NIAMOTO_SSDM_SCHEMA) for tb in tables: connection.execute("DROP TABLE IF EXISTS {} CASCADE;".format( "{}.{}".format(settings.NIAMOTO_SSDM_SCHEMA, tb))) super(BaseTestNiamotoSchemaCreated, cls).tearDownClass()
def test_add_raster(self): # Test non existing raster null_path = os.path.join(NIAMOTO_HOME, "NULL.tif") self.assertRaises(FileNotFoundError, RasterManager.add_raster, "null_raster", null_path, tile_dimension=(200, 200)) # Test existing raster test_raster = os.path.join(NIAMOTO_HOME, "data", "raster", "rainfall_wgs84.tif") RasterManager.add_raster( "rainfall", test_raster, ) df = RasterManager.get_raster_list() self.assertEqual(len(df), 1) self.assertEqual(df['name'].iloc[0], 'rainfall') engine = Connector.get_engine() inspector = Inspector.from_engine(engine) self.assertIn( 'rainfall', inspector.get_table_names(schema=settings.NIAMOTO_RASTER_SCHEMA), )
def test_base_data_publisher(self): dp = BaseDataPublisher() temp_csv = tempfile.TemporaryFile(mode='w') data = pd.DataFrame.from_records([[1, 2, 3, 4], [5, 6, 7, 8]]) dp._publish_csv(data, destination=temp_csv) dp.publish(data, 'csv', destination=temp_csv) engine = Connector.get_engine() db_url = Connector.get_database_url() dp.publish(data, 'sql', destination='test_publish_table', schema=settings.NIAMOTO_SCHEMA) dp.publish(data, 'sql', destination='test_publish_table', db_url=db_url, if_exists='replace', schema=settings.NIAMOTO_SCHEMA) inspector = Inspector.from_engine(engine) self.assertIn( 'test_publish_table', inspector.get_table_names(schema=settings.NIAMOTO_SCHEMA), ) temp_csv.close()
def _publish_sql(data, destination, *args, db_url=None, schema='public', if_exists='fail', truncate_cascade=False, set_pk=None, **kwargs): """ Publish a DataFrame as a table to a SQL database. Rely on pandas 'to_sql' method. c.f. : https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_sql.html :param data: A pandas DataFrame. :param destination: The name of the destination table. :param db_url: A sqlalchemy database url. :param schema: The name of the schema where to write the table. If None, use the default schema. :param if_exists: {‘fail’, ‘replace’, ‘append’, 'truncate'}, default ‘fail’. :param truncate_cascade: Truncate in cascade, default is False. Only active if if_exists is 'truncate' """ if db_url is None: connection = Connector.get_engine().connect() else: connection = create_engine(db_url).connect() with connection.begin(): if if_exists == 'truncate': test = \ """ SELECT table_name FROM information_schema.tables WHERE table_schema = '{}' AND table_name = '{}' """.format(schema, destination) r = connection.execute(test).rowcount if r != 0: sql = "TRUNCATE {}".format("{}.{}".format( schema, destination)) if truncate_cascade: sql += " CASCADE" connection.execute(sql) if_exists = 'append' if isinstance(data, (GeoDataFrame, GeoSeries)): return to_postgis(data, destination, con=connection, schema=schema, if_exists=if_exists) data.to_sql(destination, con=connection, schema=schema, if_exists=if_exists) if set_pk is not None: if isinstance(set_pk, (list, tuple)): set_pk = ",".join(set_pk) connection.execute(""" ALTER TABLE {}.{} ADD PRIMARY KEY ({}); """.format( schema, destination, set_pk, ))
def test_publish_to_postgis(self): CsvDataProvider.register_data_provider('csv_provider') csv_provider = CsvDataProvider( 'csv_provider', occurrence_csv_path=TEST_OCCURRENCE_CSV, ) csv_provider.sync() with Connector.get_connection() as connection: sel = select([ meta.occurrence.c.id.label('id'), meta.occurrence.c.taxon_id.label('taxon_id'), cast(meta.taxon.c.rank.label('rank'), String).label('rank'), meta.taxon.c.full_name.label('full_name'), cast(meta.occurrence.c.location, String).label('location'), ]).select_from( meta.occurrence.outerjoin( meta.taxon, meta.taxon.c.id == meta.occurrence.c.taxon_id)) df = gpd.read_postgis(sel, connection, index_col='id', geom_col='location', crs='+init=epsg:4326') BaseDataPublisher._publish_sql(df, 'test_export_postgis', schema='niamoto') engine = Connector.get_engine() inspector = Inspector.from_engine(engine) self.assertIn( 'test_export_postgis', inspector.get_table_names(schema=settings.NIAMOTO_SCHEMA), ) df2 = gpd.read_postgis(sel, connection, index_col='id', geom_col='location', crs={'init': 'epsg:4326'}) BaseDataPublisher._publish_sql( df2, 'test_export_postgis', schema='niamoto', if_exists='truncate', truncate_cascade=True, ) # Test geometry types polygon = Polygon([(0, 0), (1, 0), (1, 1)]) linestring = LineString([(0, 0), (0, 1), (1, 1)]) multipoint = MultiPoint([(1, 2), (3, 4), (5, 6)]) multilinestring = MultiLineString([[(1, 2), (3, 4), (5, 6)], [(7, 8), (9, 10)]]) polygon_2 = Polygon([(1, 1), (1, -1), (-1, -1), (-1, 1)], [[(.5, .5), (.5, -.5), (-.5, -.5), (-.5, .5)]]) multipolygon = MultiPolygon([polygon, polygon_2]) geometry = GeometryCollection([polygon, polygon_2]) BaseDataPublisher._publish_sql( gpd.GeoDataFrame([{ 'A': 1, 'geom': polygon }], geometry='geom'), 'test_export_postgis', schema='niamoto', if_exists='replace', ) BaseDataPublisher._publish_sql( gpd.GeoDataFrame([{ 'A': 1, 'geom': linestring }], geometry='geom'), 'test_export_postgis', schema='niamoto', if_exists='replace', ) BaseDataPublisher._publish_sql( gpd.GeoDataFrame([{ 'A': 1, 'geom': multilinestring }], geometry='geom'), 'test_export_postgis', schema='niamoto', if_exists='replace', ) BaseDataPublisher._publish_sql( gpd.GeoDataFrame([{ 'A': 1, 'geom': multipoint }], geometry='geom'), 'test_export_postgis', schema='niamoto', if_exists='replace', ) BaseDataPublisher._publish_sql( gpd.GeoDataFrame([{ 'A': 1, 'geom': multipolygon }], geometry='geom'), 'test_export_postgis', schema='niamoto', if_exists='replace', ) BaseDataPublisher._publish_sql( gpd.GeoDataFrame([{ 'A': 1, 'geom': geometry }], geometry='geom'), 'test_export_postgis', schema='niamoto', if_exists='replace', ) BaseDataPublisher._publish_sql( gpd.GeoDataFrame([{ 'A': 1, 'geom': geometry }], geometry='geom'), 'TEST123', schema='niamoto', if_exists='replace', ) BaseDataPublisher._publish_sql( gpd.GeoSeries([polygon]), 'test_export_postgis', schema='niamoto', if_exists='replace', ) self.assertRaises( ValueError, BaseDataPublisher._publish_sql, gpd.GeoSeries([polygon]), 'test_export_postgis', schema='niamoto', if_exists='thisisnotallowed', )
def update_synonym_mapping(self, connection=None): """ Update the synonym mapping of an already stored dataframe. To be called when a synonym had been defined or modified, but not the occurrences. :param connection: If passed, use an existing connection. """ # Log start m = "(provider_id='{}', synonym_key='{}'): Updating synonym " \ "mapping..." LOGGER.debug( m.format(self.data_provider.db_id, self.data_provider.synonym_key)) close_after = False if connection is None: connection = Connector.get_engine().connect() close_after = True # Start df = self.get_niamoto_occurrence_dataframe(connection) if close_after: connection.close() synonyms = TaxonomyManager.get_synonyms_for_key( self.data_provider.synonym_key) mapping = df["provider_taxon_id"].map(synonyms) if len(df) > 0: df["taxon_id"] = mapping df = df[['provider_id', 'provider_pk', 'taxon_id']] s = io.StringIO() df.where((pd.notnull(df)), None).rename(columns={ 'provider_id': 'prov_id', 'provider_pk': 'prov_pk', }).to_csv(s, columns=['taxon_id', 'prov_id', 'prov_pk']) s.seek(0) sql_create_temp = \ """ DROP TABLE IF EXISTS {tmp}; CREATE TABLE {tmp} ( id float, taxon_id float, prov_id float, prov_pk float ); """.format(**{ 'tmp': 'tmp_niamoto' }) sql_copy_from = \ """ COPY {tmp} FROM STDIN CSV HEADER DELIMITER ','; """.format(**{ 'tmp': 'tmp_niamoto' }) sql_update = \ """ UPDATE {occurrence_table} SET taxon_id = {tmp}.taxon_id::int FROM {tmp} WHERE {occurrence_table}.provider_id = {tmp}.prov_id::int AND {occurrence_table}.provider_pk = {tmp}.prov_pk::int; DROP TABLE {tmp}; """.format(**{ 'tmp': 'tmp_niamoto', 'occurrence_table': '{}.{}'.format( settings.NIAMOTO_SCHEMA, occurrence.name ) }) raw_connection = Connector.get_engine().raw_connection() cur = raw_connection.cursor() cur.execute(sql_create_temp) cur.copy_expert(sql_copy_from, s) cur.execute(sql_update) cur.close() raw_connection.commit() raw_connection.close() # Log end m = "(provider_id='{}', synonym_key='{}'): {} synonym mapping had " \ "been updated." LOGGER.debug( m.format(self.data_provider.db_id, self.data_provider.synonym_key, len(synonyms))) return mapping, synonyms