Flask内のDBにおいて、テーブルを作成したい。（SQLite）

models.py

1from datetime import datetime 2from flask_sqlalchemy import SQLAlchemy 3from sqlalchemy import desc 4from collections import namedtuple 5 6db = SQLAlchemy() 7 8class Users(db.Model): 9 __tablename__ = 'users' 10 id = db.Column(db.Integer, primary_key=True) 11 username = db.Column(db.String(255)) 12 results = db.relationship('Results', backref='user', lazy=True) 13 14class Results(db.Model): 15 __tablename__ = 'results' 16 id = db.Column(db.Integer, primary_key=True) 17 user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False) 18 level = db.Column(db.Text, nullable=False) 19 clearTime = db.Column(db.Integer, nullable=False) 20 collectedCoins = db.Column(db.Integer, nullable=False) 21 timestamp = db.Column(db.DateTime, index=True, default=datetime.now) 22 23def add_user(username): 24 already = db.session.query(Users).filter(Users.username == username) 25 if not already: 26 model = Users(username=username) 27 db.session.add(model) 28 db.session.commit() 29 30def result_post(username, level, collectedCoins, clearTime): 31 user_id = db.session.query(Users).filter(Users.username == username) 32 model = Results( 33 user_id=user_id, 34 level=level, 35 clearTime=clearTime, 36 collectedCoins=collectedCoins 37 ) 38 db.session.add(model) 39 db.session.commit() 40 41 42Ranking = namedtuple('Ranking', [ 43 'result_id', 44 'username', 45 'level', 46 'clear_time', 47 'collected_coins', 48 'timestamp' 49]) 50def get_ranking(): 51 rank5_init = db.session.query( 52 Results.id, 53 Users.username, 54 Results.level, 55 Results.clearTime, 56 Results.collectedCoins, 57 Results.timestamp 58 ).join(Users, Users.id == Results.user_id 59 ).order_by( 60 desc(Results.collectedCoins), 61 desc(Results.clearTime) 62 ).limit(5).all() 63 64 rank5 = [Ranking( 65 result_id=row[0], 66 username=row[1], 67 level=row[2], 68 clear_time=row[3], 69 collected_coins=row[4], 70 timestamp=row[5] 71 ) for row in rank5_init] 72 73 # デバッグ用コード 74 print(f"Retrieved rankings: {rank5}") 75 return rank5