def test_setup_db(dbfile): dbfile = pathlib.Path(dbfile).resolve() if dbfile.exists(): dbfile.unlink() with sql.Database(dbfile) as db: with db.cursor() as c: c.executescript(""" CREATE TABLE categories ( id INTEGER PRIMARY KEY AUTOINCREMENT, category TEXT, parent INTEGER REFERENCES categories, UNIQUE (category,parent) ON CONFLICT IGNORE ); CREATE TABLE orders ( id INTEGER PRIMARY KEY AUTOINCREMENT, category INTEGER REFERENCES categories, type TEXT, date timestamp, num TEXT, sourcename TEXT, memo TEXT, quantity REAL, cost REAL, total REAL, dateadded timestamp /* To enforce unique values */ ); """)
def setUp(self): self.db = sql.Database(":memory:") self.db.addtables( sql.Table("""CREATE TABLE test (a TEXT, b INT);"""), sql.Table( """CREATE TABLE test2 (a INTEGER PRIMARY KEY AUTOINCREMENT, b INT);""" )) self.table1 = self.db.getadvancedtable("test") self.table2 = self.db.getadvancedtable("test2") return super().setUp()
def test_update_db(file, dbfile): """ TEST FUNCTION """ from alcustoms import IntegerPointer totalitems = IntegerPointer() today = datetime.datetime.today() def recurse(category, categoryid=None): for item in category['Items']: v = { "".join(k.lower().split()): item[k] for k in ["Type", "Date", "Num", "Memo", "Source Name"] } for k, Key in [("quantity", "Qty"), ("cost", "Cost Price"), ("total", "Amount")]: v[k] = item[Key] v['date'] = datetime.datetime.strptime(v['date'], "%m/%d/%Y") v['category'] = categoryid ## Have to check that we're not adding duplicates ## Unfortunately, there are no unique identifiers for lineitems row = ordertable.quickselect(**v).first() if row: ## Check if row was added during this run: if it wasn't ## then we are assuming that it's a duplicate if row.dateadded != today: continue ## If it was added as part of this loop, then it's a different ## lineitem, even if everything else is the same v['dateadded'] = today ordertable.addrow(**v) totalitems.increment() for cat, cdict in category['Categories'].items(): cid = categorytable.get_or_addrow(category=cat, parent=categoryid).first() recurse(cdict, cid) with open(file, 'r') as f: data = json.load(f) with sql.Database(dbfile, row_factory=sql.advancedrow_factory, detect_types=sql.PARSE_DECLTYPES) as db: ordertable = db.getadvancedtable("orders") categorytable = db.getadvancedtable("categories") with db.cursor() as c: recurse(data) def recurseitems(category, items=0): items += len(category['Items']) for cat in category['Categories'].values(): items = recurseitems(cat, items) return items items = recurseitems(data) print("Data Total Items:", items) print("Items Added:", totalitems) print("Total Items in DB:", len(ordertable.selectall()))
def test_analyse_function(file, dbfile): """ TEST FUNCTION """ with open(file, 'r') as f: data = json.load(f) db = sql.Database(dbfile, row_factory=sql.advancedrow_factory, detect_types=sql.PARSE_DECLTYPES) try: items = db.getadvancedtable("orders").selectall() finally: db.close() items = {item.item: item for item in items} inventory = data['Categories']['Inventory'] for itemcategory in inventory['Categories'].values(): for item, subdict in itemcategory['Categories'].items(): itemname = item.split("(")[0].strip() if itemname in items: print(itemname) else: print(">>>", itemname)
def loaddatabase(self, file): askclose = tkmessagebox.askokcancel( "Load New Database", "We must close the current database to open the new one.\nProceed?" ) if not askclose: return self.commandqueue = [] self.commandqueueindex = 0 self.database.close() self.showoutput(f">>>>>>>>>> Database Closed <<<<<<<<<<", "system") if file != MEMORY: shutil.copy(file, TEMPDB) file = TEMPDB self.database = sql.Database(file) self.database.row_factory = sql.dict_factory if file != MEMORY: self.showoutput(f">>>>>>>>>> Database {file} Loaded <<<<<<<<<<", "system") else: self.showoutput(">>>>>>>>>> Sample Database Loaded <<<<<<<<<<", "system") return True
def loaddb(self): file = filedialog.askopenfilename() if not file: return file = pathlib.Path(file).resolve() if not file.exists(): messagebox.showerror("Invalid File", "File does not exist") return try: file = shutil.copy(str(file), str(pathlib.Path.cwd().resolve() / "temp")) except Exception as e: messagebox.showerror( "Copy Failure", f"Failed to create work-copy of database:\n{e}") return try: db = sql.Database(file, row_factory=sql.dict_factory) except: messagebox.showerror("Invalid File", "Could not load Database") return return self.parent.loadexplorer(db)
def setUp(self): self.connection = sql.Database(":memory:")