Hi guys,
Thanks for the answers. I wrote a python script. It is far from being elegant
but it does the job. I attach it to this message in case it can be helpful to
some of you.
Cheers,
Phil
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# grab data to generate summary for surveys of a cave network
import sqlite3
import pandas as pd
import numpy as np
conn = sqlite3.connect(':memory:')
conn.executescript(open('cave.sql').read())
stats = open('survey_stats.dat','w')
# sql to panda
surv = pd.read_sql_query('select ID, NAME from SURVEY', conn)
surv = surv[surv.NAME != ''] # drop empty survey names
ctli = pd.read_sql_query('select ID, SURVEY_ID, TOPO_DATE, LENGTH from CENTRELINE', conn)
ctli = ctli.dropna() # drop empty date centerlines
topo = pd.read_sql_query('select PERSON_ID, CENTRELINE_ID from TOPO', conn)
team = pd.read_sql_query('select ID, NAME, SURNAME from PERSON', conn)
team = team.drop_duplicates()
team = team.to_numpy()
#iterate over surveys
for index, s in surv.iterrows():
print(s.NAME)
stats.write('%s\n'%(s.NAME))
for index, c in ctli.iterrows():
if (c.SURVEY_ID == s.ID) :
print(' date ',c.TOPO_DATE)
stats.write(' date %s\n'%(c.TOPO_DATE))
print(' length ',c.LENGTH)
stats.write(' length %s\n'%(c.LENGTH))
for index, t in topo.iterrows():
if (t.CENTRELINE_ID == c.ID) :
name = team[team[:,0] == t.PERSON_ID]
print (' {} {}'.format(name[0,1],name[0,2]))
stats.write(' {} {}\n'.format(name[0,1],name[0,2]))
stats.close()
> On 3 May 2023, at 23:44, Andrew Atkinson <and...@wotcc.org.uk> wrote:
>
>
>
> On 03/05/2023 21:40, Tarquin Wilton-Jones via Therion wrote:
>> However, I don't know what it will output if you ask it to export a
>> database. Probably nothing useful.
>
> As fr as I remember it was very useful, I wrote a web page that queried it,
> this was over 10 years ago, the page is still there but it appears that I
> have removed the database at some point
>
> https://wotcc.org.uk/Cheddar/
>
>
>
> The therion book p66 gives the structure. For things like who did what, I
> think each leg has the data of who was on the team doing what. With the right
> sql query you should be able to find out most things recorded.
>
> Andrew
> _______________________________________________
> Therion mailing list
> Therion@speleo.sk
> https://mailman.speleo.sk/listinfo/therion
_______________________________________________
Therion mailing list
Therion@speleo.sk
https://mailman.speleo.sk/listinfo/therion
