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