from datetime import datetime
from __init__ import db
from sqlalchemy import desc
from flask_login import UserMixin
from werkzeug.security import check_password_hash, generate_password_hash
tags = db.Table('trip_tag',
db.Column('tag_id', db.Integer, db.ForeignKey('tag.id')),
db.Column('trip_id', db.Integer, db.ForeignKey('trip.id')))
class Trip(db.Model):
id = db.Column(db.Integer, primary_key=True)
url = db.Column(db.Text, nullable=False)
date = db.Column(db.DateTime, default=datetime.utcnow)
description = db.Column(db.String(300))
destination = db.Column(db.String(300))
#need to figure out a way to store DateTime properly
outbound_date = db.Column(db.String(300))
outbound_time = db.Column(db.String(300))
inbound_date = db.Column(db.String(300))
inbound_time = db.Column(db.String(300))
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
_tags = db.relationship('Tag', secondary=tags, lazy='joined', backref=db.backref('trips', lazy='dynamic'))
privacy = db.Column(db.Boolean, default=False)
@staticmethod
def newest(num):
from __init__ import db, app
from flask_login import UserMixin
from werkzeug.security import generate_password_hash, check_password_hash
from flask_security import Security, SQLAlchemyUserDatastore, RoleMixin
from forms import RegisterUser, LoginForm
from datetime import datetime
roles_user = db.Table('user_roles',db.Model.metadata,
db.Column('user_id',db.Integer, db.ForeignKey('user.id')),
db.Column('role_id',db.Integer, db.ForeignKey('role.id')),
db.UniqueConstraint('user_id','role_id', name = 'UC_user_id_role_id')
)
book_user = db.Table('book_users',db.Model.metadata,
db.Column('user_id',db.Integer, db.ForeignKey('user.id')),
db.Column('book_id',db.Integer, db.ForeignKey('book.id')),
db.UniqueConstraint('user_id','book_id',name='UC_user_id_book_id')
)
class User(db.Model, UserMixin):
id = db.Column(db.Integer,primary_key=True)
first_name = db.Column(db.String(200),index = True)
last_name = db.Column(db.String(200),index = True)
username = db.Column(db.String(200), index = True)
email = db.Column(db.String(200), index = True)
password = db.Column(db.String(200),index = True)
year = db.Column(db.Date)
active = db.Column(db.Boolean())
roles = db.relationship('Role',secondary = roles_user, backref = 'users')
book = db.relationship('Book', secondary = book_user, lazy = 'select')
# -*- coding: utf-8 -*-
from datetime import datetime
from flask.ext.security import UserMixin, RoleMixin, SQLAlchemyUserDatastore, Security
from __init__ import app, db
from flask_security.forms import RegisterForm
from wtforms.validators import Required, Length
from wtforms import TextField, TextAreaField, BooleanField
roles_users = db.Table(
'roles_users',
db.Column('user_id', db.Integer(), db.ForeignKey('user.id')),
db.Column('role_id', db.Integer(), db.ForeignKey('role.id'))
)
conversation_users = db.Table(
'conversation_users',
db.Column('user_id', db.Integer(), db.ForeignKey('user.id')),
db.Column('conversation_id', db.Integer(), db.ForeignKey('conversation.id'))
)
categories_types = db.Table(
'categories_types',
db.Column('category_id', db.Integer(), db.ForeignKey('category.id')),
db.Column('stufftype_id', db.Integer(), db.ForeignKey('stufftype.id'))
)
"""
stuff_type_stuff_list = db.Table('stuff_type_stuff_list',
db.Column('stuff_id', db.Integer(), db.ForeignKey('stuff.id')),
from __init__ import db
from flask_security import UserMixin, RoleMixin
role_user_link = db.Table(
'role_user', db.Column('user_id', db.Integer(), db.ForeignKey('user.id')),
db.Column('role_id', db.Integer(), db.ForeignKey('role.id')))
palindrome_user_link = db.Table(
'palindrome_user',
db.Column('user_id', db.Integer(), db.ForeignKey('user.id')),
db.Column('palindrome_id', db.Integer(), db.ForeignKey('palindrome.id')))
class User(db.Model, UserMixin):
id = db.Column(db.Integer(), primary_key=True)
email = db.Column(db.String(255), unique=True)
password = db.Column(db.String(255))
scores = db.Column(db.Integer(), default=0)
active = db.Column(db.Boolean(), default=True)
roles = db.relationship('Role',
secondary=role_user_link,
backref=db.backref('users', lazy='dynamic'))
palindromes = db.relationship('Palindrome',
secondary=palindrome_user_link,
backref=db.backref('users', lazy='dynamic'))
def __repr__(self):
return 'ID: {}, Email: {}'.format(self.id, self.email)
import sqlalchemy
from __init__ import db
from sqlalchemy.orm import backref
from sqlalchemy.sql.schema import ForeignKey
_planes_modules = db.Table('_planes_modules',
db.Column('plane_id', db.Integer, db.ForeignKey('planes.plane_id')),
db.Column('module_id', db.Integer, db.ForeignKey('planes_modules.plane_module_id'))
)
_planes_sus_arm = db.Table('_planes_sus_arm',
db.Column('plane_id', db.Integer, db.ForeignKey('planes.plane_id')),
db.Column('sus_arm_id', db.Integer, db.ForeignKey('suspended_armaments.sus_arm_id')),
)
class Country(db.Model):
__tablename__ = "countries"
country_id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(30), nullable=False, unique=True)
planes = db.relationship('Plane', backref='country')
class PlaneClass(db.Model):
__tablename__ = "plane_classes"
plane_class_id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(30), nullable=False, unique=True)
planes = db.relationship('Plane', backref='plane_class')
class PlaneModule(db.Model):
__tablename__ = "planes_modules"
from __init__ import db
Base = db.Model #declarative_base()
#Base2 = declarative_base()
# for yUML model
# http://yuml.me/edit/a541cdb0
# http://yuml.me/ba69937b
# many to many relationships
# seasons to teams #Base.metadata,
season_team = db.Table('season_team',
db.metadata,
db.Column('season_id', db.Integer,
db.ForeignKey('season.season_id')),
db.Column('team_id', db.Integer,
db.ForeignKey('team.team_id')),
extend_existing=True)
# games to teams
# team_game = db.Table('team_game', db.metadata,
# db.Column('team_id', db.Integer, db.ForeignKey('team.team_id')),
# db.Column('game_id', db.Integer, db.ForeignKey('game.game_id')),
# extend_existing=True
# )
# --------------
# Season
# --------------
return f'polls_text:{self.text}'
class Choice(db.Model):
__tablename__ = 'choice'
choice_id = Column(Integer,
unique=True,
primary_key=True,
autoincrement=True)
choice_text = Column(String, nullable=False)
votes = Column(Integer, default=0, nullable=False)
question_id = Column(Integer, ForeignKey('polls.id', ondelete='CASCADE'))
roles_users = db.Table(
"roles_users", Column('user_id', db.Integer(), db.ForeignKey('users.id')),
Column('role_id', db.Integer(), db.ForeignKey('roles.id')))
class Role(db.Model):
__tablename__ = 'roles'
id = Column(Integer, primary_key=True)
name = Column(String(80), unique=True)
description = Column(String(255))
def __repr__(self):
return self.name
class User(db.Model):
__tablename__ = 'users'
from datetime import datetime
from __init__ import db
from sqlalchemy import desc
from flask_login import UserMixin
from werkzeug.security import check_password_hash, generate_password_hash
tags = db.Table(
'bookmark_tag', db.Column('tag_id', db.Integer, db.ForeignKey('tag.id')),
db.Column('bookmark_id', db.Integer, db.ForeignKey('bookmark.id')))
class Bookmark(db.Model):
id = db.Column(db.Integer, primary_key=True)
url = db.Column(db.Text, nullable=False)
date = db.Column(db.DateTime, default=datetime.utcnow)
description = db.Column(db.String(300))
destination = db.Column(db.String(300))
#need to figure out a way to store DateTime properly
outbound_date = db.Column(db.String(300))
outbound_time = db.Column(db.String(300))
inbound_date = db.Column(db.String(300))
inbound_time = db.Column(db.String(300))
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
_tags = db.relationship('Tag',
secondary=tags,
backref=db.backref('bookmarks', lazy='dynamic'))
@staticmethod
def newest(num):
from hashlib import md5
from __init__ import db
followers = db.Table(
'followers', db.Column('follower_id', db.Integer,
db.ForeignKey('user.id')),
db.Column('followed_id', db.Integer, db.ForeignKey('user.id')))
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
nickname = db.Column(db.String(64), index=True, unique=True)
email = db.Column(db.String(120), index=True, unique=True)
posts = db.relationship('Post', backref='author', lazy='dynamic')
about_me = db.Column(db.String(140))
last_seen = db.Column(db.DateTime)
followed = db.relationship('User',
secondary=followers,
primaryjoin=(followers.c.follower_id == id),
secondaryjoin=(followers.c.followed_id == id),
backref=db.backref('followers', lazy='dynamic'),
lazy='dynamic')
@staticmethod
def make_unique_nickname(nickname):
if User.query.filter_by(nickname=nickname).first() is None:
return nickname
version = 2
while True:
new_nickname = nickname + str(version)
if User.query.filter_by(nickname=new_nickname).first() is None:
from __init__ import db
from flask_login import UserMixin
from __init__ import lm
ROLE_USER = '******'
ROLE_TEACHER = 'teacher'
ROLE_ADMIN = 'admin'
@lm.user_loader
def load_user(id):
return User.query.get(int(id))
subject_attestation = db.Table(
'subject_attestation',
db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
db.Column('subject_id', db.Integer, db.ForeignKey('subject.id')))
class User(db.Model, UserMixin):
__tablename__ = 'user'
id = db.Column(db.Integer, primary_key=True)
nickname = db.Column(db.String(64), index=True, unique=True)
password = db.Column(db.String(128), index=True)
email = db.Column(db.String(120), index=True, unique=True)
role = db.Column(db.String, default=ROLE_TEACHER)
def __repr__(self):
return '<User ' + str(self.nickname) + ', role ' + str(self.role) + '>'
Resources:
This page seems well maintained and has many videos
https://www.sqlalchemy.org/library.html#tutorials
This model.py is using advanced relationship patterns as described in the reference:
https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html
This video is 7 minutes and shares a lot of more advanced relationship concepts
https://www.youtube.com/watch?v=47i-jzrrIGQ
'''
# Define a many-to-many association table
# ... used as table in the middle between Project and Tag, see ForeignKeys
projects_tags = db.Table(
'projects_tags',
db.Column('project_id', db.Integer, db.ForeignKey('projects.id')),
db.Column('tag_id', db.Integer, db.ForeignKey('tags.id')))
# Define a many-to-many association table with extra data
# ... used as table in the middle between Project and Job, see ForeignKeys
# ... defined as Class as ProjectJob contains user_id, which is assigned in code
class ProjectJob(db.Model):
__tablename__ = 'projects_jobs'
project_id = db.Column(db.ForeignKey('projects.id'), primary_key=True)
job_id = db.Column(db.ForeignKey('jobs.id'), primary_key=True)
user_id = db.Column(
db.Integer
) # "extra data" in association, a user_id associated with a ProjectJob
from datetime import datetime
from itsdangerous import TimedJSONWebSignatureSerializer as Serializer
from __init__ import db, login_manager, app
from flask_login import UserMixin
@login_manager.user_loader
def load_user(user_id):
return User.query.get(int(user_id))
subs = db.Table(
'subs', db.Column('user_id', db.Integer, db.ForeignKey('user.user_id')),
db.Column('prod_id', db.Integer, db.ForeignKey('hobies.prod_id')))
class User(db.Model, UserMixin):
user_id = db.Column(db.Integer, primary_key=True)
first_name = db.Column(db.String(120), nullable=False)
last_name = db.Column(db.String(120), nullable=False)
email = db.Column(db.String(120), unique=True, nullable=False)
password = db.Column(db.String(60), nullable=False)
def get_id(self):
return self.user_id
def get_reset_token(self, expires_sec=1800):
s = Serializer(app.config['SECRET_KEY'], expires_sec)
return s.dumps({'user_id': self.id}).decode('utf-8')
@staticmethod
edgetype = db.Column(db.String(255))
properties = db.relationship("ggi_edge_properties", backref='ggi_edge', lazy='dynamic',
cascade="all, delete-orphan")
class ggi_edge_properties(db.Model):
'''properties about a gene-gene interaction edge in a GGI network'''
__tablename__='ggi_edge_property'
id = db.Column(db.BigInteger, primary_key=True)
ggi_edge_id = db.Column(db.BigInteger, db.ForeignKey('ggi_edge.id',
ondelete="CASCADE"))
property_name = db.Column(db.String(255))
property_value = db.Column(db.String(255))
go_modules = db.Table('ensembl_go',
db.Column('ensembl_id', db.BigInteger, db.ForeignKey('ensembl.id'), primary_key=True),
db.Column('geneontology_id', db.BigInteger, db.ForeignKey('geneontology.id'), primary_key=True)
)
class geneontology(db.Model):
'''a database of gene ontologies from msigdb that has been processed for DGCA'''
__tablename__='geneontology'
id = db.Column(db.BigInteger, primary_key=True)
system = db.Column(db.String(255))
category = db.Column(db.String(255))
population_hits = db.Column(db.Integer)
population_total = db.Column(db.Integer)
genes = db.relationship('ensembl',secondary=go_modules, lazy='subquery',backref=db.backref('geneontology', lazy=True))
class ensembl(db.Model):
'''a denormalized Ensembl database as output from pyensembl'''
from __init__ import db
from sqlalchemy import text, UniqueConstraint
from sqlalchemy.sql import func
from sqlalchemy.orm import relationship, backref
from sqlalchemy.dialects.postgresql import ARRAY, array
from helpers import datetime_to_epoch
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.ext.associationproxy import association_proxy
# ALDJEMY_DATA_TYPES = {
# 'UUIDField': lambda field: UUID(),
# 'StringField': lambda field: str()
# }
feed_article_category_table = db.Table(
'feed_category', db.Model.metadata,
db.Column('feed_id', db.String, db.ForeignKey('feeds.id')),
db.Column('categories', db.Integer, db.ForeignKey('categories.id')))
class App(db.Model):
__tablename__ = 'apps'
id = db.Column(db.String(100), primary_key=True)
name = db.Column(db.String(100), nullable=False)
slug = db.Column(db.String(100), nullable=False)
token = db.Column(db.String)
description = db.Column(db.Text, nullable=True)
created_at = db.Column(db.DateTime, nullable=False, default=func.now())
updated_at = db.Column(db.DateTime,
nullable=False,
default=func.now(),
