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)
