def register():
#get current max id
c = dbExecute(getConnection(), 'SELECT max(ID) from Users')
max_id = c.fetchone()[0]
if not max_id:
max_id = 0
conn = getConnection()
dbExecute(conn, "INSERT INTO Users VALUES(?, ?, ?, ?, ?)",
(max_id + 1, request.json['username'], request.json['password'],
request.json['first'], request.json['last']))
conn.commit()
return jsonify({"id": max_id + 1, "username": request.json['username']})
def handler(event, context):
url = 'https://info.sporttery.cn/iframe/lottery_notice.php'
req = requests.get(url)
req.encoding='GB2312'
html = req.text
today = datetime.date.today()
todayISO = today.isoformat()
todayCN = '%d月%d日' % (today.month, today.day)
pattern = '%s竞彩游戏开售时间为(\d{2}:\d{2}),停售时间为(\d{2}:\d{2})' % (todayCN)
matched = re.findall(re.compile(pattern), html)
if len(matched) > 0:
conn = db.getConnection()
(startTime, stopTime) = matched[0]
startTimestamp = floor(datetime.datetime.timestamp(datetime.datetime.strptime('%s %s +0800' % (todayISO, startTime), '%Y-%m-%d %H:%M %z')) * 1000)
stopTimestamp = floor(datetime.datetime.timestamp(datetime.datetime.strptime('%s %s +0800' % (todayISO, stopTime), '%Y-%m-%d %H:%M %z')) * 1000)
try:
with conn.cursor() as cursor:
sql = 'REPLACE INTO `saletime` (`date`, `startTime`, `stopTime`) VALUES (%s, %s, %s)'
logger.info((todayISO, startTimestamp, stopTimestamp))
cursor.execute(sql, (todayISO, startTimestamp, stopTimestamp))
conn.commit()
finally:
conn.close()
return 'Done'
def getAnswer(self, category, language):
dbConnection = db.getConnection()
self.logger.info(u"getting answer {0} {1}".format(language,category))
c = dbConnection.cursor()
c.execute(u"SELECT `answer`, `code`, `id` FROM `answers` WHERE `lang` = %s AND `category` = %s ORDER BY RAND() LIMIT 1;", (language, category))
answers_result = c.fetchone()
if answers_result != None:
answer = answers_result[0]
c.execute(u"UPDATE `answers` SET `count` = `count` + 1 WHERE `id` = %s;", (answers_result[2]))
dbConnection.commit()
if answers_result[1] != None and answers_result[1] != "":
exec answers_result[1]
else:
# What if answer doesnt exist?
answer = dbTalk.localizations['answer']['notfound'][language].format(language, category)
c.close()
dbConnection.close()
return answer
def main(argv):
inputFile = ""
dataFile = ""
dbFile = "riskData.db"
outputFile = "riskFactor_output.txt"
tableName = "risks"
try:
opts,args = getopt.getopt(argv,"i:d:",["ifile=","dfile="])
except getopt.GetoptError:
print "python bayes.py -i <inputfile> -d <datafile>"
sys.exit(2)
for opt,arg in opts:
if opt == "-i":
inputFile = arg
elif opt == "-d":
dataFile = arg
fin = open(inputFile,"r")
fout = open(outputFile,"w")
graph = initGraph()
db.initDB(dataFile,dbFile)
conn = db.getConnection(dbFile)
cursor = db.getCursor(conn)
initCPT(graph,cursor,tableName)
lines = fin.readlines()
testCaseNum = (int)(lines[0])
for i in range(1,testCaseNum+1):
query = eval(lines[i])
db.endConnection(conn)
fin.close()
fout.close()
def processFeed(item, feedname):
print(('processing ' + feedname))
global stories
#run through each item in the feeds
conn = db.getConnection()
title = item.getElementsByTagName("title")
link = item.getElementsByTagName("link")
desc = item.getElementsByTagName("description")
if title:
ftitle = processor.getText(title[0].childNodes)
if link:
flink = processor.getText(link[0].childNodes)
if desc:
fdesc = processor.getText(desc[0].childNodes)
newItem = Item(flink, ftitle, fdesc)
AllFeeds.feedList[feedname].add_feed_item(newItem)
stories.addToStory(newItem, conn)
return AllFeeds.feedList[feedname]
def newQuestion(self, speech, language):
dbConnection = db.getConnection()
c = dbConnection.cursor()
c.execute(u"SELECT `last_question` FROM `assistants` WHERE `assistantId` = %s;", (self.assistant.assistantId))
question = c.fetchone()
new_question = self.ask(dbTalk.localizations['newquestion']['lastquestion'][language].format(unicode(question[0])))
save_answer = self.ask(dbTalk.localizations['newquestion']['newquestion'][language].format(new_question))
if re.match(dbTalk.localizations['newquestion']['register_save'][language], save_answer, re.IGNORECASE) != None:
c.execute(u"SELECT `category` FROM `questions` WHERE %s REGEXP `question`;", (question[0]))
question_result = c.fetchone()
if question_result != None:
question_formated = (u"^{0}".format(new_question))
c.execute(u"INSERT INTO `questions` (`question`,`category`) VALUES (%s,%s);", (question_formated,question_result[0]))
dbConnection.commit()
self.say(dbTalk.localizations['newquestion']['saved'][language])
else:
self.say(dbTalk.localizations['newquestion']['canceled'][language])
c.close()
dbConnection.close()
self.complete_request()
def processFeed(item, feedname):
print(('processing ' + feedname))
global stories
#run through each item in the feeds
conn = db.getConnection()
title = item.getElementsByTagName("title")
link = item.getElementsByTagName("link")
desc = item.getElementsByTagName("description")
if title:
ftitle = processor.getText(title[0].childNodes)
if link:
flink = processor.getText(link[0].childNodes)
if desc:
fdesc = processor.getText(desc[0].childNodes)
newItem = Item(flink, ftitle, fdesc)
AllFeeds.feedList[feedname].add_feed_item(newItem)
stories.addToStory(newItem, conn)
return AllFeeds.feedList[feedname]
def __init__(self, conn, lang):
asyncore.dispatcher_with_send.__init__(self, conn)
self.ssled = False
self.secure_connection(certfile="server.passless.crt",
keyfile="server.passless.key",
server_side=True)
self.consumed_ace = False
self.data = ""
self.binary_mode = False
self.decompressor = zlib.decompressobj()
self.compressor = zlib.compressobj()
self.unzipped_input = ""
self.unzipped_output_buffer = ""
self.output_buffer = ""
self.speech = dict()
self.pong = 1
self.ping = 0
self.httpClient = AsyncOpenHttp(self.handle_google_data,
self.handle_google_failure)
self.gotGoogleAnswer = False
self.googleData = None
self.lastRequestId = None
self.dictation = None
self.dbConnection = db.getConnection()
self.assistant = None
self.sendLock = threading.Lock()
self.current_running_plugin = None
self.current_location = None
self.plugin_lastAceId = None
self.logger = logging.getLogger("logger")
self.lang = lang
def get(self):
self.response.headers['Access-Control-Allow-Origin'] = '*'
iso=self.request.get('iso')
format=self.request.get('format')
# try to fetch json string from mem cache
jsonString = memcache.get('iso_'+iso)
# if jsonString is not None:
# self.response.out.write( jsonString )
# logging.info("DATA FROM CACHE: %s Size=%s bytes." % (iso, len(jsonString) ) )
# return
# if mem cache is empty, get it from database
if jsonString is None:
(conn,cursor)=db.getConnection()
jsonString=db.executeSelect(cursor,"SELECT * FROM fiscal_data f WHERE f.iso=\"%s\" " % iso)
conn.close()
if len (jsonString)<990000:
memcache.add('iso_'+iso, jsonString)
if format=="csv":
self.response.headers['Content-Type'] = 'application/csv'
self.response.headers['Content-Disposition'] = str("attachment; filename=fiscal_data_%s.csv" % iso)
self.response.out.write( db.jsonToCSV(jsonString) )
elif format=="html":
self.response.headers['Content-Type'] = 'text/html'
self.response.out.write( db.jsonToHTML(jsonString) )
else:
self.response.headers['Content-Type'] = 'application/json'
self.response.out.write( jsonString )
def getOdds(matchId, item, saleTime):
conn = db.getConnection()
try:
if item in ('win', 'level', 'lose'):
sql = "SELECT `{item}` FROM `spfodds` WHERE `matchId` = {matchId} AND `releaseTime` < {saleTime} ORDER BY `releaseTime` DESC LIMIT 1"
elif item in ('letWin', 'letLevel', 'letLose'):
sql = "SELECT `{item}` FROM `rqspfodds` WHERE `matchId` = {matchId} AND `releaseTime` < {saleTime} ORDER BY `releaseTime` DESC LIMIT 1"
elif item in ('zeroToZero', 'zeroToOne', 'zeroToTwo', 'zeroToThree',
'zeroToFour', 'zeroToFive', 'oneToZero', 'oneToOne',
'oneToTwo', 'oneToThree', 'oneToFour', 'oneToFive',
'twoToZero', 'twoToOne', 'twoToTwo', 'twoToThree',
'twoToFour', 'twoToFive', 'threeToZero', 'threeToOne',
'threeToTwo', 'threeToThree', 'fourToZero', 'fourToOne',
'fourToTwo', 'fiveToZero', 'fiveToOne', 'fiveToTwo',
'winOther', 'levelOther', 'loseOther'):
sql = "SELECT `{item}` FROM `bfodds` WHERE `matchId` = {matchId} AND `releaseTime` < {saleTime} ORDER BY `releaseTime` DESC LIMIT 1"
elif item in ('winWin', 'winLevel', 'winLose', 'levelWin',
'levelLevel', 'levelLose', 'loseWin', 'loseLevel',
'loseLose'):
sql = "SELECT `{item}` FROM `bqcodds` WHERE `matchId` = {matchId} AND `releaseTime` < {saleTime} ORDER BY `releaseTime` DESC LIMIT 1"
elif item in ('zero', 'one', 'two', 'three', 'four', 'five', 'six',
'seven'):
sql = "SELECT `{item}` FROM `zjqodds` WHERE `matchId` = {matchId} AND `releaseTime` < {saleTime} ORDER BY `releaseTime` DESC LIMIT 1"
sql = sql.format(item=item, matchId=matchId, saleTime=saleTime)
with conn.cursor() as cursor:
# 查询赛事基本信息
cursor.execute(sql)
dbResult = cursor.fetchone()
return dbResult.get(item)
except Exception as e:
logger.error(traceback.format_exc())
finally:
conn.close()
def __init__(self, conn):
asyncore.dispatcher_with_send.__init__(self, conn)
self.ssled = False
self.secure_connection(certfile="server.passless.crt", keyfile="server.passless.key", server_side=True)
self.consumed_ace = False
self.data = ""
self.binary_mode = False
self.decompressor = zlib.decompressobj()
self.compressor = zlib.compressobj()
self.unzipped_input = ""
self.unzipped_output_buffer = ""
self.output_buffer = ""
self.speech = dict()
self.pong = 1
self.ping = 0
self.httpClient = AsyncOpenHttp(self.handle_google_data, self.handle_google_failure)
self.gotGoogleAnswer = False
self.googleData = None
self.lastRequestId = None
self.dictation = None
self.dbConnection = db.getConnection()
self.assistant = None
self.sendLock = threading.Lock()
self.current_running_plugin = None
self.current_location = None
self.plugin_lastAceId = None
self.logger = logging.getLogger("logger")
def add_sensor(user_id: str):
conn = db.getConnection()
try:
db.addSensor(conn, user_id, request.json['sensorType'],
request.json['name'])
except:
return "Need to supply sensorType: {'temperature', 'humidity} and name", 400
return jsonify(success=True)
def addOrder():
if not request.json:
abort(400)
#get current max id
c = dbExecute(getConnection(), 'SELECT max(ID) from Orders')
max_id = c.fetchone()[0]
if not max_id:
max_id = 0
conn = getConnection()
dbExecute(
conn, "INSERT INTO Orders VALUES(?, ?, ?, ?, ?)",
(max_id + 1, json.dumps(request.json['contents']),
request.json['locationID'], request.json['customerID'], "Incomplete"))
conn.commit()
return jsonify({"id": max_id + 1})
def getPrice(symbol, local = False):
conn = getConnection(local)
try:
if local: df = pd.read_sql(sql_local , con = conn, params = [symbol] )
else: df = pd.read_sql(sql, con = conn, params = [symbol])
return df.head(1)
except Exception, e:
print('no price of symbol :' + str(symbol))
raise e
def getLocations():
c = dbExecute(getConnection(), 'SELECT * FROM Locations')
return (jsonify({
"locations": [{
"Name": x[0],
"Latitude": x[1],
"Longitude": x[2],
"ID": x[3]
} for x in c.fetchall()]
}))
def getPriceAt(symbol, date, local = False):
'获取某只股票某日期的行情'
conn = getConnection(local)
try:
if local: df = pd.read_sql(sql_local_price_at, con = conn, params = [symbol, date + ' 00:00:00'])
else: df = pd.read_sql(sql_priceat, con = conn, params = [symbol, date])
return df.head(1)
except Exception, e:
print('no price of symbol :' + str(symbol))
raise e
def get(self):
self.response.headers['Content-Type'] = 'application/json'
self.response.headers['Access-Control-Allow-Origin'] = '*'
id=self.request.get('id')
(conn,cursor)=db.getConnection()
jsonString=db.executeSelect(cursor,"SELECT * FROM fiscal_data_log WHERE id=%s ORDER BY change_date" % id)
self.response.out.write( jsonString )
conn.close()
def crawl(date):
conn = db.getConnection()
now = floor(datetime.datetime.timestamp(datetime.datetime.now()) * 1000)
url = 'https://i.sporttery.cn/wap/fb_match_list/get_fb_match_result/?format=json&date=%s&_=%d' % (date, now)
try:
# 抓取赛事数据
req = requests.get(url)
resultText = req.text
h = hashlib.sha256()
h.update(resultText.encode())
sha256 = h.hexdigest()
# 赛事数据没有更新的话直接结束
with conn.cursor() as cursor:
cursor.execute('SELECT `id` FROM `crawlerlog` WHERE `date` = %s AND `sha256` = %s', (date, sha256))
dbResult = cursor.fetchone()
if dbResult != None:
logger.info('No updated data')
return 'Done'
result = json.loads(resultText)
if result['data']:
if result['data']['result']:
for value in result['data']['result']:
logger.info(value)
# 保存赛事基本数据
row = dict()
matchId = value['id']
matchStatus = value['match_status']
half = ''
final = ''
if matchStatus == 'Final':
half = value['half']
final = value['final']
matchDetailCrawler.crawl(matchId)
with conn.cursor() as cursor:
sql = 'UPDATE `matchinfo` SET matchStatus = %s, half = %s, final = %s WHERE matchId = %s AND matchStatus IS NULL'
logger.info((matchStatus, half, final, matchId))
cursor.execute(sql, (matchStatus, half, final, matchId))
# 保存本次抓取日志
with conn.cursor() as cursor:
cursor.execute('INSERT INTO `crawlerlog` (`date`, `sha256`, `content`) VALUES (%s, %s, %s)', (date, sha256, resultText))
conn.commit()
return 'Done!'
except Exception as e:
logger.error(e)
finally:
conn.close()
def main():
conn = db.getConnection()
db.cleardb(conn)
global getPool
print('start main')
for feedname in processor.feedList.getFeedNames():
try:
getPool.add_task(getFeed, feedname)
print(('add get task' + feedname))
except:
print(("There is an error with the " + feedname + " feeds."))
poll()
def main():
conn = db.getConnection()
db.cleardb(conn)
global getPool
print('start main')
for feedname in processor.feedList.getFeedNames():
try:
getPool.add_task(getFeed, feedname)
print(('add get task' + feedname))
except:
print(("There is an error with the " + feedname + " feeds."))
poll()
def login():
username = request.json['username']
password = request.json['password']
c = dbExecute(getConnection(),
'SELECT ID FROM Users WHERE username=? AND password=?', (
username,
password,
))
result = c.fetchone()
if result:
return jsonify({"id": result[0], "username": username})
else:
abort(401)
def home():
if not google_auth.is_logged_in():
return redirect(url_for('welcome'))
else:
try:
city_id = session['city_id']
except:
city_id = 'Boston'
user_id = google_auth.get_user_info()['id']
try:
source = urllib.request.urlopen(
'http://api.openweathermap.org/data/2.5/weather?q=' + city_id +
'&appid=' + API_KEY).read()
except:
city_id = 'Boston (default)'
source = urllib.request.urlopen(
'http://api.openweathermap.org/data/2.5/weather?q=boston&appid='
+ API_KEY).read()
list_of_data = json.loads(source)
data = {
"country_code":
str(list_of_data['sys']['country']),
"coordinate":
str(list_of_data['coord']['lon']) + ' ' +
str(list_of_data['coord']['lat']),
"temp":
str(list_of_data['main']['temp']) + 'k',
"temp_cel":
tocelcius(list_of_data['main']['temp']) + ' Celcius',
"pressure":
str(list_of_data['main']['pressure']),
"humidity":
str(list_of_data['main']['humidity']),
"cityname":
city_id,
}
conn = db.getConnection()
sensors = db.getSensors(conn, user_id)
plots = list(map(lambda x: create_plot(x[0], x[1]), sensors))
names = list(map(lambda x: x[1], sensors))
sensorTypes = list(map(lambda x: x[0], sensors))
plotInfo = zip(plots, names, sensorTypes)
return render_template('home.html',
plotInfo=plotInfo,
data=data,
user_info=google_auth.get_user_info())
def getDeals(num=None, since=None, terms=None):
"""\
@param num: Number of deals to return. None means all.
@param since: Return deals with a higher deal_id than since.
@param terms: Search term string.
@return: Generator that yields matching deals.
"""
params = []
conn = db.getConnection()
curs = conn.cursor()
# These fields are lined up to correspond with Deal.__init__
sql = "select d.deal_id, d.description, f.name, d.url, d.received_on from deals d join feeds f on d.feed_id = f.feed_id "
whereClause = []
if since:
# FIXME: This assumes deal_ids are chronological
whereClause.append("deal_id > %s")
params.append(since)
if terms:
terms = parse_terms(terms)
whereClause.append('upper(d.description) like upper(%s)')
params.append("'%" + '%'.join(terms) + "%'")
if whereClause:
sql += (' where ' + ' AND '.join(whereClause))
sql += " order by d.received_on desc, d.deal_id desc"
if num:
sql += " limit %s"
params.append(num)
try:
curs.execute(sql, params)
except:
conn.commit()
raise
next = 1
while next:
next = curs.fetchone()
if next:
yield Deal(*next)
conn.commit()
def handler(event, context):
events = json.loads(event)
if events.get('matchId'):
for matchId in events.get('matchId'):
crawl(matchId)
else:
conn = db.getConnection()
with conn.cursor() as cursor:
cursor.execute(
'SELECT `matchId` FROM `matchinfo` WHERE `matchStatus` IS NULL'
)
rows = cursor.fetchall()
for row in rows:
crawl(row['matchId'])
return 'Done'
def startFactory(self):
logging.getLogger().info("Loading Session Certificates")
caFile = open("keys/SessionCACert.pem")
self.sessionCACert = crypto.load_certificate(crypto.FILETYPE_PEM,caFile.read())
caFile.close()
sessionCertFile = open("keys/SessionCert.pem")
self.sessionCert = crypto.load_certificate(crypto.FILETYPE_PEM, sessionCertFile.read())
sessionCertFile.close()
logging.getLogger().info("Setting Up Database")
db.setup()
logging.getLogger().info("Connection to Database")
self.dbConnection = db.getConnection()
logging.getLogger().info("Loading Plugin Framework")
PluginManager.load_api_keys()
PluginManager.load_plugins()
logging.getLogger().info("Server is running and listening for connections")
def setVar(self, key, val):
self.logger.info(u"setting key {0} = {1}".format(key,val))
dbConnection = db.getConnection()
c = dbConnection.cursor()
c.execute(u"SELECT `key`, `value` FROM `personal_vars` WHERE `Id` = %s AND `key` = %s;", (self.assistant.assistantId, key))
result = c.fetchone()
if result != None:
c.execute(u"UPDATE `personal_vars` set `value` = %s WHERE `Id` = %s and `key` = %s;", (val, self.assistant.assistantId, key))
else:
c.execute(u"INSERT INTO `personal_vars` (`Id`,`key`,`value`) VALUES (%s,%s,%s);", (self.assistant.assistantId, key, val))
dbConnection.commit()
c.close()
dbConnection.close()
def getVar(self, key):
self.logger.info(u"getting key {0}".format(key))
dbConnection = db.getConnection()
c = dbConnection.cursor()
c.execute(u"SELECT `value` FROM `personal_vars` WHERE `Id` = %s and `key` = %s;", (self.assistant.assistantId, key))
result = c.fetchone()
c.close()
dbConnection.close()
if result != None:
return result[0]
else:
# Default?
return "-"
def startFactory(self):
logging.getLogger().info("Loading Session Certificates")
caFile = open("keys/SessionCACert.pem")
self.sessionCACert = crypto.load_certificate(crypto.FILETYPE_PEM,
caFile.read())
caFile.close()
sessionCertFile = open("keys/SessionCert.pem")
self.sessionCert = crypto.load_certificate(crypto.FILETYPE_PEM,
sessionCertFile.read())
sessionCertFile.close()
logging.getLogger().info("Setting Up Database")
db.setup()
logging.getLogger().info("Connection to Database")
self.dbConnection = db.getConnection()
logging.getLogger().info("Loading Plugin Framework")
PluginManager.load_api_keys()
PluginManager.load_plugins()
logging.getLogger().info(
"Server is running and listening for connections")
def export():
wb = xlwt.Workbook()
ws = wb.add_sheet("My Sheet")
#Add title.
for i, title in enumerate(ShopExcel.TITLES):
ws.write(0, i, title)
conn = db.getConnection();
c = conn.cursor();
c.execute("SELECT * FROM shop where new == 1");
conn.commit();
row = 1;
for r in c.fetchall():
ws.write(row, ShopExcel.ROW_PROVINCE, r[ShopTable.COL_PROVINCE])
ws.write(row, ShopExcel.ROW_CITY, r[ShopTable.COL_CITY])
ws.write(row, ShopExcel.ROW_NAME, r[ShopTable.COL_NAME])
ws.write(row, ShopExcel.ROW_ADDRESS, r[ShopTable.COL_ADDRESS])
row += 1;
c.close();
wb.save("myworkbook.xls")
def export():
wb = xlwt.Workbook()
ws = wb.add_sheet("My Sheet")
#Add title.
for i, title in enumerate(ShopExcel.TITLES):
ws.write(0, i, title)
conn = db.getConnection()
c = conn.cursor()
c.execute("SELECT * FROM shop where new == 1")
conn.commit()
row = 1
for r in c.fetchall():
ws.write(row, ShopExcel.ROW_PROVINCE, r[ShopTable.COL_PROVINCE])
ws.write(row, ShopExcel.ROW_CITY, r[ShopTable.COL_CITY])
ws.write(row, ShopExcel.ROW_NAME, r[ShopTable.COL_NAME])
ws.write(row, ShopExcel.ROW_ADDRESS, r[ShopTable.COL_ADDRESS])
row += 1
c.close()
wb.save("myworkbook.xls")
def handler(environ, start_response):
try:
request_body_size = int(environ.get('CONTENT_LENGTH', 0))
except (ValueError):
request_body_size = 0
request_body = environ['wsgi.input'].read(request_body_size)
params = json.loads(request_body)
if not (params.get('matchNumber')):
return http400(start_response)
matchNumber = params.get('matchNumber')
conn = db.getConnection()
try:
fromTime = floor(
datetime.timestamp(datetime.now() - timedelta(days=1)) * 1000)
# 根据 matchNumber 查询赛事，查询范围为自24小时前起
with conn.cursor() as cursor:
cursor.execute(
'SELECT `matchId`, `number`, `saleStopTime` FROM `matchinfo` WHERE `saleStopTime` > %s AND `number` = %s',
(fromTime, matchNumber))
dbResult = cursor.fetchone()
if dbResult:
resp = dict()
resp['status'] = 0
resp['resultTime'] = dbResult.get('saleStopTime') + 3600000 * 3
return http200(start_response, resp)
else:
resp = dict()
resp['status'] = 404
return http200(start_response, resp)
except Exception as e:
logger.error(e)
logger.error(sys.exc_info()[2])
return http500(start_response)
finally:
logger.info('finally')
conn.close()
def startFactory(self):
logging.getLogger().info("Loading Session Certificates")
caFile = open("keys/SessionCACert.pem")
self.sessionCACert = crypto.load_certificate(crypto.FILETYPE_PEM,caFile.read())
caFile.close()
sessionCertFile = open("keys/SessionCert.pem")
self.sessionCert = crypto.load_certificate(crypto.FILETYPE_PEM, sessionCertFile.read())
sessionCertFile.close()
logging.getLogger().info("Setting Up Database")
db.setup()
logging.getLogger().info("Connection to Database")
self.dbConnection = db.getConnection()
logging.getLogger().info("Loading Plugin Framework")
PluginManager.load_api_keys()
PluginManager.load_plugins()
datenow = datetime.datetime.now()
timenow = datetime.time(12, 55, 0)
timestamp = datetime.datetime.combine(datenow, timenow)
logging.getLogger().info("{0}: Server is listening for connections".format(timestamp))
logging.getLogger().warning("{0}: Server is listening for connections".format(timestamp))
logging.getLogger().error("{0}: Server is listening for connections".format(timestamp))
def newAnswer(self, speech, language):
dbConnection = db.getConnection()
c = dbConnection.cursor()
c.execute(u"SELECT `last_question` FROM `assistants` WHERE `assistantId` = %s;", (self.assistant.assistantId))
question = c.fetchone()
new_answer = self.ask(dbTalk.localizations['newanswer']['lastquestion'][language].format(question[0]))
save_answer = self.ask(dbTalk.localizations['newanswer']['newanswer'][language].format(new_answer))
if re.match(dbTalk.localizations['newanswer']['register_save'][language], save_answer, re.IGNORECASE) != None:
c.execute(u"SELECT `category` FROM `questions` WHERE `lang` = %s AND %s REGEXP `question`;", (language,question[0]))
question_result = c.fetchone()
if question_result != None:
question_cat = question_result[0]
else:
c.execute(u"SELECT `category` FROM `questions` ORDER BY `category` DESC LIMIT 1;")
question_result = c.fetchone()
question_cat = int(question_result[0])+ 1
if question_cat < 1001:
question_cat = 1001
question_formated = (u"^{0}".format(question[0]))
c.execute(u"INSERT INTO `questions` (`lang`,`question`,`category`) VALUES (%s,%s,%s);", (language,question_formated,question_cat))
dbConnection.commit()
c = self.connection.dbConnection.cursor()
c.execute(u"INSERT INTO `answers` (`lang`,`answer`,`category`) VALUES (%s,%s,%s);", (language,new_answer,question_cat))
dbConnection.commit()
self.say(dbTalk.localizations['newanswer']['saved'][language])
else:
self.say(dbTalk.localizations['newanswer']['canceled'][language])
c.close()
dbConnection.close()
self.complete_request()
def get(self):
# conn = db.getConnectionOnly()
# cursor = conn.cursor()
(conn,cursor)=db.getConnection()
cursor.execute("""
SELECT distinct c.*
FROM countries c, fiscal_data f
WHERE c.iso=f.iso
ORDER BY c.country
""")
self.response.headers['Content-Type'] = 'application/json'
self.response.headers['Access-Control-Allow-Origin'] = '*'
columns_description = cursor.description
rows=cursor.fetchall()
self.response.out.write( db.getJsonString(rows,columns_description ))
conn.close()
def getDealByID(deal_id):
conn = db.getConnection()
curs = conn.cursor()
# These fields are lined up to correspond with Deal.__init__
sql = "select d.deal_id, d.description, f.name, d.url, d.received_on from deals d join feeds f on d.feed_id = f.feed_id where d.deal_id=%s"
params = [deal_id]
try:
curs.execute(sql, params)
except:
conn.commit()
raise
deal = None
row = curs.fetchone()
if row:
deal = Deal(*row)
conn.commit()
return deal
def handler(environ, start_response):
conn = db.getConnection()
data = []
now = floor(datetime.datetime.timestamp(datetime.datetime.now()) * 1000)
try:
sql = """SELECT `matchinfo`.`matchId`,`leagueAbbr` as `league`,`hostTeamAbbr` as `hostTeam`,`visitingTeamAbbr` as `visitingTeam`,`matchPeriod`,`number`,`printStopTime`,`saleStopTime`,`startTime`,`stopTime`,
`isSpf`,`isSingle`,`win`,`level`,`lose`,
`isRqspf`,`isLetSingle`,`letCount`,`letWin`,`letLevel`,`letLose`,
`isBf`,`zeroToZero`,`zeroToOne`,`zeroToTwo`,`zeroToThree`,`zeroToFour`,`zeroToFive`,`oneToZero`,`oneToOne`,`oneToTwo`,`oneToThree`,`oneToFour`,`oneToFive`,`twoToZero`,`twoToOne`,`twoToTwo`,`twoToThree`,`twoToFour`,`twoToFive`,`threeToZero`,`threeToOne`,`threeToTwo`,`threeToThree`,`fourToZero`,`fourToOne`,`fourToTwo`,`fiveToZero`,`fiveToOne`,`fiveToTwo`,`winOther`,`levelOther`,`loseOther`,
`isBqc`,`winWin`,`winLevel`,`winLose`,`levelWin`,`levelLevel`,`levelLose`,`loseWin`,`loseLevel`,`loseLose`,
`isZjq`,`zero`,`one`,`two`,`three`,`four`,`five`,`six`,`seven`
FROM `matchinfo`
LEFT OUTER JOIN `spf` ON `matchinfo`.`matchId` = `spf`.`matchId`
LEFT OUTER JOIN `rqspf` ON `matchinfo`.`matchId` = `rqspf`.`matchId`
LEFT OUTER JOIN `bf` ON `matchinfo`.`matchId` = `bf`.`matchId`
LEFT OUTER JOIN `bqc` ON `matchinfo`.`matchId` = `bqc`.`matchId`
LEFT OUTER JOIN `zjq` ON `matchinfo`.`matchId` = `zjq`.`matchId`
WHERE saleStopTime >= %s
"""
with conn.cursor() as cursor:
cursor.execute(sql, (now))
dbResult = cursor.fetchall()
if len(dbResult) > 0:
for row in dbResult:
matchData = {
**defaultFields,
**row, 'matchPeriod':
row.get('matchPeriod').isoformat()
}
data += [matchData]
finally:
conn.close()
status = '200 OK'
response_headers = [('Content-type', 'application/json')]
start_response(status, response_headers)
return [json.dumps(data).encode()]
def mergeOrders():
#get all current orders
c = dbExecute(getConnection(),
'SELECT Contents, LocationID, CustomerID FROM Orders')
combined = {}
for order in c.fetchall():
contents = json.loads(order[0])
location = order[1]
customer = order[2]
formatted = {
"customer": customer,
"location": location,
"contents": contents
}
if location in combined:
combined[location].append(formatted)
else:
combined[location] = [formatted]
return jsonify(combined)
def post(self):
#if the user is not logined in return
if not users.get_current_user():
return;
self.response.headers['Content-Type'] = 'application/json'
self.response.headers['Access-Control-Allow-Origin'] = '*'
iso=self.request.get('iso')
jsonStringRequest=self.request.body
(conn,cursor)=db.getConnection()
# check if the user is a spammer
if db.isSpammer(cursor,users.get_current_user().nickname()):
return
# update query
(id_value,updateQuery)=db.buildUpdateQuery('fiscal_data','id','changed_by',users.get_current_user().nickname(), jsonStringRequest)
cursor.execute(updateQuery)
# fetch saved record
jsonStringOneRecord=db.executeSelect( cursor ,"SELECT * FROM fiscal_data f WHERE f.id=%s" % id_value)
# should write ??
# {"success":true,"message":"Updated User 2","data":{"id":2,"first":"Wilma","last":"Flintstone","email":"*****@*****.**"}}
self.response.out.write( jsonStringOneRecord )
#refresh mem cache
memcache.delete('iso_'+iso)
jsonString=db.executeSelect(cursor,"SELECT * FROM fiscal_data f WHERE f.iso=\"%s\"" % iso)
if len (jsonString)<990000:
memcache.add('iso_'+iso, jsonString)
conn.close()
def handler(environ, start_response):
try:
request_body_size = int(environ.get('CONTENT_LENGTH', 0))
except (ValueError):
request_body_size = 0
request_body = environ['wsgi.input'].read(request_body_size)
params = json.loads(request_body)
if not (params.get('betting') and params.get('options')
and params.get('multiple') and params.get('bettingTime')):
return http400(start_response)
bettingTime = params.get('bettingTime')
options = params.get('options')
betting = params.get('betting')
multiple = params.get('multiple')
for option in options:
if not option in mxnOptions.keys():
return http400(start_response)
matchNumbers = []
for matchBetting in betting:
matchNumbers.append(matchBetting.get('matchNumber'))
conn = db.getConnection()
logger.info('params: {}'.format(params))
try:
matchIds = []
matchInfoById = dict()
matchInfoByNumber = dict()
refundIds = []
resultById = dict()
# 根据 matchNumber 查询赛事，查询范围为自投注时间起 4 天内
with conn.cursor() as cursor:
# 查询赛事基本信息
cursor.execute(
'SELECT `matchId`, `number`, `saleStopTime` FROM `matchinfo` WHERE `saleStopTime` BETWEEN %s AND %s AND `number` IN %s',
(bettingTime, bettingTime + 86400000 * 4, matchNumbers))
matches = cursor.fetchall()
if len(matches) < len(matchNumbers):
for mi in matches:
matchNumbers.remove(mi.get('number'))
resp = dict()
resp['status'] = 101
resp['message'] = 'Invalid matchNumbers: ' + ', '.join(
matchNumbers)
return http200(start_response, resp)
else:
for mi in matches:
matchInfoById[mi.get('matchId')] = mi
matchInfoByNumber[mi.get('number')] = mi
matchIds.append(mi.get('matchId'))
logger.info('matchIds: {}'.format(matchIds))
logger.info('matchInfoById: {}'.format(matchInfoById))
logger.info('matchInfoByNumber: {}'.format(matchInfoByNumber))
# 查询赛事状态
cursor.execute(
'SELECT `matchId`, `matchStatus`, `result` FROM `matchstatus` WHERE `matchId` IN %(matchIds)s AND `result` IS NOT NULL',
{'matchIds': matchIds})
matchStatuses = cursor.fetchall()
logger.info('matchStatuses: {}'.format(matchStatuses))
for ms in matchStatuses:
resultById[ms.get('matchId')] = ms.get('result').split(',')
matchIds.remove(ms.get('matchId'))
if matchIds:
cursor.execute(
'SELECT `matchId` FROM `matchstatus` WHERE `matchId` IN %(matchIds)s AND `matchStatus` = "Refund"',
{'matchIds': matchIds})
matchRefund = cursor.fetchall()
logger.info('matchRefund: {}'.format(matchRefund))
for mr in matchRefund:
refundIds.append(mr.get('matchId'))
matchIds.remove(mr.get('matchId'))
if len(matchIds) > 0:
resp = dict()
resp['status'] = 100
resp[
'message'] = 'The following matches are not finished: ' + ', '.join(
map(lambda x: matchInfoById.get(x).get('number'),
matchIds))
return http200(start_response, resp)
logger.info('resultById: {}'.format(resultById))
# 猜中比赛的赔率，计算奖金只需要这些
odds = []
for matchBetting in betting:
matchId = matchInfoByNumber.get(
matchBetting.get('matchNumber')).get('matchId')
if matchId in refundIds:
for b in matchBetting.get('bettingItems'):
odds.append("1")
continue
resultSet = set(matchBetting.get('bettingItems')) & set(
resultById.get(matchId))
if resultSet:
odds.append(getOdds(matchId, resultSet.pop(), bettingTime))
else:
odds.append("0")
logger.info('odds: {}'.format(odds))
# 终于开始算奖了，注意，算钱的时候一定要用 Decimal，除非都是整数
totalBonus = D(0)
for option in options:
if option == 'single':
for o in odds:
totalBonus += D(2) * D(o)
else:
m = int(option.split('x')[0])
ns = mxnOptions.get(option)
for comb0 in combinations(odds, m):
for n in ns:
for comb1 in combinations(comb0, n):
bonus = D(2)
for v in comb1:
bonus *= D(v)
bonus = roundBonus(bonus)
totalBonus += min(bonus, maxBonus.get(n))
totalBonus *= D(multiple)
resp = dict()
resp['status'] = 0
resp['bonus'] = format(totalBonus, '.2f')
return http200(start_response, resp)
except Exception as e:
logger.error(traceback.format_exc())
return http500(start_response)
finally:
logger.info('finally')
conn.close()
def handler(event, context):
forceUpdate = False
try:
evt = json.loads(event)
forceUpdate = evt.get('forceUpdate') == True
except Exception as e:
logger.error(e)
conn = db.getConnection()
try:
today = datetime.date.today().isoformat()
startTime = None
stopTime = None
# 查询售卖时间
with conn.cursor() as cursor:
cursor.execute(
'SELECT `startTime`, `stopTime` FROM `saletime` WHERE `date` = %s',
(today))
dbResult = cursor.fetchone()
if dbResult != None:
now = datetime.datetime.timestamp(
datetime.datetime.now()) * 1000
startTime = dbResult['startTime']
stopTime = dbResult['stopTime']
# 抓取赛事数据
url = 'https://evs.500.com/esinfo/lotinfo/lot_info?lotid=46&lottype=huntou&page=1&pagesize=100&webviewsource=touch&platform=touch'
req = requests.get(url)
resultText = req.text
h = hashlib.sha256()
h.update(resultText.encode())
sha256 = h.hexdigest()
logger.info('sha256: ' + sha256)
if not forceUpdate:
# 赛事数据没有更新的话直接结束
with conn.cursor() as cursor:
cursor.execute(
'SELECT `id` FROM `crawlerlog` WHERE `date` = %s AND `type` = %s AND `sha256` = %s',
(today, CRAWLER_LOG_TYPE, sha256))
dbResult = cursor.fetchone()
if dbResult != None:
logger.info('No updated data')
return 'Done'
result = json.loads(resultText)
if result.get('status') == '100' and result['data']:
matches = dict()
matchIds = list(
map(lambda x: int(x['mdata']['id']), result['data']))
with conn.cursor() as cursor:
cursor.execute(
'SELECT * FROM `matchinfo` WHERE `matchId` IN %s',
([matchIds]))
dbResult = cursor.fetchall()
if dbResult:
for m in dbResult:
matches[m['matchId']] = m
for value in result['data']:
tmpValue = value['mdata']
matchId = int(tmpValue['id'])
# 保存赛事基本数据
row = dict()
row['matchId'] = matchId
row['league'] = tmpValue['simpleleague']
row['leagueAbbr'] = tmpValue['simpleleague']
row['hostTeam'] = tmpValue['homesxname']
row['hostTeamAbbr'] = tmpValue['homesxname']
row['hostTeamOrder'] = tmpValue['homestanding']
row['visitingTeam'] = tmpValue['awaysxname']
row['visitingTeamAbbr'] = tmpValue['awaysxname']
row['visitingTeamOrder'] = tmpValue['awaystanding']
row['matchPeriod'] = tmpValue['matchdate']
row['number'] = tmpValue['matchnum']
row['500fid'] = tmpValue['fid']
row['saleStopTime'] = int(
datetime.datetime.timestamp(
datetime.datetime.strptime(
'{}:00 +0800'.format(tmpValue['endtime']),
'%Y-%m-%d %H:%M:%S %z')) * 1000)
with conn.cursor() as cursor:
if not matches.get(matchId):
fieldsStr = ', '.join(
map(lambda x: '`' + x + '`', row.keys()))
valuesStr = ', '.join([] + ['%s'] * len(row))
sql = 'INSERT INTO `matchinfo` (%s) VALUES (%s)' % (
fieldsStr, valuesStr)
cursor.execute(sql, tuple(row.values()))
else:
sql = 'UPDATE `matchinfo` SET `500fid` = %s WHERE `matchId` = %s'
cursor.execute(sql, (row['500fid'], row['matchId']))
# sql = 'UPDATE `matchinfo` SET `500fid` = %s, `saleStopTime` = %s WHERE `matchId` = %s'
# cursor.execute(sql, (row['500fid'], row['saleStopTime'], row['matchId']))
# 保存本次抓取日志
with conn.cursor() as cursor:
cursor.execute(
'INSERT INTO `crawlerlog` (`date`, `type`, `sha256`, `content`) VALUES (%s, %s, %s, %s)',
(today, CRAWLER_LOG_TYPE, sha256, resultText))
conn.commit()
return 'Done!'
finally:
conn.close()
def crawl(matchId, matchPeriod=None):
conn = db.getConnection()
now = floor(datetime.datetime.timestamp(datetime.datetime.now()) * 1000)
try:
# 抓取赛事基本信息
url = 'https://i.sporttery.cn/api/fb_match_info/get_match_info?mid=%s&_=%d' % (
matchId, now)
req = requests.get(url)
result = req.json()
if result.get('result'):
value = result.get('result')
if matchPeriod == None:
matchDay = weekdays[value['s_num'][1]]
logger.info(matchDay)
matchDateTime = datetime.datetime.strptime(
'%s %s +0800' % (value['date_cn'], value['time_cn']),
'%Y-%m-%d %H:%M:%S %z')
logger.info(matchDateTime)
matchPeriodDate = matchDateTime.date()
while matchPeriodDate.weekday() > matchDay:
matchPeriodDate -= datetime.timedelta(days=1)
matchPeriod = matchPeriodDate.isoformat()
row = dict()
row['matchId'] = matchId
row['league'] = value['l_cn']
row['leagueAbbr'] = value['l_cn_abbr']
row['leagueId'] = value['l_id_dc']
row['hostTeam'] = value['h_cn']
row['hostTeamAbbr'] = value['h_cn_abbr']
row['hostTeamId'] = value['h_id_dc']
row['hostTeamOrder'] = '[%s%s]' % (value['l_cn_abbr'],
value['table_h'])
row['visitingTeam'] = value['a_cn']
row['visitingTeamAbbr'] = value['a_cn_abbr']
row['visitingTeamId'] = value['a_id_dc']
row['visitingTeamOrder'] = '[%s%s]' % (value['l_cn_abbr'],
value['table_a'])
row['matchPeriod'] = matchPeriod
row['number'] = value['s_num']
row['saleStopTime'] = floor(
datetime.datetime.timestamp(matchDateTime) * 1000)
logger.info(row)
with conn.cursor() as cursor:
fieldsStr = ', '.join(map(lambda x: '`' + x + '`', row.keys()))
valuesStr = ', '.join([] + ['%s'] * len(row))
sql = 'INSERT IGNORE INTO `matchinfo` (%s) VALUES (%s)' % (
fieldsStr, valuesStr)
cursor.execute(sql, tuple(row.values()))
# 抓取赛事投注数据
url = 'https://i.sporttery.cn/api/fb_match_info/get_pool_rs/?mid=%s&_=%d' % (
matchId, now)
req = requests.get(url)
result = req.json()
if result.get('result'):
if result['result'].get('odds_list'):
if result['result']['odds_list'].get('had'):
value = result['result']['odds_list'].get('had')
row = dict()
row['matchId'] = matchId
row['isSingle'] = value['single']
for tmpValue in value['odds']:
row['releaseTime'] = floor(
datetime.datetime.timestamp(
datetime.datetime.strptime(
'%s %s +0800' %
(tmpValue['date'], tmpValue['time']),
'%Y-%m-%d %H:%M:%S %z')) * 1000)
row['win'] = tmpValue['h']
row['level'] = tmpValue['d']
row['lose'] = tmpValue['a']
logger.info(row)
with conn.cursor() as cursor:
fieldsStr = ', '.join(
map(lambda x: '`' + x + '`', row.keys()))
valuesStr = ', '.join([] + ['%s'] * len(row))
sql = 'INSERT IGNORE INTO `spfodds` (%s) VALUES (%s)' % (
fieldsStr, valuesStr)
cursor.execute(sql, tuple(row.values()))
if result['result']['odds_list'].get('hhad'):
value = result['result']['odds_list'].get('hhad')
row = dict()
row['matchId'] = matchId
row['isLetSingle'] = value['single']
row['letCount'] = value['goalline']
for tmpValue in value['odds']:
row['releaseTime'] = floor(
datetime.datetime.timestamp(
datetime.datetime.strptime(
'%s %s +0800' %
(tmpValue['date'], tmpValue['time']),
'%Y-%m-%d %H:%M:%S %z')) * 1000)
row['letWin'] = tmpValue['h']
row['letLevel'] = tmpValue['d']
row['letLose'] = tmpValue['a']
logger.info(row)
with conn.cursor() as cursor:
fieldsStr = ', '.join(
map(lambda x: '`' + x + '`', row.keys()))
valuesStr = ', '.join([] + ['%s'] * len(row))
sql = 'INSERT IGNORE INTO `rqspfodds` (%s) VALUES (%s)' % (
fieldsStr, valuesStr)
cursor.execute(sql, tuple(row.values()))
if result['result']['odds_list'].get('crs'):
value = result['result']['odds_list'].get('crs')
row = dict()
row['matchId'] = matchId
for tmpValue in value['odds']:
row['releaseTime'] = floor(
datetime.datetime.timestamp(
datetime.datetime.strptime(
'%s %s +0800' %
(tmpValue['date'], tmpValue['time']),
'%Y-%m-%d %H:%M:%S %z')) * 1000)
row['zeroToZero'] = tmpValue['0000']
row['zeroToOne'] = tmpValue['0001']
row['zeroToTwo'] = tmpValue['0002']
row['zeroToThree'] = tmpValue['0003']
row['zeroToFour'] = tmpValue['0004']
row['zeroToFive'] = tmpValue['0005']
row['oneToZero'] = tmpValue['0100']
row['oneToOne'] = tmpValue['0101']
row['oneToTwo'] = tmpValue['0102']
row['oneToThree'] = tmpValue['0103']
row['oneToFour'] = tmpValue['0104']
row['oneToFive'] = tmpValue['0105']
row['twoToZero'] = tmpValue['0200']
row['twoToOne'] = tmpValue['0201']
row['twoToTwo'] = tmpValue['0202']
row['twoToThree'] = tmpValue['0203']
row['twoToFour'] = tmpValue['0204']
row['twoToFive'] = tmpValue['0205']
row['threeToZero'] = tmpValue['0300']
row['threeToOne'] = tmpValue['0301']
row['threeToTwo'] = tmpValue['0302']
row['threeToThree'] = tmpValue['0303']
row['fourToZero'] = tmpValue['0400']
row['fourToOne'] = tmpValue['0401']
row['fourToTwo'] = tmpValue['0402']
row['fiveToZero'] = tmpValue['0500']
row['fiveToOne'] = tmpValue['0501']
row['fiveToTwo'] = tmpValue['0502']
row['winOther'] = tmpValue['-1-h']
row['levelOther'] = tmpValue['-1-d']
row['loseOther'] = tmpValue['-1-a']
logger.info(row)
with conn.cursor() as cursor:
fieldsStr = ', '.join(
map(lambda x: '`' + x + '`', row.keys()))
valuesStr = ', '.join([] + ['%s'] * len(row))
sql = 'INSERT IGNORE INTO `bfodds` (%s) VALUES (%s)' % (
fieldsStr, valuesStr)
cursor.execute(sql, tuple(row.values()))
if result['result']['odds_list'].get('hafu'):
value = result['result']['odds_list'].get('hafu')
row = dict()
row['matchId'] = matchId
for tmpValue in value['odds']:
row['releaseTime'] = floor(
datetime.datetime.timestamp(
datetime.datetime.strptime(
'%s %s +0800' %
(tmpValue['date'], tmpValue['time']),
'%Y-%m-%d %H:%M:%S %z')) * 1000)
row['winWin'] = tmpValue['hh']
row['winLevel'] = tmpValue['hd']
row['winLose'] = tmpValue['ha']
row['levelWin'] = tmpValue['dh']
row['levelLevel'] = tmpValue['dd']
row['levelLose'] = tmpValue['da']
row['loseWin'] = tmpValue['ah']
row['loseLevel'] = tmpValue['ad']
row['loseLose'] = tmpValue['aa']
logger.info(row)
with conn.cursor() as cursor:
fieldsStr = ', '.join(
map(lambda x: '`' + x + '`', row.keys()))
valuesStr = ', '.join([] + ['%s'] * len(row))
sql = 'INSERT IGNORE INTO `bqcodds` (%s) VALUES (%s)' % (
fieldsStr, valuesStr)
cursor.execute(sql, tuple(row.values()))
if result['result']['odds_list'].get('ttg'):
value = result['result']['odds_list'].get('ttg')
row = dict()
row['matchId'] = matchId
for tmpValue in value['odds']:
row['releaseTime'] = floor(
datetime.datetime.timestamp(
datetime.datetime.strptime(
'%s %s +0800' %
(tmpValue['date'], tmpValue['time']),
'%Y-%m-%d %H:%M:%S %z')) * 1000)
row['zero'] = tmpValue['s0']
row['one'] = tmpValue['s1']
row['two'] = tmpValue['s2']
row['three'] = tmpValue['s3']
row['four'] = tmpValue['s4']
row['five'] = tmpValue['s5']
row['six'] = tmpValue['s6']
row['seven'] = tmpValue['s7']
logger.info(row)
with conn.cursor() as cursor:
fieldsStr = ', '.join(
map(lambda x: '`' + x + '`', row.keys()))
valuesStr = ', '.join([] + ['%s'] * len(row))
sql = 'INSERT IGNORE INTO `zjqodds` (%s) VALUES (%s)' % (
fieldsStr, valuesStr)
cursor.execute(sql, tuple(row.values()))
conn.commit()
return 'Done!'
except Exception as e:
logger.error(e)
finally:
logger.info('finally')
conn.close()
def handler(environ, start_response):
conn = db.getConnection()
now = floor(datetime.datetime.timestamp(datetime.datetime.now()) * 1000)
day90startTime = now - 86400000 * 90
day30startTime = now - 86400000 * 30
result = dict()
tplFile = open("trendImage.tpl", "r")
html = tplFile.read()
try:
matchData = []
sql = """
WITH tmptable AS (
SELECT matchinfo.matchId, matchInfo.matchPeriod, matchinfo.saleStopTime, matchinfo.number, matchinfo.hostTeamAbbr AS hostTeam, matchinfo.visitingTeamAbbr as visitingTeam, matchinfo.half, matchinfo.final, win, level, lose, 0 as letCount, releaseTime
FROM spfodds JOIN matchinfo ON matchinfo.matchId = spfodds.matchId
WHERE isSingle = '1' AND (matchinfo.matchStatus IN ('Final', 'Close') OR matchinfo.matchStatus IS NULL) AND saleStopTime > %s
UNION ALL
SELECT matchinfo.matchId, matchInfo.matchPeriod, matchinfo.saleStopTime, matchinfo.number, matchinfo.hostTeamAbbr , matchinfo.visitingTeamAbbr, matchinfo.half, matchinfo.final, letWin, letLevel, letLose, letCount, releaseTime
FROM rqspfodds JOIN matchinfo ON matchinfo.matchId = rqspfodds.matchId
WHERE isLetSingle = '1' AND (matchinfo.matchStatus IN ('Final', 'Close') OR matchinfo.matchStatus IS NULL) AND saleStopTime > %s
)
SELECT * FROM tmptable ORDER BY saleStopTime ASC, releaseTime DESC;
"""
with conn.cursor() as cursor:
cursor.execute(sql, (day90startTime, day90startTime))
matchData = cursor.fetchall()
alldata = preProcessData(matchData)
miss = calcMiss(alldata)
data = filter30day(alldata, day30startTime)
result = dict({'miss': miss, 'data': data})
contentTableHtml = """
<table>
<thead>
<tr>
<th class="date" rowspan="2">日期</th>
<th class="weekday" rowspan="2">周</th>
<th class="match" rowspan="2">比赛</th>
<th class="spf" rowspan="2">胜</th>
<th class="spf" rowspan="2">平</th>
<th class="spf" rowspan="2">负</th>
<th class="odds" colspan="3">赔率</th>
<th class="total" colspan="3">总进球</th>
<th class="half" rowspan="2">半全场</th>
</tr>
<tr>
<th class="odds">低赔</th>
<th class="odds">中赔</th>
<th class="odds">高赔</th>
<th class="total">0-1</th>
<th class="total">2-3</th>
<th class="total">4-5</th>
</tr>
</thead>
<tbody>
"""
for v in data:
contentTableHtml += '<tr>'
contentTableHtml += '<td class="date">%s</td>' % (v['date'])
contentTableHtml += '<td class="weekday">%s</td>' % (v['weekday'])
hostTeam = v['hostTeam']
if v['letCount'] != '0':
hostTeam = '(' + v['letCount'] + ')' + hostTeam
if v.get('finalScore'):
contentTableHtml += '<td class="match">%s<span class="score">%s</span>%s</td>' % (
hostTeam, v['finalScore'], v['visitingTeam'])
contentTableHtml += '<td class="spf %s">%s</td>' % (
'active-win' if v['letResult'] == 1 else '', v['winOdds'])
contentTableHtml += '<td class="spf %s">%s</td>' % (
'active-level' if v['letResult'] == 0 else '',
v['levelOdds'])
contentTableHtml += '<td class="spf %s">%s</td>' % (
'active-lose' if v['letResult'] == -1 else '',
v['loseOdds'])
contentTableHtml += '<td class="odds %s">%s</td>' % (
'active-low' if v['miss']['low'] == 0 else '',
'低' if v['miss']['low'] == 0 else v['miss']['low'])
contentTableHtml += '<td class="odds %s">%s</td>' % (
'active-middle' if v['miss']['middle'] == 0 else '',
'中' if v['miss']['middle'] == 0 else v['miss']['middle'])
contentTableHtml += '<td class="odds %s">%s</td>' % (
'active-high' if v['miss']['high'] == 0 else '',
'高' if v['miss']['high'] == 0 else v['miss']['high'])
contentTableHtml += '<td class="total %s">%s</td>' % (
'active-low' if v['miss']['zero'] == 0 else '',
'小' if v['miss']['zero'] == 0 else v['miss']['zero'])
contentTableHtml += '<td class="total %s">%s</td>' % (
'active-middle' if v['miss']['two'] == 0 else '',
'中' if v['miss']['two'] == 0 else v['miss']['two'])
contentTableHtml += '<td class="total %s">%s</td>' % (
'active-high' if v['miss']['four'] == 0 else '',
'大' if v['miss']['four'] == 0 else v['miss']['four'])
contentTableHtml += '<td class="half">%s</td>' % (
resultToChinese.get(v['halfResult']) +
resultToChinese.get(v['result']))
else:
contentTableHtml += '<td class="match">%s VS %s</td>' % (
hostTeam, v['visitingTeam'])
contentTableHtml += '<td class="spf">%s</td>' % (v['winOdds'])
contentTableHtml += '<td class="spf">%s</td>' % (
v['levelOdds'])
contentTableHtml += '<td class="spf">%s</td>' % (v['loseOdds'])
contentTableHtml += '<td class="odds">\</td>'
contentTableHtml += '<td class="odds">\</td>'
contentTableHtml += '<td class="odds">\</td>'
contentTableHtml += '<td class="total">\</td>'
contentTableHtml += '<td class="total">\</td>'
contentTableHtml += '<td class="total">\</td>'
contentTableHtml += '<td class="half">\</td>'
contentTableHtml += '<tr>'
contentTableHtml += "</tbody></table>"
missTableHtml = """
<table>
<tr>
<td rowspan="2">最高遗漏次数<br/><span class="comment">（近3个月）</span></span></td>
<td>低赔</td>
<td>中赔</td>
<td>高赔</td>
<td>0-1球</td>
<td>2-3球</td>
<td>4-5球</td>
</tr>
<tr>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
</tr>
</table>
""" % (miss['low'], miss['middle'], miss['high'], miss['zero'],
miss['two'], miss['four'])
html = html.replace('//CONTENT_TABLE//', contentTableHtml)
html = html.replace('//MISS_TABLE//', missTableHtml)
finally:
conn.close()
status = '200 OK'
# response_headers = [('Content-type', 'application/json')]
response_headers = [('Content-type', 'text/html')]
start_response(status, response_headers)
# return [json.dumps(result).encode()]
return [html.encode('utf-8')]
def crawl(date):
conn = db.getConnection()
now = floor(datetime.datetime.timestamp(datetime.datetime.now()) * 1000)
url = 'https://i.sporttery.cn/wap/fb_match_list/get_fb_match_result/?format=json&date=%s&_=%d' % (
date, now)
try:
# 抓取赛事数据
req = requests.get(url)
resultText = req.text
h = hashlib.sha256()
h.update(resultText.encode())
sha256 = h.hexdigest()
matchInfoMap = dict()
matchStatusMap = dict()
result = json.loads(resultText)
matchIds = list(map(lambda x: x['id'], result['data']['result']))
updated = False
# 赛事数据没有更新的话直接结束
with conn.cursor() as cursor:
cursor.execute('SELECT * FROM `matchinfo` WHERE `matchId` IN %s',
([matchIds]))
dbResult = cursor.fetchall()
if dbResult:
for m in dbResult:
matchInfoMap[int(m['matchId'])] = m
cursor.execute('SELECT * FROM `matchstatus` WHERE `matchId` IN %s',
([matchIds]))
dbResult = cursor.fetchall()
if dbResult:
for m in dbResult:
matchStatusMap[int(m['matchId'])] = m
if result['data']:
if result['data']['result']:
for value in result['data']['result']:
logger.info(value)
# 保存赛事基本数据
row = dict()
matchId = int(value['id'])
matchStatus = value['match_status']
half = value['half']
final = value['final']
# 仅当抓取到的状态与库中状态不一致时更新
if matchInfoMap.get(matchId) and matchInfoMap[matchId][
'matchStatus'] != matchStatus:
updated = True
matchDetailCrawler.crawl(matchId)
with conn.cursor() as cursor:
sql = 'UPDATE `matchinfo` SET matchStatus = %s, half = %s, final = %s WHERE matchId = %s'
logger.info((matchStatus, half, final, matchId))
cursor.execute(sql,
(matchStatus, half, final, matchId))
# 如果赛事已开奖，但赛事比分状态仍然不是已结束，则去更新赛事的比分状态
if matchStatus in ('Final', 'Define') and not (
matchStatusMap.get(matchId)
and matchStatusMap[matchId]['matchStatus']
in ('Played', 'Refund')):
updateMatchStatusToPlayed(
matchId, final, half, value['fixedodds'],
conn)
if updated:
# 保存本次抓取日志
with conn.cursor() as cursor:
cursor.execute(
'INSERT INTO `crawlerlog` (`date`, `type`, `sha256`, `content`) VALUES (%s, %s, %s, %s)',
(date, CRAWLER_LOG_TYPE, sha256, resultText))
conn.commit()
return 'Done!'
except Exception as e:
logger.error(e)
finally:
conn.close()
if node.transitions[x][1] > m:
m = node.transitions[x][1]
mi2 = mi
mi = x
if m != -1:
traverse(node.transitions[mi][0], indent + 1, tested,
node.transitions[mi][1])
if mi2 != -1:
traverse(node.transitions[mi2][0], indent + 1, tested,
node.transitions[mi2][1])
#for x in node.transitions:
# traverse(node.transitions[x][0],indent+1,tested,node.transitions[x][1])
conn = db.getConnection()
r = db.loadDataFromDB(conn, query="id > 100000", orderBy="id", limit=25000)
print('load')
model = ModelBuilder.ModelBuilder([tests, results])
nodes = {}
i = 0
for x in r.rows:
model.addData(x)
i += 1
if i % 250 == 0:
print(i)
#print("Markov done")
model.calculateProbobilities()
fullRules, partRules = model.buildRules()
print(len(fullRules), len(partRules))
from flask import *
from flask.ext.sqlalchemy import SQLAlchemy
from flask.ext.login import LoginManager
from forms import RegistrationForm
import db
from users import User
from secret import HOSTNAME , USERNAME, PASSWORD, DATABASE, SESSION_KEY
from register import makePassword
app = Flask(__name__)
con = db.getConnection(HOSTNAME,USERNAME,PASSWORD,DATABASE)
app.secret_key = SESSION_KEY
login_manager = LoginManager()
login_manager.init_app(app)
db = SQLAlchemy(app)
@login_manager.user_loader
def load_user(id):
return User.query.get(int(id))
@app.route('/')
def index():
return render_template('index.html')
@app.route('/login')
def login():
return render_template('login.html')
def crawl(date):
conn = db.getConnection()
url = 'https://i.sporttery.cn/api/match_live_2/get_match_list'
try:
# 抓取赛事数据
req = requests.get(url)
resultText = req.text
resultText = resultText.split(' ')[-1][:-1]
sha256 = sha256Sum(resultText)
# 赛事数据没有更新的话直接结束
with conn.cursor() as cursor:
cursor.execute(
'SELECT `id` FROM `crawlerlog` WHERE `date` = %s AND `type` = %s AND `sha256` = %s',
(date, CRAWLER_LOG_TYPE, sha256))
dbResult = cursor.fetchone()
if dbResult != None:
logger.info('No updated data')
return 'Done'
result = json.loads(resultText)
if len(result.keys()) > 0:
for value in result.values():
logger.info(value)
# 保存赛事基本数据
row = dict()
row['matchId'] = value['m_id']
row['matchStatus'] = value['status']
if (value['fs_h']):
row['hostFinalScore'] = value['fs_h']
if (value['fs_a']):
row['visitingFinalScore'] = value['fs_a']
if (value['hts_h']):
row['hostHalfScore'] = value['hts_h']
if (value['hts_a']):
row['visitingHalfScore'] = value['hts_a']
if value['status'] == 'Played':
row['result'] = ','.join(calcResult(value))
# matchDetailCrawler.crawl(matchId)
with conn.cursor() as cursor:
fieldsStr = ', '.join(
map(lambda x: '`' + x + '`', row.keys()))
updateStr = ', '.join(
map(lambda x: '`' + x + '`=VALUES(`' + x + '`)',
row.keys()))
valuesStr = ', '.join([] + ['%s'] * len(row))
sql = 'INSERT INTO `matchstatus` (%s) VALUES (%s) ON DUPLICATE KEY UPDATE %s' % (
fieldsStr, valuesStr, updateStr)
logger.info('sql: ' + sql)
cursor.execute(sql, tuple(row.values()))
# 保存本次抓取日志
with conn.cursor() as cursor:
cursor.execute(
'INSERT INTO `crawlerlog` (`date`, `type`, `sha256`, `content`) VALUES (%s, %s, %s, %s)',
(date, CRAWLER_LOG_TYPE, sha256, resultText))
conn.commit()
return 'Done!'
except Exception as e:
logger.error(e)
finally:
conn.close()
def create_opname():
conn = db.getConnection()
cur = conn.cursor()
all_operations = '''
Exchange| EXCHANGE
Exchange| Exchange
Exchange| MERGING-EXCHANGE
Exchange| PX BLOCK ITERATOR
Exchange| PX COORDINATOR
Exchange| PX RECEIVE
Exchange| PX SEND BROADCAST
Exchange| PX SEND QC (RANDOM)
FilterProject| ExprEval
FilterProject| FILTER
FilterProject| Filter
FilterProject| VIEW
FilterProject| SELECT STATEMENT
FilterProject| TopK
FilterProject| TOP-N
GroupBy| AGGREGATE
GroupBy| BUFFER SORT
GroupBy| GROUPBY
GroupBy| GroupBy
GroupBy| GroupByHash
GroupBy| GroupByPipe
GroupBy| HASH GROUP BY
GroupBy| HASH JOIN, EXCHANGE, AGGREGATE, EXCHANGE, AGGREGATE, SCAN HDFS
GroupBy| PX SEND HASH
GroupBy| PX SEND HYBRID HASH
GroupBy| SORT AGGREGATE
GroupBy| SORT GROUP BY STOPKEY
GroupBy| STATISTICS COLLECTOR
GroupBy| Sort
Join| CROSS JOIN, EXCHANGE, SCAN HDFS
Join| HASH JOIN
Join| HASH JOIN ANTI
Join| HASH JOIN BUFFERED
Join| HASH JOIN RIGHT SEMI BUFFERED
Join| HASH JOIN SEMI BUFFERED
Join| HASH JOIN, AGGREGATE, EXCHANGE, AGGREGATE, SCAN HDFS
Join| HASH JOIN, EXCHANGE, CROSS JOIN
Join| HASH JOIN, EXCHANGE, SCAN HDFS
Join| JOIN
Join| JOIN FILTER CREATE
Join| JOIN FILTER USE
Join| Join
Join| MERGE JOIN CARTESIAN
Join| ParallelUnion
Planning| NewEENode
Planning| Pre-Execution
Planning| Root
Planning| WINDOW BUFFER
TableScan| COUNT STOPKEY
TableScan| INDEX UNIQUE SCAN
TableScan| NESTED LOOPS
TableScan| SCAN HDFS
TableScan| SCAN HDFS, SCAN HDFS
TableScan| Scan
TableScan| TABLE ACCESS BY INDEX ROWID
TableScan| TABLE ACCESS INMEMORY FULL
TableScan| TableScan
'''
olist = []
for o in all_operations.split('\n'):
x = o.split('|')
if len(x) == 2:
olist.append((x[0], x[1].strip()))
cur.execute("DELETE FROM operator_names;")
conn.commit()
for op in olist:
print "Inserting: %s, %s" % ((op))
qry = "INSERT INTO operator_names VALUES ('%s', '%s');" % ((op))
cur.execute(qry)
conn.commit()
def post(self):
#if the user is not logined in return
if not users.get_current_user():
return
self.response.headers['Content-Type'] = 'application/json'
self.response.headers['Access-Control-Allow-Origin'] = '*'
id=self.request.get('id')
jsonStringRequest=self.request.body
request_dictionary=db.fromJsonString(jsonStringRequest)[0]
active=request_dictionary.get('active')
status=request_dictionary.get('status')
(conn,cursor)=db.getConnection()
# check if the user is a moderator
if db.isModerator(cursor,users.get_current_user().nickname())==False :
return
# set all records in fiscal_data_log as not active if this is activation
if active == 'yes':
cursor.execute("UPDATE fiscal_data_log SET active='no' WHERE id=%s " % id)
#add verification_date
# update record in the _log table
(id_value,updateQuery)=db.buildUpdateQuery('fiscal_data_log','log_id','verified_by',users.get_current_user().nickname(), jsonStringRequest)
cursor.execute(updateQuery)
# fetch saved record
json_updated_log_record=db.executeSelect( cursor ,"SELECT * FROM fiscal_data_log WHERE log_id=%s" % id_value)
list=db.fromJsonString(json_updated_log_record)
updated_log_dictionary=list[0]
# update fiscal_data
if active=='yes':
#delete redundant fields
if 'log_id' in updated_log_dictionary: del updated_log_dictionary['log_id']
if 'verified_by' in updated_log_dictionary: del updated_log_dictionary['verified_by']
if 'verification_date' in updated_log_dictionary: del updated_log_dictionary['verification_date']
if 'active' in updated_log_dictionary: del updated_log_dictionary['active']
#!!!!!! set action to switch off update trigger on fiscal_data
updated_log_dictionary['action']='verify'
#make json string
s=json.dumps(list)
# build query
(id_val,fiscal_data_updateQuery)=db.buildUpdateQuery('fiscal_data','id',None,None, s)
logging.info( fiscal_data_updateQuery)
cursor.execute(fiscal_data_updateQuery)
else:
# it is active (present in fiscal_data table)
# and status changed then update fiscal_data
if updated_log_dictionary.get("active")=='yes' and status is not None:
#update record in fiscal_data
cursor.execute("UPDATE fiscal_data SET action='verify', status='%s' WHERE id=%s" % (status,id) )
# extract iso from fiscal_data_log record
dict=db.fromJsonString(json_updated_log_record)[0]
iso=dict['iso']
#refresh mem cache
memcache.delete('iso_'+iso)
jsonString=db.executeSelect(cursor,"SELECT * FROM fiscal_data WHERE iso=\"%s\"" % iso)
if len (jsonString)<990000:
memcache.add('iso_'+iso, jsonString)
# should write ??
# {"success":true,"message":"Updated User 2","data":{"id":2,"first":"Wilma","last":"Flintstone","email":"*****@*****.**"}}
self.response.out.write( json_updated_log_record )
conn.close()
'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/39.0.2171.71 Safari/537.36'
}
req = urllib2.Request(url = reqUrlStr, headers = reqHeaders)
res = urllib2.urlopen(req)
trueUrl = res.geturl()
head = res.info()
body = res.read()
md5Str = hashlib.md5(body).hexdigest()
jsonObj = json.loads(body)
print 'md5Str: ', md5Str
# 链接数据库，只是为了隐藏： MySQLdb.connect(host = 'localhost', port = 3306, user = '******', passwd = 'root', db = 'liwushuo')
conn = db.getConnection('liwushuo')
cur = conn.cursor()
# 判断当前获取的页面是否已经处理过
sqlStr = 'select count(*) as cnt from liwushuo_site_md5 where md5=%s'
retNum = cur.execute(sqlStr, (md5Str, ))
ret = cur.fetchmany(retNum)
isChanged = True if retNum > 0 and ret[0][0] < 1 else False
print 'isChanged: ', isChanged
if isChanged == True:
# 记录当前获取的页面的md5，用来后续判断
sqlStr = 'insert into liwushuo_site_md5 (md5) values (%s);'
cur.execute(sqlStr, (md5Str, ))
items = []
def writeResults( ): """Write the results of our web crawling to our database.""" con = db.getConnection( host, user, password, dbName ) print db.checkIfArtistExists( con, "testAratiast")
def handler(environ, start_response):
try:
request_body_size = int(environ.get('CONTENT_LENGTH', 0))
except (ValueError):
request_body_size = 0
request_body = environ['wsgi.input'].read(request_body_size)
params = json.loads(request_body)
if not params.get('tickets'):
return http400(start_response)
result = []
matchNumbers = []
matchIds = []
matchInfoById = dict()
matchInfoByNumber = dict()
oddsById = dict()
bettingTime = 0
for ticket in params.get('tickets'):
bettingTime = ticket.get('bettingTime')
betting = ticket.get('betting')
for matchBetting in betting:
matchNumbers.append(matchBetting.get('matchNumber'))
conn = db.getConnection()
try:
# 根据 matchNumber 查询赛事，查询范围为自投注时间起 4 天内
with conn.cursor() as cursor:
# 查询赛事基本信息
cursor.execute(
'SELECT `matchId`, `number`, `saleStopTime` FROM `matchinfo` WHERE `saleStopTime` BETWEEN %s AND %s AND `number` IN %s',
(bettingTime, bettingTime + 86400000 * 4, matchNumbers))
matches = cursor.fetchall()
if len(matches) < len(matchNumbers):
for mi in matches:
matchNumbers.remove(mi.get('number'))
resp = dict()
resp['status'] = 101
resp['message'] = 'Invalid matchNumbers: ' + ', '.join(
matchNumbers)
return http200(start_response, resp)
else:
for mi in matches:
matchInfoById[mi.get('matchId')] = mi
matchInfoByNumber[mi.get('number')] = mi
matchIds.append(mi.get('matchId'))
logger.info('matchIds: {}'.format(pf(matchIds)))
logger.info('matchInfoById: {}'.format(pf(matchInfoById)))
# 查询胜平负赔率
sql = 'SELECT `matchId`, `win`, `level`, `lose`, `releaseTime` FROM `spfodds` WHERE `matchId` IN %s AND `releaseTime` < %s ORDER BY `releaseTime` DESC'
cursor.execute(sql, (matchIds, bettingTime))
oddsList = cursor.fetchall()
for odds in oddsList:
if not oddsById.get(odds['matchId']):
matchOdds = dict()
else:
matchOdds = oddsById.get(odds['matchId'])
if not matchOdds.get('win'):
matchOdds = {**matchOdds, **odds}
oddsById[matchOdds['matchId']] = matchOdds
# 查询让球胜平负赔率
sql = 'SELECT `matchId`, `letWin`, `letLevel`, `letLose`, `releaseTime` FROM `rqspfodds` WHERE `matchId` IN %s AND `releaseTime` < %s ORDER BY `releaseTime` DESC'
cursor.execute(sql, (matchIds, bettingTime))
oddsList = cursor.fetchall()
for odds in oddsList:
if not oddsById.get(odds['matchId']):
matchOdds = dict()
else:
matchOdds = oddsById.get(odds['matchId'])
if not matchOdds.get('letWin'):
matchOdds = {**matchOdds, **odds}
oddsById[matchOdds['matchId']] = matchOdds
# 查询比分赔率
sql = 'SELECT `matchId`, `zeroToZero`, `zeroToOne`, `zeroToTwo`, `zeroToThree`, `zeroToFour`, `zeroToFive`, `oneToZero`, `oneToOne`, `oneToTwo`, `oneToThree`, `oneToFour`, `oneToFive`, `twoToZero`, `twoToOne`, `twoToTwo`, `twoToThree`, `twoToFour`, `twoToFive`, `threeToZero`, `threeToOne`, `threeToTwo`, `threeToThree`, `fourToZero`, `fourToOne`, `fourToTwo`, `fiveToZero`, `fiveToOne`, `fiveToTwo`, `winOther`, `levelOther`, `loseOther`, `releaseTime` FROM `bfodds` WHERE `matchId` IN %s AND `releaseTime` < %s ORDER BY `releaseTime` DESC'
cursor.execute(sql, (matchIds, bettingTime))
oddsList = cursor.fetchall()
for odds in oddsList:
if not oddsById.get(odds['matchId']):
matchOdds = dict()
else:
matchOdds = oddsById.get(odds['matchId'])
if not matchOdds.get('zeroToZero'):
matchOdds = {**matchOdds, **odds}
oddsById[matchOdds['matchId']] = matchOdds
# 查询半全场赔率
sql = 'SELECT `matchId`, `winWin`, `winLevel`, `winLose`, `levelWin`, `levelLevel`, `levelLose`, `loseWin`, `loseLevel`, `loseLose`, `releaseTime` FROM `bqcodds` WHERE `matchId` IN %s AND `releaseTime` < %s ORDER BY `releaseTime` DESC'
cursor.execute(sql, (matchIds, bettingTime))
oddsList = cursor.fetchall()
for odds in oddsList:
if not oddsById.get(odds['matchId']):
matchOdds = dict()
else:
matchOdds = oddsById.get(odds['matchId'])
if not matchOdds.get('winWin'):
matchOdds = {**matchOdds, **odds}
oddsById[matchOdds['matchId']] = matchOdds
# 查询总进球赔率
sql = 'SELECT `matchId`, `zero`, `one`, `two`, `three`, `four`, `five`, `six`, `seven`, `releaseTime` FROM `zjqodds` WHERE `matchId` IN %s AND `releaseTime` < %s ORDER BY `releaseTime` DESC'
cursor.execute(sql, (matchIds, bettingTime))
oddsList = cursor.fetchall()
for odds in oddsList:
if not oddsById.get(odds['matchId']):
matchOdds = dict()
else:
matchOdds = oddsById.get(odds['matchId'])
if not matchOdds.get('zero'):
matchOdds = {**matchOdds, **odds}
oddsById[matchOdds['matchId']] = matchOdds
logger.info('oddsById: {}'.format(pf(oddsById)))
for ticket in params.get('tickets'):
tid = ticket.get('id')
options = ticket.get('options')
betting = ticket.get('betting')
multiple = ticket.get('multiple')
for option in options:
if not option in mxnOptions.keys():
return http400(start_response)
# 每场比赛所投注的最高赔率
odds = []
for matchBetting in betting:
matchId = matchInfoByNumber.get(
matchBetting.get('matchNumber')).get('matchId')
maxOdds = D("0")
for k in matchBetting.get('bettingItems'):
if D(oddsById[matchId][k]) > maxOdds:
maxOdds = D(oddsById[matchId][k])
odds.append(maxOdds)
logger.info('odds: {}'.format(pf(odds)))
# 终于开始算奖了，注意，算钱的时候一定要用 Decimal，除非都是整数
totalBonus = D(0)
for option in options:
if option == 'single':
for o in odds:
totalBonus += D(2) * D(o)
else:
m = int(option.split('x')[0])
ns = mxnOptions.get(option)
for comb0 in combinations(odds, m):
for n in ns:
for comb1 in combinations(comb0, n):
bonus = D(2)
for v in comb1:
bonus *= D(v)
bonus = roundBonus(bonus)
totalBonus += min(bonus, maxBonus.get(n))
totalBonus *= D(multiple)
result.append({
'id': tid,
'highestBonus': format(totalBonus, '.2f')
})
resp = {'status': 0, 'result': result}
return http200(start_response, resp)
except Exception as e:
logger.error(traceback.format_exc())
return http500(start_response)
finally:
logger.info('finally')
conn.close()
def handler(event, context):
forceUpdate = False
try:
evt = json.loads(event)
forceUpdate = evt.get('forceUpdate') == True
except Exception as e:
logger.error(e)
conn = db.getConnection()
try:
today = datetime.date.today().isoformat()
startTime = None
stopTime = None
# 查询售卖时间
with conn.cursor() as cursor:
cursor.execute(
'SELECT `startTime`, `stopTime` FROM `saletime` WHERE `date` = %s',
(today))
dbResult = cursor.fetchone()
if dbResult != None:
now = datetime.datetime.timestamp(
datetime.datetime.now()) * 1000
startTime = dbResult['startTime']
stopTime = dbResult['stopTime']
# 抓取赛事数据
url = 'https://i.sporttery.cn/odds_calculator/get_odds?i_format=json&poolcode[]=hhad&poolcode[]=had&poolcode[]=crs&poolcode[]=ttg&poolcode[]=hafu'
req = requests.get(url)
resultText = req.text
h = hashlib.sha256()
h.update(resultText.encode())
sha256 = h.hexdigest()
logger.info('sha256: ' + sha256)
if not forceUpdate:
# 赛事数据没有更新的话直接结束
with conn.cursor() as cursor:
cursor.execute(
'SELECT `id` FROM `crawlerlog` WHERE `date` = %s AND `type` = %s AND `sha256` = %s',
(today, CRAWLER_LOG_TYPE, sha256))
dbResult = cursor.fetchone()
if dbResult != None:
logger.info('No updated data')
return 'Done'
result = json.loads(resultText)
if result['data']:
for value in result['data'].values():
matchId = value['id']
# 保存赛事基本数据
row = dict()
row['matchId'] = matchId
row['league'] = value['l_cn']
row['leagueAbbr'] = value['l_cn_abbr']
row['leagueId'] = value['l_id']
row['hostTeam'] = value['h_cn']
row['hostTeamAbbr'] = value['h_cn_abbr']
row['hostTeamId'] = value['h_id']
row['hostTeamOrder'] = value['h_order']
row['visitingTeam'] = value['a_cn']
row['visitingTeamAbbr'] = value['a_cn_abbr']
row['visitingTeamId'] = value['a_id']
row['visitingTeamOrder'] = value['a_order']
row['matchPeriod'] = value['b_date']
row['number'] = value['num']
row['startTime'] = startTime
row['stopTime'] = stopTime
row['saleStopTime'] = floor(
datetime.datetime.timestamp(
datetime.datetime.strptime(
'%s %s +0800' % (value['date'], value['time']),
'%Y-%m-%d %H:%M:%S %z')) * 1000)
with conn.cursor() as cursor:
fieldsStr = ', '.join(
map(lambda x: '`' + x + '`', row.keys()))
valuesStr = ', '.join([] + ['%s'] * len(row))
sql = 'REPLACE INTO `matchinfo` (%s) VALUES (%s)' % (
fieldsStr, valuesStr)
cursor.execute(sql, tuple(row.values()))
# 保存赛事胜平负数据
if value.get('had'):
tmpValue = value['had']
row = dict()
row['matchId'] = matchId
# row['isSpf'] = tmpValue['p_status'] if tmpValue['allup'] != '0' else ''
row['isSpf'] = tmpValue['p_status']
row['isSingle'] = tmpValue['single']
row['win'] = tmpValue['h']
row['level'] = tmpValue['d']
row['lose'] = tmpValue['a']
with conn.cursor() as cursor:
fieldsStr = ', '.join(
map(lambda x: '`' + x + '`', row.keys()))
valuesStr = ', '.join([] + ['%s'] * len(row))
sql = 'REPLACE INTO `spf` (%s) VALUES (%s)' % (
fieldsStr, valuesStr)
cursor.execute(sql, tuple(row.values()))
# 保存赛事让球胜平负数据
if value.get('hhad'):
tmpValue = value['hhad']
row = dict()
row['matchId'] = matchId
# row['isRqspf'] = tmpValue['p_status'] if tmpValue['allup'] != '0' else ''
row['isRqspf'] = tmpValue['p_status']
row['isLetSingle'] = tmpValue['single']
row['letCount'] = tmpValue['fixedodds']
row['letWin'] = tmpValue['h']
row['letLevel'] = tmpValue['d']
row['letLose'] = tmpValue['a']
with conn.cursor() as cursor:
fieldsStr = ', '.join(
map(lambda x: '`' + x + '`', row.keys()))
valuesStr = ', '.join([] + ['%s'] * len(row))
sql = 'REPLACE INTO `rqspf` (%s) VALUES (%s)' % (
fieldsStr, valuesStr)
cursor.execute(sql, tuple(row.values()))
# 保存赛事比分数据
if value.get('crs'):
tmpValue = value['crs']
row = dict()
row['matchId'] = matchId
# row['isBf'] = tmpValue['p_status'] if tmpValue['allup'] != '0' else ''
row['isBf'] = tmpValue['p_status']
row['zeroToZero'] = tmpValue['0000']
row['zeroToOne'] = tmpValue['0001']
row['zeroToTwo'] = tmpValue['0002']
row['zeroToThree'] = tmpValue['0003']
row['zeroToFour'] = tmpValue['0004']
row['zeroToFive'] = tmpValue['0005']
row['oneToZero'] = tmpValue['0100']
row['oneToOne'] = tmpValue['0101']
row['oneToTwo'] = tmpValue['0102']
row['oneToThree'] = tmpValue['0103']
row['oneToFour'] = tmpValue['0104']
row['oneToFive'] = tmpValue['0105']
row['twoToZero'] = tmpValue['0200']
row['twoToOne'] = tmpValue['0201']
row['twoToTwo'] = tmpValue['0202']
row['twoToThree'] = tmpValue['0203']
row['twoToFour'] = tmpValue['0204']
row['twoToFive'] = tmpValue['0205']
row['threeToZero'] = tmpValue['0300']
row['threeToOne'] = tmpValue['0301']
row['threeToTwo'] = tmpValue['0302']
row['threeToThree'] = tmpValue['0303']
row['fourToZero'] = tmpValue['0400']
row['fourToOne'] = tmpValue['0401']
row['fourToTwo'] = tmpValue['0402']
row['fiveToZero'] = tmpValue['0500']
row['fiveToOne'] = tmpValue['0501']
row['fiveToTwo'] = tmpValue['0502']
row['winOther'] = tmpValue['-1-h']
row['levelOther'] = tmpValue['-1-d']
row['loseOther'] = tmpValue['-1-a']
with conn.cursor() as cursor:
fieldsStr = ', '.join(
map(lambda x: '`' + x + '`', row.keys()))
valuesStr = ', '.join([] + ['%s'] * len(row))
sql = 'REPLACE INTO `bf` (%s) VALUES (%s)' % (
fieldsStr, valuesStr)
cursor.execute(sql, tuple(row.values()))
# 保存赛事半全场数据
if value.get('hafu'):
tmpValue = value['hafu']
row = dict()
row['matchId'] = matchId
# row['isBqc'] = tmpValue['p_status'] if tmpValue['allup'] != '0' else ''
row['isBqc'] = tmpValue['p_status']
row['winWin'] = tmpValue['hh']
row['winLevel'] = tmpValue['hd']
row['winLose'] = tmpValue['ha']
row['levelWin'] = tmpValue['dh']
row['levelLevel'] = tmpValue['dd']
row['levelLose'] = tmpValue['da']
row['loseWin'] = tmpValue['ah']
row['loseLevel'] = tmpValue['ad']
row['loseLose'] = tmpValue['aa']
with conn.cursor() as cursor:
fieldsStr = ', '.join(
map(lambda x: '`' + x + '`', row.keys()))
valuesStr = ', '.join([] + ['%s'] * len(row))
sql = 'REPLACE INTO `bqc` (%s) VALUES (%s)' % (
fieldsStr, valuesStr)
cursor.execute(sql, tuple(row.values()))
# 保存赛事总比分数据
if value.get('ttg'):
tmpValue = value['ttg']
row = dict()
row['matchId'] = matchId
# row['isZjq'] = tmpValue['p_status'] if tmpValue['allup'] != '0' else ''
row['isZjq'] = tmpValue['p_status']
row['zero'] = tmpValue['s0']
row['one'] = tmpValue['s1']
row['two'] = tmpValue['s2']
row['three'] = tmpValue['s3']
row['four'] = tmpValue['s4']
row['five'] = tmpValue['s5']
row['six'] = tmpValue['s6']
row['seven'] = tmpValue['s7']
with conn.cursor() as cursor:
fieldsStr = ', '.join(
map(lambda x: '`' + x + '`', row.keys()))
valuesStr = ', '.join([] + ['%s'] * len(row))
sql = 'REPLACE INTO `zjq` (%s) VALUES (%s)' % (
fieldsStr, valuesStr)
cursor.execute(sql, tuple(row.values()))
# 保存本次抓取日志
with conn.cursor() as cursor:
cursor.execute(
'INSERT INTO `crawlerlog` (`date`, `type`, `sha256`, `content`) VALUES (%s, %s, %s, %s)',
(today, CRAWLER_LOG_TYPE, sha256, resultText))
conn.commit()
return 'Done!'
finally:
conn.close()
def getItems(category):
c = dbExecute(getConnection(), 'SELECT * FROM Items WHERE Type = ?',
(category, ))
items = c.fetchall()
parsed = {"results": {x[1]: x[2] for x in items}}
return jsonify(parsed)
def getFeeds():
conn = db.getConnection()
curs = conn.cursor()
sql = "select name, url, feed_id from feeds"
curs.execute(sql)
return [Feed(row[0], row[1], row[2]) for row in curs.fetchall()]
