def get_tasks_info(table_title, game_id, login=None):
class UserTasks(object):
pass
engine = sqlalchemy.create_engine(
'sqlite:///' + os.path.join(basedir, 'databases', 'tasks_states.db'))
metadata = sqlalchemy.MetaData(engine)
table = sqlalchemy.Table(f"{game_id}_{table_title}",
metadata,
autoload=True)
orm.mapper(UserTasks, table)
_session = orm.sessionmaker(bind=engine)
session = _session()
game = db.session.query(Game).filter(Game.id == game_id).first()
result = dict()
if login is not None:
user_states = session.query(UserTasks).filter(
UserTasks.login == login).first()
if game.game_type == 'domino':
result['picked_tasks'] = getattr(user_states,
'picked_tasks').split()
for i in range(1, game.tasks_number + 1):
result[f't{i}'] = getattr(user_states, f't{i}')
else:
tasks = session.query(UserTasks).filter(True).all()
for task in tasks:
result[task.key] = {'number_of_sets': task.number_of_sets}
result[task.key]['current_checking_id'] = task.current_checking_id
if (game.game_type == 'penalty'):
result[task.key]['cost'] = task.cost
return result
def update_tasks_info(table_title, game_id, changes, login=None):
class UserTasks(object):
pass
engine = sqlalchemy.create_engine(
'sqlite:///' + os.path.join(basedir, 'databases', 'tasks_states.db'))
metadata = sqlalchemy.MetaData(engine)
table = sqlalchemy.Table(f"{game_id}_{table_title}",
metadata,
autoload=True)
orm.mapper(UserTasks, table)
_session = orm.sessionmaker(bind=engine)
session = _session()
print(table, game_id, changes)
if login is not None:
user_states = session.query(UserTasks).filter(
UserTasks.login == login).first()
for state in changes.items():
setattr(user_states, state[0], state[1])
session.commit()
else:
for change in changes.items():
task = session.query(UserTasks).filter(
UserTasks.key == change[0]).first()
print('change', change)
print('change[1]', change[1])
print(task.cost, task.key, task.id)
for attr in change[1].items():
print('attr', attr)
setattr(task, attr[0], attr[1])
session.commit()
def get_results(game_id):
class UserTasks(object):
pass
engine = sqlalchemy.create_engine(
'sqlite:///' + os.path.join(basedir, 'databases', 'tasks_states.db'))
metadata = sqlalchemy.MetaData(engine)
table = sqlalchemy.Table(f"{game_id}_states", metadata, autoload=True)
orm.mapper(UserTasks, table)
_session = orm.sessionmaker(bind=engine)
session = _session()
game = db.session.query(Game).filter(Game.id == game_id).first()
result = []
users_states = session.query(UserTasks).filter(True).all()
for user_states in users_states:
new_result = []
s = 0
login = user_states.login
if game.game_format == 'personal':
title = login
else:
team = db.session.query(Team).filter(Team.login == login).first()
title = team.title
new_result.append(title)
for i in range(1, game.tasks_number + 1):
new_result.append(getattr(user_states, f't{i}'))
print(user_states)
s += int(new_result[-1][:-2])
new_result.append(s)
result.append(new_result)
result.sort(key=lambda x: -x[-1])
return result
def create_session(self, options):
"""Create the session factory used by :meth:`create_scoped_session`.
The factory **must** return an object that SQLAlchemy recognizes as a session,
or registering session events may raise an exception.
Valid factories include a :class:`~sqlalchemy.orm.session.Session`
class or a :class:`~sqlalchemy.orm.session.sessionmaker`.
The default implementation creates a ``sessionmaker``
for :class:`SignallingSession`.
:param options: dict of keyword arguments passed to session class
"""
return orm.sessionmaker(class_=CustomSignallingSession,
db=self,
**options)
def add_user_to_game_table(login, game_id):
class UserTasks(object):
def __init__(self, attr_dict):
for key, val in attr_dict.items():
setattr(self, key, val)
engine = sqlalchemy.create_engine(
'sqlite:///' + os.path.join(basedir, 'databases', 'tasks_states.db'))
metadata = sqlalchemy.MetaData(engine)
table = sqlalchemy.Table(f"{game_id}_states", metadata, autoload=True)
orm.mapper(UserTasks, table)
_session = orm.sessionmaker(bind=engine)
session = _session()
game = db.session.query(Game).filter(Game.id == game_id).first()
dict_of_attr = {'login': login}
if game.game_type == 'domino':
dict_of_attr['picked_tasks'] = ''
for i in range(1, game.tasks_number + 1):
dict_of_attr[f't{i}'] = '0ok'
user = UserTasks(dict_of_attr)
session.add(user)
session.commit()
def add_task_for_manual_checking_db(game_id, attrs, is_result=False):
class ResultTask(object):
def __init__(self, attr_dict):
for key, val in attr_dict.items():
setattr(self, key, val)
class WorkingTask(object):
def __init__(self, attr_dict):
for key, val in attr_dict.items():
setattr(self, key, val)
engine = sqlalchemy.create_engine(
'sqlite:///' + os.path.join(basedir, 'databases', 'tasks_states.db'))
metadata = sqlalchemy.MetaData(engine)
if is_result:
result_table = sqlalchemy.Table(f'{game_id}_result_manual_check',
metadata,
autoload=True)
orm.mapper(ResultTask, result_table)
else:
working_table = sqlalchemy.Table(f"{game_id}_working_manual_check",
metadata,
autoload=True)
orm.mapper(WorkingTask, working_table)
_session = orm.sessionmaker(bind=engine)
session = _session()
if is_result:
print('attrs', attrs)
result = ResultTask(attrs)
session.add(result)
else:
working_task = WorkingTask(attrs)
session.add(working_task)
session.commit()
def get_current_manual_checking(game_id):
engine = sqlalchemy.create_engine(
'sqlite:///' + os.path.join(basedir, 'databases', 'tasks_states.db'))
metadata = sqlalchemy.MetaData(engine)
task_id = get_tasks_info('numbers_of_sets',
game_id)['t1']['current_checking_id']
class WorkingTask(object):
def __init__(self, attr_dict):
for key, val in attr_dict.items():
setattr(self, key, val)
working_table = sqlalchemy.Table(f"{game_id}_working_manual_check",
metadata,
autoload=True)
orm.mapper(WorkingTask, working_table)
_session = orm.sessionmaker(bind=engine)
session = _session()
checking_answer = session.query(WorkingTask).filter(
WorkingTask.id == task_id).first()
if checking_answer is None:
return 'Not found'
else:
return checking_answer, task_id
def create_tasks_tables(game_id, tasks_number):
engine = sqlalchemy.create_engine(
'sqlite:///' + os.path.join(basedir, 'databases', 'tasks_states.db'))
metadata = sqlalchemy.MetaData(engine)
game = db.session.query(Game).filter(Game.id == game_id).first()
working_manual_check_table = sqlalchemy.Table(
f"{game_id}_working_manual_check", metadata,
sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True),
sqlalchemy.Column('login', sqlalchemy.String),
sqlalchemy.Column('task_id', sqlalchemy.String),
sqlalchemy.Column('position', sqlalchemy.String),
sqlalchemy.Column('answer', sqlalchemy.String),
sqlalchemy.Column('ans_picture', sqlalchemy.Boolean),
sqlalchemy.Column('time', sqlalchemy.String))
result_manual_check_table = sqlalchemy.Table(
f'{game_id}_result_manual_check', metadata,
sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True),
sqlalchemy.Column('login', sqlalchemy.String),
sqlalchemy.Column('task_id', sqlalchemy.String),
sqlalchemy.Column('position', sqlalchemy.String),
sqlalchemy.Column('answer', sqlalchemy.String),
sqlalchemy.Column('time', sqlalchemy.String),
sqlalchemy.Column('result', sqlalchemy.Boolean))
if game.game_type == 'domino':
state_table = sqlalchemy.Table(
f"{game_id}_states", metadata,
sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True),
sqlalchemy.Column('login', sqlalchemy.String),
sqlalchemy.Column('picked_tasks', sqlalchemy.String), *[
sqlalchemy.Column('t' + str(i), sqlalchemy.Integer)
for i in range(1, tasks_number + 1)
])
numbers_of_sets_table = sqlalchemy.Table(
f"{game_id}_numbers_of_sets", metadata,
sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True),
sqlalchemy.Column('current_checking_id', sqlalchemy.Integer),
sqlalchemy.Column('key', sqlalchemy.String),
sqlalchemy.Column('number_of_sets', sqlalchemy.Integer))
dict_of_attrs = {'number_of_sets': game.sets_number}
else:
state_table = sqlalchemy.Table(
f"{game_id}_states", metadata,
sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True),
sqlalchemy.Column('login', sqlalchemy.String), *[
sqlalchemy.Column('t' + str(i), sqlalchemy.Integer)
for i in range(1, tasks_number + 1)
])
numbers_of_sets_table = sqlalchemy.Table(
f"{game_id}_numbers_of_sets", metadata,
sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True),
sqlalchemy.Column('current_checking_id', sqlalchemy.Integer),
sqlalchemy.Column('key', sqlalchemy.String),
sqlalchemy.Column('cost', sqlalchemy.Integer),
sqlalchemy.Column('number_of_sets', sqlalchemy.Integer))
dict_of_attrs = dict([('number_of_sets', game.sets_number),
('cost', 16)])
class Tasks(object):
def __init__(self, dict_of_attrs):
for item in dict_of_attrs.items():
print(item[0], item[1])
setattr(self, item[0], item[1])
metadata.create_all(engine)
print('what?')
orm.mapper(Tasks, numbers_of_sets_table)
_session = orm.sessionmaker(bind=engine)
session = _session()
dict_of_attrs['current_checking_id'] = 1
for i in range(1, game.tasks_number + 1):
new_dict_of_attrs = dict_of_attrs.copy()
new_dict_of_attrs['key'] = f't{i}'
task = Tasks(new_dict_of_attrs)
session.add(task)
session.commit()
if column.name in self.column_filter:
continue
if isinstance(column.type, sqltypes.DateTime):
local_time = convert_utc_to_local(getattr(self, column.name),
'Asia/Shanghai')
d[column.name] = local_time.strftime(
'%Y-%m-%d %H:%M:%S') if local_time else DEFAULT_DATETIME
else:
d[column.name] = getattr(self, column.name)
return d
db = SQLAlchemy(query_class=SeedQuery)
migrate = Migrate()
session = orm.scoped_session(orm.sessionmaker(autocommit=True))
ma = Marshmallow()
class BaseModel(db.Model, SessionMixin):
__abstract__ = True
column_filter = ['created', 'updated']
id = db.Column(db.Integer, primary_key=True)
created = db.Column(db.DateTime, nullable=False, default=datetime.utcnow())
updated = db.Column(db.DateTime,
default=datetime.utcnow(),
onupdate=datetime.utcnow())
def __init__(self, *args, **kwargs):
super(BaseModel, self).__init__(*args, **kwargs)
def create_session(self, options):
return orm.sessionmaker(class_=SignallingSession, db=self, **options)