longbox (15557B)
1 #!/usr/bin/env python3 2 # 3 # ░█░░░█▀█░█▀█░█▀▀░█▀▄░█▀█░█░█░░ 4 # ░█░░░█░█░█░█░█░█░█▀▄░█░█░▄▀▄░░ 5 # ░▀▀▀░▀▀▀░▀░▀░▀▀▀░▀▀░░▀▀▀░▀░▀░░ 6 # 7 # what ░ comic book stash manager 8 # who ░ pyratebeard <root@pyratebeard.net> 9 # repo ░ https://git.pyratebeard.net/longbox 10 11 #░ imports 12 import os 13 import argparse 14 import re 15 from getpass import getpass 16 from mysql.connector import connect, Error 17 from prettytable import PrettyTable 18 import sqlite3 19 from sqlite3 import Error 20 import operator 21 import configparser 22 23 #░ local database dir 24 def get_db_dir(): 25 data_home = os.environ.get('XDG_DATA_HOME') 26 if data_home is None: 27 data_home = os.path.join(os.environ.get('HOME'), '.local', 'share') 28 return os.path.join(data_home, 'longbox') 29 30 #░ local conf dir 31 def get_conf_dir(): 32 conf_home = os.environ.get('XDG_CONFIG_HOME') 33 if conf_home is None: 34 conf_home = os.path.join(os.environ.get('HOME'), '.config') 35 return os.path.join(conf_home, 'longbox') 36 37 #░ mkdirs if not exist 38 def mkdirs(path): 39 try: 40 if not os.path.exists(path): 41 os.makedirs(path) 42 except Exception as e: 43 print(e) 44 os._exit(1) 45 46 #░ local database files 47 #░ for stash and wishlist 48 dbpath = get_db_dir() 49 mkdirs(dbpath) 50 stashdb = os.path.join(dbpath, 'stash.db') 51 wishdb = os.path.join(dbpath, 'wishlist.db') 52 53 #░ local conf file 54 confpath = get_conf_dir() 55 mkdirs(confpath) 56 longboxrc = os.path.join(confpath, 'longboxrc') 57 58 #░ parse arguments 59 parser = argparse.ArgumentParser(description = "comic book stash manager") 60 parser.add_argument("-q", "--query", help = "search for stuff", required = False, action='store', nargs='+') 61 parser.add_argument("-i", "--issue-id", help = "show specific issue id", required = False, default = None) 62 parser.add_argument("-s", "--series-id", help = "list all issues in series", required = False, default = None) 63 parser.add_argument("--show-variants", help = "show issue variants", required = False, action = 'store_true') 64 subparsers = parser.add_subparsers(help = "manage your stash", dest="stash") 65 66 parser_stash = subparsers.add_parser("stash", help = "manage your stash") 67 parser_stash.add_argument("-s", "--series-id", help = "list all issues in series", required = False, default = None) 68 parser_stash.add_argument("-c", "--category", help = "list or create a category", required = False, default = 'unsorted') 69 parser_stash.add_argument("-i", "--stash-issue", help = "stash specific issue", required = False, default = None) 70 parser_stash.add_argument("--show-variants", help = "show issue variants", required = False, action = 'store_true') 71 72 argument = parser.parse_args() 73 74 #░ vars from args 75 query = argument.query 76 issue_id = argument.issue_id 77 series_id = argument.series_id 78 variants = argument.show_variants 79 80 #░ stash vars from args 81 stash = argument.stash 82 if stash: 83 stash_category = argument.category 84 stash_category = stash_category.replace('-', '_') 85 stash_issue_id = argument.stash_issue 86 variants = argument.show_variants 87 88 #░ default search items 89 search_series = '%' 90 search_number = None 91 search_date = None 92 93 #░ parse query for date and/or issue number 94 #░ issue number must start with # 95 if query: 96 search_series = query[0] 97 # switch wildcard for use with mysql 98 search_series = search_series.replace('*', '%') 99 if len(query) >= 2: 100 numregexp = re.compile(r'^#') 101 dateregexp = re.compile(r'^[0-9]') 102 for q in query[1:]: 103 if numregexp.search(q): 104 search_number = q.lstrip('#') 105 if dateregexp.search(q): 106 search_date = q 107 108 #░ parse config file 109 config = configparser.ConfigParser() 110 config.read(longboxrc) 111 if 'gcd' in config: 112 gcdconf = config['gcd'] 113 if 'user' in gcdconf: 114 gcduser = gcdconf['user'] 115 else: 116 gcduser = input("gcd username: ") 117 if 'password' in gcdconf: 118 gcdpass = gcdconf['password'] 119 else: 120 gcdpass = getpass("gcd password: ") 121 122 #░ build some mysql statements 123 #░ if we are searching with an issue number 124 if search_number: 125 if variants: 126 search_terms = """ 127 SELECT gcd_issue.id,left(gcd_series.name, 50),left(gcd_issue.number, 10),left(gcd_issue.variant_name, 50),CONCAT(coalesce(gcd_series.year_began, 'unknown'), '-', coalesce(gcd_series.year_ended, 'unknown')),gcd_series.id,gcd_issue.publication_date,right(gcd_issue.barcode, 5) 128 FROM gcd_series JOIN gcd_issue 129 WHERE gcd_series.name LIKE %(search_series)s 130 AND (gcd_issue.number = %(search_number)s OR gcd_issue.number LIKE CONCAT(%(search_number)s, ' (%)')) 131 AND gcd_issue.series_id = gcd_series.id 132 AND (gcd_series.year_began <= %(search_began)s AND (gcd_series.year_ended >= %(search_ended)s or gcd_series.year_ended is null)) 133 """ 134 else: 135 search_terms = """ 136 SELECT gcd_issue.id,left(gcd_series.name, 50),left(gcd_issue.number, 10),left(gcd_issue.variant_name, 50),CONCAT(coalesce(gcd_series.year_began, 'unknown'), '-', coalesce(gcd_series.year_ended, 'unknown')),gcd_series.id,gcd_issue.publication_date,right(gcd_issue.barcode, 5) 137 FROM gcd_series JOIN gcd_issue 138 WHERE gcd_series.name LIKE %(search_series)s 139 AND (gcd_issue.number = %(search_number)s OR gcd_issue.number LIKE CONCAT(%(search_number)s, ' (%)')) 140 AND gcd_issue.series_id = gcd_series.id 141 AND (gcd_series.year_began <= %(search_began)s AND (gcd_series.year_ended >= %(search_ended)s or gcd_series.year_ended is null)) 142 GROUP BY gcd_issue.number 143 """ 144 else: 145 #░ if we are only searching with a date 146 if search_date: 147 search_terms = """ 148 SELECT gcd_series.id,left(gcd_series.name, 50),CONCAT(coalesce(gcd_series.year_began, 'unknown'), ' - ', coalesce(gcd_series.year_ended, 'unknown')),first.number,last.number 149 FROM gcd_series 150 INNER JOIN gcd_issue first 151 ON first.id = gcd_series.first_issue_id 152 INNER JOIN gcd_issue last 153 ON last.id = gcd_series.last_issue_id 154 WHERE gcd_series.name LIKE %(search_series)s 155 AND (gcd_series.year_began <= %(search_began)s AND (gcd_series.year_ended >= %(search_ended)s or gcd_series.year_ended is null)) 156 """ 157 else: 158 search_terms = """ 159 SELECT gcd_series.id,left(gcd_series.name, 50),CONCAT(coalesce(gcd_series.year_began, 'unknown'), ' - ', coalesce(gcd_series.year_ended, 'unknown')),first.number,last.number 160 FROM gcd_series 161 INNER JOIN gcd_issue first 162 ON first.id = gcd_series.first_issue_id 163 INNER JOIN gcd_issue last 164 ON last.id = gcd_series.last_issue_id 165 WHERE gcd_series.name LIKE %s 166 """ 167 168 #░ if issue id given 169 show_issue = """ 170 SELECT gcd_issue.id,left(gcd_series.name, 50),left(gcd_issue.number, 10),left(gcd_issue.variant_name, 50),CONCAT(coalesce(gcd_series.year_began, 'unknown'), ' - ', coalesce(gcd_series.year_ended, 'unknown')),gcd_series.id,gcd_issue.publication_date,right(gcd_issue.barcode, 5) 171 FROM gcd_series JOIN gcd_issue 172 WHERE gcd_issue.id = %s 173 AND gcd_issue.series_id = gcd_series.id 174 """ 175 176 #░ if series id given 177 if variants: 178 show_series = """ 179 SELECT gcd_issue.id,left(gcd_series.name, 50),left(gcd_issue.number, 10),left(gcd_issue.variant_name, 50),CONCAT(coalesce(gcd_series.year_began, 'unknown'), ' - ', coalesce(gcd_series.year_ended, 'unknown')),gcd_issue.publication_date,right(gcd_issue.barcode, 5) 180 FROM gcd_series JOIN gcd_issue 181 WHERE gcd_issue.series_id = %s 182 AND gcd_issue.series_id = gcd_series.id 183 """ 184 else: 185 show_series = """ 186 SELECT gcd_issue.id,left(gcd_series.name, 50),left(gcd_issue.number, 10),left(gcd_issue.variant_name, 50),CONCAT(coalesce(gcd_series.year_began, 'unknown'), ' - ', coalesce(gcd_series.year_ended, 'unknown')),gcd_issue.publication_date,right(gcd_issue.barcode, 5) 187 FROM gcd_series JOIN gcd_issue 188 WHERE gcd_issue.series_id = %s 189 AND gcd_issue.series_id = gcd_series.id 190 GROUP BY gcd_issue.number 191 """ 192 193 if stash: 194 stash_show_tables = """ 195 SELECT name FROM sqlite_master WHERE type='table'; 196 """ 197 198 stash_create_table = """ 199 CREATE TABLE IF NOT EXISTS {} ( 200 stash_issue_id INT(11), 201 stash_series_name VARCHAR(255), 202 stash_issue_number VARCHAR(50), 203 stash_variant_name VARCHAR(255), 204 stash_series_year INT, 205 stash_series_id INT(11), 206 stash_issue_date VARCHAR(255), 207 stash_barcode VARCHAR(38) 208 ); 209 """.format(stash_category) 210 211 stash_show_category = """ 212 SELECT * FROM {}; 213 """.format(stash_category) 214 215 stash_issue = """ 216 INSERT INTO {} ( 217 stash_issue_id, 218 stash_series_name, 219 stash_issue_number, 220 stash_variant_name, 221 stash_series_year, 222 stash_series_id, 223 stash_issue_date, 224 stash_barcode 225 ) VALUES ( 226 ?, 227 ?, 228 ?, 229 ?, 230 ?, 231 ?, 232 ?, 233 ? 234 ); 235 """.format(stash_category) 236 237 def stash_connection(db_file): 238 conn = None 239 try: 240 conn = sqlite3.connect(db_file) 241 stashcursor = conn.cursor() 242 if stash_issue_id: 243 try: 244 with connect( 245 host="localhost", 246 user=gcduser, 247 password=gcdpass, 248 database="gcd" 249 ) as connection: 250 with connection.cursor() as cursor: 251 cursor.execute(show_issue, (stash_issue_id,)) 252 for issue in cursor.fetchall(): 253 gcd_issue_id = issue[0] 254 gcd_series_name = issue[1] 255 gcd_issue_number = issue[2] 256 gcd_variant_name = issue[3] 257 gcd_series_year = issue[4] 258 gcd_series_id = issue[5] 259 gcd_issue_date = issue[6] 260 gcd_barcode = issue[7] 261 except error as e: 262 print(e) 263 264 stashcursor.execute(stash_create_table, {'category': stash_category,}) 265 266 stash_issue_tuple = (gcd_issue_id, gcd_series_name, gcd_issue_number, gcd_variant_name, gcd_series_year, gcd_series_id, gcd_issue_date, gcd_barcode) 267 stashcursor.execute('select * from ' + stash_category + ' where stash_issue_id='+stash_issue_id) 268 entry = stashcursor.fetchone() 269 270 if entry is none: 271 stashcursor.execute(stash_issue, stash_issue_tuple) 272 conn.commit() 273 else: 274 print('already stashed') 275 os._exit(0) 276 277 x.field_names = ['id','series','issue','variant','year','series_id','issue date','barcode'] 278 stashcursor.execute(stash_show_category) 279 for stashedissue in stashcursor.fetchall(): 280 x.add_row(stashedissue) 281 print(x.get_string(sort_key=operator.itemgetter(2, 0), sortby="series")) 282 283 elif stash_category != 'unsorted': 284 stashcursor.execute(stash_create_table, {'category': stash_category,}) 285 x.field_names = ['id','series','issue','variant','year','series_id','issue date','barcode'] 286 stashcursor.execute(stash_show_category) 287 for stashedissue in stashcursor.fetchall(): 288 x.add_row(stashedissue) 289 print(x.get_string(sort_key=operator.itemgetter(2, 0), sortby="series")) 290 291 else: 292 # can we get rowcount? 293 x.field_names = ['category', 'quantity'] 294 stashcursor.execute(stash_show_tables) 295 for stashcategory in stashcursor.fetchall(): 296 str = '' 297 for item in stashcategory: 298 str = str + item 299 category_quantity = stashcursor.execute("select * from " + str) 300 stashcategory_quantity = len(category_quantity.fetchall()) 301 stash_list = [str, stashcategory_quantity] 302 x.add_row(stash_list) 303 x.sortby = "category" 304 print(x) 305 306 except error as e: 307 print(e) 308 finally: 309 if conn: 310 conn.close() 311 312 #░ prettytable short var 313 x = PrettyTable() 314 x.align = "l" 315 316 #░ manage stash 317 if stash: 318 stash_connection(stashdb) 319 else: 320 #░ query gcd database 321 try: 322 with connect( 323 host="localhost", 324 user=gcduser, 325 password=gcdpass, 326 database="gcd" 327 ) as connection: 328 with connection.cursor() as cursor: 329 if issue_id: 330 x.field_names = ['id','series','issue','variant','year','series_id','issue date','barcode'] 331 cursor.execute(show_issue, (issue_id,)) 332 for issue in cursor.fetchall(): 333 x.add_row(issue) 334 print(x) 335 elif series_id: 336 x.field_names = ['id','series','issue','variant','year','issue date','barcode'] 337 cursor.execute(show_series, (series_id,)) 338 for issue in cursor.fetchall(): 339 x.add_row(issue) 340 x.sortby = "id" 341 print(x) 342 elif search_number: 343 x.field_names = ['id','series','issue','variant','year','series_id','issue date','barcode'] 344 if search_date is None: 345 cursor.execute(search_terms, {'search_series': search_series, 'search_number': search_number, 'search_number': search_number, 'search_began': 9999 , 'search_ended': 0 }) 346 for issue in cursor.fetchall(): 347 x.add_row(issue) 348 print(x.get_string(sort_key=operator.itemgetter(2, 0), sortby="series")) 349 else: 350 cursor.execute(search_terms, {'search_series': search_series, 'search_number': search_number, 'search_number': search_number, 'search_began': search_date, 'search_ended': search_date,}) 351 for issue in cursor.fetchall(): 352 x.add_row(issue) 353 print(x.get_string(sort_key=operator.itemgetter(2, 0), sortby="series")) 354 elif search_date: 355 x.field_names = ['id','series','year','first issue', 'last issue'] 356 print(search_date) 357 cursor.execute(search_terms, {'search_series': search_series, 'search_number': search_number, 'search_number': search_number, 'search_began': search_date, 'search_ended': search_date,}) 358 for issue in cursor.fetchall(): 359 x.add_row(issue) 360 print(x.get_string(sort_key=operator.itemgetter(2, 0), sortby="year")) 361 elif search_series != '%': 362 x.field_names = ['id','series','year','first issue', 'last issue'] 363 cursor.execute(search_terms, (search_series,)) 364 for issue in cursor.fetchall(): 365 x.add_row(issue) 366 x.sortby = "year" 367 print(x) 368 else: 369 parser.print_help() 370 os._exit(0) 371 except Error as e: 372 print(e)