Example #1
0
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']})
Example #2
0
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'
Example #3
0
 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
Example #4
0
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()
Example #5
0
File: rss.py Project: stephegn/rss
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]
Example #6
0
 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()
Example #7
0
File: rss.py Project: stephegn/rss
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]
Example #8
0
    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
Example #9
0
    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()
Example #11
0
    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")
Example #12
0
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)
Example #13
0
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})
Example #14
0
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
Example #15
0
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()]
    }))
Example #16
0
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
Example #17
0
    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()
Example #18
0
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()
Example #19
0
File: rss.py Project: stephegn/rss
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()
Example #20
0
File: rss.py Project: stephegn/rss
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()
Example #21
0
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)
Example #22
0
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())
Example #23
0
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()
Example #24
0
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'
Example #25
0
 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")
Example #26
0
 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()
Example #27
0
 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 "-"
Example #28
0
 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")
Example #29
0
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")
Example #30
0
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))
Example #33
0
 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()
Example #34
0
    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()
Example #35
0
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
Example #36
0
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()]
Example #37
0
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)
Example #38
0
    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()
Example #41
0
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()
Example #42
0
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')]
Example #43
0
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()
Example #44
0
        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))
Example #45
0
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')
Example #46
0
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()
Example #47
0
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()
Example #48
0
    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()
Example #49
0
        '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 = []
Example #50
0
def writeResults( ):
	"""Write the results of our web crawling to our database."""
	con = db.getConnection( host, user, password, dbName )

	print db.checkIfArtistExists( con, "testAratiast")
Example #51
0
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()
Example #52
0
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()
Example #53
0
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)
Example #54
0
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()]