def create():
orders = Table.Table('orders')
clients = Table.Table('clients')
archive = Table.Table('archive')
try:
archive.create_table({
'user_id': 'text',
'count': 'text',
'date': 'text',
})
except OperationalError:
print('WARNING: Operational error while creating ArchiveTable')
try:
orders.create_table({
'user_id': 'text',
'count': 'text',
'room': 'text',
'time': 'text',
'type': 'text',
'date': 'text',
})
except OperationalError:
print('WARNING: Operational error while creating OrderTable')
try:
clients.create_table({
'user_id': 'text',
'room': 'text',
})
except OperationalError:
print('WARNING: Operational error while creating ClientsTable')
def distinct(table: Table, column):
""" returns a filtered table where all rows are distinct """
newTable = Table(table.name, table.schema)
columnIndex = table.keyToPosDict[column]
valueSet = set()
for row in table:
val = row[columnIndex]
if val in valueSet:
continue
valueSet.add(val)
newTable.append(row)
return newTable
def where(table: Table, column, symbol, constant):
"""
tuple = (column_name, operator, constant)
SLECT * IS NOT NULL
SELECT * WHERE id > 4;
>, <,=, !=, IS NOT, IS.
returns a filtered table
"""
constant = None if constant == "NULL" else constant
newTable = Table(table.name, table.schema)
func = operatorDictionary[symbol]
columnIndex = table.keyToPosDict[column]
# for row in table: # suspicious
# row.cleanRow(table.schema) # suspicious
for row in table:
if row[columnIndex] is None and constant is not None:
continue
if func(row[columnIndex], constant):
newTable.append(row)
return newTable
def createTable(self, tokens: deque) -> list:
""" conn.execute("CREATE TABLE myTable(name TEXT)")
CREATE TABLE IF NOT EXISTS students
"""
assert (tokens.popleft() == "CREATE")
assert (tokens.popleft() == "TABLE")
ifNotExists = False # Does sql statement have CREATE TABLE 'IF NOT EXISTS'?
if tokens[0] == "IF" and tokens[1] == "NOT" and tokens[2] == "EXISTS":
ifNotExists = True
tokens.popleft()
tokens.popleft()
tokens.popleft()
tableName = tokens.popleft()
# short circuit if table already exists and IF NOT EXISTS
if ifNotExists is True and tableName in self.db:
return
assert (tokens.popleft() == "(")
typesDict = {"TEXT": str, "INTEGER": int, "REAL": float, "NULL": None}
# maps variable title to the sqlite type's equivalent in python i.e. {name --> string, age --> integer}
# order is important to determine the supposed type of a value while it's being inserted as a row
schema = OrderedDict()
while True:
token = tokens.popleft()
if token in (')', ';'):
break
elif token == ',':
continue
else:
tokenType = typesDict[tokens.popleft()]
token = tableName + "." + token
schema[token] = tokenType
self.db[tableName] = Table(tableName, schema)
return [] # SQL CREATE returns an empty list
def leftOuterJoin(self, tokens):
"""
"SELECT students.name, students.grade, classes.course, classes.instructor
FROM students LEFT OUTER JOIN classes
ON students.class = classes.course
WHERE students.grade > 60
ORDER BY classes.instructor, students.name, students.grade;"
"""
whereArr = []
if "WHERE" in tokens:
tokens = list(tokens)
whereIndex = tokens.index("WHERE")
orderIndex = tokens.index("ORDER")
whereArr = tokens[whereIndex:orderIndex]
del tokens[whereIndex:orderIndex]
tokens = deque(tokens)
# PARSING STRING FIRST
fields, leftTableName, rightTableName, leftJoinCol, rightJoinCol, orderByColumns = leftJoinParse(
tokens)
# Make deep copies of left and right tables
leftTable: Table = deepcopy(self.db[leftTableName])
rightTable: Table = deepcopy(self.db[rightTableName])
newTableName = "joinedTable"
newSchema = OrderedDict()
for key, value in leftTable.schema.items():
newKeyName = newTableName + "." + key
newSchema[newKeyName] = value
for key, value in rightTable.schema.items():
newKeyName = newTableName + "." + key
newSchema[newKeyName] = value
newTable = Table(newTableName, newSchema)
self.db[newTableName] = newTable
lkeyToPosDict, rkeyToPosDict = leftTable.keyToPosDict, rightTable.keyToPosDict
rightNones = len(rightTable[0]) * [None]
# populate new table
for lrow in leftTable:
row = Row()
if lrow.getVal(leftJoinCol, lkeyToPosDict) is None:
row.extend(lrow + rightNones)
newTable.append(row)
continue
for rrow in rightTable:
if lrow.getVal(leftJoinCol, lkeyToPosDict) == rrow.getVal(
rightJoinCol, rkeyToPosDict):
row.extend(lrow + rrow)
newTable.append(row) # needs fixing, maybe?
break # there might be multiple tables with same matching id
else:
row.extend(lrow + rightNones)
newTable.append(row)
continue
for i, field in enumerate(fields):
fields[i] = addTableName(newTableName, field)
for i, field in enumerate(orderByColumns):
orderByColumns[i] = addTableName(newTableName, field)
fieldsString = ", ".join(fields)
orderString = ", ".join(orderByColumns)
whereString = ""
if whereArr:
whereArr[1] = addTableName(newTableName, whereArr[1])
whereArr = [str(i) for i in whereArr] # maybe switch none to NULL
whereString = " ".join(whereArr)
query = " SELECT {} FROM {} {} ORDER BY {};".format(
fieldsString, newTableName, whereString, orderString)
result = self.executeHelper(query)
# delete the table we created
self.db.pop(newTableName)
return result
def create():
products = Table.Table('products')
try:
products.create_table({'category': 'text', 'name': 'text'})
except OperationalError:
print('WARNING: Operational error while creating ProductTable')
import Database.Table as Table
import datetime
from math import factorial, exp
products = Table.Table('products')
users = Table.Table('users')
def parse_date(date):
arr = date.split('.')
return datetime.date(arr[2], arr[1], arr[0])
def probability_puasson(alpha, num):
return (exp(-alpha * num) * (alpha ** num)) / factorial(num)
class User:
def __init__(self, id):
self.id = id
self.dates_products = {}
self.get_dates_products()
def get_dates_products(self):
tickets = users.get_info_by_id(id=self.id, names=['tickets', 'date'])
"""
id_user | tickets | date
id id_prod id_prod id_prod id_prod dd.mm.yy
"""
for ticket in tickets:
date = parse_date(ticket[1])
def reset():
table_orders = Table.Table('orders')
records = table_orders.select(['user_id', 'date'])
for record in records:
if need_delete_order(record[1]):
table_orders.delete_info_by_user_id(record[0])