API tools faq
paste
Guest User

Untitled

a guest
Mar 8th, 2018
41
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 29.29 KB | None | 0 0
raw download clone embed print report
  1. __author__ = 'Willow'
  2. import mysql.connector
  3. import csv
  4. import threading
  5.  
  6. class WDatabase:
  7.  
  8. scrapperCount = 0
  9.  
  10. def __init__(self):
  11. self.dbhost = '138.201.105.247'
  12. self.dbuser = 'willow'
  13. self.dbpass = 'ASDFqwer!@#$1234'
  14. self.dbname = 'oddmatcher'
  15.  
  16. self.dbhost = 'localhost'
  17. self.dbuser = 'root'
  18. self.dbpass = 'ASDFqwer!@#$1234'
  19. self.dbpass = ''
  20.  
  21. self.lock = threading.Lock()
  22. self.cnx_object = mysql.connector.connect(user=self.dbuser, password=self.dbpass, host=self.dbhost, database=self.dbname)
  23. self.updateBookmakerOddList = []
  24. self.updateExchangeOddList = []
  25. self.oddChunkSize = 500
  26.  
  27. self.bInsertEventList = []
  28. self.bUpdateEventList = []
  29.  
  30. self.eInsertEventList = []
  31. self.eUpdateEventList = []
  32. self.eventChunkSize = 100
  33.  
  34. def truncateTable(self):
  35. cursor = self.cnx_object.cursor(buffered=True)
  36. sql = "TRUNCATE TABLE bookmaker_event"
  37. cursor.execute(sql)
  38. sql = "TRUNCATE TABLE exchange_event"
  39. cursor.execute(sql)
  40. sql = "TRUNCATE TABLE match_log"
  41. cursor.execute(sql)
  42. sql = "TRUNCATE TABLE match_result"
  43. cursor.execute(sql)
  44. sql = "TRUNCATE TABLE adm_report"
  45. cursor.execute(sql)
  46. sql = "UPDATE adm_config SET last_b_id=0, last_e_id=0 WHERE id=1"
  47. cursor.execute(sql)
  48. self.cnx_object.commit()
  49. cursor.close()
  50.  
  51. def truncateEventTable(self, siteType, siteId):
  52. cursor = self.cnx_object.cursor(buffered=True)
  53. if siteType == 1:
  54. table = 'bookmaker_event'
  55. else:
  56. table = 'exchange_event'
  57. sql = "DELETE FROM "+table+" WHERE siteid = "+str(siteId)
  58. cursor.execute(sql)
  59. self.cnx_object.commit()
  60. cursor.close()
  61.  
  62. def truncateOddTable(self, siteType, siteId):
  63. cursor = self.cnx_object.cursor(buffered=True)
  64. if siteType == 1:
  65. sql = "DELETE FROM bookmaker_odd WHERE eventid IN (SELECT id FROM bookmaker_event WHERE siteid="+str(siteId)+")"
  66. else:
  67. sql = "DELETE FROM exchange_odd WHERE eventid IN (SELECT id FROM exchange_event WHERE siteid="+str(siteId)+")"
  68. cursor.execute(sql)
  69. self.cnx_object.commit()
  70. cursor.close()
  71.  
  72. def setUpdateFlag(self):
  73. try:
  74. cursor = self.cnx_object.cursor(buffered=True)
  75. sql ='UPDATE bookmaker_event SET update_flag=0 WHERE 1=1'
  76. cursor.execute(sql)
  77. sql ='UPDATE exchange_event SET update_flag=0 WHERE 1=1'
  78. cursor.execute(sql)
  79. self.cnx_object.commit()
  80. cursor.close()
  81. except Exception as e:
  82. print("ok")
  83.  
  84. def deleteRemovedEvents(self):
  85. try:
  86. cursor = self.cnx_object.cursor(buffered=True)
  87. sql = "DELETE FROM `match_result` WHERE bookmaker_event IN (SELECT id FROM bookmaker_event WHERE update_flag=0 )"
  88. cursor.execute(sql)
  89. self.cnx_object.commit()
  90.  
  91. sql = "DELETE FROM `match_result` WHERE exchange_event IN (SELECT id FROM exchange_event WHERE update_flag=0 )"
  92. cursor.execute(sql)
  93. self.cnx_object.commit()
  94.  
  95. sql = "DELETE FROM `match_log` WHERE bookmaker IN (SELECT id FROM bookmaker_event WHERE update_flag=0 )"
  96. cursor.execute(sql)
  97. self.cnx_object.commit()
  98.  
  99. sql = "DELETE FROM `match_log` WHERE exchange IN (SELECT id FROM exchange_event WHERE update_flag=0 )"
  100. cursor.execute(sql)
  101. self.cnx_object.commit()
  102.  
  103. sql = "DELETE FROM `exchange_event` WHERE update_flag=0"
  104. cursor.execute(sql)
  105. self.cnx_object.commit()
  106.  
  107. sql = "DELETE FROM `bookmaker_event` WHERE update_flag=0"
  108. cursor.execute(sql)
  109. self.cnx_object.commit()
  110. cursor.close()
  111. except Exception as e:
  112. self.log(0, "Database: deleteRemovedEvents >>exception " +str(e))
  113. cursor.close()
  114.  
  115. def deleteOldEvents(self):
  116. try:
  117. cursor = self.cnx_object.cursor(buffered=True)
  118. #sql = "DELETE FROM `match_result` WHERE match_result.datetime < NOW() - INTERVAL 8 HOUR"
  119. sql = "DELETE FROM `match_result` WHERE match_result.datetime < NOW()"
  120. sql = "DELETE FROM match_result WHERE id IN (SELECT * FROM (SELECT id FROM match_result WHERE match_result.datetime < NOW() order by id) AS X);"
  121. cursor.execute(sql)
  122. self.cnx_object.commit()
  123. sql = "DELETE FROM `exchange_event` WHERE exchange_event.datetime < NOW()"
  124. sql = "DELETE FROM exchange_event WHERE id IN (SELECT * FROM (SELECT id FROM exchange_event WHERE exchange_event.datetime < NOW() order by id) AS X)"
  125. cursor.execute(sql)
  126. self.cnx_object.commit()
  127. sql = "DELETE FROM `bookmaker_event` WHERE bookmaker_event.datetime < NOW()"
  128. sql = "DELETE FROM bookmaker_event WHERE id IN (SELECT * FROM (SELECT id FROM bookmaker_event WHERE bookmaker_event.datetime < NOW() order by id) AS X)"
  129. cursor.execute(sql)
  130. self.cnx_object.commit()
  131. sql = "DELETE FROM `match_log` WHERE match_log.datetime < NOW()"
  132. sql = "DELETE FROM match_log WHERE id IN (SELECT * FROM (SELECT id FROM match_log WHERE match_log.datetime < NOW() order by id) AS X) "
  133. cursor.execute(sql)
  134. self.cnx_object.commit()
  135. cursor.close()
  136. except Exception as e:
  137. self.log(0, "Database: deleteOldEvents >>exception " +str(e))
  138. cursor.close()
  139.  
  140.  
  141. def insertLeagueMatch(self, siteId, sportId, name, rowId=-1):
  142. '''
  143. cursor = self.cnx_object.cursor(buffered=True)
  144. sql = 'SELECT * FROM cfg_league_match WHERE s_id=%s AND league="%s" AND g_id=%s' % (siteId, name, sportId)
  145. cursor.execute(sql)
  146. if cursor.rowcount < 1:
  147. sql = 'INSERT INTO cfg_league_match(s_id, l_id, g_id, league) VALUES (%s, %s, %s, "%s")' % (siteId, rowId, sportId, name)
  148. cursor.execute(sql)
  149. self.cnx_object.commit()
  150. cursor.close()
  151. '''
  152. return
  153.  
  154. def insertLeague(self,sportId, name):
  155. row_no = -1
  156. cursor = self.cnx_object.cursor(buffered=True)
  157. sql = 'SELECT id FROM cfg_leagues WHERE league="%s" AND g_id=%s' % (name, sportId)
  158. cursor.execute(sql)
  159. if cursor.rowcount < 1:
  160. sql = ("INSERT INTO cfg_leagues"
  161. "(g_id, league)"
  162. "VALUES (%s, %s)")
  163. cursor.execute(sql , (sportId, name))
  164. self.cnx_object.commit()
  165. row_no = cursor.lastrowid
  166. else:
  167. row_no = cursor.fetchone()[0]
  168. return row_no
  169. cursor.close()
  170.  
  171. def insertEventChunkData(self, sitetype, insertData, updateData):
  172. try:
  173. cursor = self.cnx_object.cursor(buffered=True)
  174. if sitetype == 1:
  175. while True:
  176. try:
  177. if len(updateData) != 0:
  178. sql = "UPDATE bookmaker_event SET update_flag=%s WHERE datetime=%s AND name=%s AND siteid=%s AND gametype=%s"
  179. cursor.executemany(sql, updateData)
  180. if len(insertData) != 0:
  181. sql = ("INSERT INTO bookmaker_event (name, league, datetime, siteid, gametype, eventId, update_flag) VALUES (%s, %s, %s, %s, %s, %s, %s)")
  182. cursor.executemany(sql, insertData)
  183. break
  184. except Exception as e:
  185. self.log(1, "Exception insertEventChunkData " + str(e))
  186. continue
  187.  
  188. else:
  189. while True:
  190. try:
  191. if len(updateData) != 0:
  192. sql = "UPDATE exchange_event SET update_flag=%s WHERE datetime=%s AND name=%s AND siteid=%s AND gametype=%s"
  193. cursor.executemany(sql, updateData)
  194. if len(insertData) != 0:
  195. sql = ("INSERT INTO exchange_event (name, league, datetime, siteid, gametype, eventId, update_flag) VALUES (%s, %s, %s, %s, %s, %s, %s)")
  196. cursor.executemany(sql, insertData)
  197. break
  198. except Exception as e:
  199. self.log(1, "Exception insertEventChunkData " + str(e))
  200. continue
  201. self.cnx_object.commit()
  202. cursor.close()
  203. except Exception as e:
  204. self.log(0, "Database: insertOddChunkData >>exception " + str(e))
  205.  
  206. def insertEvent(self, name, league, datetime, siteid, gametype, sitetype, eventId):
  207. try:
  208. if str(league).lower().__contains__("fantasy"):
  209. return
  210. cursor = self.cnx_object.cursor(buffered=True)
  211. table = 'bookmaker_event'
  212. if sitetype == 1:
  213. table = 'bookmaker_event'
  214. else:
  215. table = 'exchange_event'
  216. sql = 'SELECT * FROM '+table+' WHERE datetime="'+str(datetime)+'" AND name="'+name+'" AND siteid = '+str(siteid) +' AND gametype = ' + str(gametype)
  217. cursor.execute(sql)
  218. if cursor.rowcount < 1:
  219. if sitetype == 1:
  220. self.bInsertEventList.append((name, league, datetime, siteid, gametype, eventId, 1))
  221. else:
  222. self.eInsertEventList.append((name, league, datetime, siteid, gametype, eventId, 1))
  223.  
  224. if len(self.bInsertEventList) >= self.eventChunkSize or len(self.bUpdateEventList) >= self.eventChunkSize:
  225. tempInsertData = self.bInsertEventList[:self.eventChunkSize]
  226. self.bInsertEventList= self.bInsertEventList[self.eventChunkSize:]
  227. tempUpdateData = self.bUpdateEventList[:self.eventChunkSize]
  228. self.bUpdateEventList = self.bUpdateEventList[self.eventChunkSize:]
  229. self.insertEventChunkData(sitetype, tempInsertData, tempUpdateData )
  230. else:
  231. if sitetype == 1:
  232. self.bUpdateEventList.append((1, datetime, name, siteid, gametype))
  233. else:
  234. self.eUpdateEventList.append((1, datetime, name, siteid, gametype))
  235.  
  236. if len(self.eInsertEventList) >= self.eventChunkSize or len(self.eUpdateEventList) >= self.eventChunkSize:
  237. tempInsertData = self.eInsertEventList[:self.eventChunkSize]
  238. self.eInsertEventList= self.eInsertEventList[self.eventChunkSize:]
  239. tempUpdateData = self.eUpdateEventList[:self.eventChunkSize]
  240. self.eUpdateEventList = self.eUpdateEventList[self.eventChunkSize:]
  241. self.insertEventChunkData(sitetype, tempInsertData , tempUpdateData )
  242.  
  243. cursor.close()
  244. except Exception as e:
  245. self.log(0, "Database: insertEvent >>exception " +str(e))
  246. return None
  247.  
  248. '''
  249. description: Under / Over 4.5
  250. name: : Under
  251. '''
  252. def insertOddChunkData(self, sitetype, data):
  253. try:
  254. cursor = self.cnx_object.cursor(buffered=True)
  255. if sitetype == 1:
  256. if len(data) != 0:
  257. while True:
  258. try:
  259. sql = "UPDATE match_result SET backodd_update=%s, backodd=%s WHERE bookmaker_event=%s AND runner=%s AND market=%s"
  260. cursor.executemany(sql, data)
  261. break
  262. except Exception as e:
  263. self.log(1, "Exception insertOddThread " + str(e))
  264. continue
  265.  
  266. else:
  267. if len(data) != 0:
  268. while True:
  269. try:
  270. sql = "UPDATE match_result SET layodd_update=%s, layodd=%s, liquidity=%s WHERE exchange_event=%s AND runner=%s AND market=%s"
  271. cursor.executemany(sql, data)
  272. break
  273. except Exception as e:
  274. self.log(1, "Exception insertOddThread" + str(e))
  275. continue
  276. self.cnx_object.commit()
  277. cursor.close()
  278. except Exception as e:
  279. self.log(0, "Database: insertOddChunkData >>exception " + str(e))
  280.  
  281.  
  282. def insertOdd(self, sitetype, eventid, oddtype, description, name, odd, liquidity = 0):
  283.  
  284. try:
  285. if sitetype == 1:
  286. self.updateBookmakerOddList.append((1, odd, eventid, name, oddtype))
  287. if(len(self.updateBookmakerOddList) >= self.oddChunkSize):
  288. tempBookmakerUpdateData = self.updateBookmakerOddList[:self.oddChunkSize]
  289. self.updateBookmakerOddList = self.updateBookmakerOddList[self.oddChunkSize:]
  290. self.insertOddChunkData(sitetype, tempBookmakerUpdateData)
  291. else:
  292. self.updateExchangeOddList.append((1, odd, liquidity, eventid, name, oddtype))
  293. if (len(self.updateExchangeOddList) >= self.oddChunkSize):
  294. tempExchangeUpdateData = self.updateExchangeOddList[:self.oddChunkSize]
  295. self.updateExchangeOddList = self.updateExchangeOddList[self.oddChunkSize:]
  296. self.insertOddChunkData(sitetype, tempExchangeUpdateData)
  297. except Exception as e:
  298. self.log(0, "Database: insertOdd >>exception " +str(e))
  299. return None
  300.  
  301.  
  302. def getSiteInfo(self, name):
  303. try:
  304. cursor = self.cnx_object.cursor(buffered=True)
  305. sql = "SELECT * FROM cfg_bookies WHERE link='"+name+"'"
  306. cursor.execute(sql)
  307. ret = cursor.fetchone()
  308. return ret
  309. except Exception as e:
  310. self.log(0, "Database: getSiteInfo >>exception " +str(e))
  311. return None
  312.  
  313. def getSports(self):
  314. try:
  315. cursor = self.cnx_object.cursor(buffered=True)
  316. sql = "SELECT * FROM cfg_games"
  317. cursor.execute(sql)
  318. ret = []
  319. results = cursor.fetchall()
  320. for row in results:
  321. sport = dict()
  322. sport[row[0]] = row
  323. ret.append(sport)
  324. return ret
  325. except Exception as e:
  326. self.log(0, "Database: getSports >>exception " +str(e))
  327. return None
  328.  
  329. def test(self):
  330. cursor = self.cnx_object.cursor(buffered=True)
  331. cursor.executemany("UPDATE bookmaker_event SET name=%s, league=%s WHERE siteid=%s", [("test", "test", 5), ("test", "test", 13), ("test", "test", 4)])
  332. self.cnx_object.commit()
  333. cursor.close()
  334.  
  335. def getOdds(self, siteid):
  336. try:
  337. cursor = self.cnx_object.cursor(buffered=True)
  338. sql = "SELECT * FROM cfg_parse JOIN cfg_oddtype ON cfg_parse.oddId = cfg_oddtype.id WHERE cfg_parse.siteId='" + str(siteid) +"'"
  339. cursor.execute(sql)
  340. ret = []
  341. results = cursor.fetchall()
  342. for row in results:
  343. temp = dict()
  344. temp["id"] = row[2]
  345. temp["pattern"] = row[3]
  346. temp["sportId"] = row[5]
  347. ret.append(temp)
  348. return ret
  349. except Exception as e:
  350. self.log(0, "Database: getOdds >>exception " +str(e))
  351. return None
  352.  
  353. def loadSeparator(self, siteid):
  354. try:
  355. # Getting Team Separtor Letter
  356. cursor = self.cnx_object.cursor(buffered=True)
  357. sql = "SELECT * FROM cfg_team_sep WHERE siteId='"+str(siteid)+"'"
  358. cursor.execute(sql)
  359. row = cursor.fetchone()
  360. return row[2]
  361. except Exception as e:
  362. self.log(0, "Database: loadSeparator >>exception " +str(e))
  363. cursor.close()
  364. return None
  365.  
  366. # Read all matched events from 'matched_event' table by siteID
  367. # id, name, league, datetime, siteid, gametype, eventId (Table structure)
  368. def getMatchedEvents(self, siteId, siteType):
  369. try:
  370. ret = []
  371.  
  372. cursor = self.cnx_object.cursor(buffered=True)
  373. if siteType == 1:
  374. sql = "UPDATE match_result SET backodd_update=0 WHERE bookmaker='"+str(siteId)+"'"
  375. else:
  376. sql = "UPDATE match_result SET layodd_update=0 WHERE exchange='"+str(siteId)+"'"
  377. cursor.execute(sql)
  378. self.cnx_object.commit()
  379.  
  380. if siteType == 1:
  381. table_name = "bookmaker_event"
  382. sql = "SELECT DISTINCT(bookmaker_event) FROM match_result WHERE bookmaker='"+str(siteId)+"'"
  383. sql = "SELECT DISTINCT(id) FROM bookmaker_event WHERE siteid='"+str(siteId)+"'"
  384. else:
  385. table_name = "exchange_event"
  386. sql = "SELECT DISTINCT(exchange_event) FROM match_result WHERE exchange='"+str(siteId)+"'"
  387. sql = "SELECT DISTINCT(id) FROM exchange_event WHERE siteid='" + str(siteId) + "'"
  388. cursor.execute(sql)
  389. rows = cursor.fetchall()
  390. for row in rows:
  391. sql = 'SELECT * FROM %s WHERE id="%s" ' % (table_name, row[0])
  392. cursor.execute(sql)
  393. ret.append(cursor.fetchone())
  394. return ret
  395. except Exception as e:
  396. self.log(0, "Database: getMatchedEvents >>exception " +str(e))
  397. cursor.close()
  398.  
  399. def updateLastIds(self):
  400. cursor = self.cnx_object.cursor(buffered=True)
  401. sql = "SELECT id FROM bookmaker_event ORDER BY id DESC LIMIT 0,1"
  402. cursor.execute(sql)
  403. row = cursor.fetchone()
  404. if row == None:
  405. b_lastId = 0
  406. else:
  407. b_lastId = row[0]
  408. sql = "SELECT id FROM exchange_event ORDER BY id DESC LIMIT 0,1"
  409. cursor.execute(sql)
  410. row = cursor.fetchone()
  411. if row == None:
  412. e_lastId = 0
  413. else:
  414. e_lastId = row[0]
  415. sql ='UPDATE adm_config SET last_b_id=%s, last_e_id=%s WHERE id = %s' % (b_lastId, e_lastId, 1)
  416. cursor.execute(sql)
  417. self.cnx_object.commit()
  418. cursor.close()
  419.  
  420. def executeSQL(self, sql):
  421. cursor = self.cnx_object.cursor(buffered=True)
  422. cursor.execute(sql)
  423. self.cnx_object.commit()
  424. cursor.close()
  425.  
  426. # Sisal, Lottomatica
  427. def getMatchedLeagues(self, siteId, siteType):
  428. try:
  429. ret = []
  430. cursor = self.cnx_object.cursor(buffered=True)
  431. if siteType == 1:
  432. table_name = "bookmaker_event";
  433. sql = "SELECT DISTINCT(league) FROM bookmaker_event WHERE id IN ( SELECT DISTINCT(bookmaker_event) FROM match_result WHERE bookmaker=%s)" %(siteId)
  434. sql = "SELECT DISTINCT(league) FROM bookmaker_event WHERE siteid='"+str(siteId)+"'"
  435. else:
  436. table_name = "exchange_event";
  437. sql = "SELECT DISTINCT(league) FROM exchange_event WHERE id IN ( SELECT DISTINCT(exchange_event) FROM match_result WHERE exchange=%s)" % (siteId)
  438. cursor.execute(sql)
  439. rows = cursor.fetchall()
  440. for row in rows:
  441. if str(row[0]).__contains__('"'):
  442. sql = "SELECT * FROM %s WHERE league = '%s' AND siteid=%s" % ( table_name, str(row[0]) , siteId)
  443. else:
  444. sql = 'SELECT * FROM %s WHERE league = "%s" AND siteid=%s' % ( table_name, str(row[0]) ,siteId)
  445. cursor.execute(sql)
  446. record = cursor.fetchone()
  447. ret.append(record)
  448. return ret
  449. except Exception as e:
  450. self.log(0, "Database: getMatchedLeagues >>exception " +str(e))
  451. cursor.close()
  452.  
  453. def updateEventReport(self, siteId, f_count, t_count, b_count, takeTime, endTime):
  454. try:
  455. cursor = self.cnx_object.cursor(buffered=True)
  456. sql = "SELECT type FROM cfg_bookies WHERE id=%s" % (siteId)
  457. cursor.execute(sql)
  458. row = cursor.fetchone()
  459. if row[0] == 1:
  460. self.insertEventChunkData(row[0], self.bInsertEventList, self.bUpdateEventList)
  461. self.bInsertEventList = []
  462. self.bUpdateEventList = []
  463. else:
  464. self.insertEventChunkData(row[0], self.eInsertEventList, self.eUpdateEventList)
  465. self.eInsertEventList = []
  466. self.eUpdateEventList = []
  467.  
  468. sql ="SELECT * FROM adm_report WHERE siteId="+str(siteId)
  469. cursor.execute(sql)
  470. if cursor.rowcount < 1:
  471. sql = ("INSERT INTO adm_report (siteId, f_count, t_count, b_count, event_taken_time,last_event_time) VALUES (%s, %s, %s, %s, %s, %s)")
  472. cursor.execute(sql , (siteId, f_count, t_count,b_count, takeTime, endTime))
  473. else:
  474. sql ='UPDATE adm_report SET f_count=%s, t_count=%s, b_count=%s, event_taken_time=%s, last_event_time ="%s", e_update_count=e_update_count + 1 WHERE siteId=%s ' % (f_count, t_count, b_count, takeTime, endTime, siteId)
  475. cursor.execute(sql)
  476. self.cnx_object.commit()
  477. except Exception as e:
  478. self.log(0, "Database: updateEventReport >>exception " +str(e))
  479. cursor.close()
  480.  
  481. def updateOddReport(self,siteType, siteId, m_count, odd_count, takenTime, endTime):
  482. cursor = self.cnx_object.cursor(buffered=True)
  483. try:
  484. if siteType == 1:
  485. tempBookmakerUpdateData = self.updateBookmakerOddList
  486. self.insertOddChunkData(siteType, tempBookmakerUpdateData)
  487. self.updateBookmakerOddList =[]
  488. else:
  489. tempExchangeUpdateData = self.updateExchangeOddList
  490. self.insertOddChunkData(siteType, tempExchangeUpdateData)
  491. self.updateExchangeOddList = []
  492.  
  493. sql ="SELECT * FROM adm_report WHERE siteId="+str(siteId)
  494. cursor.execute(sql)
  495. if cursor.rowcount < 1:
  496. sql = ("INSERT INTO adm_report (siteId, m_count, oddcount, odd_taken_time,last_odd_time) VALUES (%s, %s, %s, %s, %s)")
  497. cursor.execute(sql , (siteId, m_count, odd_count, takenTime, endTime))
  498. else:
  499. sql ='UPDATE adm_report SET m_count=%s, oddcount=%s, odd_taken_time=%s, last_odd_time ="%s", o_update_count=o_update_count+1 WHERE siteId=%s ' % (m_count, odd_count,takenTime, endTime, siteId)
  500. cursor.execute(sql)
  501. self.cnx_object.commit()
  502.  
  503.  
  504. if siteType == 1:
  505. sql = "UPDATE match_result SET backodd=0 WHERE id IN (SELECT * FROM (SELECT id FROM match_result WHERE bookmaker='%s' AND backodd_update = 0) AS X)" % (siteId)
  506. else:
  507. sql = "UPDATE match_result SET layodd=0 WHERE id IN (SELECT * FROM (SELECT id FROM match_result WHERE exchange='%s' AND layodd_update = 0) AS X)" % (siteId)
  508.  
  509. cursor.execute(sql)
  510. self.cnx_object.commit()
  511.  
  512. except Exception as e:
  513. self.log(0, "Database: updateOddReport >>exception " +str(e))
  514. cursor.close()
  515.  
  516. def getTimerConfig(self):
  517. try:
  518. cursor = self.cnx_object.cursor(buffered=True)
  519. sql ='SELECT e_timer, o_timer FROM adm_config WHERE id = 1'
  520. cursor.execute(sql)
  521. row = cursor.fetchone()
  522. cursor.close()
  523. if row != None:
  524. return row
  525. else:
  526. return None
  527. except Exception as e:
  528. self.log(0, "Database: getTimerConfig >>exception " +str(e))
  529. cursor.close()
  530. return None
  531.  
  532.  
  533. def getActive(self, siteId):
  534. cursor = self.cnx_object.cursor(buffered=True)
  535. try:
  536. sql ='SELECT active FROM cfg_bookies WHERE id = ' + str(siteId)
  537. cursor.execute(sql)
  538. row = cursor.fetchone()
  539. cursor.close()
  540. return row[0]
  541. except Exception as e:
  542. self.log(0, "Database: getActive >>exception " +str(e))
  543. cursor.close()
  544. return 0
  545.  
  546. def log(self, type, message):
  547. print(message)
  548.  
  549. def update_many(self, data_list=None, mysql_table=None):
  550. cur = self.cnx_object.cursor(buffered=True)
  551. query = ""
  552. values = []
  553. for data_dict in data_list:
  554.  
  555. if not query:
  556. columns = ', '.join('`{0}`'.format(k) for k in data_dict)
  557. duplicates = ', '.join('{0}=VALUES({0})'.format(k) for k in data_dict)
  558. place_holders = ', '.join('%s'.format(k) for k in data_dict)
  559. query = "INSERT INTO {0} ({1}) VALUES ({2})".format(mysql_table, columns, place_holders)
  560. query = "{0} ON DUPLICATE KEY UPDATE {1}".format(query, duplicates)
  561.  
  562. v = tuple(data_dict.values())
  563. values.append(v)
  564.  
  565. try:
  566. cur.executemany(query, values)
  567. except Exception as e:
  568. print "MySQL Error: %s" % str(e)
  569. self.cnx_object.rollback()
  570. return False
  571.  
  572. self.cnx_object.commit()
  573. cur.close()
  574.  
  575. def insertOddThread(self):
  576. while True:
  577. cursor = self.cnx_object.cursor()
  578. self.lock.acquire()
  579. tempBookmakerUpdateData = WDatabase.updateBookmakerOddList[:1000]
  580. WDatabase.updateBookmakerOddList = WDatabase.updateBookmakerOddList[1000:]
  581.  
  582. tempExchangeUpdateData = WDatabase.updateExchangeOddList[:1000]
  583. WDatabase.updateExchangeOddList = WDatabase.updateExchangeOddList[1000:]
  584. self.lock.release()
  585. if len(tempBookmakerUpdateData) != 0:
  586. while True:
  587. try:
  588. sql = "UPDATE match_result SET backodd_update=%s, backodd=%s WHERE bookmaker_event=%s AND runner=%s AND market=%s"
  589. cursor.executemany(sql, tempBookmakerUpdateData)
  590. break
  591. except Exception as e:
  592. self.log(1, "Exception insertOddThread " + str(e))
  593. continue
  594. if len(tempExchangeUpdateData) != 0:
  595. while True:
  596. try:
  597. sql = "UPDATE match_result SET layodd_update=%s, layodd=%s, liquidity=%s WHERE exchange_event=%s AND runner=%s AND market=%s"
  598. cursor.executemany(sql, tempExchangeUpdateData)
  599. break
  600. except Exception as e:
  601. self.log(1, "Exception insertOddThread" + str(e))
  602. continue
  603.  
  604. self.cnx_object.commit()
  605. cursor.close()
  606. threading._sleep(2)
  607.  
  608. def insertEventThread(self, number):
  609. while True:
  610. cursor = self.cnx_object.cursor()
  611. if number==WDatabase.scrapperCount and len(WDatabase.bInsertEventList) == 0 and len(WDatabase.eInsertEventList) == 0 and len(WDatabase.bUpdateEventList) == 0 and len(WDatabase.eUpdateEventList) == 0:
  612. break
  613. self.lock.acquire()
  614. bTempInsertData = WDatabase.bInsertEventList[:1000]
  615. WDatabase.bInsertEventList = WDatabase.bInsertEventList[1000:]
  616.  
  617. eTempInsertData = WDatabase.eInsertEventList[:1000]
  618. WDatabase.eInsertEventList = WDatabase.eInsertEventList[1000:]
  619.  
  620. bTempUpdateData= WDatabase.bUpdateEventList[:1000]
  621. WDatabase.bUpdateEventList = WDatabase.bUpdateEventList[1000:]
  622.  
  623. eTempUpdateData= WDatabase.eUpdateEventList[:1000]
  624. WDatabase.eUpdateEventList = WDatabase.eUpdateEventList[1000:]
  625.  
  626. self.lock.release()
  627.  
  628. while True:
  629. try:
  630. sql = "UPDATE bookmaker_event SET update_flag=%s WHERE datetime=%s AND name=%s AND siteid=%s AND gametype=%s"
  631. cursor.executemany(sql, bTempUpdateData)
  632. sql = "UPDATE exchange_event SET update_flag=%s WHERE datetime=%s AND name=%s AND siteid=%s AND gametype=%s"
  633. cursor.executemany(sql, eTempUpdateData)
  634. break
  635. except Exception as e:
  636. self.log(1,"Exception insertEventThread"+ str(e))
  637. continue
  638.  
  639. while True:
  640. try:
  641. sql = ("INSERT INTO bookmaker_event (name, league, datetime, siteid, gametype, eventId, update_flag) VALUES (%s, %s, %s, %s, %s, %s, %s)")
  642. cursor.executemany(sql, bTempInsertData)
  643. sql = (
  644. "INSERT INTO exchange_event (name, league, datetime, siteid, gametype, eventId, update_flag) VALUES (%s, %s, %s, %s, %s, %s, %s)")
  645. cursor.executemany(sql, eTempInsertData)
  646. break
  647. except Exception as e:
  648. if str(e).__contains__("Duplicate entry")==True:
  649. break
  650. self.log(1,"Exception insertEventThread"+ str(e))
  651. continue
  652.  
  653. self.cnx_object.commit()
  654. cursor.close()
  655. threading._sleep(10)
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!