def createview():
view = db.Table('v_stockown', db.MetaData(db.engine), schema='public')
definition = text(
"SELECT * FROM (SELECT trx.uid, sum(trx.quantity * trx.transtypeid) AS quantityheld, stocks.symbol, stocks.name FROM transactions trx LEFT JOIN stocks stocks ON trx.stockid = stocks.stockid GROUP BY trx.uid, trx.stockid, stocks.symbol, stocks.name) AS prev WHERE prev.quantityheld>0"
)
create_view = CreateView(view, definition, or_replace=True)
db.engine.execute(create_view)
def create_tables():
metadata = db.MetaData()
users_table = db.Table('user', metadata,
db.Column('id', db.Integer, primary_key=True),
db.Column('login', db.String, unique=True),
db.Column('hash_password', db.String))
posts_table = db.Table(
'post', metadata, db.Column('id', db.Integer, primary_key=True),
db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
db.Column('title', db.String), db.Column('body', db.String),
db.Column('publication_date', db.DateTime))
posts_table = db.Table(
'comment',
metadata,
db.Column('id', db.Integer, primary_key=True),
db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
db.Column('post_id', db.Integer, db.ForeignKey('post.id')),
db.Column('body', db.String),
db.Column('publication_date', db.DateTime),
)
posts_table = db.Table(
'subscriber', metadata, db.Column('id', db.Integer, primary_key=True),
db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
db.Column('subscriber_id', db.Integer, db.ForeignKey('user.id')))
metadata.create_all(db.engine)
def create_table():
engine = db.create_engine(app.config.get('SQLALCHEMY_DATABASE_URI'), {})
metadata = db.MetaData(bind=engine)
metadata.reflect(bind=engine)
time = db.Table('time',
metadata,
db.Column('id', db.Integer, primary_key=True),
db.Column('time', db.DateTime),
extend_existing=True)
time.create(engine)
return 'created table!'
class Ticket():
meta = db.MetaData()
engine = create_engine(SQLALCHEMY_DATABASE_URI)
table = db.Table('tickets', meta, autoload=True, autoload_with=engine)
tid = table.columns.id
eid = table.columns.eid
type = table.columns.type
price = table.columns.price
currency = table.columns.currency
conn = engine.connect()
trans = conn.begin()
def __init__(self,
eid=None,
tid=None,
type=None,
price=None,
currency=None):
self.tid = tid
self.eid = eid
self.type = type
self.price = price
self.currency = currency
def save(self):
sql = text(
'insert into tickets (eid, type, price, currency) values (:eid, :type, :price, :currency)'
)
res = self.engine.execute(sql,
eid=self.eid,
type=self.type,
price=self.price,
currency=self.currency)
return self
def get(self):
sql = text('select * from tickets where eid = :eid')
res = self.engine.execute(sql, eid=self.eid)
return json.dumps([dict(r) for r in res])
def getTicketData(self):
sql = text('select * from tickets t ' +
'join events e on t.eid = e.id ' +
'join user u on e.userId = u.id ' + 'where t.id = :tid')
res = self.engine.execute(sql, tid=self.tid)
return json.dumps([dict(r) for r in res])
def create(claims, attrs):
if claims['role'] != 'coach':
raise ValidationError(
message={"Role": "Only coach can create a training"})
if attrs['date'] <= date.today():
raise ValidationError(
message={"date": "Cannot plan a training for past"})
metadata = db.MetaData()
metadata.bind = db.engine
vUserCoach = db.Table("vcoaches_athletes",
metadata,
db.Column("coach_id",
db.Integer,
primary_key=True),
db.Column("athlete_id", db.Integer,
db.ForeignKey("users.id")),
autoload=True)
coach_athlete = db.session.query(vUserCoach).filter(
vUserCoach.columns.coach_id == claims['userID']).all()
athletes_ids = [t[1] for t in coach_athlete]
trainings = Training.query.filter_by(
athletes_id=attrs['athletes_id']).all()
# can add training only if athlete coached by logged in coach and if it is first training of particular athlete
# second is required as it won't be possible to add first training otherwise, as vUserCoach is based on trainings table
# if attrs['athletes_id'] not in athletes_ids and len(trainings) != 0:
# raise ValidationError(message={"athleteID": "Provided athlete is not coached by logged in coach"})
training = Training(date=attrs['date'],
athletes_id=attrs['athletes_id'],
coaches_id=claims['userID'])
athlete = User.query.get(training.athletes_id)
coach = User.query.get(training.coaches_id)
db.session.add(training)
db.session.commit()
#for key, item in attrs.items():
# Logger.log_message(claims['userID'], 'trainings', key, 'null', item)
#logging only id instead of all attributes
Logger.log_message(claims['userID'], 'trainings', 'id', 'null',
training.id)
handler.new_training_msg(athlete.mail_address, training, coach)
return training.id
class Comment():
meta = db.MetaData()
engine = create_engine(SQLALCHEMY_DATABASE_URI)
table = db.Table('comments', meta, autoload=True, autoload_with=engine)
cid = table.columns.id
eid = table.columns.eid
uid = table.columns.uid
comment = table.columns.comment
reaction = table.columns.reaction
conn = engine.connect()
trans = conn.begin()
def __init__(self, eid=None, uid=None, comment=None, reaction=None):
self.eid = eid
self.uid = uid
self.comment = comment
self.reaction = reaction
def postComment(self):
sql = text(
'insert into comments (eid, uid, comment) values (:eid, :uid, :comment)'
)
res = self.engine.execute(sql,
eid=self.eid,
uid=self.uid,
comment=self.comment)
return self
def loadComments(self):
sql = text(
'select * from comments c inner join user u on u.id = c.uid where eid = :eid and comment is not null'
)
res = self.engine.execute(sql, eid=self.eid)
return json.dumps([dict(r) for r in res])
def addReaction(self):
sql = text(
'insert into comments(eid, uid, reaction) values (:eid, :uid, :reaction)'
)
res = self.engine.execute(sql,
eid=self.eid,
uid=self.uid,
reaction=self.reaction)
return self
def get_all(coach_id) -> list:
metadata = db.MetaData()
metadata.bind = db.engine
#create view table
vUserCoach = db.Table("vcoaches_athletes",
metadata,
db.Column("coach_id",
db.Integer,
primary_key=True),
db.Column("athlete_id", db.Integer,
db.ForeignKey("users.id")),
autoload=True)
coach_athlete = db.session.query(vUserCoach).filter(
vUserCoach.columns.coach_id == coach_id).all()
#coach_athlete is a list of tuples of (coach_id, athlete_id)
athletes_ids = [t[1] for t in coach_athlete]
athletes = User.query.filter(User.id.in_(athletes_ids)).all()
return athletes
def create_mat_view(name, selectable, metadata=db.metadata):
_mt = db.MetaData() # temp metadata just for initial Table object creation
t = db.Table(name,
_mt) # the actual mat view class is bound to db.metadata
for c in selectable.c:
t.append_column(db.Column(c.name, c.type, primary_key=c.primary_key))
if not (any([c.primary_key for c in selectable.c])):
t.append_constraint(
PrimaryKeyConstraint(*[c.name for c in selectable.c]))
db.event.listen(metadata, 'after_create',
CreateMaterializedView(name, selectable))
@db.event.listens_for(metadata, 'after_create')
def create_indexes(target, connection, **kw):
for idx in t.indexes:
idx.create(connection)
db.event.listen(metadata, 'before_drop',
db.DDL('DROP MATERIALIZED VIEW IF EXISTS ' + name))
return t
from app import app, db
from flask import render_template, url_for, request, redirect
from app.models.forms import BeerForm, age_choices
from app.models.tables import Beer_Table
import pandas as pd
import numpy as np
from secret import engine
conn = engine.connect()
metadata = db.MetaData()
beer = db.Table('beer', metadata)
@app.route('/index')
@app.route('/', methods=['GET', 'POST'])
def index():
beer_form = BeerForm()
if request.method == 'POST':
age_res = str(beer_form.age.data.split('-')[0].strip()), str(
beer_form.age.data.split('-')[1].strip())
abv_res = float(beer_form.abv.data.split('-')[0].strip()), float(
beer_form.abv.data.split('-')[1].strip())
ibu_res = int(beer_form.ibu.data.split('-')[0].strip()), int(
beer_form.ibu.data.split('-')[1].strip())
srm_res = int(beer_form.srm.data.split('-')[0].strip()), int(
beer_form.srm.data.split('-')[1].strip())
beer_df = pd.read_csv('data/beer_final.csv')
result = beer_df[(beer_df['ABV_max'] >= abv_res[0])
& (beer_df['ABV_max'] <= abv_res[1]) &
(beer_df['IBU_max'] >= ibu_res[0]) &
class User(UserMixin):
meta = db.MetaData()
engine = create_engine(SQLALCHEMY_DATABASE_URI)
table = db.Table('user', meta, autoload=True, autoload_with=engine)
uid = table.columns.id
username = table.columns.userName
password = table.columns.password
name = table.columns.name
surname = table.columns.surname
picture = table.columns.picture
role = table.columns.role
conn = engine.connect()
trans = conn.begin()
def __init__(self,
role=None,
username=None,
password=None,
name=None,
surname=None,
uid=None,
aboutme=None,
picture=None):
self.role = role
self.username = username
self.password = password
self.name = name
self.surname = surname
self.id = uid
self.aboutme = aboutme
self.picture = picture
super(User, self).__init__()
def __repr__(self):
return '<User %s>' % self.id
def is_active(self):
return True
@property
def is_authenticated(self):
return True
def get_id(self):
return unicode(self.id)
def exists(self):
sql = text('select 1 from user where username = :username')
result = self.engine.execute(sql, username=self.username)
res = []
for r in result:
res.append(r)
if len(res) > 0:
return True
else:
return False
def register(self):
sql = text(
'insert into user(userName, password, name, surname, aboutme, role) values (:username,:password,:name,:surname,:aboutme, :role)'
)
self.engine.execute(sql,
username=self.username,
password=self.password,
name=self.name,
surname=self.surname,
aboutme=self.aboutme,
role=self.role)
return True
def login(self):
username = self.username
password = self.password
sql = text(
'select * from user where username = :username and password = :password'
)
result = self.engine.execute(sql, username=username, password=password)
res = result.fetchone()
if res:
return res
else:
return False
def updateInfo(self):
name = self.name
surname = self.surname
uid = self.id
aboutme = self.aboutme
role = self.role
sql = text(
'update user set name = :name, surname = :surname, aboutme = :aboutme, role = :role where id = :uid'
)
result = self.engine.execute(sql,
name=name,
surname=surname,
uid=uid,
aboutme=aboutme,
role=role)
return self
def updateAllInfo(self):
name = self.name
surname = self.surname
uid = self.id
aboutme = self.aboutme
role = self.role
sql = text(
'update user set name = :name, surname = :surname, aboutme = :aboutme, picture = :picture, role = :role where id = :uid'
)
result = self.engine.execute(sql,
name=name,
surname=surname,
uid=uid,
picture=self.picture,
aboutme=aboutme,
role=role)
return self
def getUserData(self):
sql = text(
'select * from user u inner join roles r on u.role = r.role where id = :uid'
)
result = self.engine.execute(sql, uid=self.id)
return json.dumps([dict(r) for r in result])
def getUserProfileInfo(self):
sql = text('select * from user where id = :uid')
result = self.engine.execute(sql, uid=self.id)
return json.dumps([dict(r) for r in result])
def getAllEvents(self):
uid = self.id
#sql = text('select * from events')
sql = text(
'select *, (select count(comment) from comments where eid = ev.id group by eid) as numberOfComments,'
+
'(select count(reaction) from comments where eid = ev.id and reaction = "like" group by eid) as likes,'
+
'(select count(reaction) from comments where eid = ev.id and reaction = "love" group by eid) as love '
+ 'from events ev')
result = self.engine.execute(sql)
return json.dumps([dict(r) for r in result])
def getMyEvents(self):
uid = self.id
sql = text('select * from events where userId =:uid')
result = self.engine.execute(sql, uid=uid)
return json.dumps([dict(r) for r in result])
def setProfilePic(self):
sql = text('update user set picture = :picture where id = :uid')
res = self.engine.execute(sql, picture=self.picture, uid=self.id)
return True
def updatePassword(self):
p = self.password
u = self.username
sql = text(
'update user set password = :password where username = :username')
result = self.engine.execute(sql, password=p, username=u)
return True
def returnUserData(self):
res = self.engine.execute('select * from user')
return [dict(r) for r in res]
def getToken(self, expiration=100):
s = Serializer('SECRET_KEY', expiration)
return s.dumps({'username': self.username}).decode('utf-8')
def verifyToken(self, token):
s = Serializer('SECRET_KEY')
try:
data = s.loads(token)
except:
return None
username = data.get('username')
print username
return username
class Event():
meta = db.MetaData()
engine = create_engine(SQLALCHEMY_DATABASE_URI)
table = db.Table('events', meta, autoload=True, autoload_with=engine)
eid = table.columns.id
uid = table.columns.userId
title = table.columns.eventTitle
image = table.columns.eventImage
created = table.columns.eventCreated
time = table.columns.eventTime
description = table.columns.eventDescription
lat = table.columns.lat
lng = table.columns.lng
conn = engine.connect()
trans = conn.begin()
def __init__(self,
eid=None,
title=None,
created=None,
time=None,
description=None,
image=None,
uid=None,
lng=None,
lat=None):
self.title = title
self.image = image
self.created = created
self.time = time
self.uid = uid
self.description = description
self.lat = lat
self.lng = lng
self.eid = eid
def add(self):
sql = text(
'insert into events(userId, eventTitle, eventCreated, eventTime, eventImage, eventDescription, lat, lng) values (:uid, :eTitle, :eCreated, :eTime, :eImage, :eDescription, :lat, :lng)'
)
res = self.engine.execute(sql,
uid=self.uid,
eTitle=self.title,
eCreated=self.created,
eTime=self.time,
eImage=self.image,
eDescription=self.description,
lat=self.lat,
lng=self.lng)
return self
def removeEvent(self):
sql = text('delete from events where id = :id')
res = self.engine.execute(sql, id=self.eid)
print self.eid
print res
return self
def getEventData(self):
sql = text('select * from events where id = :id')
res = self.engine.execute(sql, id=self.eid)
return json.dumps([dict(r) for r in res])
def returnId(self):
sql = text("select id from events where eventCreated = :eCreated")
res = self.engine.execute(sql, eCreated=self.created)
return [dict(r) for r in res][0]['id']
