taguette-update_tags_from_sheet.py

   1 #!/usr/bin/env python3
   2
   3 import requests
   4 from csv import DictReader
   5 import sqlite3
   6 from configparser import ConfigParser
   7 import re
   8 import json
   9 import os
  10
  11 config_files = [f for f in os.listdir() if f.startswith('.taguette_gdocs')]
  12
  13 for file_path in config_files:
  14
  15     config = ConfigParser()
  16     config.read(file_path)
  17
  18     # this is project ID from the configuration
  19     project_id = int(config['General']['taguette_project_id'])
  20     print(project_id)
  21     taguette_database_file = config['General']['taguette_database_file']
  22
  23     ## connect to sqlite3
  24     con = sqlite3.connect(taguette_database_file)
  25     cur = con.cursor()
  26
  27     # load the googgle sheet ID from the configuration
  28     gsheet_id = config['General']['gsheet_id']
  29     gsheet_gid = config['General']['gsheet_gid']
  30
  31     # get the spreadsheet data
  32     axial_url = f"https://docs.google.com/spreadsheets/d/{gsheet_id}/export?format=csv&id={gsheet_id}&gid={gsheet_gid}"
  33     rv = requests.get(axial_url)
  34     csv_text = rv.content.decode('utf-8')
  35
  36     # import taguette.database as tagdb
  37     # db = tagdb.connect("sqlite:////home/mako/taguette-snapshot-20210422-1.sqlite3")
  38
  39     for row in DictReader(csv_text.splitlines(), delimiter=","):
  40         #print(row)
  41         tag_id = row['id']
  42         new_name = row['tag']
  43         axial_code = row['axial codes']
  44         category = row['category']
  45         description = row['description']
  46
  47         sql_stmt_get = "SELECT id, path, description from TAGS where id = ? AND project_id = ?"
  48         cur.execute(sql_stmt_get, (tag_id, project_id))
  49         tag_info = cur.fetchall()
  50
  51         if len(tag_info) > 1:
  52             print(f"ERROR: '{id}' is not unique, SKIPPING")
  53         elif len(tag_info) == 0:
  54             print(f"ERROR: 'tag with ID {id}' does not exist, SKIPPING")
  55         else:
  56             oldname = tag_info[0][1]
  57             old_description = tag_info[0][2]
  58
  59             if axial_code:
  60                 newname = axial_code.lower() + "_" + new_name.lower()
  61             else:
  62                 newname = new_name.lower()
  63
  64             new_description = description
  65             if description and category:
  66                 new_description += " "
  67             if category:
  68                 new_description += json.dumps({'category' : category})
  69
  70             if not oldname == newname:
  71                 sql_stmt_update = "UPDATE tags SET path = ? WHERE project_id = ? AND id = ?"
  72                 cur.execute(sql_stmt_update, (newname, project_id, tag_id))
  73                 print(f"UPDATE TAG for {project_id}: {oldname} → {newname}")
  74
  75             if new_description.strip() != old_description.strip():
  76                 sql_stmt_update = "UPDATE tags SET description = ? WHERE project_id = ? AND id = ?"
  77                 cur.execute(sql_stmt_update, (new_description, project_id, tag_id))
  78                 print(f"UPDATE DESC for {project_id}:: {old_description} → {new_description}")
  79
  80     con.commit()
  81     con.close()
  82
  83