def test_insert(self): db = Database.instance(":memory:", "test_table", {"a": "integer"}) assert db db.executemany("INSERT INTO test_table(a) VALUES(?)", [(1, ), (2, )]) db.commit() db = Database.instance(None) v = db.execute("Select * FROM test_table") for record in v: assert (type(record) is tuple) assert (record[0] in [1, 2])
def build_db(database, table, headers): """ Build the productdata store :param database: The database :param table: Table to use :param headers: Headers to use :return: A prebuilt database object """ Database.instance(database, table, headers)
def create_tables(csv, create, headers, database, build_tables): """ Prepares the database :param csv: Whether to upload a csv :param create: Whether to create tables :param headers: Any csv headers :param database: Database or path to database to use :param build_tables: Whether to create tables """ if csv and create.lower() == "true": if headers: headers = json.loads(headers) else: product_headers = { "product_id": "varchar", "quantity": "integer", "wholesale_price": "double precision", "sale_price": "double precision", "supplier_id": "varchar" } order_headers = { "date": "integer", "author_id": "varchar", "zip": "varchar", "product_id": "varchar", "quantity": "integer" } order_mappings = { "date": "utc", "author_id": "varchar", "zip": "varchar", "product_id": "varchar", "quantity": "integer" } if build_tables: build_db(database, "products", headers) create_product_table() create_order_table() create_users_table() else: _db = Database.instance(database) upload_csv("product_data.csv", product_headers.keys(), table="products", has_headers=True) upload_csv("order_data.csv", order_headers.keys(), table="orders", has_headers=True, csv_mappings=order_mappings) else: _db = Database.instance(database)
async def below(message): """This function should now be able to handle more than just $20. This function searches the product dB and displays the first ten values under the price input by the user. """ # Substring the user's input to get the integer value. below = message.content[6:] # Check if the Substring contains a digit. if below.isdigit(): _db = Database.instance(None) sel = Select("products", ["product_id"]) sel.less_than_or_equal_to("sale_price", below) products = [] for product in get_record(sel): products.append(product[0]) rand_products = [] while rand_products != 10: rand_product_id = random.randint(0, len(products) - 1) chosen_random_prod = products[rand_product_id][0] rand_products.append(chosen_random_prod) msg = "The products below ", below, " are: {}".format( str(rand_products)) await message.channel.send(msg) else: # Simple help response if the user inputs something other than a digit. await message.channel.send( 'Please type a integer after !BELOW' '\n Example !BELOW20' '\n this will return the first ten items below $20')
def test_update_product(self): """ Test update a product """ _db = Database.instance(":memory:") try: create_product_table() except Exception as e: print(e) product = Product(1, 10.0, 20.0, 'test', 'test') product.save() product = GetProduct() product.by_product_id('test') products = product.query() print(products) assert len(products) >= 1 product = UpdateProduct() product.set_quantity(100) product.update() product = GetProduct() product.by_product_id('test') products = product.query() print(products) assert len(products) >= 1 assert products[0].get("quantity", 0) == 100
def write_csv_from_sql(db, headers, fpath, table="products", batch_size=10000): """ Writes the db file stored in the database to the file :param db: The db to write :param headers: The headers to use in the select :param fpath: The file path :param table: The table to insert into """ conn = Database.instance(db) c = conn.cursor() csv = CSVSink(fpath, headers) select = Select(table, headers) select = str(select) batch = [] for record in c.execute(str(select)): rdict = dict(zip(headers, record)) batch.append(rdict) if len(batch) > batch_size: csv.write_rows(batch) batch = [] if len(batch) > 0: csv.write_rows(batch) csv.close() del batch
def test_delete_order(self): _db = Database.instance(":memory:") try: create_order_table() except Exception as e: print(e) order = Order('test', 'test-order', city="test", state="test", address="test") order.product_id("test") order.price(10) order.quantity(100) order.save() order = GetOrders('test') orders = order.query() print(orders) assert len(orders) >= 1 assert orders[0].get("product_id", "NE") == "test" order = DeleteOrdersByUser('test') order.delete() order = GetOrders('test') orders = order.query() print(orders) assert len(orders) == 0
def test_create_records(self): """ Test create a record """ mp = {"a": "integer"} keys = mp.keys() _db = Database.instance(":memory:", "test_table", mp) create = Create("test_table", keys) create_records(keys, create, [{"a": 1}, {"a": 2}])
def test_create_product_table(self): """ Tests creating the product table """ _db = Database.instance(":memory:") try: create_users_table() except Exception as e: print(e)
def test_drop_users_table(self): """ Test drop the user table """ _db = Database.instance(":memory:") try: create_users_table() except Exception as e: print(e) drop_users_table()
def test_update_record(self): """ Test the updater """ mp = {"a": "integer"} keys = mp.keys() _db = Database.instance(":memory:", "test_table", mp) up = Update("test_table", {"a": 2}) up.less_than_or_equal_to("a", 3) update_record(up)
def test_dop_orders(self): """ Test drop the orders table """ _db = Database.instance(":memory:") try: create_order_table() except Exception as e: print(e) drop_order_table()
def test_create_user(self): """ Test creating a product """ _db = Database.instance(":memory:") try: create_users_table() except Exception as e: print(e) user = User('test', 'test', 'test', 'test', 'test') user.save()
def test_create_product(self): """ Test creating a product """ _db = Database.instance(":memory:") try: create_product_table() except Exception as e: print(e) product = Product(1, 10.0, 20.0, 'test', 'test') product.save()
def test_build_db(self): """ Test build db in main """ self.create_test_db() db = Database.instance(None) c = db.cursor() try: c.execute("SELECT * FROM products") finally: c.close()
def test_singleton_from_file(self): """ Test the singleton instance from file """ db = Database.instance("test.db") assert db v = db.execute("Select 1") assert (v) for record in v: assert (type(record) is tuple) assert (record[0] == 1)
def test_singleton_in_memory(self): """ Test the singleton in RAM """ db = Database.instance(":memory:") assert db v = db.execute("Select 1") assert v for record in v: assert (type(record) is tuple) assert (record[0] == 1)
def drop_table(query): """ Drop a table in the database :param query: Drop table query """ query = str(query) db = Database.instance(None) c = db.cursor() c.execute(query) db.commit() c.close()
def update_records(query): """ Update the records :param query: Query to run """ query = str(query) db = Database.instance(query) c = db.cursor() c.execute(query) db.commit() c.close()
def create_table(query): """ Create a table in the database :param query: Query object to serialize """ query = str(query) db = Database.instance(None) c = db.cursor() c.execute(query) db.commit() c.close()
def delete_record(query): """ Delete a record :param query: The delete query """ query = str(query) db = Database.instance(None) c = db.cursor() c.execute(query) db.commit() c.close()
def update_record(query): """ Update a record :param query: Query to update with """ query = str(query) db = Database.instance(None) c = db.cursor() c.execute(query) db.commit() c.close()
def write_csv_to_sql(filepath, db=":memory:", table="products", headers=None, has_headers=False, table_mappings=None, batch_size=100, csv_mappings=None): """ Writes a given CSV to a table Field mappings have the form { "product_id": "varchar", "cost": "double", "quantity": "integer" } :param filepath: The file path :param db: The database object :param table: Name of the table :param headers: Headers list in the order they appear in the db :param has_headers: The headers :param table_mappings: Table mappings for creation or None to avoid table creation :param batch_size: Number of records per batch :param csv_mappings: If present, forces the mappings to conform """ _conn = Database.instance(db, table, table_mappings) csv_headers = None if has_headers is False: csv_headers = headers elif headers: csv_headers = headers csv = CSVSource(filepath, headers=csv_headers, has_headers=has_headers, batch_size=batch_size) try: for batch in csv: if len(batch) > 0: if has_headers: record = batch[0] if csv_headers is None: headers = record.keys() else: csv_headers = headers query = Create(table, headers) if csv_mappings: batch = prepare_batch(batch, csv_mappings) create_records(headers, query, batch) finally: csv.close()
def get_record(query): """ Select records :param query: The query object :param generator: Whether to turn this fetch into a generator :return: records """ query = str(query) db = Database.instance(None) c = db.cursor() for row in c.execute(query): yield row c.close()
def test_select(self): """ Test the select """ mp = {"a": "integer"} db = Database.instance(":memory:", "test_table", mp) keys = mp.keys() create = Create("test_table", keys) create_records(keys, create, [{"a": 1}, {"a": 2}]) sel = Select("test_table", ["a"]) sel.less_than_or_equal_to("a", 3) rvals = [] for r in get_record(sel): rvals.append(r) assert (len(rvals) > 0)
def test_update_product(self): """ Test updating a product """ _db = Database.instance(":memory:") try: create_product_table() except Exception as e: print(e) product = Product(1, 10.0, 20.0, 'test', 'test') product.save() product = GetProduct() product.by_product_id('test') products = product.query() print(products) assert len(products) == 1
def test_get_user(self): """ Test obtaining a product """ _db = Database.instance(":memory:") try: create_users_table() except Exception as e: print(e) user = User('test', 'test', 'test', 'test', 'test') user.save() user = GetUsers() user.by_author_id('test') users = user.query() assert len(users) == 1 assert users[0].get("author_id", "NE") == 'test'
def create_records(keys, query, batch): """ Create a batch of records :para db: The database :param keys: Keys to use :param query: The query to use :param batch: Batch to update """ db = Database.instance(None) query = str(query) data = [tuple([record[x] for x in record.keys()]) for record in batch] c = db.cursor() c.executemany(query, data) db.commit() c.close()
def test_create_order(self): """ Test creating an order """ _db = Database.instance(":memory:") try: create_order_table() except Exception as e: print(e) order = Order('test', 'test-order', city="test", state="test", address="test") order.price(10) order.quantity(100) order.save()
def test_delete_record(self): mp = {"a": "integer"} _db = Database.instance(":memory:", "test_table", mp) keys = mp.keys() create = Create("test_table", keys) create_records(keys, create, [{"a": 1}, {"a": 2}]) sel = Select("test_table", ["a"]) sel.less_than_or_equal_to("a", 3) rvals = [] for r in get_record(sel): rvals.append(r) d = Delete("test_table") d.greater_than("a", 1) delete_record(d) sel = Select("test_table", ["a"]) sel.less_than_or_equal_to("a", 3) rvals = [] for r in get_record(sel): rvals.append(r)