def update_lot_forms(tbl, file_list):
print('update table %s' % tbl.__table__.name)
session = access.Session()
rows = session.query(tbl)
print('%s records in table' % rows.count())
total_db = 0
for row in rows.all():
if row is not None:
try:
id = '%s_%s_%s_lot_form' % (row.Trench, row.Date.year,
int(row.Lot))
if id in file_list:
total_db = total_db + 1
row.Link_to_scanned_lot_form = access_link(
file_list[id][1])
else:
row.Link_to_scanned_lot_form = ''
except:
print('error, no Date in database?')
print('Trench %s | Lot %s | Date %s' %
(row.Trench, int(row.Lot), row.Date))
session.commit()
print('%s link_to_scanned_lot_forms set in db' % (total_db))
print('commit changes')
session.close()
def update_trench_reports(tbl, file_list, url_changed=False):
print('update table %s' % tbl.__table__.name)
session = access.Session()
total_db = 0
for f in file_list:
m = re.match(r'^([A-Z].+)_(\d{4})_trench_report', f)
if m:
url = file_list[f][1]
trench = m.group(1)
year = m.group(2)
q = session.query(tbl).filter(tbl.Trench == trench,
tbl.Trench_Year == year)
if url_changed:
q.delete()
q = session.query(tbl).filter(tbl.Trench == trench,
tbl.Trench_Year == year)
if q.count() == 0: # new record
tr = tbl(Trench=trench,
Trench_Year=year,
Trench_report_URL=access_link(url))
session.add(tr)
total_db = total_db + 1
session.commit()
print('%s trench_report_urls set in db' % (total_db))
print('commit changes')
session.close()
def update_picture_urls(tbl, file_list):
print('update table %s' % tbl.__table__.name)
id_list = picture_id_list(file_list)
session = access.Session()
rows = session.query(tbl)
print('%s records in table' % rows.count())
total_db = 0
for row in rows.all():
if row is not None:
if row.Picture_ID.strip() in id_list:
total_db = total_db + 1
row.picture_url = access_link(id_list[row.Picture_ID.strip()])
print('%s picture_urls set in db' % (total_db))
print('commit changes')
session.commit()
session.close()
def update_drawing_urls(tbl, file_list):
print('update table %s' % tbl.__table__.name)
session = access.Session()
rows = session.query(tbl)
print('%s records in table' % rows.count())
total_db = 0
id_list = {}
# Drawing_No should be first 2 parts of filename
for f in file_list:
url = file_list[f][1]
a = f.split('_')
id = '%s_%s' % (a[0], a[1])
id_list[id] = url
for row in rows.all():
if row is not None:
if row.Drawing_No in id_list:
total_db = total_db + 1
row.Drawing_url = access_link(id_list[row.Drawing_No])
print('%s drawing_urls set in db' % (total_db))
print('commit changes')
session.commit()
session.close()
def update_bh_picture_urls(tbl, file_list, url_changed=False):
print('update table %s' % tbl.__table__.name)
session = access.Session()
total_db = 0
for f in file_list:
m = re.match(r'^BH(\d+)_.+', f)
if m:
url = file_list[f][1]
bhnum = m.group(1)
if url_changed:
q = session.query(tbl).filter(tbl.BH_Number == bhnum)
q.delete()
# a BH_Number can have more than one picture, so the combination is unique
q = session.query(tbl).filter(tbl.BH_Number == bhnum,
tbl.picture_url == access_link(url))
if q.count() == 0: # new record
bh = tbl(BH_Number=bhnum, picture_url=access_link(url))
session.add(bh)
total_db = total_db + 1
session.commit()
print('%s picture_urls set in db' % (total_db))
print('commit changes')
session.close()
def update_daily_reports(tbl, file_list):
print('update table %s' % tbl.__table__.name)
session = access.Session()
rows = session.query(tbl)
print('%s records in table' % rows.count())
total_db = 0
for row in rows.all():
if row is not None:
try:
id = '%s_%s_%s_%s_daily_report' % (row.Trench, '{:02}'.format(
row.Date.day), '{:02}'.format(
row.Date.month), row.Date.year)
if id in file_list:
total_db = total_db + 1
row.Link_to_daily_report = access_link(file_list[id][1])
else:
row.Link_to_daily_report = ''
except:
print('error, no Date in database?')
print('Trench %s | Date %s' % (row.Trench, row.Date))
session.commit()
print('%s link_to_daily_reports set in db' % (total_db))
print('commit changes')
session.close()