def create_game_logs_table(db_manager: DbManager):
sql_create_game_logs_table = """
CREATE TABLE IF NOT EXISTS GameLogs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
player_id TEXT,
team_game_number INTEGER,
player_game_number INTEGER,
is_playoffs INTEGER,
season INTEGER,
date DATE,
team TEXT,
at_home INTEGER,
opponent TEXT,
game_started INTEGER,
seconds_played INTEGER,
field_goals INTEGER,
field_goal_attempts INTEGER,
three_points INTEGER,
three_point_attempts INTEGER,
free_throws INTEGER,
free_throw_attempts INTEGER,
offensive_rebounds INTEGER,
defensive_rebounds INTEGER,
assists INTEGER,
steals INTEGER,
blocks INTEGER,
turnovers INTEGER,
fouls INTEGER,
points INTEGER,
plus_minus INTEGER,
fanduel_score REAL) """
if db_manager.execute(sql_create_game_logs_table) is None:
print("Error creating GameLogs table.")
def get_player_ids(db_manager: DbManager) -> PlayerIds:
get_ids_query = 'SELECT id FROM Players'
result = db_manager.execute(get_ids_query)
player_ids = []
if result is not None:
player_ids = [player_id[0] for player_id in result.fetchall()]
return player_ids
def create_players_table(db_manager: DbManager):
sql_create_players_table = """
CREATE TABLE IF NOT EXISTS Players (
id TEXT PRIMARY KEY,
name TEXT NOT NULL);
"""
if db_manager.execute(sql_create_players_table) is None:
print("Error creating Players table.")
sys.path.append("..")
from dbmanager import DbManager
from common import Common
os.chdir("../")
cfg = Common.loadConfig()
#print cfg['db']
db = DbManager()
db.initConn(cfg['db'])
sql = "show databases like 'cms_site_%'"
n,data = db.executeQuery(0,sql)
for row in data:
pid = row[0].replace('cms_site_','')
#创建statistics表
sql = "CREATE TABLE IF NOT EXISTS `cms_template_statistics` (`template_id` int(11) NOT NULL COMMENT '模板id',`document_count` int(11) NOT NULL COMMENT '文档数',UNIQUE KEY `template_id` (`template_id`),KEY `document_count` (`document_count`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
n,createdata = db.execute(pid,sql)
print sql
print '@@@@@@@@'
sql = "show tables like 'cms_tbl_%'"
n ,tbldata = db.executeQuery(pid,sql)
for tbl in tbldata:
tid = tbl[0].replace('cms_tbl_','')
sql = 'select count(document_id) as doc_count from '+tbl[0]
n,statdata = db.executeQuery(pid,sql)
for stat in statdata:
print 'TID:',tid,' Count:',stat[0]
sql = "INSERT INTO `cms_template_statistics` (template_id,document_count) VALUES("+str(tid)+", "+str(stat[0])+") ON DUPLICATE KEY UPDATE `document_count`=`document_count`+1"
n,insertdata = db.execute(pid,sql)
print sql
print '======',pid,'======'
rfind('.'):]
note = "\n<!--build by cms at " + datetime.datetime.now().strftime(
'%Y-%m-%d %H:%M:%S') + "-->" if ext_name in ('.html', '.shtml', 'htm',
'shtm') else ''
fp.write(template_view + note)
fp.close()
logging.info('[Compiler]: create html succ,url:' + publish_url)
if preview == 'N':
# 回写document记录
sql = "update `cms_tbl_{$tid}` set `publish_time`=now(),`publish_user`='{$user}',`publish_url`='{$publish_url}' where document_id=" + str(
did)
sql = sql.replace('{$tid}',
str(tid)).replace('{$user}', curent_user).replace(
'{$publish_url}', publish_url)
n, data = db.execute(pid, sql)
pass
# 断言更新文档是否成功
cms_assert(n < 1, 500, 'build template view error!' + sql)
pass
make_page_code = 201 if preview == 'N' else 200
# 更新邮箱
task_mail.append({
'code': make_page_code,
'errmsg': u'生成模板页面成功!',
'progress': 100,
'pid': str(pid),
'url': publish_url
})
redis.setex(task_id, timeout, json.dumps(task_mail, ensure_ascii=False))
def insert_game_log(db_manager: DbManager, game_log_tuple: Tuple[Any]):
insert_game_log_query = 'INSERT INTO GameLogs VALUES (NULL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
db_manager.execute(insert_game_log_query, game_log_tuple)
def insert_player(db_manager: DbManager, player_info_tuple: PlayerInfo):
insert_player_query = 'INSERT INTO Players (id,name) VALUES (?, ?)'
db_manager.execute(insert_player_query, player_info_tuple)
def table_exists(db_manager: DbManager, table_name: str) -> bool:
table_query = "SELECT count(*) FROM sqlite_master WHERE type='table' AND name='{}'".format(table_name)
return int(db_manager.execute(table_query).fetchone()[0]) == 1
