def _modify_req(request: Request) -> Response:
if request.authenticated_userid is None:
return Response(status=401,
body=json.dumps({}),
content_type='application/json')
try:
req_data = request.json_body
req_stmt = text('SELECT * from cocollector."Pedido" where "ID" = :id'
).bindparams(id=request.authenticated_userid)
req: dict = json.loads((db.execute(req_stmt)))
if 'total' in req_data:
req['Total'] = req_data['total']
if 'cantidad' in req_data:
req['Cantidad'] = req_data['cantidad']
if 'orden' in req_data:
req['Orden'] = req_data['orden']
if 'producto' in req_data:
req['Producto'] = req_data['producto']
update_stmt = text(
'UPDATE cocollector."Pedido" SET "Total" = :total, '
'"Cantidad" = :cantidad, "Orden" = :orden, '
'"Producto" = :producto where "ID" = :id').bindparams(
total=req['Total'],
cantidad=req['Cantidad'],
orden=req['Orden'],
producto=req['Producto'])
db.execute(update_stmt)
except Exception as e:
print(e)
return Response(status=404, content_type='text/json')
def register():
"""Register user"""
if request.method == "POST":
username = request.form.get("username")
usr = db.execute("SELECT * FROM users WHERE username = :username", {
"username": username
}).fetchone()
# If db.execute returns a value to `usr` then the username is taken
if usr is not None:
flash("Name already in use.")
return render_template("register.html")
password = request.form.get("new-password")
if not password or len(password) < 5:
flash("Password must contain at least 5 characters.")
return render_template("register.html")
passhash = generate_password_hash(password)
db.execute(
"INSERT INTO users (username, password) VALUES \
(:username, :password)", {
"username": username,
"password": passhash
})
db.commit()
return redirect("/")
else:
return render_template("register.html")
def do_add_note(self, note, experiment_name):
'''Add a note to a given experiment.'''
with db:
db.execute(
'''INSERT INTO notes (experiment_name, note)
VALUES (?, ?)''', (experiment_name, note))
return format_notes_html(experiment_name)
def do_add_note(self, note, experiment_name):
'''Add a note to a given experiment.'''
with db:
db.execute('''INSERT INTO notes (experiment_name, note)
VALUES (?, ?)''',
(experiment_name, note))
return format_notes_html(experiment_name)
def do_delete(self, table, entry):
'''Delete an entry from a permitted table.'''
ids = {'experiments': 'name', 'strains': 'name', 'notes': 'timestamp'}
primary_key = ids[table]
with db:
db.execute('''DELETE FROM %s WHERE %s=?''' % (table, primary_key),
(entry, ))
def do_start_new_experiment(self, **kwargs):
'''Process the "new experiment" form and start an experiment.'''
logger.info('Starting new experiment %s...', kwargs['name'])
def prepare_event(event, kwargs):
arguments = list(inspect.signature(event.__init__).parameters)[1:]
name = event.__name__
prepared_kwargs = {
a: kwargs['%s_%s' % (event.__name__, a)]
for a in arguments
}
return event(**prepared_kwargs)
start = StartExperiment(**kwargs)
prepared_events = [
prepare_event(e, kwargs) for e in events
if e.__name__ + '__check' in kwargs
]
if not prepared_events:
raise ValueError('No measurement events scheduled.')
with db:
to_record = [
kwargs[_] for _ in [
'name', 'description', 'strain', 'row1', 'row2', 'row3',
'row4', 'col1', 'col2', 'col3', 'col4', 'col5'
]
]
db.execute(
'''INSERT INTO experiments (name, description, strain_name, row1, row2, row3, row4, col1, col2, col3, col4, col5)
VALUES (?, ?, ?, ?,?,?,?, ?,?,?,?,?)''', to_record)
reactor_scheduler.enter(0, -1, start)
for e in prepared_events:
reactor_scheduler.enter(0, 0, e)
raise cherrypy.HTTPRedirect('/')
def api(isbn):
query_book = f"SELECT * FROM books WHERE isbn = '{isbn}'"
query_review = f"SELECT * FROM reviews WHERE isbn = '{isbn}'"
bk = db.execute(query_book)
book = bk.fetchone()
rv = db.execute(query_review)
reviews = rv.fetchall()
score = 0
for rev in reviews:
score += int(rev['rating'])
avg_score = -1 if len(reviews) == 0 else score / len(reviews)
data_set = {
"title": book['title'],
"author": book['author_names'],
"year": book['year'],
"isbn": book['isbn'],
"review_count": len(reviews),
"average_score": avg_score,
}
json_dump = json.dumps(data_set)
json_object = json.loads(json_dump)
return json_object
def register():
if request.method == 'POST':
name = request.form.get('username')
#double checking, since it is done in the front end
if not name:
return apology("You must provide a user name.")
row = db.execute("select * from users where name = :name", {"name":name}).fetchone()
if row is not None:
return apology('This user is already registered.')
password = request.form.get('password')
if not password:
return apology("You must provide a passord")
confirmation = request.form.get('confirmation')
if not confirmation:
return apology("You must confirm your passord.")
# end double ckeck
hash_passw = generate_password_hash(password)
if not check_password_hash(hash_passw, confirmation):
return apology('Password and Confirmation do not match.')
else:
db.execute('insert into users(name, password) values(:name,:password)',
{"name":name, "password":hash_passw})
db.commit()
flash(f"User {name} is registered.")
return render_template('index.html')
# return redirect('/')
else:
return render_template('register.html')
def login_entry(request):
username = request.json_body['username']
password = request.json_body['password']
try:
stmt: TextClause = text(
'SELECT "username", "password" from usuario where logged = false and "username" = :username AND "password" = :password'
)
stmt = stmt.bindparams(username=username, password=password)
result: ResultProxy = db.execute(stmt)
user = [dict(r) for r in result][0]
print(user)
if user is not None:
token = request.create_jwt_token(user['username'])
stmt: TextClause = text(
'UPDATE usuario set logged = true where username = :usern')
stmt = stmt.bindparams(usern=username)
db.execute(stmt)
return Response(status=200,
content_type='application/json',
body=json.dumps({
'token': token,
'username': user['username']
}),
charset='utf-8')
except Exception as e:
return Response(status=400, content_type='application/json')
return Response(status=404, content_type='application/json')
def login(uid, token, bot):
api = get_api(uid, token, new=True)
try:
# Check for successful login
api.messages.getConversations(count=1)
except ConnectionError:
# Proxy error
log.error("Bot don't has access to VK.com")
return 1
except exceptions.ApiError:
# Unsuccessful login
return 2
chats = execute(
f"select chat_id from chats where uid = {uid} and vchat_id != 0;")
if chats:
for chat in chats:
bot.send_message(
chat_id=chat[0],
text="This chat has been deactivated because"
" you've logout from your account.\nUse /lc to choose new one")
execute(f"delete from chats where uid = {uid};")
execute(f"update logins set token = '{token}' where uid = {uid}")
# Adding api object to memory
apis[f"{uid}"] = api
return 0
def books(isbn):
# query database to get book data
book = db.execute('select * from books where isbn = :isbn', {"isbn":isbn}).fetchone()
# query goodreads apis(goodreads, bookrack) for average rating and number of ratings
book_data = api_book(book, isbn)
reviews = db.execute('''select id, isbn_id, name, review, rating from
users
join
(select user_id, isbn_id, review, rating
from reviews where isbn_id=:isbn)
as isbn_reviews
on id = isbn_reviews.user_id;''',
{"isbn":isbn}).fetchall()
# print(reviews)
rack_data = {}
this_has_review = check_user(reviews);
print(this_has_review)
if reviews:
counter = 0;
sum = 0;
for review in reviews:
counter += 1
sum += review['rating']
avg = sum / counter
rack_data['rack_avg'] = f'{avg:.2f}'
rack_data['rack_reviews'] = counter
return render_template('books.html', book=book_data, reviews=reviews,
rack=rack_data, this_has_review=this_has_review)
def deepPlayerSearch(name):
url = (
"http://www.mlb.com/lookup/json/named.search_player_all.bam?sport_code='mlb'&name_part='%s%%25'&active_sw='Y'"
% urllib2.urlencode(name.upper())
)
try:
file = urllib2.urlopen(url)
except urllib2.HTTPError:
return False
results = json.load(file)["search_player_all"]["query_results"]
if int(results["totalSize"]) == 1:
player = results["row"]
# Should probably figure out how to get their jersey number
db.execute(
"INSERT OR IGNORE INTO players (mlb_id, first, last, num, team) VALUES (?, ?, ?, ?, ?)",
(player["player_id"], player["name_first"], player["name_last"], 0, player["team_abbrev"]),
)
return getPlayer(player["player_id"])
return False
def api(isbn):
# query database to get book data
book = db.execute('select * from books where isbn = :isbn', {"isbn":isbn}).fetchone()
if book:
# print(book_data)
book_api = dict(book.items())
reviews = db.execute('''select review, rating from reviews
where isbn_id=:isbn''',
{"isbn":isbn}).fetchall()
print(reviews)
if reviews:
counter = 0;
sum = 0;
for review in reviews:
counter += 1
sum += review['rating']
avg = sum / counter
book_api['review_count'] = counter
book_api['average_score'] = float(f'{avg:.1f}')
else:
book_api['review_count'] = 0
# book_api['average_score'] = '-'
return jsonify(book_api), 200, {'Content-Type': 'application/json'}
else:
abort(404)
def post(self):
try:
args = parser.parse_args()
username = args['username']
password = args['password']
salt = uuid.uuid4().hex
user = execute('SELECT * FROM user WHERE username = %s', username)
if (user[0]['password'] == hashlib.sha512(
password.encode() + user[0]['salt'].encode()).hexdigest()):
newdataJson = json.loads(args['theme'].replace("'", '"'))
execute(
'''
INSERT INTO colortheme
(personID, main, background, background_lines, textcolor, textcolor2, papercolor)
VALUES(%s, %s, %s, %s, %s, %s, %s)
ON DUPLICATE KEY UPDATE
main = %s, background=%s, background_lines=%s,
textcolor=%s, textcolor2=%s, papercolor=%s
''', (newdataJson['personID'], newdataJson['main'],
newdataJson['background'], newdataJson['background_lines'],
newdataJson['textcolor'], newdataJson['textcolor2'],
newdataJson['papercolor'], newdataJson['main'],
newdataJson['background'], newdataJson['background_lines'],
newdataJson['textcolor'], newdataJson['textcolor2'],
newdataJson['papercolor']))
return {'success': True}
else:
return {'success': False}
except Exception as e:
return {'error': str(e)}
def post(self):
try:
args = parser.parse_args()
username = args['username']
password = args['password']
salt = uuid.uuid4().hex
user = execute('SELECT * FROM user WHERE username = %s', username)
if (user[0]['password'] == hashlib.sha512(
password.encode() + user[0]['salt'].encode()).hexdigest()):
newdataJson = json.loads(args['newdata'].replace("'", '"'))
execute(
'''
UPDATE person SET
name=%s, title=%s,
email=%s, location=%s,
resume=%s, image=%s,
github=%s, linkedin=%s
WHERE id=%s ''', (newdataJson['name'], newdataJson['title'],
newdataJson['email'], newdataJson['location'],
newdataJson['resume'], newdataJson['image'],
newdataJson['github'], newdataJson['linkedin'],
newdataJson['id']))
return {'success': True}
else:
return {'success': False}
except Exception as e:
return {'error': str(e)}
def login_entry(request):
if request.method == 'OPTIONS':
return login_cors(request)
user = request.json_body['user']
pwd = request.json_body['password']
try:
stmt: TextClause = text(
'SELECT "pk_usuario", "conexion" from usuario '
'where usuario."usuario" = :user AND "contraseña" = :pwd')
stmt = stmt.bindparams(user=user, pwd=pwd)
result: ResultProxy = db.execute(stmt)
user_id = [dict(r) for r in result][0]
print(user_id)
if user_id is not None and not user_id['conexion']:
token = request.create_jwt_token(user_id['pk_usuario'])
stmt: TextClause = text(
'UPDATE usuario SET conexion = true where pk_usuario = :id')
stmt = stmt.bindparams(id=user_id['pk_usuario'])
db.execute(stmt)
return Response(status=200,
content_type='application/json',
body=json.dumps({'token': token}),
charset='utf-8')
except Exception as e:
print(e)
return Response(status=404, content_type='application/json')
return Response(status=404, content_type='application/json')
def signup():
try:
name = request.form.get("name")
email = request.form.get("email")
password = (hashlib.sha256(
request.form.get("password").encode())).hexdigest()
if name is "" or password is "" or email is "":
raise Exception
if db.execute("SELECT * FROM user_details WHERE email = :username", {
"username": email
}).rowcount == 0:
db.execute(
"INSERT INTO user_details (name, email, password) VALUES (:name, :email, :password)",
{
"name": name,
"email": email,
"password": password
})
db.commit()
return render_template(
"success.html", message="Your account is created successfully")
else:
return render_template("error.html",
message="Email already Registered")
except Exception as e:
return render_template("error.html", message="Invalid URL.")
def post(self):
try:
args = parser.parse_args()
username = args['username']
password = args['password']
salt = uuid.uuid4().hex
user = execute('SELECT * FROM user WHERE username = %s', username)
if (user[0]['password'] == hashlib.sha512(
password.encode() + user[0]['salt'].encode()).hexdigest()):
newdataJson = json.loads(args['newdata'].replace("'", '"'))
if (newdataJson['title'] == "" and newdataJson['body'] == ""):
execute(
'''
DELETE FROM resume
WHERE id=%s''', (newdataJson['id']))
else:
execute(
'''
UPDATE resume SET
title=%s, body=%s
WHERE personID=%s AND id=%s''',
(newdataJson['title'], newdataJson['body'],
newdataJson['personID'], newdataJson['id']))
return {'success': True}
else:
return {'success': False}
except Exception as e:
return {'error': str(e)}
def create_transaction(request):
try:
user_data = request.json_body
str = 'En proceso'
stmt: TextClause = text(
'INSERT into bancoco."Transaccion"("Monto",'
'"Fecha",'
'"Status",'
'"Descripcion",'
'"Institucion",'
'"Tarjeta") VALUES (:monto, NOW(), :status,'
':desc, :institucion, :cuentahabiente) RETURNING "ID"')
stmt = stmt.bindparams(monto=user_data['monto'],
status=str,
desc=user_data['descripcion'],
institucion=user_data['institucion'],
cuentahabiente=user_data['tarjeta'])
results = db.execute(stmt)
result = [dict(r) for r in results][0]
stmt: TextClause = text(
'Select "Status" from bancoco."Transaccion" where "ID" = :id')
stmt = stmt.bindparams(id=result['ID'])
results = db.execute(stmt)
result = [dict(r) for r in results][0]
if result['Status'] == 'Aprobado':
return Response(status=200)
else:
return Response(status=500)
except Exception as e:
print(e)
return Response(status=400)
def update_user(request):
try:
user_data = request.json_body
user_stmt = text(
'SELECT * from bancoco."Cuentahabiente" where "Tarjeta" = :tarjeta'
).bindparams(tarjeta=user_data['Tarjeta'])
users = db.execute(user_stmt)
user = ([dict(r) for r in users][0])
if 'Nombre' in user_data:
user['Nombre'] = user_data['Nombre']
if 'Apellido_paterno' in user_data:
user['Apellido_paterno'] = user_data['Apellido_paterno']
if 'Apellido_materno' in user_data:
user['Apellido_materno'] = user_data['Apellido_materno']
if 'nickname' in user_data:
user['nickname'] = user_data['nickname']
if 'Correo' in user_data:
user['Correo'] = user_data['Correo']
if 'Contrasena' in user_data:
user['Contrasena'] = user_data['Contrasena']
if 'Nickname' in user_data:
user['Nickname'] = user_data['Nickname']
if 'Fondos' in user_data:
user['Fondos'] = user_data['Fondos']
if 'Municipio' in user_data:
user['Municipio'] = user_data['Municipio']
if 'CP' in user_data:
user['CP'] = user_data['CP']
update_stmt = text(
'UPDATE bancoco."Cuentahabiente" SET "Nickname" = :nickname,'
'"Correo" = :correo,'
'"Contrasena" = :contrasena,'
'"Nombre" = :nombre,'
'"Apellido_paterno" = :apellidoPaterno,'
'"Apellido_materno" = :apellidoMaterno,'
'"Fecha_Expiracion" = :fechaExpiracion,'
'"CVV" = :cvv,'
'"Fondos" = :fondos,'
'"CP" = :cp,'
'"Municipio" = :municipio where "Tarjeta" = :tarjeta').bindparams(
nickname=user['Nickname'],
correo=user['Correo'],
contrasena=user['Contrasena'],
nombre=user['Nombre'],
apellidoPaterno=user['Apellido_paterno'],
apellidoMaterno=user['Apellido_materno'],
tarjeta=user['Tarjeta'],
fechaExpiracion=user['Fecha_Expiracion'],
cvv=user['CVV'],
fondos=user['Fondos'],
cp=user['CP'],
municipio=user['Municipio'])
db.execute(update_stmt)
return Response(status=200, content_type='text/json')
except Exception as e:
print(e)
return Response(status=404, content_type='text/json')
def __call__(self):
data = reactor.fill_with_water()
with db:
db.execute('''INSERT INTO water__ml (experiment_name, data)
VALUES (?, ?)''',
(current_experiment, data))
logger.info('%s %s', type(self).__name__, data.mean())
reactor_scheduler.enter(self.delay,0,self)
def __call__(self):
data = reactor.light_out_array()
with db:
db.execute('''INSERT INTO light_out__uEm2s (experiment_name, data)
VALUES (?, ?)''',
(current_experiment, data))
logger.info('%s %s', type(self).__name__, data.mean())
reactor_scheduler.enter(self.delay,0,self)
def __call__(self):
data = reactor.light_out_array()
with db:
db.execute(
'''INSERT INTO light_out__uEm2s (experiment_name, data)
VALUES (?, ?)''', (current_experiment, data))
logger.info('%s %s', type(self).__name__, data.mean())
reactor_scheduler.enter(self.delay, 0, self)
def __call__(self):
data = reactor.fill_with_water()
with db:
db.execute(
'''INSERT INTO water__ml (experiment_name, data)
VALUES (?, ?)''', (current_experiment, data))
logger.info('%s %s', type(self).__name__, data.mean())
reactor_scheduler.enter(self.delay, 0, self)
def logout(request):
try:
user_id = request.authenticated_userid
stmt: TextClause = text('UPDATE usuario SET conexion = false where pk_usuario = :id')
stmt = stmt.bindparams(id=user_id)
db.execute(stmt)
return Response(status=200)
except Exception as e:
print(e)
def do_delete(self, table, entry):
'''Delete an entry from a permitted table.'''
ids = {'experiments': 'name',
'strains' : 'name',
'notes' : 'timestamp'}
primary_key = ids[table]
with db:
db.execute('''DELETE FROM %s WHERE %s=?'''%(table, primary_key),
(entry,))
def _create_user(request: Request) -> Response:
try:
user_data = request.json_body
print(user_data)
stmt: TextClause = text(
'select * from bancoco."Cuentahabiente" where "Tarjeta" = :tarjeta and "Fecha_Expiracion" = :fecha'
)
stmt = stmt.bindparams(tarjeta=user_data['tarjeta'],
fecha=user_data['fechaExpiracion'])
result = db.execute(stmt)
data = [dict(r) for r in result]
if data.__len__() == 0:
return Response(status=400)
stmt: TextClause = text(
'INSERT into cocollector."Usuario"("Nombre_usuario",'
'"Correo",'
'"Contrasena",'
'"Nombre",'
'"Apellido_paterno",'
'"Apellido_materno",'
'"Tarjeta_credito",'
'"Fecha_Expiracion",'
'"Tipo") VALUES (:nombre_usuario, :correo, :contrasena, '
":nombre, :apellido_paterno, :apellido_materno, :tarjeta, :fecha_expiracion, 'Usuario' )"
)
nombre_usuario = user_data['nombreUsuario']
stmt = stmt.bindparams(nombre_usuario=nombre_usuario,
correo=user_data['correo'],
contrasena=user_data['contrasena'],
nombre=user_data['nombre'],
apellido_paterno=user_data['apellidoPaterno'],
apellido_materno=user_data['apellidoMaterno'],
tarjeta=user_data['tarjeta'],
fecha_expiracion=user_data['fechaExpiracion'])
db.execute(stmt)
stmt = text(
'SELECT * FROM cocollector."Usuario" where "Nombre_usuario" = :username'
)
stmt = stmt.bindparams(username=nombre_usuario)
result = db.execute(stmt)
user_data = [dict(r) for r in result][0]
token = request.create_jwt_token(user_data['ID'])
return Response(status=200,
charset='utf-8',
content_type='application/json',
body=json.dumps({
'token': token,
'userType': user_data['Tipo']
}))
except Exception as e:
print(e)
return Response(status=400)
def _get_order(request: Request) -> Response:
"""Obtinene una request y devuelve todas las ordenes que tenga el usuario en JSON,
en caso de recibir el parametro id, se devuelve la orden especifica en JSON."""
if request.authenticated_userid is not None:
try:
order_data = request.params
if 'id' in order_data:
order_id = request.params.get('id', -1)
if order_id == -1:
return Response(status=404)
else:
try:
stmt: TextClause = text(
'SELECT * from cocollector."Orden" where "ID"= :id'
)
stmt = stmt.bindparams(id=order_id)
get_order: ResultProxy = db.execute(stmt)
return Response(status=200,
body=json.dumps(
[dict(r) for r in get_order][0],
default=alchemyencoder),
content_type='text/json')
except Exception as e:
print(e)
return Response(status=404, content_type='text/plain')
else:
order_usuario = request.authenticated_userid
if order_usuario == -1:
return Response(status=404)
else:
try:
stmt: TextClause = text(
'SELECT * from cocollector."Orden" where "Usuario"= :usuario'
)
stmt = stmt.bindparams(usuario=order_usuario)
get_order: ResultProxy = db.execute(stmt)
return Response(status=200,
body=json.dumps(
[dict(r) for r in get_order],
default=alchemyencoder),
content_type='text/json')
except Exception as e:
print(e)
return Response(status=404, content_type='text/plain')
except Exception as e:
print(e)
return Response(status=404, content_type='text/plain')
else:
return Response(status=403, content_type='text/plain')
def delete_transaction(request):
try:
user_data = request.json_body
stmt: TextClause = text(
'Delete from bancoco."Transaccion" where "ID" = :id')
stmt = stmt.bindparams(id=user_data['id'])
db.execute(stmt)
return Response(status=200, content_type='text/json')
except Exception as e:
print(e)
return Response(status=400, content_type='text/plain')
def delete_category(request):
try:
category_id = request.json_body
stmt: TextClause = text(
'DELETE FROM cocollector."Categoria" WHERE "ID" = :id')
stmt = stmt.bindparams(id=category_id['id'])
db.execute(stmt)
return Response(status=200)
except Exception as e:
print(e)
return Response(status=404, content_type='text/plain')
def __call__(self):
logger.info('Experiment %s starting...', current_experiment)
reactor.fill_with_media()
reactor.set_target_temp(self.temp)
with db:
reactor.set_light_input(self.light)
light_in_data = reactor.light_input_array()
db.execute('''INSERT INTO light_in__uEm2s (experiment_name, data)
VALUES (?, ?)''',
(current_experiment, light_in_data))
reactor.pause()
def start(update, context):
uid = update.effective_user.id
chat_id = update.message.chat_id
context.bot.send_message(chat_id=update.message.chat_id,
text="Hello, i'm Wikk!"
"\nDo you want to sign in? Write /auth")
execute(f"insert into logins (uid) values ({uid}) on conflict do nothing")
# Insert chat with bot
execute(
f"insert into chats (uid, chat_id, vchat_id) values ({uid}, {chat_id}, 0) on conflict do nothing"
)
def add(isbn):
if request.method == 'POST':
rating = int(request.form.get('rating'))
review = request.form.get('review')
db.execute('''insert into reviews(isbn_id,user_id,review,rating)
values(:isbn,:user_id,:review,:rating)''',
{"isbn":isbn,"user_id":session['user_id'],
"review":review,"rating":rating})
db.commit()
flash(f'Added review for book with isbn {isbn}')
return redirect('/')
def create_reservation(request):
try:
data = request.json_body
user = request.authenticated_userid
stmt: TextClause = text(
'INSERT into reservaciones (usuario, vuelo, cantidad) VALUES (:user, :flight, :quantity)')
stmt = stmt.bindparams(user=user, flight=data['id'], quantity=data['quantity'])
db.execute(stmt)
return Response(status=200)
except Exception as e:
print(e)
return Response(status=500)
def __call__(self):
logger.info('Experiment %s starting...', current_experiment)
reactor.fill_with_media()
reactor.set_target_temp(self.temp)
with db:
reactor.set_light_input(self.light)
light_in_data = reactor.light_input_array()
db.execute(
'''INSERT INTO light_in__uEm2s (experiment_name, data)
VALUES (?, ?)''',
(current_experiment, light_in_data))
reactor.pause()
def book(isbn):
if user_session(session["username"], session["password"]):
book = db.execute("SELECT * FROM book_details WHERE isbn = :isbn", {
"isbn": isbn
}).fetchone()
if book is None:
return render_template("error.html", message="No such book.")
try:
res = requests.get(
"https://www.goodreads.com/book/review_counts.json",
params={
"key": "z9zVunvvovvlluqZHkTaw",
"isbns": isbn
}).json()
work_rating = res["books"][0]["work_ratings_count"]
average_rating = res["books"][0]["average_rating"]
if db.execute(
"SELECT * FROM feedback WHERE isbn = :isbn and email = :username",
{
"isbn": isbn,
"username": session["username"]
}).rowcount == 0:
try:
return render_template("book.html",
book=book,
review="",
rating=0,
work_rating=work_rating,
average_rating=average_rating)
except Exception as e:
return render_template("error.html",
message=("Invalid URL1", e))
else:
try:
feedback = db.execute(
"SELECT * FROM feedback WHERE isbn = :isbn and email = :username",
{
"isbn": isbn,
"username": session["username"]
}).fetchone()
return render_template("book.html",
book=book,
review=feedback.review,
rating=feedback.rating,
work_rating=work_rating,
average_rating=average_rating)
except Exception as e:
return render_template("error.html",
message=("Invalid URL2", e))
except Exception as e:
return render_template("error.html", message=("Invalid URL.", e))
else:
return render_template("error.html", message="Invalid URL!!!")
def parse_formula(experiment, formula):
'''Return a python function corresponding to the given formula and experiment's strain.'''
with db:
query = db.execute('''SELECT strain_name FROM experiments
WHERE name=?''',
(experiment,))
strain = query.fetchone()[0]
query = db.execute('''SELECT %s FROM strains
WHERE name=?'''%formula,
(strain,))
formula = query.fetchone()[0]
formula = eval('lambda x:'+formula, np.__dict__)
return formula
def getPlayersOnTeam(team):
db.execute("SELECT * FROM players WHERE team = ?", (team.code,))
rows = db.fetchall()
players = []
if rows is None or len(rows) < 1:
return []
for player in rows:
players.append(Player(*player))
return players
def __call__(self):
reactor.drain_well(self.drain_volume)
drained_data = np.array([[self.drain_volume]*4]*5)
media_data = reactor.fill_with_media_array()
with db:
db.execute('''INSERT INTO drained__ml (experiment_name, data)
VALUES (?, ?)''',
(current_experiment, drained_data))
db.execute('''INSERT INTO media__ml (experiment_name, data)
VALUES (?, ?)''',
(current_experiment, media_data))
logger.info('%s: drain %s, media fill %s', type(self).__name__, 'drain', drained_data.mean(), 'media fill', media_data.mean())
reactor_scheduler.enter(self.delay,0,self)
def format_archive_html():
'''Load all experiments from the database and list them in the HTML template.'''
with db:
entries = '\n'.join(t_archive_entry.format(HTMLnotes=format_notes_html(r['name']),
**r)
for r in db.execute('''SELECT * FROM experiments
ORDER BY timestamp DESC'''))
return t_main.format(HTMLmain_article=t_archive.format(HTMLarchive_entries=entries))
def format_strains_html():
'''Load all strains from the database and list them in the HTML template.'''
with db:
translate_power_sign = lambda _: {k: _[k].replace('**', '^') if _[k] and k not in ('name', 'description') else _[k]
for k in _.keys()}
entries = '\n'.join(t_strains_entry.format(**translate_power_sign(r))
for r in db.execute('''SELECT * FROM strains
ORDER BY name ASC'''))
return t_main.format(HTMLmain_article=t_strains.format(HTMLstrains_entries=entries))
def format_notes_html(experiment):
'''Prepare an AJAX-ish list of all notes for a given experiment.'''
with db:
notes = db.execute('''SELECT timestamp, note FROM notes
WHERE experiment_name=?
ORDER BY timestamp DESC''',
(experiment,))
return t_note_main.format(HTMLnotes='\n'.join(t_note.format(**r) for r in notes),
experiment_name=experiment)
def getPlayer(mlb_id):
db.execute("SELECT * FROM players WHERE mlb_id = ?", (mlb_id,))
row = db.fetchone()
if row is None:
# Let's go find this player! First we need to find out what team they're on, and what that team's last game_id was
try:
file = urllib2.urlopen("http://gd2.mlb.com/components/game/mlb/year_2012/batters/%s.xml" % mlb_id).read()
except urllib2.HTTPError:
return False
dom = minidom.parseString(file)
game_id = dom.getElementsByTagName("batting")[0].getAttribute("game_id")
year, month, day, meh = game_id.split("/")
game_id = game_id.replace("-", "_").replace("/", "_")
try:
file = urllib2.urlopen(
"http://gd2.mlb.com/components/game/mlb/year_%s/month_%s/day_%s/gid_%s/batters/%s.xml"
% (year, month, day, game_id, mlb_id)
).read()
except urllib2.HTTPError:
return False
player = minidom.parseString(file).getElementsByTagName("Player")[0]
db.execute(
"INSERT OR IGNORE INTO players (mlb_id, first, last, num, team) VALUES (?, ?, ?, ?, ?)",
(
mlb_id,
player.getAttribute("first_name"),
player.getAttribute("last_name"),
player.getAttribute("jersey_number"),
getTeam(player.getAttribute("team")).code,
),
)
connection.commit()
return getPlayer(mlb_id)
return Player(*row)
def format_addedit_strain_html(strain=None):
'''Load a strain in an edit page or show a "new strain" page.'''
if strain:
with db:
c = db.execute('''SELECT * FROM strains WHERE name=?''', (strain,))
strain = c.fetchone()
return t_main.format(HTMLmain_article=t_addedit_strain.format(**strain))
else:
strain = collections.defaultdict(str)
return t_main.format(HTMLmain_article=t_addedit_strain.format_map(strain))
def announce():
# POST : 글 작성
if request.method == 'POST':
cursor = db.execute("INSERT INTO `announce` (`title`, `content`) " +
"VALUES ('" + request.form['title'] +
"', '" + request.form['content'] + "');")
cursor.close()
return '{result:"success"}'
# GET : 공지사항 작성하기
return render_template('announce.html')
def do_addedit_strain(self, name, description, light_ratio_to_od_formula,
od_to_biomass_formula, od_to_cell_count_formula):
with db:
db.execute('''UPDATE OR IGNORE strains
SET description=?,
light_ratio_to_od_formula=?,
od_to_biomass_formula=?,
od_to_cell_count_formula=?
WHERE name=?''',
(description, light_ratio_to_od_formula,
od_to_biomass_formula, od_to_cell_count_formula,
name))
db.execute('''INSERT OR IGNORE INTO strains
(name, description,
light_ratio_to_od_formula,
od_to_biomass_formula,
od_to_cell_count_formula)
VALUES (?, ?, ?, ?, ?)''',
(name, description, light_ratio_to_od_formula,
od_to_biomass_formula, od_to_cell_count_formula))
return t_main.format(HTMLmain_article='<h1>Strain Changes Commited!</h1>')
def read_experiment(experiment, table):
'''Read one of the measurement tables or notes for a given experiment as a dataframe.'''
with db:
all_tables = [_[0] for _ in
db.execute('''SELECT name FROM sqlite_master
WHERE type='table'
AND name GLOB '*__*' ''')]
assert table in all_tables+['notes'], 'No such table.'
df = pd.read_sql_query('SELECT * FROM %s WHERE experiment_name=? ORDER BY timestamp ASC'%table,
db,
index_col='timestamp',
params=(experiment,))
return df
def getTeam(name):
if isinstance(name, Team):
_localCache[name.code] = name
return name
if name in _localCache:
return _localCache[name]
db.execute("SELECT * FROM teams WHERE code = ?", (name,))
row = db.fetchone()
if row is not None:
_localCache[row["code"]] = Team(*row)
return _localCache[row["code"]]
name = name.lower()
for code in team_matching:
if name in team_matching[code]:
return getTeam(code)
return None
def temp_control():
I = 0
while not self._stop_temperature_control.is_set():
error = self.mean_temp()-self._target_temp
P = -error
control = P
if -1 < control < 1: # XXX simplistic windup protection
I += P
else:
I = 0
control += I/10
control = min(+1., control)
control = max(-1., control)
self.set_heat_flow(control)
with db:
db.execute('''INSERT INTO temperature_control_log
(target_temp, error,
proportional, integral)
VALUES (?,?,?,?)''',
(self._target_temp, error, P, I))
print('\r',(self._target_temp, error, P, I, control), flush=True)
time.sleep(10)
def send(self, msg, debug=True):
msg += ('#%X'%binascii.crc32(msg)).encode()
with self.lock:
count = 0
while count<5:
buf = self.serial.read_all()
if buf.endswith(b'\r\nready\r\n\x04'):
logger.info('The Arduino was reset.')
if debug:
print('reset has happened')
elif buf:
raise ComProtocolError('Buffer not clean.')
self.serial.write(msg + b'\r')
echo = self.serial.read_until(b'\x04') # ascii EOT
ret = self.serial.read_until(b'\x04') # ascii EOT
expected = msg + b'\r\r\n' + msg + b'\r\n\x04'
if debug:
print('out : ',msg)
print('expected: ',expected)
print('echo : ',echo)
print('return : ',ret)
if echo == expected:
break
logger.info('The Arduino connection produced garbled echo. Resetting USB and retrying...')
count += 1
with db:
db.execute('''INSERT INTO communication_log
(note ) VALUES (? )''',
('reset %d on msg="%s" expected="%s" echo="%s" return="%s"'%(count, msg, expected, echo, ret),))
self.reset()
else:
raise ComProtocolError('Repeated garbled echo!')
if ret == b'\r\n-\r\n\x04':
raise ComProtocolError('Arduino error!')
ret, crc = ret[2:-3].split(b'#')
if int(crc,16) != binascii.crc32(ret):
raise ComProtocolError('Incorrect checksum!')
return [float(_) if b'.' in _ else int(_) for _ in ret.split(b' ')]
def format_new_html():
'''Create a configuration page for the setup of a new experiment.'''
events_html='\n'.join([t_new_event.format(
event_name=e.__name__,
event_description=e.__doc__,
HTMLevent_arguments=format_event_arguments(e))
for e in events])
eventbuttons_html=' '.join([t_new_event_button.format(event_name=e.__name__) for e in events])
with db:
strainoptions_html=''.join('''<option value="{name}">{name}</option>'''.format(**r)
for r in db.execute('SELECT name FROM strains ORDER BY name ASC'))
return t_main.format(HTMLmain_article=t_new.format(HTMLevents=events_html,
HTMLeventbuttons=eventbuttons_html,
HTMLstrainoptions=strainoptions_html))
def do_start_new_experiment(self, **kwargs):
'''Process the "new experiment" form and start an experiment.'''
logger.info('Starting new experiment %s...', kwargs['name'])
def prepare_event(event, kwargs):
arguments = list(inspect.signature(event.__init__).parameters)[1:]
name = event.__name__
prepared_kwargs = {a: kwargs['%s_%s'%(event.__name__,a)]
for a in arguments}
return event(**prepared_kwargs)
start = StartExperiment(**kwargs)
prepared_events = [prepare_event(e, kwargs) for e in events
if e.__name__+'__check' in kwargs]
if not prepared_events:
raise ValueError('No measurement events scheduled.')
with db:
to_record = [kwargs[_] for _ in ['name', 'description', 'strain', 'row1', 'row2', 'row3', 'row4', 'col1', 'col2', 'col3', 'col4', 'col5']]
db.execute('''INSERT INTO experiments (name, description, strain_name, row1, row2, row3, row4, col1, col2, col3, col4, col5)
VALUES (?, ?, ?, ?,?,?,?, ?,?,?,?,?)''',
to_record)
reactor_scheduler.enter(0,-1,start)
for e in prepared_events:
reactor_scheduler.enter(0,0,e)
raise cherrypy.HTTPRedirect('/')
def format_experiment_html(experiment):
'''Load all data for a given experiment, make bokeh plots, and load in the HTML template.'''
links = '\n'.join('''<div class="pure-u-1-5"><a class="pure-input-1 pure-button" href="/experiment/{experiment}/{plot_type}">{plot_type}</a></div>
'''.format(experiment=experiment,
plot_type=p)
for p in possible_plots.keys())
with db:
query = db.execute('''SELECT * FROM experiments WHERE name=?''',
(experiment,))
r = query.fetchone()
return t_main.format(HTMLmain_article=t_experiment.format(
HTMLlinks=links,
HTMLnotes=format_notes_html(experiment),
**r
))
def format_status_html():
'''Create a status page for the current experiment.'''
if current_experiment is None:
return t_main.format(HTMLmain_article='<h1>No Experiments Running</h1>')
logger.info('Generating status page for experiment %s...', current_experiment)
with db:
c = db.execute('''SELECT strain_name, description FROM experiments
WHERE name=?''',
(current_experiment,))
strain, description = c.fetchone()
return t_main.format(HTMLmain_article=t_status.format(experiment_name=current_experiment,
strain=strain,
description=description,
HTMLschedule=format_schedule_html(),
HTMLnotes=format_notes_html(current_experiment)))
def delete():
app.secret_key = flask_session_secret_key
method = request.form.get('_method')
id = request.args.get('id')
if method == 'DELETE':
db.query(Collaboration) \
.filter(or_(Collaboration.entity_id1 == id, Collaboration.entity_id2 == id)) \
.delete(synchronize_session='evaluate')
db.query(Dataconnection) \
.filter(or_(Dataconnection.giver_id == id, Dataconnection.receiver_id == id)) \
.delete(synchronize_session='evaluate')
db.query(Employment) \
.filter(or_(Employment.entity_id1 == id, Employment.entity_id2 == id)) \
.delete(synchronize_session='evaluate')
db.query(Revenue).filter(Revenue.entity_id == id).delete(synchronize_session='evaluate')
db.query(Expense).filter(Expense.entity_id == id).delete(synchronize_session='evaluate')
db.query(Relation) \
.filter(or_(Relation.entity_id1 == id, Relation.entity_id2 == id)) \
.delete(synchronize_session='evaluate')
db.query(Fundingconnection) \
.filter(or_(Fundingconnection.giver_id == id, Fundingconnection.receiver_id == id)) \
.delete(synchronize_session='evaluate')
db.query(Edit).filter(Edit.entity_id == id).delete(synchronize_session='evaluate')
db.execute("DELETE FROM location_table WHERE entity_id=" + id + ";")
db.execute("DELETE FROM category_table WHERE entity_id=" + id + ";")
db.execute("DELETE FROM keypeople_table WHERE entity_id=" + id + ";")
db.query(Entity).filter(Entity.id == id).delete(synchronize_session='evaluate')
db.commit()
cache.clear()
flash("Delete was successful")
return redirect('/admin')
def getGame(team, gameday, game=1):
db.execute("SELECT * FROM games WHERE (home_code = ? OR away_code = ?) AND date = ?", (team.code, team.code, gameday.strftime("%Y/%m/%d")))
rows = db.fetchall()
if len(rows) == game and rows[game-1]["version"] == db_version:
return Game(*rows[game-1])
try:
file = urllib2.urlopen("http://gd2.mlb.com/components/game/mlb/year_%d/month_%02d/day_%02d/master_scoreboard.json" % (gameday.year, gameday.month, gameday.day))
except urllib2.HTTPError:
# There's no file for this day. Maybe future, maybe too far in the past?
return False
# No games today?
try:
games = json.load(file)["data"]["games"]["game"]
except KeyError:
games = []
for game in games:
if game["home_name_abbrev"] == team.code or game["away_name_abbrev"] == team.code:
home_team = getTeam(game["home_name_abbrev"])
away_team = getTeam(game["away_name_abbrev"])
home_homers = []
away_homers = []
if "home_runs" in game:
for player in game["home_runs"]["player"]:
if player["team_code"] == home_team.schedule_code:
home_homers.append(int(player["id"]))
elif player["team_code"] == away_team.schedule_code:
away_homers.append(int(player["id"]))
data = dict(
gameday = game["gameday"],
start_time = game["time"],
away_code = game["away_name_abbrev"],
home_code = game["home_name_abbrev"],
status = game["status"]["status"],
version = db_version,
away_r = game["linescore"]["r"]["away"],
away_h = game["linescore"]["h"]["away"],
away_e = game["linescore"]["e"]["away"],
home_r = game["linescore"]["r"]["home"],
home_h = game["linescore"]["h"]["home"],
home_e = game["linescore"]["e"]["home"],
winning_pitcher = game["winning_pitcher"]["id"] if "winning_pitcher" in game else None,
losing_pitcher = game["losing_pitcher"]["id"] if "losing_pitcher" in game else None,
save_pitcher = game["save_pitcher"]["id"] if "save_pitcher" in game else None,
away_homers = "\n".join(away_homers),
home_homers = "\n".join(home_homers),
date = game["original_date"]
)
game = Game(**data)
#db.execute("INSERT INTO games (gameday, start_time, away_code, home_code, status, version, away_r, away_h, away_e, home_r, home_h, home_e, winning_pitcher, losing_pitcher, save_pitcher, away_homers, home_homers, date) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", *data)
return game
return False
def delete(id):
cursor = db.execute("DELETE FROM `announce` WHERE `id`=" + id + ";")
return u'삭제하였습니다.'
def list():
cursor = db.execute("SELECT * FROM `announce`;")
return render_template('list.html', data = cursor.fetchall())