def run(config_dict=None, db=None, config_id=None):
if not config_dict:
config_dict = get_config(config_id)
bootstrap(config_dict)
logs = get_logs_from_config_id(config_dict.general.id)
if not logs["config"]:
raise ValueError("Empty log file")
metric_keys = set(["imagination_log_likelihood", "loss", "time_taken"])
plot_dir = config_dict.plot.base_path
for mode in ["train", "val"]:
for key in logs[mode]:
if key in metric_keys:
plot(logs[mode][key], mode, key, plot_dir)
if (USE_DATABASE):
best_metric_logs = log_to_spreadsheet(logs)
try:
if (not db):
db = Database(connect_to_firebase=False)
db.update_job(job_id=config_dict.general.id,
project_id=PROJECT,
data_to_update={"status": "recorded"})
except FileNotFoundError as f:
print("Could not log results to journal")
return best_metric_logs
else:
return None
def run_all_synced():
'''Method to run all the tasks that have been completed'''
sample_config = get_sample_config()
db = Database(connect_to_firebase=False)
appid_list = map(lambda x: x["id"],
db.list_jobs(status="synced", project=PROJECT))
metrics = []
flag = False
appid_list_to_print = []
appid_list = list(map(lambda x: str(x), appid_list))
for app_id in appid_list:
flag = True
print(app_id)
remote_config = make_remote_config(sample_config, app_id)
# print(app_id)
try:
best_metric_logs = run(config_dict=remote_config, db=db)
metrics.append(best_metric_logs)
except ValueError as e:
print("Error for {}. Message: {}".format(app_id, e))
continue
appid_list_to_print.append(app_id)
sleep(2)
if (flag):
print(appid_list_to_print)
summary_file_path = os.path.join(sample_config.general.base_path,
"summary.json")
with open(summary_file_path, "w") as f:
f.write(json.dumps(metrics, indent=4))
def comm_stats(self, bot: Bot, update: Update):
"""/stats for info about the current process"""
bot.send_message(update.message.chat_id,
"Thread count: %d\n"
"database total connection count: %d\n"
"database active connection count: %d\n"
"Process id: %d"
% (threading.active_count(), Database.get_count()[0], Database.get_count()[1], os.getpid()))
def set_blank_user_record(self, user):
db = Database()
sql = """
INSERT INTO economy_users (reddit_id)
VALUES (?)
"""
db.connection.cursor().execute(sql, (user.id, ))
db.connection.commit()
db.close()
def store_changes(self, user, funds_delta=0, items_delta=0):
db = Database()
sql = """
UPDATE economy_users
SET items_available = items_available + ?, funds_available = funds_available + ?
WHERE economy_users.reddit_id = ?
"""
db.connection.cursor().execute(sql,
(items_delta, funds_delta, user.id))
db.connection.commit()
db.close()
def set_database_schema(self):
db = Database()
sql = """
CREATE TABLE IF NOT EXISTS economy_users (
id integer PRIMARY KEY,
reddit_id text NOT NULL UNIQUE,
items_available integer NOT NULL DEFAULT 0,
funds_available integer NOT NULL DEFAULT 0)
"""
db.connection.cursor().execute(sql).fetchone()
db.close()
def retrieve_user_inventory(self, user):
db = Database()
sql = 'SELECT * FROM economy_users WHERE economy_users.reddit_id = ?'
user_record = db.connection.cursor().execute(sql,
(user.id, )).fetchone()
if user_record == None:
self.set_blank_user_record(user)
user_record = db.connection.cursor().execute(
sql, (user.id, )).fetchone()
db.close()
return user_record
def main():
bot_db = Database.connect(host=PG_HOST, port=PG_PORT,
user=PG_USER, password=PG_PASS,
database=PG_NAME)
bot = TikTokInformerBot(token=TOKEN, database=bot_db)
bot.run()
def get_one(cls, id):
todo_data = Database.find_one(collection="tasks", query={'id': id})
return cls(id=todo_data.get('id'),
author=todo_data.get('author'),
task=todo_data.get('task'),
date=todo_data.get('date'),
done=todo_data.get('done'))
def token_check(self, token):
self.db = Database()
token_data = self.db.select_one('tokens', dict(token=token))
if token_data:
return ObjectId(token_data['users_id'])
raise tornado.web.HTTPError(403)
def _delete_account(self):
# curselection return Tuple. I converted to a list (This tuple returns line numbers or elements)
index = list(self.list_box.curselection())
# Index returning the first item in its list
accounts = self._get_accounts()
username = accounts[index[0]]
answer = messagebox.askyesno('Remove account!',
'Are you sure you want to remove?')
if answer:
db = Database()
is_deleted = db.delete_account(username[3])
if is_deleted:
accounts = db.get_accounts()
self._set_accounts(accounts)
self.frame_list_box.delete(0, END)
else:
messagebox.showerror('SQL Error',
'There is problem with delete function')
def test_retrieve_user_inventory(self):
# Should make a new record if one does not exist
economy = Economy(MockReddit(), self.BASE_CONFIG)
user = MockUser()
sql = 'SELECT * FROM economy_users WHERE economy_users.reddit_id = ?'
record = Database().connection.cursor().execute(sql, (user.id, )).fetchone()
self.assertIsNone(record)
blank_record = economy.retrieve_user_inventory(user)
self.assertIsNotNone(blank_record)
async def main():
informer_db = Database.connect(host=PG_HOST,
port=PG_PORT,
user=PG_USER,
password=PG_PASS,
database=PG_NAME)
updater = Updater(token=TOKEN)
informer = TikTokInformer(database=informer_db, bot=updater.bot)
await informer.run()
async def websocket_endpoint(title: str, websocket: WebSocket):
title = title.replace("_", " ")
database = Database(sslmode=False)
with database.Session() as sess:
result = sess.query(WikiMap).filter_by(title=title, lpp=12).first()
logging.info(result)
graph = WikipediaGraph("Elon Musk", levels=3, lpp=12)
await websocket.accept()
steps = 0
delay = [1.5, 1, 0.75]
for json_chunk in graph.generate_from_wikimap(result, yield_size=15):
await websocket.send_json(json_chunk)
if steps < 3:
await asyncio.sleep(delay[0])
elif steps < 5:
await asyncio.sleep(delay[1])
else:
await asyncio.sleep(delay[2])
steps += 1
await websocket.close(code=1000)
class User(Database.declare_base()):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
email = Column(String(50))
points = Column(Integer)
all_points = Column(Integer)
time = Column(Time)
def __init__(self, email, amount, points, all_points, time):
self.email = email
self.amount = amount
self.points = points
self.all_points = all_points
self.time = time
def comm_sql_cmd(bot: Bot, update: Update):
"""/sql executes a mysql insert/update command on the pokemon database"""
txt = update.message.text
if len(txt) <= len("/sql_cmd "):
return # no parameter given
cmd = txt[len("/sql_cmd "):]
with Database() as database:
try:
database.cmd(cmd)
bot.send_message(update.message.chat_id, "Executed command without errors")
except MySQLError as e:
bot.send_message(update.message.chat_id, "{0}".format(e))
class Picture(Database.declare_base()):
__tablename__ = 'pictures'
id = Column(Integer, primary_key=True, autoincrement=True)
level = Column(Integer)
category = Column(String(50))
picture = Column(String(100))
def __init__(self, id, level, category, picture):
self.id = id
self.level = level
self.category = category
self.picture = picture
def __repr__(self):
return f"<User(amount={self.name}, accuracy={self.category}', time={self.picture})>"
def comm_sql_get(self, bot: Bot, update: Update):
"""/sql executes a mysql select command on the pokemon database"""
txt = update.message.text
if len(txt) <= len("/sql_get "):
return # no parameter given
cmd = txt[len("/sql_get "):]
with Database() as database:
try:
result = database.get_data_sorted(cmd)
send_large_code_message(bot, update.message.chat_id,
json.dumps(result, default=self.jdefault, indent=4))
except MySQLError as e:
bot.send_message(update.message.chat_id, "{0}".format(e))
def handle(bot_p: Bot, update: Update, _cmd_handlers):
with Database() as database:
trainer = Trainer(update.message.from_user.id)
menu_id = None
if trainer.does_exist(database):
trainer.load_values(values="menu_id", database=database)
menu_id = trainer.menu_id
if menu_id not in _cmd_handlers:
return
func = _cmd_handlers[menu_id]
use_db = self.uses_db(func)
if use_db:
func(bot_p, update, trainer, database)
if not use_db:
func(bot_p, update, trainer)
def run(storage_dir, db_collection, db_name, db_connection, batch_size):
"""A script for loading data from the MongoDB to the storage binary files.
"""
config = Config(
storage_dir=storage_dir,
db_collection=db_collection,
db_connection=db_connection,
db_name=db_name,
batch_size=batch_size,
)
session = Session(
config=config,
collection=get_db_collection(
connection_str=config.db_connection, db_name=config.db_name, collection_name=config.db_collection,
),
storage=Database(config.storage_dir),
)
start_data_watcher(session)
def init_schema(database):
with Database(database) as db:
db.execute("""CREATE TABLE IF NOT EXISTS chats (
chat_id INTEGER PRIMARY KEY,
lang VARCHAR(5) NOT NULL,
voice_enabled INTEGER,
photos_enabled INTEGER,
qr_enabled INTEGER,
active INTEGER,
ban INTEGER)
""")
db.execute("""CREATE TABLE IF NOT EXISTS stats (
month_year INTEGER PRIMARY KEY,
audio_num INTEGER,
min_tot_audio INTEGER,
min_transcribed_audio INTEGER,
num_pictures INTEGER)
""")
def button_handle(bot_p: Bot, update: Update):
id = ButtonId.from_string(update.callback_query.data)
if id not in self.buttonHandlers:
return
bttn_handlers = self.buttonHandlers[id]
with Database() as database:
trainer = Trainer(update.callback_query.from_user.id)
menu_id = None
if trainer.does_exist(database):
trainer.load_values(values="menu_id", database=database)
menu_id = trainer.menu_id
if menu_id not in bttn_handlers:
return
func = bttn_handlers[menu_id]
use_db = self.uses_db(func)
if use_db:
func(bot_p, update, trainer, database)
if not use_db:
func(bot_p, update, trainer)
def run(storage_dir):
"""The main user interface to select the query the stored data.
"""
config = Config(storage_dir=storage_dir, )
session = Session(
config=config,
storage=Database(config.storage_dir),
)
while True:
for index, question in enumerate(questions):
click.secho(f" {index+1} - {question.question}", fg="green")
value = click.prompt("Please choose the question number", type=int)
if value < 1 or value > len(questions):
click.secho("Bad number, choose again", fg="red")
continue
question = questions[value - 1]
click.secho(f"\n The chosen question: {question.question}", fg="green")
click.secho("Searching...", fg="green")
search_result = session.storage.count(question.collection_name,
limit=question.limit,
sorting=question.sorting)
results = search_result.results
click.secho("\nThe answer is:", fg="yellow")
if len(results) == 1:
click.secho(f" {results[0].value}", fg="yellow")
else:
for index, result in enumerate(results):
click.secho(f" {index+1}. {result.value}",
fg="yellow")
click.secho(
f"Searched {search_result.data_size} records in {search_result.time:0.2f} seconds."
)
click.secho("\nDo you want to search again?")
def __init__(self):
super(ApplicationWindow, self).__init__()
self.db = Database()
self.ui = Ui_Schedule()
self.ui.setupUi(self)
self.check_for_first_exec()
self.ui.prevWeekButton.clicked.connect(self.render_prev_week)
self.ui.nextWeekButton.clicked.connect(self.render_next_week)
self.render_controller = SchedulerRender(self.db)
self.class_info = self.render_controller.get_week_schedule()[0]
for i in range(28):
day_number = i // 4
class_number = i % 4 + 1
property = getattr(
self.ui, self.day_translation[day_number] + 'CheckBox_' +
str(class_number))
property.stateChanged.connect(self.check_state_changed)
property = getattr(
self.ui, self.day_translation[day_number] + 'ClassButton_' +
str(class_number))
property.clicked.connect(self.class_clicked)
def _save(self, update_or_save):
database = Database()
name = self.name.get()
phone = self.phone.get()
username = self.username.get()
password = self.password.get()
if username != '' or password != '':
if update_or_save:
database.save_account(name, phone, username, password)
messagebox.showinfo('INFO', 'Account saved')
self._reset()
accounts = self._get_accounts()
self._set_accounts(accounts)
else:
database.update_account(name, phone, username, password)
messagebox.showinfo('INFO', 'Account update')
self._reset()
self.update_button.config(state='disabled')
accounts = self._get_accounts()
self._set_accounts(accounts)
else:
messagebox.showerror('Credentials',
'Please enter username or password')
import asyncio
import re
import discord
from discord.ext import commands
from database.db import Database
from config import DATABASE_URL
db = Database(DATABASE_URL)
class Moderation(commands.Cog, name='модерация'):
def __init__(self, bot):
self.bot = bot
@commands.Cog.listener()
async def on_message(self, message):
banned_words = db.get_banword(message.channel.id)
banned_users = db.get_banned_users(message.channel.id)
if not message.author.bot:
for i in banned_words:
if message.content.startswith(i[0]) or re.search(
i[0], message.content):
await message.delete()
for i in banned_users:
if message.author.id == i[0]:
await message.delete()
@commands.command(
name='add',
help=
def show(self):
print('\nCargando... Por favor espere')
db = Database()
backup = File('backup')
# Opciones CON base de datos
if db.connect():
os.system('cls')
print('>>> EL AHORCADO (x.x) <<< - ' + self.nickname)
db.updateData()
backup = File('backup')
if backup.count():
print('1. Jugar\n' + '2. Ver puntuaciones\n' +
'3. Agregar palabra (Hay ' + str(backup.count()) +
')\n' + '4. Revisar palabras (-.-)\n' +
'5. Salir\n\nElige una opción: ')
option = input().lower()
if option == '1':
game = Game('online')
while not game.isOver():
os.system('cls')
print(
'PALABRAS\t Total: ' + game.getWordTotal() +
' (Punt. máx.) \tRestante: ' +
game.getWordCount() +
'\n-----------------------------------------------------\n'
+ '\tNo. de intentos: ' + game.getAtt() +
'\tPuntaje: ' + game.getScore() +
'\n-----------------------------------------------------'
+ game.getHanged() + '\n' + game.getWordPrint())
print('\n' + game.play(
input(
"\nEscribe una letra ('end' para salir/terminar): "
).upper()))
input()
os.system('cls')
print("---------------------" + "| FIN DE LA PARTIDA |" +
"---------------------\n" + 'Puntaje: ' +
game.getScore())
if int(game.getScore()) > 0:
if db.connect():
db.regScore(self.nickname, game.getScore())
print("Partida registrada con exito")
else:
localScore = File('score').add(self.nickname +
'|' +
game.getScore())
print(
"Partida completada, intenta conectarte al servidor para que sea registrada"
)
input()
if option == '2':
os.system('cls')
print('---> Puntuaciones <---\n')
score = File('score').getWords()
print(
"JUGADOR \tPUNTAJE MÁX. \tPUNTAJE TOTAL \tPARTIDAS JUGADAS\n"
)
for row in score:
row = row.split('|')
print(row[0] + '\t\t' + str(row[1]) + '\t\t' +
str(row[2]) + '\t\t' + str(row[3]))
input()
elif option == '3':
word = input(
'\nEscribe la palabra a ingresar ("c" para cancelar):\n'
).upper()
if word != 'C':
db.insertWord(word)
elif option == '4':
os.system('cls')
print('Palabras guardadas (¡Esponja enloqueciste!)\n')
for word in backup.getWords():
print(word)
input()
elif option == '5':
backup.close()
db.close()
exit()
else:
print('1. Agregar palabras para jugar\n' +
'2. Salir\n\nElige una opción: ')
option = input().lower()
if option == '1':
word = input(
'\nEscribe la palabra a ingresar ("c" para cancelar):\n'
).upper()
if word != 'C':
db.insertWord(word)
elif option == '2':
db.close()
backup.close()
exit()
# Opciones SIN base de datos
else:
os.system('cls')
print(
'>>> EL AHORCADO (x.x) <<< - ' + self.nickname + '\n' +
'No hay conexión al servidor\n' +
'Tus partidas y palabras añadidas se guardarán cuando haya conexión\n'
)
backup = File('backup')
if backup.count():
print('1. Jugar\n' + '2. Ver palabras disponibles\n' +
'3. Conectarse al servidor\n' + '4. Ver puntuaciones\n' +
'5. Añadir palabras\n' +
'6. Salir\n\nElige una opción: ')
option = input().lower()
if option == '1':
game = Game('offline')
while not game.isOver():
os.system('cls')
print(
'PALABRAS\t Total: ' + game.getWordTotal() +
' (Punt. máx.) \tRestante: ' +
game.getWordCount() +
'\n-----------------------------------------------------\n'
+ '\tNo. de intentos: ' + game.getAtt() +
'\tPuntaje: ' + game.getScore() +
'\n-----------------------------------------------------'
+ game.getHanged() + '\n' + game.getWordPrint())
print('\n' + game.play(
input(
"\nEscribe una letra ('end' para salir/terminar): "
).upper()))
input()
os.system('cls')
print("---------------------" + "| FIN DE LA PARTIDA |" +
"---------------------\n" + 'Puntaje: ' +
game.getScore())
if int(game.getScore()) > 0:
if db.connect():
db.regScore(self.nickname, game.getScore())
print("Partida registrada con exito")
else:
localScore = File('unsaved_score').add(
self.nickname + '|' + game.getScore())
print(
"Partida completada, intenta conectarte al servidor para que sea registrada"
)
input()
if option == '2':
os.system('cls')
print('Palabras guardadas (¡Esponja enloqueciste!)\n')
for word in backup.getWords():
print(word)
input()
elif option == '4':
os.system('cls')
print('---> Puntuaciones <---\n')
score = File('score').getWords()
print(
"JUGADOR \tPUNTAJE MÁX. \tPUNTAJE TOTAL \tPARTIDAS JUGADAS\n"
)
for row in score:
row = row.split('|')
print(row[0] + '\t\t' + str(row[1]) + '\t\t' +
str(row[2]) + '\t\t' + str(row[3]))
unsavedScore = File('unsaved_score').getWords()
if len(unsavedScore):
print("\nPartidas por registrar:")
for row in unsavedScore:
row = row.split('|')
print(row[0] + '\t' + str(row[1]))
input()
elif option == '5':
word = ''
while word.lower() != 'n':
word = str(
input(
'\nEscribe la palabra a ingresar ("n" para terminar): '
))
if word.lower() != 'n':
backup.add(word)
elif option == '6':
backup.close()
exit()
else:
print('1. Agregar palabras para jugar\n' +
'2. Salir\n\nElige una opción: ')
option = input().lower()
if option == '1':
word = ''
while word.lower() != 'n':
word = str(
input(
'\nEscribe la palabra a ingresar ("n" para terminar): '
))
if word.lower() != 'n':
backup.add(word)
elif option == '2':
backup.close()
exit()
backup.close()
self.show()
def testFull():
conn = Database().connect()
conn.drop_database("raceall_test")
conn.disconnect()
data = {}
data['user0'] = dict(username="******")
data['user1'] = dict(username="******")
data['user2'] = dict(username="******")
data['user3'] = dict(username="******")
data['user4'] = dict(username="******")
print "-------------------------------------------"
print "Create Users"
headers = {'content-type': 'application/json'}
url = "http://localhost:8888/user/"
for user in data:
payload = dict(username = user,
password1 = user,
password2 = user)
jdata = json.dumps(payload)
r = requests.post(url, data=jdata, headers=headers)
print "-------------------------------------------"
print "Login Users"
headers = {'content-type': 'application/json'}
url = "http://localhost:8888/user/login/"
for user in data:
payload = dict(username = user,
password = user)
jdata = json.dumps(payload)
r = requests.post(url, data=jdata, headers=headers)
data[user]['token'] = r.json['token']
print "-------------------------------------------"
print "Get all uuids"
headers = {'content-type': 'application/json'}
url = "http://localhost:8888/user/"
r = requests.get(url)
id_data = r.json
print "-------------------------------------------"
print "Create Friendships"
for user in ['user1', 'user3']:
url = "http://localhost:8888/friend/" + id_data[user] + '/'
headers = {'Authorization': data['user0']['token']}
r = requests.post(url, data=None, headers=headers)
for user in ['user0', 'user4']:
url = "http://localhost:8888/friend/" + id_data[user] + '/'
headers = {'Authorization': data['user1']['token']}
r = requests.post(url, data=None, headers=headers)
print "-------------------------------------------"
print "Get Friendships"
url = "http://localhost:8888/friend/"
headers = {'Authorization': data['user0']['token']}
r = requests.get(url, data=None, headers=headers)
print r.json
#print "Delete Friendships"
#url = "http://localhost:8888/friend/" + id_data['user1'] + '/'
#headers = {'Authorization': data['user0']['token']}
#r = requests.delete(url, data=None, headers=headers)
#print "Get Friendships"
#url = "http://localhost:8888/friend/"
#headers = {'Authorization': data['user0']['token']}
#r = requests.get(url, data=None, headers=headers)
#print r.json
print "-------------------------------------------"
print "Create Races"
url = "http://localhost:8888/race/"
headers = {'Authorization': data['user0']['token']}
payload = dict(name="race0",
vehicle="Cars",
description="race0",
private=True,
time=160)
jdata = json.dumps(payload)
r = requests.post(url, data=jdata, headers=headers)
print "-------------------------------------------"
print "Get Friendships Info"
url = "http://localhost:8888/friend/" + id_data['user0'] + "/"
headers = {'Authorization': data['user1']['token']}
r = requests.get(url, data=None, headers=headers)
print r.json
print "-------------------------------------------"
print "Get Friends Races"
url = "http://localhost:8888/friend/" + id_data['user0'] + '/'
headers = {'Authorization': data['user1']['token']}
r = requests.get(url, data=None, headers=headers)
print r.json
race_data = r.json['0']['race']['_id']
'''
print "-------------------------------------------"
print "Get Race Data"
url = "http://localhost:8888/race/" + race_data + '/'
headers = {'Authorization': data['user1']['token']}
r = requests.get(url, data=None, headers=headers)
print r.json
#print "Delete Entire Race"
#url = "http://localhost:8888/race/" + race_data + '/'
#headers = {'Authorization': data['user0']['token']}
#r = requests.delete(url, data=None, headers=headers)
#print "Get Friends Races"
#url = "http://localhost:8888/friend/race/" + id_data['user0'] + '/'
#headers = {'Authorization': data['user1']['token']}
#r = requests.get(url, data=None, headers=headers)
#print r.json
'''
print "-------------------------------------------"
print "Add yourslef to race"
url = "http://localhost:8888/race/" + race_data + '/user/'
headers = {'Authorization': data['user1']['token']}
payload = dict(friend_id=id_data['user0'])
#payload = {}
jdata = json.dumps(payload)
r = requests.post(url, data=jdata, headers=headers)
#print "Delete Race User Data"
#url = "http://localhost:8888/race/" + race_data + '/user/'
#headers = {'Authorization': data['user1']['token']}
#r = requests.delete(url, data=None, headers=headers)
#print r.json
print "-------------------------------------------"
print "Get Race User Data"
url = "http://localhost:8888/race/" + race_data + '/user/'
headers = {'Authorization': data['user0']['token']}
r = requests.get(url, data=None, headers=headers)
print r.json
print "-------------------------------------------"
print "Create Race Times"
url = "http://localhost:8888/race/" + race_data + '/time/'
headers = {'Authorization': data['user0']['token']}
for time in [9999, 1234, 4321, 1111, 2222, 3333, 20, 20, 15, 999999]:
payload = dict(time=time)
jdata = json.dumps(payload)
r = requests.post(url, data=jdata, headers=headers)
print "-------------------------------------------"
print "Create Race Times"
url = "http://localhost:8888/race/" + race_data + '/time/'
headers = {'Authorization': data['user1']['token']}
for time in [999, 134, 421, 111, 2222, 3333, 20, 20, 15, 99999]:
payload = dict(time=time)
jdata = json.dumps(payload)
r = requests.post(url, data=jdata, headers=headers)
#XXX: Don't think I need this
#print "-------------------------------------------"
#print "Get Race Times"
#url = "http://localhost:8888/race/" + race_data + '/time/'
#headers = {'Authorization': data['user0']['token']}
#r = requests.get(url, data=None, headers=headers)
#print r.json
print "-------------------------------------------"
print "Get User Race Data"
url = "http://localhost:8888/user/races/"
headers = {'Authorization': data['user0']['token']}
r = requests.get(url, data=None, headers=headers)
print r.json
print "-------------------------------------------"
print "Get Full Race Data"
url = "http://localhost:8888/race/" + race_data + "/"
headers = {'Authorization': data['user0']['token']}
r = requests.get(url, data=None, headers=headers)
print r.json
from database.db import Database
db = Database()
def upsert_provider(params):
sql_params = [params['provider_name'], params['provider_name']]
sql_stmt = '''insert into providers
(provider) values (%s)
ON CONFLICT (provider) DO UPDATE
SET provider = %s RETURNING id'''
return db.run_query(sql_stmt, sql_params, 'one')
def update_provider(params, provider_id):
sql_params = [params['provider_name']]
sql_str = ''
if 'street_address' in params:
sql_str += ', street_address = %s'
sql_params.append(params['street_address'])
if 'phone' in params:
sql_str += ', phone = %s'
sql_params.append(params['phone'])
if 'speciality' in params:
sql_str += ', speciality = %s'
sql_params.append(params['speciality'])
if 'notes' in params:
sql_str += ', notes = %s'
sql_params.append(params['notes'])
if 'city' in params:
sql_str += ', city = %s'
sql_params.append(params['city'])
if 'state' in params:
"""
engine = create_engine(uri)
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
engine.dispose()
def create_database(uri: str) -> None:
engine = create_engine(uri)
Base.metadata.create_all(engine)
engine.dispose()
def get_all(db):
sess = db.Session()
query = sess.query(WikiMap).order_by(WikiMap.title).all()
sess.close()
return query
if __name__ == "__main__":
database = Database(sslmode=False)
# delete_recreate_database(database.DATABASE_URI)
create_sample_entries(database.Session)
results = get_all(database)
print(results)
database.close()
# prod_url = config.get_production_config_locally()
# database = Database(prod_url)
def initialize(self):
self.db = Database()
class BaseHandler(tornado.web.RequestHandler):
def initialize(self):
self.db = Database()
def token_new(self, username):
user_data = self.db.select_one('users', dict(username=username))
#Check if user already has token
token = self.db.select_one('tokens', dict(users_id=user_data['_id']))
if token:
self.write(dict(token = token['token']))
else:
token = base64.b64encode(OpenSSL.rand.bytes(16))
self.db.insert('tokens', dict(users_id=user_data['_id'],
token=token,
created=datetime.datetime.utcnow()))
self.write(dict(token = token))
def token_check(self, token):
self.db = Database()
token_data = self.db.select_one('tokens', dict(token=token))
if token_data:
return ObjectId(token_data['users_id'])
raise tornado.web.HTTPError(403)
def pw_encrpyt(self, password):
return hashpw(password, gensalt())
def friend_check(self, user_id, friend_id):
friend_data = self.db.select_one('friends', dict(user_id = user_id,
friend_id = friend_id))
user_data = self.db.select_one('friends', dict(user_id = friend_id,
friend_id = user_id))
if friend_data and user_data:
return True
return False
def race_user_check(self, user_id, race_id):
if self.db.select_one('race_user', dict(user_id = user_id,
race_id = race_id)):
return True
return False
def race_owner_check(self, user_id, race_id):
if self.db.select_one('races', dict(user_id = user_id,
_id = race_id)):
return True
return False
def race_public_check(self, race_id):
if self.db.select_one('races', dict(private = False,
_id = race_id)):
return True
return False
def on_finish(self):
self.db.close()
class DML:
def __init__(self, db=None):
if db:
self.conn = db
else:
self.conn = Database().create_connection()
self.query_comanda_exists = """
SELECT * FROM Comandas
WHERE cliente_id = {}
AND fim IS NULL
AND data_comanda = '{}';
"""
def destroy_me(self):
try:
self.conn.close()
except Exception as err:
logging.critical(err, type(err))
##############################################
# Clientes #
##############################################
def insert_client(self, cliente: Cliente):
self.conn.execute(
"INSERT INTO Clientes (nome, cpf, telefone) VALUES (?, ?, ?)",
(cliente.nome, cliente.cpf, cliente.telefone))
self.conn.commit()
def delete_client(self, _id: int):
self.conn.execute("DELETE FROM Clientes WHERE ID = ?", str(_id))
self.conn.commit()
def edit_client(self, set_query, where):
self.conn.execute(f"UPDATE Clientes SET {set_query} WHERE {where}")
self.conn.commit()
def find_client(self, where):
execute = self.conn.execute(f"SELECT * FROM Clientes WHERE {where}")
fetch = execute.fetchone()
return {k[0]: v for k, v in list(zip(execute.description, fetch))}
def find_all_clientes(self):
try:
execute = self.conn.execute(
"SELECT ID, nome, telefone FROM Clientes;")
fetch = execute.fetchall()
columns = [v[0] for v in execute.description]
list_return = []
for f in fetch:
list_return.append(dict(zip(columns, f)))
return list_return
except Exception as e:
logging.critical(e, type(e))
return []
##############################################
# Comandas #
##############################################
def insert_comanda(self, cliente_id: str, inicio: str, data_comanda: str):
verify = self.conn.execute(
self.query_comanda_exists.format(cliente_id, data_comanda))
if not verify.fetchone():
self.conn.execute(
"INSERT INTO Comandas (cliente_id, inicio, data_comanda) VALUES (?, ?, ?)",
(cliente_id, inicio, data_comanda))
self.conn.commit()
def delete_comanda(self, cliente_id: int, data_comanda: str):
self.conn.execute(
f"DELETE FROM Comandas WHERE cliente_id = {str(cliente_id)} AND data_comanda = '{data_comanda}' AND fim IS NULL"
)
self.conn.commit()
def find_active_comanda_by_client_id(self, cliente_id: int):
try:
execute = self.conn.execute(
"SELECT * FROM Comandas WHERE cliente_id = {} AND fim IS NULL".
format(cliente_id))
fetch = execute.fetchone()
return {k[0]: v for k, v in list(zip(execute.description, fetch))}
except Exception as err:
logging.critical(err, type(err))
return {}
def order(self, comanda_id: int):
try:
execute = self.conn.execute(
"""SELECT PedidosComanda.produto_id, produto, quantidade, Produtos.valor, ROUND(quantidade*valor, 2) as total
FROM PedidosComanda
JOIN Produtos ON PedidosComanda.produto_id = Produtos.ID
WHERE comanda_id = {}""".format(comanda_id))
fetch = execute.fetchall()
columns = [v[0] for v in execute.description]
list_return = []
for f in fetch:
list_return.append(dict(zip(columns, f)))
return list_return
except Exception as err:
logging.critical(err, type(err))
return None
def total_comanda(self, comanda_id):
query = f"""
SELECT SUM(valor * quantidade) AS total_value FROM PedidosComanda
JOIN Produtos on PedidosComanda.produto_id = Produtos.ID
WHERE comanda_id = {comanda_id};
"""
try:
execute = self.conn.execute(query)
fetch = execute.fetchone()
return fetch[0]
except Exception as err:
logging.critical(err, type(err))
return None
def find_active_comandas(self):
try:
execute = self.conn.execute("""
SELECT c.ID, c.cliente_id, cl.nome, c.inicio, c.fim, c.data_comanda
FROM Comandas c
JOIN Clientes cl ON c.cliente_id = cl.ID
WHERE fim IS NULL
ORDER BY inicio ASC;
""")
fetch = execute.fetchall()
columns = [v[0] for v in execute.description]
list_return = []
for f in fetch:
list_return.append(dict(zip(columns, f)))
for comanda in list_return:
total = self.total_comanda(comanda["ID"])
comanda["total"] = round(total, 2) if total else 0
return list_return
except Exception as e:
logging.critical(e, type(e))
return []
def find_finished_comandas(self):
try:
execute = self.conn.execute("""
SELECT c.ID, c.cliente_id, cl.nome, c.inicio, c.fim, c.data_comanda
FROM Comandas c
JOIN Clientes cl ON c.cliente_id = cl.ID
WHERE fim IS NOT NULL
ORDER BY inicio ASC;
""")
fetch = execute.fetchall()
columns = [v[0] for v in execute.description]
list_return = []
for f in fetch:
list_return.append(dict(zip(columns, f)))
for comanda in list_return:
total = self.total_comanda(comanda["ID"])
comanda["total"] = round(total, 2) if total else 0
return list_return
except Exception as e:
logging.critical(e, type(e))
return []
def edit_comanda(self, set_query, where):
query = f"UPDATE Comandas SET {set_query} WHERE {where}"
self.conn.execute(query)
self.conn.commit()
def finish_comanda(self, cliente_id: int, fim: str):
self.conn.execute(
f"UPDATE Comandas SET fim = '{fim}' WHERE cliente_id = {cliente_id} and fim IS NULL"
)
self.conn.commit()
##############################################
# PRODUTOS #
##############################################
def insert_produto(self, produto_values: Produto):
verify = self.conn.execute(
f"SELECT * FROM Produtos WHERE produto = '{produto_values.produto}'; "
)
if not verify.fetchone():
self.conn.execute(
"INSERT INTO Produtos (produto, valor, unidade) VALUES (?, ?, ?);",
(produto_values.produto, produto_values.valor,
produto_values.unidade))
_id = self.conn.execute(
f"SELECT ID FROM Produtos WHERE produto = '{produto_values.produto}'; "
).fetchone()[0]
self.conn.execute(
"INSERT INTO Estoque (produto_id, quantidade) VALUES (?, ?);",
(_id, 0))
self.conn.commit()
def delete_produto(self, produto_id: int):
self.conn.execute(f"DELETE FROM Produtos WHERE ID = {produto_id};")
self.conn.execute(
f"DELETE FROM Estoque WHERE produto_id = {produto_id};")
self.conn.commit()
def find_all_products(self):
try:
execute = self.conn.execute("SELECT * FROM Produtos;")
fetch = execute.fetchall()
columns = [v[0] for v in execute.description]
list_return = []
for f in fetch:
list_return.append(dict(zip(columns, f)))
return list_return
except Exception as e:
logging.critical(e, type(e))
return []
def find_like_produtos(self, product_name: str):
try:
execute = self.conn.execute(
f"SELECT * FROM Produtos WHERE produto LIKE '%{product_name}%';"
)
fetch = execute.fetchall()
columns = [v[0] for v in execute.description]
list_return = []
for f in fetch:
list_return.append(dict(zip(columns, f)))
return list_return
except Exception as e:
logging.critical(e, type(e))
return []
def edit_produto(self, set_query, where):
query = f"UPDATE Produtos SET {set_query} WHERE {where};"
self.conn.execute(query)
self.conn.commit()
##############################################
# PEDIDOSCOMANDA #
##############################################
def select_all_pedido(self, comanda_id):
try:
execute = self.conn.execute(f"""
SELECT * FROM PedidosComanda
JOIN Produtos P on PedidosComanda.produto_id = P.ID
WHERE comanda_id={comanda_id};
""")
fetch = execute.fetchall()
columns = [v[0] for v in execute.description]
list_return = []
for f in fetch:
list_return.append(dict(zip(columns, f)))
return list_return
except Exception as e:
logging.critical(e, type(e))
return []
def insert_pedido(self, comanda_id: int, produto_id: int, quantidade):
self.conn.execute(
"INSERT INTO PedidosComanda (comanda_id, produto_id, quantidade) VALUES (?, ?, ?)",
(comanda_id, produto_id, quantidade))
self.conn.commit()
def remove_pedido(self, comanda_id: int, produto_id: int):
self.conn.execute(
"DELETE FROM PedidosComanda WHERE comanda_id = {} AND produto_id = {}"
.format(comanda_id, produto_id))
self.conn.commit()
def edit_pedido(self, set_query, where):
query = f"UPDATE PedidosComanda SET {set_query} WHERE {where}"
self.conn.execute(query)
self.conn.commit()
##############################################
# ESTOQUE #
##############################################
def edit_quantity_estoque(self, produto_id: int, new_quantity: int):
self.conn.execute(
f"UPDATE Estoque SET quantidade = {new_quantity} WHERE produto_id = {produto_id};"
)
self.conn.commit()
def view_estoque(self):
try:
execute = self.conn.execute("""
SELECT Estoque.*, P.produto FROM Estoque
JOIN Produtos P on Estoque.produto_id = P.ID
""")
fetch = execute.fetchall()
columns = [v[0] for v in execute.description]
list_return = []
for f in fetch:
list_return.append(dict(zip(columns, f)))
return list_return
except Exception as e:
logging.critical(e, type(e))
return []
def view_single_product_estoque(self, product_id: int):
execute = self.conn.execute(f"""
SELECT Estoque.*, P.produto FROM Estoque
JOIN Produtos P on Estoque.produto_id = P.ID
WHERE produto_id = {product_id}
""")
fetch = execute.fetchone()
return {k[0]: v for k, v in list(zip(execute.description, fetch))}
def __init__(self, db=None):
if db:
self.conn = db
else:
self.conn = Database().create_connection()
self.query_comanda_exists = """
