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
