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 __init__(self, author_id):
"""
Constructor
:param author_id: author_id to orders by
"""
self._fields = ORDER_TABLE_MAPPING.keys()
self.select = Select("orders", self._fields)
self.select.equals("author_id", author_id)
def query(self):
"""
Perform the queries. Reset the selector for reuse.
:return: Dictionaries of objects
"""
records = []
# must use the for loop does not work otherwise
for row in get_record(self.select):
record = dict(zip(self._fields, row))
records.append(record)
self.select = Select("orders", self._fields)
return records
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 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_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)
class GetOrders(object):
def __init__(self, author_id):
"""
Constructor
:param author_id: author_id to orders by
"""
self._fields = ORDER_TABLE_MAPPING.keys()
self.select = Select("orders", self._fields)
self.select.equals("author_id", author_id)
def by_author_id(self, author_id):
"""
Get the user with the specific author_id
:param author_id: The user author_id
"""
self.select.equals("author_id", author_id)
def with_product_id(self, product_id):
"""
The product id
:param product_id: Product id
"""
self.select.equals("product_id", product_id)
def query(self):
"""
Perform the queries. Reset the selector for reuse.
:return: Dictionaries of objects
"""
records = []
# must use the for loop does not work otherwise
for row in get_record(self.select):
record = dict(zip(self._fields, row))
records.append(record)
self.select = Select("orders", self._fields)
return records
def test_select(self):
sel = Select("test_table", ["a", "b"])
assert str(sel) == "SELECT a,b FROM test_table"
def __init__(self):
"""
Constructor
"""
self._fields = USER_TABLE_MAPPING.keys()
self.select = Select("users", self._fields)
class GetUsers(object):
def __init__(self):
"""
Constructor
"""
self._fields = USER_TABLE_MAPPING.keys()
self.select = Select("users", self._fields)
def by_author_id(self, author_id):
"""
Sets the author_id condition.
:param author_id: author_id to search by
"""
self.select.equals("author_id", author_id)
def by_address(self, address):
"""
Get by the address
:param address: Address to search by
"""
self.select.equals("address", address)
def in_city(self, city):
"""
Get by the city
:param city: City to search by
"""
self.select.equals("city", city)
def in_state(self, state):
"""
Get by the state
:param state: The state to search by
"""
self.select.equals("state", state)
def in_zip(self, zip):
"""
Get by the zip code
:param zip: Zip code to get by
"""
self.select.equals("zip", zip)
def query(self):
"""
Get the users
:return: Dictionaries of objects
"""
records = []
# must use the for loop does not work otherwise
for row in get_record(self.select):
record = dict(zip(self._fields, row))
records.append(record)
self.select = Select("users", self._fields)
return records