From: Benjamin Mako Hill Date: Fri, 7 May 2021 22:46:47 +0000 (-0700) Subject: initial import of stuff into google sheets X-Git-Url: https://code.communitydata.science/taguette_google_sheet_integration.git/commitdiff_plain/5dbb55e4d144aa2960239b1daee3e95dad9d955e initial import of stuff into google sheets - currently specific to ecology project --- 5dbb55e4d144aa2960239b1daee3e95dad9d955e diff --git a/taguette-export_tags_to_csv.py b/taguette-export_tags_to_csv.py new file mode 100755 index 0000000..31a267d --- /dev/null +++ b/taguette-export_tags_to_csv.py @@ -0,0 +1,32 @@ +#!/usr/bin/env python3 + +import re +import sqlite3 + +## connect to sqlite3 +con = sqlite3.connect('taguette-working.sqlite3') +cur = con.cursor() + +## this is the hardcoded project id +project_id = 13 + +sql_stmt_get = "SELECT id, path, description FROM tags WHERE project_id = ?" +cur.execute(sql_stmt_get, (project_id,)) + +while True: + row = cur.fetchone() + if row == None: + break + + tag_id, path, description = row + + m = re.match(r'^(.+)\.(.*)$', path) + if m: + axial = m.group(1) + tag = m.group(2) + else: + axial = "" + tag = path + + print("\t".join([str(tag_id), axial, tag, description])) + diff --git a/taguette-update_tags_from_sheet.py b/taguette-update_tags_from_sheet.py new file mode 100755 index 0000000..036d04d --- /dev/null +++ b/taguette-update_tags_from_sheet.py @@ -0,0 +1,63 @@ +#!/usr/bin/env python3 + +import requests +from csv import DictReader +import sqlite3 + +gsheet_id = "1bfKljA7vw2V4yKrowxCPLdYaBrTzty5_O7msbuFw7Nc" +gsheet_gid = "0" + +## this is the hardcoded project id +project_id = 13 + + +## get the spreadsheet data +axial_url = f"https://docs.google.com/spreadsheets/d/{gsheet_id}/export?format=csv&id={gsheet_id}&gid={gsheet_gid}" +rv = requests.get(axial_url) +csv_text = rv.content.decode('utf-8') + +## connect to sqlite3 +con = sqlite3.connect('taguette-working.sqlite3') +cur = con.cursor() + +## import taguette.database as tagdb +## db = tagdb.connect("sqlite:////home/mako/taguette-snapshot-20210422-1.sqlite3") + +for row in DictReader(csv_text.splitlines(), delimiter=","): + #print(row) + tag_id = row['ID'] + new_name = row['tag'] + axial_code = row['Axial Codes'] + + sql_stmt_get = "SELECT id, path, description from TAGS where id = ? AND project_id = ?" + cur.execute(sql_stmt_get, (tag_id, project_id)) + tag_info = cur.fetchall() + + if len(tag_info) > 1: + print(f"ERROR: '{id}' is not unique, SKIPPING") + elif len(tag_info) == 0: + print(f"ERROR: 'tag with ID {id}' does not exist, SKIPPING") + else: + oldname = tag_info[0][1] + old_description = tag_info[0][2] + + if row['Axial Codes']: + newname = row['Axial Codes'].lower() + "." + new_name.lower() + else: + newname = new_name.lower() + + if not oldname == newname: + #print(tag_id) + sql_stmt_update = "UPDATE tags SET path = ? WHERE project_id = ? AND id = ?" + cur.execute(sql_stmt_update, (newname, project_id, tag_id)) + print(f"UPDATE TAG: {oldname} → {newname}") + + if row["description"].strip() != old_description.strip(): + sql_stmt_update = "UPDATE tags SET description = ? WHERE project_id = ? AND id = ?" + cur.execute(sql_stmt_update, (row['description'], project_id, tag_id)) + print(f"UPDATE DESC: {old_description} → {row['description']}") + +con.commit() +con.close() + +