107 lines
3.1 KiB
Python
107 lines
3.1 KiB
Python
from pycen import con_gn,con_sicen, update_to_sql
|
|
import pandas as pd
|
|
|
|
|
|
############################
|
|
####### Araignée sp. #######
|
|
######## Fungi sp. #########
|
|
############################
|
|
dict_tax = {
|
|
'Pachygnatha sp':195648,
|
|
'Clubiona sp':190950,
|
|
'Enoplognatha sp':192179,
|
|
'Tetragnatha sp':198316,
|
|
'Linyphiidae sp':184366,
|
|
'Episinus sp':192245,
|
|
'Evarcha sp':192480,
|
|
'Thanatus sp':198365,
|
|
'Xysticus sp':199101,
|
|
'Araneidae sp':184354,
|
|
'Pardosa cf saltans':457342, # doute
|
|
'Zora sp':199158,
|
|
'Piratula sp':806893,
|
|
'Pardosa sp':195830,
|
|
'Thomisidae sp':184390,
|
|
'Gnaphosidae sp':184362,
|
|
'Araniella sp':189390,
|
|
'Pirata sp':196303,
|
|
'Trochosa sp':198691,
|
|
'Zelotes sp':199129,
|
|
'Agelena sp':188876,
|
|
'Ozyptila sp':202569,
|
|
'Metellina sp':202546,
|
|
'Hyptiotes cf paradoxus':1058, # doute
|
|
'Phrurolithus sp':196174,
|
|
'Tegenaria sp':198254,
|
|
'Singa sp':197676,
|
|
'Heliophanus sp':193148,
|
|
'Zodarion sp':199147,
|
|
'Tmarus sp':198482,
|
|
'Dysdera sp':192003,
|
|
'Liocranidae sp':184367,
|
|
'Micaria sp':194695,
|
|
'Salticidae sp':184381,
|
|
'Agroeca sp':188904,
|
|
'Lycosidae sp':184368,
|
|
'Zora cf spinimana':1753, # doute
|
|
'Marpissa sp':194474,
|
|
'Alopecosa sp':188992,
|
|
# 'Amphinema diadema':, # Contact fournisseur données : RIVOIRE Bernard
|
|
'Hyphodermella rosae':905273,
|
|
'Cartilosoma rene-hentic':905220,
|
|
# 'Phanerochaete livescens':, # Appeler CBNA / RIVOIRE Bernard
|
|
# 'Thanatephorus brevisporus':, # Contact fournisseur données : RIVOIRE Bernard
|
|
'Phlebia acerina':970280 }
|
|
|
|
|
|
sql = """
|
|
select id_obs,remarque_obs FROM saisie.saisie_observation WHERE cd_nom in ('9999077','9999078')
|
|
and remarque_obs not ilike '%%Amphinema diadema%%'
|
|
and remarque_obs not ilike '%%Phanerochaete livescens%%'
|
|
and remarque_obs not ilike '%%Thanatephorus brevisporus%%'
|
|
"""
|
|
df = pd.read_sql(sql, con_sicen)
|
|
|
|
df['cd_nom'] = (df['remarque_obs']
|
|
.str.split('|',n=1)
|
|
.str[0]
|
|
.str.replace('Nom taxon saisi :','')
|
|
.str.strip()
|
|
.replace(dict_tax)
|
|
)
|
|
|
|
sql = 'SELECT cd_nom,regne,nom_vern,phylum,famille,classe,ordre,nom_complet,nom_valide FROM taxonomie.taxref WHERE cd_nom IN {}'
|
|
tax = pd.read_sql(sql.format(tuple(df['cd_nom'].unique())), con_gn)
|
|
|
|
df = (df.merge(tax,how='left',on='cd_nom')
|
|
.drop(columns=['remarque_obs'])
|
|
.astype({'cd_nom':str}))
|
|
|
|
update_to_sql(
|
|
df,
|
|
con_sicen,
|
|
'saisie_observation',
|
|
'saisie','id_obs')
|
|
|
|
|
|
############################
|
|
######## Myotis sp. ########
|
|
############################
|
|
|
|
dict_tax = {'9999058':195005}
|
|
sql = """
|
|
select id_obs,cd_nom FROM saisie.saisie_observation WHERE cd_nom = '9999058'
|
|
"""
|
|
df = pd.read_sql(sql, con_sicen)
|
|
df.replace({'cd_nom':dict_tax},inplace=True)
|
|
|
|
sql = 'SELECT cd_nom,regne,nom_vern,phylum,famille,classe,ordre,nom_complet,nom_valide FROM taxonomie.taxref WHERE cd_nom = %i'
|
|
tax = pd.read_sql(sql%df['cd_nom'].unique()[0], con_gn)
|
|
|
|
df = (df.merge(tax,how='left',on='cd_nom')
|
|
.astype({'cd_nom':str}))
|
|
update_to_sql(
|
|
df,
|
|
con_sicen,
|
|
'saisie_observation',
|
|
'saisie','id_obs') |