def dump_data():
dir_name = 'nextgisbio/initial_data/csv/' + time.strftime("%Y_%m_%d_%H_%M_%S")
if not os.path.exists(dir_name):
os.makedirs(dir_name)
else:
raise Exception('Directory for exported csv files already exists')
get_path_name = lambda name: os.path.join(dir_name, name)
with transaction.manager:
Annotation.export_to_file(get_path_name('annotation.csv'))
Cards.export_to_file(get_path_name('cards.csv'))
Person.export_to_file(get_path_name('person.csv'))
Taxa_scheme.export_to_file(get_path_name('taxa_scheme.csv'))
Museum.export_to_file(get_path_name('museum.csv'))
Coord_type.export_to_file(get_path_name('coord_type.csv'))
Anthr_press.export_to_file(get_path_name('anthr_press.csv'))
Vitality.export_to_file(get_path_name('vitality.csv'))
Abundance.export_to_file(get_path_name('abundance.csv'))
Footprint.export_to_file(get_path_name('footprint.csv'))
Pheno.export_to_file(get_path_name('pheno.csv'))
Inforesources.export_to_file(get_path_name('inforesources.csv'))
Legend.export_to_file(get_path_name('legend.csv'))
Area_type.export_to_file(get_path_name('area_type.csv'))
Key_area.export_to_file(get_path_name('key_area.csv'))
RedBook.export_to_file(get_path_name('redbooks.csv'))
User.export_to_file(get_path_name('user.csv'))
Squares.export_to_file(get_path_name('square_karea_association.csv'))
Taxon.export_to_file(get_path_name('taxon.csv'))
Synonym.export_to_file(get_path_name('synonym.csv'))
Images.export_to_file(get_path_name('images.csv'))
CardsImages.export_to_file(get_path_name('cards_images.csv'))
return dir_name
def create_taxon(request):
new_data = dict(request.POST)
dbsession = DBSession()
taxon = Taxon()
for k, v in new_data.items():
if v == '': v = None
if hasattr(taxon, k): setattr(taxon, k, v)
dbsession.add(taxon)
dbsession.flush()
dbsession.refresh(taxon)
return {'item': taxon.as_json_dict()}
def create_taxon(request):
new_data = dict(request.POST)
dbsession = DBSession()
taxon = Taxon()
for k, v in new_data.items():
if v == '': v = None
if hasattr(taxon, k): setattr(taxon, k, v)
dbsession.add(taxon)
dbsession.flush()
dbsession.refresh(taxon)
return {'item': taxon.as_json_dict()}
def get_taxon_path(request):
taxon_id = request.matchdict['id']
taxons = Taxon.parent_taxons(taxon_id)
path = [t.id for t in taxons]
return {'success': True, 'path': path}
def get_taxon_path(request):
taxon_id = request.matchdict['id']
taxons = Taxon.parent_taxons(taxon_id)
path = [t.id for t in taxons]
return {'success': True, 'path': path}
def species_name(request):
with transaction.manager:
dbsession = DBSession()
species_types = {
'mammalia': MAMMALIA,
'aves': AVES,
'plantae': PLANTAE,
'ara': ARA,
'arthropoda': ARTHROPODA,
'moss': MOSS,
'lichenes': LICHENES
}
rows = []
rec_id = itertools.count()
try:
for sp in species_types.keys():
slist = species_types[sp]
target = dbsession.query(Taxon).filter(
Taxon.name.in_(slist)).all()
target_ids = [t.id for t in target]
tax_all = Taxon.species_by_taxon(target_ids)
rows = rows + [{
'recId': rec_id.next(),
'id': row['id'],
'name': row['name'],
'author': row['author'],
'source': row['source'],
'organism': sp,
'synonim': False
} for row in tax_all]
# соберем синонимы:
syn = dbsession.query(Synonym.species_id, Synonym.synonym,
Synonym.author, Synonym.source).filter(
Synonym.species_id.in_([
row['id'] for row in tax_all
])).all()
rows = rows + [{
'recId': rec_id.next(),
'id': row[0],
'name': row[1],
'author': row[2],
'source': row[3],
'organism': sp,
'synonim': True
} for row in syn]
except DBAPIError:
result = {'success': False, 'msg': 'Ошибка подключения к БД'}
return {'data': rows, 'success': True, 'totalCount': len(rows)}
def gen_sql(records):
with transaction.manager:
for r in records:
id = None
for t in 'Kingdom', 'Phylum', 'Class', 'Order', 'Family', 'Genus', 'Species':
try:
print t, r[t]
taxon = DBSession.query(Taxon).filter_by(taxon_type = t, name = r[t], parent_id = id).one()
print taxon
except NoResultFound:
taxon = Taxon(taxon_type = t, name = r[t], parent_id = id, author=r[t+'_author'], source=r[t+'_source'])
DBSession.add(taxon)
DBSession.flush()
id = taxon.id
print taxon.id, taxon.taxon_type, taxon.name, taxon.parent_id
def species_name(request):
with transaction.manager:
dbsession = DBSession()
species_types = {'mammalia': MAMMALIA, 'aves': AVES, 'plantae': PLANTAE, 'ara': ARA,
'arthropoda': ARTHROPODA, 'moss': MOSS, 'lichenes': LICHENES}
rows = []
rec_id = itertools.count()
try:
for sp in species_types.keys():
slist = species_types[sp]
target = dbsession.query(Taxon).filter(Taxon.name.in_(slist)).all()
target_ids = [t.id for t in target]
tax_all = Taxon.species_by_taxon(target_ids)
rows = rows + [
{'recId': rec_id.next(),
'id': row['id'],
'name': row['name'],
'author': row['author'],
'source': row['source'],
'organism': sp, 'synonim': False
}
for row in tax_all]
# соберем синонимы:
syn = dbsession.query(Synonym.species_id, Synonym.synonym, Synonym.author, Synonym.source).filter(
Synonym.species_id.in_([row['id'] for row in tax_all])).all()
rows = rows + [
{'recId': rec_id.next(),
'id': row[0],
'name': row[1],
'author': row[2],
'source': row[3],
'organism': sp, 'synonim': True
}
for row in syn]
except DBAPIError:
result = {'success': False, 'msg': 'Ошибка подключения к БД'}
return {'data': rows, 'success': True, 'totalCount': len(rows)}
def as_join_list(taxon_list=None, header=True):
'''
Выбрать аннотированные списки таксонов, перечисленных в taxon_list
Все идентификаторы заменить на соотв. значения из таблиц-справочников.
Вернуть выборку в виде списка значений.
Если taxon_list=None, выбрать все карточки.
header: добавлять ли в начало списка строку заголовков.
'''
dbsession = DBSession()
if taxon_list: # выдать списки-потомки определенных таксонов
species = Taxon.species_by_taxon(taxon_list)
species_id = [t['id'] for t in species]
else: # выдать все списки
species = dbsession.query(Taxon).all()
species_id = [t.id for t in species if t.is_last_taxon()]
qs = '''
SELECT
annotation.id,
taxon.name as species,
key_area.name as key_area,
person_1.name as identifier,
person_2.name as collecter,
inforesources.filename as biblioref,
original_name,
location,
lon,
lat,
biotop,
difference,
substrat,
status,
frequency,
quantity,
annotation,
infosourse,
year,
month,
day,
exposure
FROM annotation
LEFT OUTER JOIN
taxon ON annotation.species = taxon.id
LEFT OUTER JOIN
key_area ON annotation.key_area = key_area.id
LEFT OUTER JOIN
person as person_1 ON annotation.identifier = person_1.id
LEFT OUTER JOIN
person as person_2 ON annotation.collecter = person_2.id
LEFT OUTER JOIN
inforesources ON annotation.biblioref = inforesources.id
WHERE
annotation.species IN (%s)
''' % ", ".join([str(num) for num in species_id])
anlists = dbsession.query(Annotation).from_statement(qs).all()
dbsession.close()
names = [
'id',
'species',
'key_area',
'identifier',
'collecter',
'biblioref',
'original_name',
'location',
'lon',
'lat',
'biotop',
'difference',
'substrat',
'status',
'frequency',
'quantity',
'annotation',
'infosourse',
'year',
'month',
'day',
'exposure'
]
result = [names]
for anlist in anlists:
row = [anlist.__getattribute__(attr) for attr in names]
result.append(row)
return result
def get_taxon(request):
id = int(request.matchdict['id'])
parent_taxons = Taxon.parent_taxons(id)
def get_taxon(request):
id = int(request.matchdict['id'])
parent_taxons = Taxon.parent_taxons(id)
def as_join_list(taxon_list=None, header=True):
'''
Выбрать карточки наблюдений таксонов, перечисленных в taxon_list
Все идентификаторы заменить на соотв. значения из таблиц-справочников.
Вернуть выборку в виде списка значений.
Если taxon_list=None, выбрать все карточки.
header: добавлять ли в начало списка строку заголовков.
'''
dbsession = DBSession()
qs = '''
SELECT
cards.id,
taxon.name as species,
cards.original_name,
person.name as inserter,
person_1.name as observer,
person_2.name as identifier,
taxa_scheme.taxa_scheme,
museum.museum,
anthr_press.anthr_press,
vitality.vitality,
abundance.abundance,
footprint.footprint,
pheno.pheno,
inforesources.filename as inforesources,
cards.year,
cards.month,
cards.day,
cards.time,
cards.habitat,
cards.substrat,
cards.limit_fact,
cards.protection,
cards.area,
cards.quantity,
cards.unknown_age,
cards.unknown_sex,
cards.males,
cards.females,
cards.ad,
cards.sad,
cards.juv,
cards.pull,
cards.egs,
cards.publications,
cards.notes,
cards.location,
cards.lon,
cards.lat,
coord_type.coord_type
FROM cards
LEFT OUTER JOIN
taxon ON cards.species = taxon.id
LEFT OUTER JOIN
person ON cards.inserter = person.id
LEFT OUTER JOIN
person as person_1 ON cards.observer = person_1.id
LEFT OUTER JOIN
person as person_2 ON cards.identifier = person_2.id
LEFT OUTER JOIN
taxa_scheme ON cards.taxa_scheme = taxa_scheme.id
LEFT OUTER JOIN
museum ON cards.museum = museum.id
LEFT OUTER JOIN
anthr_press ON cards.anthr_press = anthr_press.id
LEFT OUTER JOIN
vitality ON cards.vitality = vitality.id
LEFT OUTER JOIN
abundance ON cards.abundance = abundance.id
LEFT OUTER JOIN
footprint ON cards.footprint = footprint.id
LEFT OUTER JOIN
pheno ON cards.pheno = pheno.id
LEFT OUTER JOIN
inforesources ON cards.inforesources = inforesources.id
LEFT OUTER JOIN
coord_type ON cards.coord_type = coord_type.id
'''
if taxon_list:
species = Taxon.species_by_taxon(taxon_list)
species_id = [t['id'] for t in species]
qs_where = ' WHERE cards.species IN (%s)' % ", ".join([str(num) for num in species_id])
qs += qs_where
cards = dbsession.query(Cards).from_statement(qs).all()
dbsession.close()
names = ['id', 'species', 'original_name', 'inserter', 'observer', 'identifier', 'taxa_scheme', 'museum',
'anthr_press', 'vitality', 'abundance', 'footprint', 'pheno', 'inforesources',
'year', 'month', 'day', 'time', 'habitat', 'substrat', 'limit_fact', 'protection', 'area',
'quantity', 'unknown_age', 'unknown_sex', 'males', 'females', 'ad', 'sad', 'juv', 'pull', 'egs',
'publications', 'notes', 'location', 'lon', 'lat', 'coord_type']
result = [names]
for card in cards:
row = [card.__getattribute__(attr) for attr in names]
result.append(row)
return result
def main(argv=sys.argv):
if len(argv) != 2 and len(argv) != 3:
usage(argv)
config_uri = argv[1]
setup_logging(config_uri)
settings = get_appsettings(config_uri)
engine = engine_from_config(settings, 'sqlalchemy.')
DBSession.configure(bind=engine)
md5_pass = False
if len(argv) == 3 and argv[2] == '--md5-pass':
md5_pass = True
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
# Заполним таблицы данными:
# Таксоны
taxons_file = 'nextgisbio/initial_data/csv/taxon.csv'
Taxon.add_from_file(taxons_file)
synonym_file = 'nextgisbio/initial_data/csv/synonym.csv'
Synonym.add_from_file(synonym_file)
# Справочники
person_file = 'nextgisbio/initial_data/csv/person.csv'
Person.add_from_file(person_file)
taxa_file = 'nextgisbio/initial_data/csv/taxa_scheme.csv'
Taxa_scheme.add_from_file(taxa_file)
museum_file = 'nextgisbio/initial_data/csv/museum.csv'
Museum.add_from_file(museum_file)
coord_type_file = 'nextgisbio/initial_data/csv/coord_type.csv'
Coord_type.add_from_file(coord_type_file)
ant_file = 'nextgisbio/initial_data/csv/anthr_press.csv'
Anthr_press.add_from_file(ant_file)
vital_file = 'nextgisbio/initial_data/csv/vitality.csv'
Vitality.add_from_file(vital_file)
abundance_file = 'nextgisbio/initial_data/csv/abundance.csv'
Abundance.add_from_file(abundance_file)
footprint_file = 'nextgisbio/initial_data/csv/footprint.csv'
Footprint.add_from_file(footprint_file)
pheno_file = 'nextgisbio/initial_data/csv/pheno.csv'
Pheno.add_from_file(pheno_file)
infores_file = 'nextgisbio/initial_data/csv/inforesources.csv'
Inforesources.add_from_file(infores_file)
area_type_file = 'nextgisbio/initial_data/csv/area_type.csv'
Area_type.add_from_file(area_type_file)
legend_file = 'nextgisbio/initial_data/csv/legend.csv'
Legend.add_from_file(legend_file)
key_area_file = 'nextgisbio/initial_data/csv/key_area.csv'
Key_area.add_from_file(key_area_file)
# Нужно добавить шейпы и заполнить данными таблицу
# связей (square_keyarea_association) многие-ко-многим между Squares и Key_area
shp_file = 'nextgisbio/initial_data/shp/key_areas_25km.shp'
association_file = 'nextgisbio/initial_data/csv/square_karea_association.csv'
Squares.add_from_file(association_file, shp_file)
# Карточки и аннотации
cards_file = 'nextgisbio/initial_data/csv/cards.csv'
Cards.add_from_file(cards_file)
ann_file = 'nextgisbio/initial_data/csv/annotation.csv'
Annotation.add_from_file(ann_file)
# Пользователи
users_file = 'nextgisbio/initial_data/csv/user.csv'
User.add_from_file(users_file, md5_pass)
red_books_csv = 'nextgisbio/initial_data/csv/redbooks.csv'
RedBook.import_from_csv(red_books_csv)
images_csv = 'nextgisbio/initial_data/csv/images.csv'
Images.import_from_csv(images_csv)
cards_images_csv = 'nextgisbio/initial_data/csv/cards_images.csv'
CardsImages.import_from_csv(cards_images_csv)
def as_join_list(taxon_list=None, header=True):
'''
Выбрать карточки наблюдений таксонов, перечисленных в taxon_list
Все идентификаторы заменить на соотв. значения из таблиц-справочников.
Вернуть выборку в виде списка значений.
Если taxon_list=None, выбрать все карточки.
header: добавлять ли в начало списка строку заголовков.
'''
dbsession = DBSession()
qs = '''
SELECT
cards.id,
taxon.name as species,
cards.original_name,
person.name as inserter,
person_1.name as observer,
person_2.name as identifier,
taxa_scheme.taxa_scheme,
museum.museum,
anthr_press.anthr_press,
vitality.vitality,
abundance.abundance,
footprint.footprint,
pheno.pheno,
inforesources.filename as inforesources,
cards.year,
cards.month,
cards.day,
cards.time,
cards.habitat,
cards.substrat,
cards.limit_fact,
cards.protection,
cards.area,
cards.quantity,
cards.unknown_age,
cards.unknown_sex,
cards.males,
cards.females,
cards.ad,
cards.sad,
cards.juv,
cards.pull,
cards.egs,
cards.publications,
cards.notes,
cards.location,
cards.lon,
cards.lat,
coord_type.coord_type
FROM cards
LEFT OUTER JOIN
taxon ON cards.species = taxon.id
LEFT OUTER JOIN
person ON cards.inserter = person.id
LEFT OUTER JOIN
person as person_1 ON cards.observer = person_1.id
LEFT OUTER JOIN
person as person_2 ON cards.identifier = person_2.id
LEFT OUTER JOIN
taxa_scheme ON cards.taxa_scheme = taxa_scheme.id
LEFT OUTER JOIN
museum ON cards.museum = museum.id
LEFT OUTER JOIN
anthr_press ON cards.anthr_press = anthr_press.id
LEFT OUTER JOIN
vitality ON cards.vitality = vitality.id
LEFT OUTER JOIN
abundance ON cards.abundance = abundance.id
LEFT OUTER JOIN
footprint ON cards.footprint = footprint.id
LEFT OUTER JOIN
pheno ON cards.pheno = pheno.id
LEFT OUTER JOIN
inforesources ON cards.inforesources = inforesources.id
LEFT OUTER JOIN
coord_type ON cards.coord_type = coord_type.id
'''
if taxon_list:
species = Taxon.species_by_taxon(taxon_list)
species_id = [t['id'] for t in species]
qs_where = ' WHERE cards.species IN (%s)' % ", ".join(
[str(num) for num in species_id])
qs += qs_where
cards = dbsession.query(Cards).from_statement(qs).all()
dbsession.close()
names = [
'id', 'species', 'original_name', 'inserter', 'observer',
'identifier', 'taxa_scheme', 'museum', 'anthr_press', 'vitality',
'abundance', 'footprint', 'pheno', 'inforesources', 'year',
'month', 'day', 'time', 'habitat', 'substrat', 'limit_fact',
'protection', 'area', 'quantity', 'unknown_age', 'unknown_sex',
'males', 'females', 'ad', 'sad', 'juv', 'pull', 'egs',
'publications', 'notes', 'location', 'lon', 'lat', 'coord_type'
]
result = [names]
for card in cards:
row = [card.__getattribute__(attr) for attr in names]
result.append(row)
return result