from flask_login import current_user from application import db from application.models import Base from sqlalchemy import text import os ProjectMembers = db.Table( 'project_members', db.Column('account_id', db.Integer, db.ForeignKey('account.id')), db.Column('project_id', db.Integer, db.ForeignKey('project.id'))) class Project(Base): name = db.Column(db.String(144), nullable=False) budget = db.Column(db.Numeric(scale=2), nullable=False) customer_id = db.Column(db.Integer, db.ForeignKey('customer.id'), nullable=False) members = db.relationship('User', secondary=ProjectMembers, backref='projects', lazy=True) def __init__(self, name, budget): self.name = name self.budget = budget
from application import db from sqlalchemy.sql import text ilmoittautuminen = db.Table( 'ilmoittautuminen', db.Column('asiakas_id', db.Integer, db.ForeignKey('asiakas.id'), primary_key=True), db.Column('kurssi_id', db.Integer, db.ForeignKey('kurssi.id'), primary_key=True)) class Asiakas(db.Model): id = db.Column(db.Integer, primary_key=True) kurssit = db.relationship('Kurssi', secondary=ilmoittautuminen, lazy='subquery', back_populates="asiakkaat") asiakkaan_kayttaja_id = db.Column(db.Integer, db.ForeignKey('kayttaja.id'), unique=True, nullable=False) kayttaja = db.relationship("Kayttaja", back_populates="asiakas")
@date : 2019-05-27 17:38 数据库表模型 提供三张表模型:用户表、博客表、标签表 用户与博客为一对多关系 博客与标签为多多多关系 关于模型声明参考:http://docs.jinkan.org/docs/flask-sqlalchemy/models.html """ import uuid from datetime import datetime from pytz import timezone from application import db tags = db.Table('tags', db.Column('tag_id', db.Integer, db.ForeignKey('tag.id')), db.Column('blog_id', db.Integer, db.ForeignKey('blog.id'))) class User(db.Model): id = db.Column(db.String(80), primary_key=True) name = db.Column(db.String(80), unique=True, nullable=False) role = db.Column(db.Integer, nullable=False) blogs = db.relationship('Blog', backref='User', lazy='dynamic') USER = 0 ADMIN = 1 def __init__(self, name, role): self.id = str(uuid.uuid1()) self.name = name
from datetime import datetime from application import db from werkzeug.security import generate_password_hash, check_password_hash from flask_login import UserMixin #authenticated from application import login #loader function import sqlite3 @login.user_loader def load_user(id): return User.query.get(int(id)) 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(UserMixin, db.Model): id = db.Column(db.Integer, primary_key=True) username = db.Column(db.String(64), index=True, unique=True) email = db.Column(db.String(120), index=True, unique=True) password_hash = db.Column(db.String(128)) posts = db.relationship('Post', backref='author', lazy='dynamic') about_me = db.Column(db.String(140)) last_seen = db.Column(db.DateTime, default=datetime.utcnow) followed = db.relationship('User', secondary=followers, primaryjoin=(followers.c.follower_id == id),
topic_desc = db.Column(db.String(500), nullable=False, unique=True) def __repr__(self): return ''.join([ 'Topic ID: ', self.id, ' ', '\r\n', 'Title: ', self.topic_title, '\r\n', 'Description:', self.topic_desc, '\r\n' ]) class Podcast(db.Model): id = db.Column(db.Integer, primary_key=True, autoincrement=True) podcast_title = db.Column(db.String(500), nullable=False, unique=True) podcast_detail = db.Column(db.String(500), nullable=False, unique=True) episode = db.relationship('Topics', secondary='episode', cascade='delete', backref=db.backref('episode'), lazy='dynamic') def __repr__(self): return ''.join([ 'Podcast ID: ', self.id, ' ', '\r\n', 'Title: ', self.podcast_title, '\r\n', 'Detail: ', self.podcast_detail, '\r\n' ]) episode = db.Table( 'episode', db.Model.metadata, db.Column('podcast_id', db.Integer, db.ForeignKey('podcast.id')), db.Column('topic_id', db.Integer, db.ForeignKey('topics.id')))
from application import db from application.models import Base from sqlalchemy.sql import text tagtask = db.Table('tagtask', db.Column('tag_id', db.Integer, db.ForeignKey('tag.id'), primary_key=True), db.Column('task_id', db.Integer, db.ForeignKey('task.id'), primary_key=True) ) class Task(Base): __tablename__ = 'task' name = db.Column(db.String(30), nullable=False) description = db.Column(db.String(60)) estimated_time = db.Column(db.Integer()) used_time = db.Column(db.Integer()) username = db.Column(db.String(20)) account_id = db.Column(db.Integer, db.ForeignKey('account.id'), nullable=False) tags = db.relationship('Tag', secondary='tagtask', back_populates='tasks') def __init__(self, name, description, estimated_time): self.name = name self.description = description self.estimated_time = estimated_time @staticmethod def find_users_tasks(id):
from application import db from application.models import Base relation = db.Table( "relation", db.Column('id', db.Integer, primary_key=True), db.Column('category_id', db.Integer, db.ForeignKey('category.id', ondelete="cascade")), db.Column('message_id', db.Integer, db.ForeignKey('message.id', ondelete="cascade"))) class Message(Base): subject = db.Column(db.String(144), nullable=False) body = db.Column(db.String(144), nullable=False) read = db.Column(db.Boolean, nullable=False) account_id = db.Column(db.Integer, db.ForeignKey('account.id'), nullable=False) categories = db.relationship('Category', secondary=relation, lazy='subquery', backref=db.backref('message', lazy=True)) def __init__(self, subject, body): self.subject = subject self.body = body self.read = False
from application import db from application.models import Base from application.ingredients.models import Ingredient from application.auth.models import User keywords = db.Table( 'keywords_helper', db.Column('keyword_id', db.Integer, db.ForeignKey('keyword.id'), primary_key=True), db.Column('drink_id', db.Integer, db.ForeignKey('drink.id'), primary_key=True)) class Drink(Base): __tablename__ = "drink" tags = db.relationship('Keyword', secondary=keywords, backref=db.backref('keywords_helper', lazy=True, load_on_pending=False)) ingredients = db.relationship('DrinkIngredient', back_populates='drink') name = db.Column(db.String(50), nullable=False) instructions = db.Column(db.String(250)) accepted = db.Column(db.Boolean, nullable=False)
from application import db from application.models import Base from application.authors.models import Author from sqlalchemy.sql import text bookAuthor = db.Table( 'book_author', db.Column('book_id', db.Integer, db.ForeignKey('book.id')), db.Column('author_id', db.Integer, db.ForeignKey('author.id'))) class Book(Base): name = db.Column(db.String(144), nullable=False) author = db.relationship("Author", secondary=bookAuthor, backref='book_author', lazy=True, cascade="delete") published = db.Column(db.Integer, nullable=False) count = db.Column(db.Integer, nullable=False) desc = db.Column(db.String(1050), nullable=False) #loan_id = db.Column(db.Integer, db.ForeignKey('loan.id'), nullable=False) loans = db.relationship("Loan", backref='book', lazy=True) def __init__(self, name): self.name = name @staticmethod def list_authors(bookid):
from application import db from application.tour import models from application.models import Base tours = db.Table( 'tours_users', db.Column('account_id', db.Integer, db.ForeignKey('account.id')), db.Column('tour_id', db.Integer, db.ForeignKey('tour.id'))) class User(Base): __tablename__ = "account" name = db.Column(db.String(144), nullable=False) username = db.Column(db.String(144), nullable=False) password = db.Column(db.String(144), nullable=False) gigs = db.relationship("Gig", backref='account', lazy=True) role_id = db.Column(db.Integer, db.ForeignKey('role.id'), nullable=True) role = db.relationship("Role") tours = db.relationship('Tour', secondary=tours, backref=db.backref('tours', lazy=True)) def __init__(self, name, username, password): self.name = name self.username = username self.password = password def get_id(self): return self.id
from application import app, db, login_manager from flask_login import UserMixin # Create db model of an item jointable = db.Table('jointable', db.Column('itemid', db.Integer, db.ForeignKey('itemtable.itemid'), primary_key=True), db.Column('categoryid', db.Integer, db.ForeignKey('category.categoryid'), primary_key=True) ) class Itemtable(db.Model): itemid = db.Column(db.Integer, primary_key=True) item_name = db.Column(db.String(), nullable=False) quantity = db.Column(db.Float, nullable=True) budget = db.Column(db.Float, nullable=True) urgency_level = db.Column(db.Integer, nullable=True) notes = db.Column(db.String(), nullable=True) # User Id user_id = db.Column(db.Integer,db.ForeignKey('user.user_id'),nullable=False) jointablerelation = db.relationship('Category', secondary=jointable, lazy='subquery', backref=db.backref('items', lazy=True)) # Create a funcion to return a string when we add something def __repr__(self): return "<Name %r>" % self.itemid # Create db model of Category
import uuid from flask_login import UserMixin from werkzeug.security import generate_password_hash, check_password_hash from application import db, login user_stops = db.Table( 'user_stops', db.Column('user_id', db.Integer, db.ForeignKey('users.id'), primary_key=True), db.Column('stop_id', db.Integer, db.ForeignKey('stops.id'), primary_key=True)) class Stop(db.Model): """ Model represents Public Transport Stop. """ __tablename__ = "stops" id = db.Column('id', db.Integer, primary_key=True, autoincrement=True) internal_id = db.Column('internal_id', db.String) name = db.Column('name', db.Unicode) code = db.Column('code', db.String) latitude = db.Column('latitude', db.Float)
from application import db from application.models import Base from sqlalchemy.sql import text tournamentPlayers = db.Table( 'tournament_players', db.Column('account_id', db.Integer, db.ForeignKey('account.id')), db.Column('tournament_id', db.Integer, db.ForeignKey('tournament.id'))) tournamentGames = db.Table( 'tournament_games', db.Column('tournament_id', db.Integer, db.ForeignKey('tournament.id')), db.Column('games_id', db.Integer, db.ForeignKey('game.id'))) class Tournament(Base): name = db.Column(db.String(144), nullable=False) playerCount = db.Column(db.Integer, nullable=False) done = db.Column(db.Boolean, nullable=False) account_id = db.Column(db.Integer, db.ForeignKey('account.id'), nullable=False) players = db.relationship('User', secondary=tournamentPlayers, backref='tournamentPlayers', lazy=True) games = db.relationship('Game',
from datetime import datetime as dt from flask_login import current_user from sqlalchemy.sql import text from application import db from application.models import Base from application.utils.utils import PRICE ADMIN = "ADMIN" admin = db.Table( "admin", db.Column("account_id", db.Integer, db.ForeignKey("account.id"), primary_key=True, index=True), db.Column("community_id", db.Integer, db.ForeignKey("community.id"), primary_key=True, index=True)) class Account(Base): community_id = db.Column(db.Integer, db.ForeignKey("community.id"), nullable=False, index=True) username = db.Column(db.String(144), nullable=False, unique=True,
from application import db from datetime import datetime as dt from . import user_model chats_users = db.Table( 'chats_users', db.Model.metadata, db.Column('chat_id', db.Integer, db.ForeignKey('chat.id')), db.Column('user_id', db.Integer, db.ForeignKey('user.id'))) class Chat(db.Model): __tablename__ = 'chat' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(250), nullable=False) messages = db.relationship("Message", backref="chat") participants = db.relationship("User", secondary=chats_users, lazy='dynamic', backref=db.backref('chats', lazy='dynamic')) creation_date = db.Column(db.DateTime) def __init__(self, name="Default name", participants=None): self.creation_date = dt.now() self.name = name if (participants is not None): for participant in participants: user = user_model.User.query.get(participant) if user is not None: self.participants.append(user) else: self.participants = None
from application import db from application.models import Base account_match = db.Table( 'account_match', db.Column('account_id', db.Integer, db.ForeignKey('account.id')), db.Column('match_id', db.Integer, db.ForeignKey('match.id'))) class User(Base): __tablename__ = "account" date_created = db.Column(db.DateTime, default=db.func.current_timestamp()) date_modified = db.Column(db.DateTime, default=db.func.current_timestamp(), onupdate=db.func.current_timestamp()) name = db.Column(db.String(144), nullable=False) username = db.Column(db.String(144), nullable=False) password = db.Column(db.String(144), nullable=False) matches = db.relationship('Match', secondary=account_match, backref=db.backref('match_accounts', lazy='dynamic')) def __init__(self, name, username, password): self.name = name self.username = username self.password = password
from application import db from application.models import Base from sqlalchemy.sql import text gamePlayers = db.Table( 'game_players', db.Column('account_id', db.Integer, db.ForeignKey('account.id')), db.Column('game_id', db.Integer, db.ForeignKey('game.id'))) class Game(Base): name = db.Column(db.String(144), nullable=False) playerCount = db.Column(db.Integer, nullable=False) done = db.Column(db.Boolean, nullable=False) score1 = db.Column(db.Integer) score2 = db.Column(db.Integer) account_id = db.Column(db.Integer, db.ForeignKey('account.id'), index=True) players = db.relationship('User', secondary=gamePlayers, backref='gamePlayers', lazy=True) def __init__(self, name): self.name = name self.done = False @staticmethod def setScore(gameid, a, b):
from application import db from sqlalchemy.sql import text conversations = db.Table('conversations', db.Column('account_id', db.Integer, db.ForeignKey( 'account.id'), primary_key=True), db.Column('message_id', db.Integer, db.ForeignKey( 'message.id'), primary_key=True) ) class User(db.Model): __tablename__ = "account" id = db.Column(db.Integer, primary_key=True) username = db.Column(db.String(144), unique=True, nullable=False) password = db.Column(db.String(144), nullable=False) email = db.Column(db.String(144), nullable=False) created_at = db.Column(db.DateTime, default=db.func.current_timestamp()) updated_at = db.Column(db.DateTime, default=db.func.current_timestamp(), onupdate=db.func.current_timestamp()) photos = db.relationship( "Photo", cascade="all, delete-orphan", backref='account', lazy=True) conversations = db.relationship("Message", secondary="conversations", backref="account")
from application import app, db from application.models import Base from datetime import datetime from flask_login import current_user from sqlalchemy.sql import text user_course = db.Table( "usercourse", db.Column("user_id", db.Integer, db.ForeignKey("account.id", ondelete="CASCADE")), db.Column("course_id", db.Integer, db.ForeignKey("course.id", ondelete="CASCADE"))) class Course(Base): course_id = db.Column(db.String(144), nullable=True) title = db.Column(db.String(144), nullable=False) description = db.Column(db.String(144), nullable=False) duration = db.Column(db.Integer, nullable=False) deadline = db.Column(db.DateTime, nullable=True) logs = db.relationship("Log", backref="course", lazy=True) users = db.relationship("User", secondary=user_course, backref=db.backref("courses", lazy="dynamic", passive_deletes=True)) def __init__(self, course_id, title, description, duration, deadline):
from application import db from sqlalchemy.sql import text, desc from sqlalchemy.orm import backref from datetime import datetime, timedelta association_table = db.Table('post_hashtag', db.Model.metadata, db.Column('post_id', db.Integer, db.ForeignKey('post.id')), db.Column('hashtag_id', db.Integer, db.ForeignKey('hashtag.id')) ) class Post(db.Model): id = db.Column(db.Integer, primary_key=True) # Parent id is the id of the post this post is a reply to parent_id = db.Column(db.Integer, db.ForeignKey("post.id")) create_time = db.Column(db.DateTime, default=db.func.current_timestamp(), nullable=False) modify_time = db.Column(db.DateTime, default=db.func.current_timestamp(), onupdate=db.func.current_timestamp(), nullable=False) user_id = db.Column(db.Integer, db.ForeignKey("account.id"), nullable=False) content = db.Column(db.String(3000), nullable=False) replies = db.relationship("Post", backref=backref("parent", remote_side=id), cascade="all, delete-orphan", order_by= lambda: desc(Post.create_time)) hashtags = db.relationship("Hashtag", secondary=association_table, back_populates="posts") def __init__(self, user_id, content, reply_to): self.user_id = user_id self.set_content(content) self.parent_id = reply_to def set_content(self, content):
from application import db from application.models import Base from sqlalchemy.orm import relationship, backref from sqlalchemy.sql import text song_artist = db.Table( 'song_artist', db.Column('artist_id', db.Integer, db.ForeignKey('artist.id'), primary_key=True), db.Column('song_id', db.Integer, db.ForeignKey('song.id'), primary_key=True)) album_artist = db.Table( 'album_artist', db.Column('artist_id', db.Integer, db.ForeignKey('artist.id'), primary_key=True), db.Column('album_id', db.Integer, db.ForeignKey('album.id'), primary_key=True)) class Artist(Base): song_artist = db.relationship('Song', secondary=song_artist,
from application import db from application.models import Base from sqlalchemy.sql import text from flask_login import current_user luokat = db.Table( 'luokat', db.Column('luokat_id', db.ForeignKey('luokka.id'), primary_key=True), db.Column('resepti_id', db.Integer, db.ForeignKey('resepti.id'), primary_key=True)) class Luokka(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(144), nullable=False) def __init__(self, name): self.name = name class Resepti(Base): __tablename__ = "resepti" name = db.Column(db.String(144), nullable=False) done = db.Column(db.Boolean, nullable=False) ainesosat = db.Column(db.String(1000), nullable=False) tyovaiheet = db.Column(db.String(1000), nullable=False)
from datetime import datetime from application import db # таблица многие-ко-многим: посты-теги tag_x_post = db.Table( 'tag_x_post', db.Column('tag_id', db.Integer, db.ForeignKey('tag.id'), primary_key=True), db.Column('post_id', db.Integer, db.ForeignKey('post.id'), primary_key=True)) class Post(db.Model): # Сообщение id = db.Column(db.Integer, primary_key=True) category_id = db.Column( db.Integer, db.ForeignKey('category.id')) # ссылка на категорию author_id = db.Column(db.Integer, db.ForeignKey('author.id')) # ссылка на автора поста title = db.Column(db.String(80)) # название body = db.Column(db.Text) # текст поста image = db.Column(db.String(36)) # строка с hash на картинку slug = db.Column(db.String(255), unique=True) publish_date = db.Column(db.DateTime) # таймтстамп поста live = db.Column(db.Boolean) # видимость поста--удаление поста # связи между таблицами author = db.relationship('Author', backref=db.backref('posts', lazy='dynamic')) category = db.relationship('Category',
from application import db from sqlalchemy.sql import text recipe_tag = db.Table( 'recipe_tag', db.Column('recipe_id', db.Integer, db.ForeignKey('recipe.id')), db.Column('tag_id', db.Integer, db.ForeignKey('tag.id'))) class Tag(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(50), nullable=False) category = db.Column(db.String(50), nullable=False) def __init__(self, name, category): self.name = name self.category = category class Recipe(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(50), nullable=False, index=True) duration = db.Column(db.Integer, nullable=False, index=True) instructions = db.Column(db.Text, nullable=False) account_id = db.Column(db.Integer, db.ForeignKey('account.id'), nullable=False) tags = db.relationship('Tag', secondary=recipe_tag) def __init__(self, name, duration, instructions): self.name = name
from application import db from sqlalchemy.ext.declarative import declared_attr account_project = db.Table( 'account_project', db.Column('account_id', db.Integer, db.ForeignKey('account.id', ondelete='cascade'), primary_key=True), db.Column('project_id', db.Integer, db.ForeignKey('project.id', ondelete='cascade'), primary_key=True)) class Base(db.Model): __abstract__ = True id = db.Column(db.Integer, primary_key=True) created = db.Column(db.DateTime, default=db.func.current_timestamp()) class UserResource(db.Model): __abstract__ = True def is_owned_by(self, user_id): return int(self.owner_id) == int(user_id) @declared_attr def owner_id(cls): return db.Column(db.Integer,
from application import db from application.models import Base from application.auth.models import User from flask_login import current_user from sqlalchemy.sql import text ingredients = db.Table( 'ingredients', db.Column('food_id', db.Integer, db.ForeignKey('food.id'), primary_key=True), db.Column('ingredient_id', db.Integer, db.ForeignKey('ingredient.id'), primary_key=True)) class Food(Base): name = db.Column(db.String(144), nullable=False) preparing_time = db.Column(db.Integer, nullable=False) recipe = db.Column(db.String(5000), nullable=False) type_id = db.Column(db.Integer, db.ForeignKey('type.id'), nullable=False) account_id = db.Column(db.Integer, db.ForeignKey('account.id'), nullable=False) ingredients = db.relationship('Ingredient', secondary=ingredients, lazy='subquery',
from application import db # Creating a many-to-many *-* relationship table between service and appointment service_appointment = db.Table("serviceappointment", db.Column("service_id", db.Integer, db.ForeignKey("service.id")), db.Column("appointment_id", db.Integer, db.ForeignKey("appointment.id"))) class Service(db.Model): id = db.Column(db.Integer, primary_key=True) service = db.Column(db.String(144), nullable=False) price = db.Column(db.Integer, nullable=False) serviceappointment = db.relationship("Appointment", secondary = service_appointment, lazy="subquery", backref = db.backref("services", lazy = True)) def __init__(self, service, price): self.service = service self.price = price
from application import db from application.models import Base from sqlalchemy.sql import text projectTask = db.Table( 'projectTask', db.Column('project_id', db.Integer, db.ForeignKey('project.id'), primary_key=True), db.Column('task_id', db.Integer, db.ForeignKey('task.id'), primary_key=True)) class Project(Base): name = db.Column(db.String(144), nullable=False) completed = db.Column(db.Boolean, nullable=False) account_id = db.Column(db.Integer, db.ForeignKey('account.id'), nullable=False) tasks = db.relationship("Task", secondary=projectTask, lazy='subquery', backref=db.backref('projects', lazy=True)) def __init__(self, name): self.name = name self.completed = False
'username_min': 4, 'username_max': 24, 'name_min': 4, 'name_max': 60, 'email_min': 6, 'email_max': 60, 'password_min': 6, 'password_max': 60, 'group_min': 3, 'group_max': 64, 'job_title': 64, 'avatar': 64 } flicket_groups = db.Table( 'flicket_groups', db.Column('user_id', db.Integer, db.ForeignKey('flicket_users.id')), db.Column('group_id', db.Integer, db.ForeignKey('flicket_group.id'))) class FlicketUser(PaginatedAPIMixin, Base): __tablename__ = 'flicket_users' id = db.Column(db.Integer, primary_key=True) username = db.Column(db.String(user_field_size['username_max']), index=True, unique=True) name = db.Column(db.String(user_field_size['name_max'])) password = db.Column(db.LargeBinary(user_field_size['password_max'])) email = db.Column(db.String(user_field_size['email_max']), unique=True) date_added = db.Column(db.DateTime) date_modified = db.Column(db.DateTime, onupdate=datetime.now)
from application import db from sqlalchemy.sql import text from datetime import datetime # Creating a many-to-many *-* relationship table between account and appointment account_appointment = db.Table( "accountappointment", db.Column("account_id", db.Integer, db.ForeignKey("account.id")), db.Column("appointment_id", db.Integer, db.ForeignKey("appointment.id"))) class Appointment(db.Model): id = db.Column(db.Integer, primary_key=True) start_time = db.Column(db.DateTime, default=db.func.current_timestamp()) reserved = db.Column(db.Boolean, nullable=False) accountappointment = db.relationship("User", secondary=account_appointment, lazy="subquery", backref=db.backref("appointments", lazy=True)) def __init__(self, start_time): self.start_time = start_time self.reserved = False @staticmethod def get_most_popular_services(): stmt = text(