From: Benjamin Mako Hill <mako@atdot.cc>
Date: Tue, 28 Feb 2023 00:37:35 +0000 (-0800)
Subject: added new code to support categories
X-Git-Url: https://code.communitydata.science/taguette_google_sheet_integration.git/commitdiff_plain/7d3830a5faf476da3ab3a430c95448bf1e960d74

added new code to support categories

categories will be exported as a separate column and stored in the database
with a tiny json object at the end of the description. they are supressed from
view in the google docs description column but will be added back on import
---

diff --git a/taguette-export_tags_to_csv.py b/taguette-export_tags_to_csv.py
index 53a5c48..8c39554 100755
--- a/taguette-export_tags_to_csv.py
+++ b/taguette-export_tags_to_csv.py
@@ -1,6 +1,7 @@
 #!/usr/bin/env python3
 
 import re
+import json
 import sqlite3
 from configparser import ConfigParser
 
@@ -23,6 +24,9 @@ sql_stmt_get = "SELECT id, path, description FROM tags WHERE project_id = ?"
 #sql_stmt_get = "SELECT tags.id, tags.path, tags.description, highlights.snippet FROM highlight_tags INNER JOIN tags ON highlight_tags.tag_id = tags.id INNER JOIN highlights ON highlight_tags.highlight_id = highlights.id WHERE project_id = ?"
 cur.execute(sql_stmt_get, (project_id,))
 
+# print out a header
+print("\t".join(['id', 'axial codes', 'tags', 'category', 'description']))
+
 while True:
     row = cur.fetchone()
     if row == None:
@@ -30,13 +34,21 @@ while True:
         
     tag_id, path, description = row
 
-    m = re.match(r'^(.+)\_(.*)$', path) 
-    if m:
-        axial = m.group(1)
-        tag = m.group(2)
+    tag_match = re.match(r'^(.+)\_(.*)$', path) 
+    if tag_match:
+        axial = tag_match.group(1)
+        tag = tag_match.group(2)
     else:
         axial = ""
         tag = path
+
+    # look for extra category information stored in the description
+    cat_match = re.match('^(.*)\s*(\{(.*)\})$', description)
+    if cat_match:
+        description = cat_match.group(1)
+        category = json.loads(cat_match.group(2))["category"]
+    else:
+        category = ""
     
-    print("\t".join([str(tag_id), axial, tag, description]))
+    print("\t".join([str(tag_id), axial, tag, category, description]))
 
diff --git a/taguette-update_tags_from_sheet.py b/taguette-update_tags_from_sheet.py
index 3f550c2..565a145 100755
--- a/taguette-update_tags_from_sheet.py
+++ b/taguette-update_tags_from_sheet.py
@@ -4,6 +4,8 @@ import requests
 from csv import DictReader
 import sqlite3
 from configparser import ConfigParser
+import re
+import json
 
 config = ConfigParser()
 config.read('.taguette_gdocs')
@@ -30,9 +32,11 @@ cur = con.cursor()
 
 for row in DictReader(csv_text.splitlines(), delimiter=","):
     #print(row)
-    tag_id = row['ID']
+    tag_id = row['id']
     new_name = row['tag']
-    axial_code = row['Axial Codes']
+    axial_code = row['axial codes']
+    category = row['category']
+    description = row['description']
 
     sql_stmt_get = "SELECT id, path, description from TAGS where id = ? AND project_id = ?"
     cur.execute(sql_stmt_get, (tag_id, project_id))
@@ -46,21 +50,26 @@ for row in DictReader(csv_text.splitlines(), delimiter=","):
         oldname = tag_info[0][1]
         old_description = tag_info[0][2]
 
-        if row['Axial Codes']:
-            newname = row['Axial Codes'].lower() + "_" + new_name.lower()
+        if axial_code: 
+            newname = axial_code.lower() + "_" + new_name.lower()
         else:
             newname = new_name.lower()
 
+        new_description = description
+        if description and category:
+            new_description += " "
+        if category:
+            new_description += json.dumps({'category' : category})
+
         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():
+        if new_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']}")
+            cur.execute(sql_stmt_update, (new_description, project_id, tag_id))
+            print(f"UPDATE DESC: {old_description} → {new_description}")
 
 con.commit()
 con.close()