taguette-update_tags_from_sheet.py

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