From 5dbb55e4d144aa2960239b1daee3e95dad9d955e Mon Sep 17 00:00:00 2001
From: Benjamin Mako Hill <mako@atdot.cc>
Date: Fri, 7 May 2021 15:46:47 -0700
Subject: [PATCH] initial import of stuff into google sheets

- currently specific to ecology project
---
 taguette-export_tags_to_csv.py     | 32 +++++++++++++++
 taguette-update_tags_from_sheet.py | 63 ++++++++++++++++++++++++++++++
 2 files changed, 95 insertions(+)
 create mode 100755 taguette-export_tags_to_csv.py
 create mode 100755 taguette-update_tags_from_sheet.py

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()
+
+
-- 
2.39.2