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']