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