def __init__(self, id, uuid="", owner=""):
self.tapped = False
self.parent = False
self.attached = False
self.counters = {}
s = Sql("mtg")
q = "SELECT c.id, c.number, c.name, c.set_no, c.rarity, c.legendary, c.type, c.subtype, c.power, c.toughness, c.cost, c.cmc, c.text, c.flavor, c.illus, s.abrev "
q += "FROM cards c "
q += "JOIN sets s ON c.set_no = s.id "
q += "WHERE c.id = '" + str(id) + "' "
q += "LIMIT 1;"
rows = s.ex(q)
if len(rows) == 1:
c = {}
r = rows[0]
self.id = r[0]
self.number = r[1]
self.name = r[2]
self.set_no = r[3]
self.rarity = r[4]
self.legendary = r[5]
self.type = r[6]
self.subtype = r[7]
self.power = r[8]
self.toughness = r[9]
self.cost = r[10]
self.cmc = r[11]
self.text = r[12]
self.flavor = r[13]
self.illus = r[14]
self.abrev = r[15]
self.uuid = uuid
self.owner = owner
def get(self, deckid):
s = Sql("mtg")
q = "SELECT name, decklist, updateDate FROM decks WHERE id = '"+deckid+"' LIMIT 1;"
rows = s.ex(q)
if len(rows) == 1:
r = rows[0]
arr = json.loads(r[1])
c_lst = ",".join(str(x) for x in arr)
sql = "SELECT t.id AS t_id, "
sql += "t.size AS size, "
sql += "t.t_type AS t_type, "
sql += "l.img AS img, "
sql += "c.name AS card_name "
sql += "FROM token_lookup AS l "
sql += "JOIN tokens t ON t.id = l.token_id "
sql += "JOIN cards c ON c.id = l.card_id "
sql += "WHERE card_id IN (" + c_lst + ");"
toks = s.ex(sql)
if len(toks) > 0:
tokens = []
for x in toks:
tokens.append({
'id': x[0],
'size': x[1],
'type': x[2],
'img_src': x[3],
'card_name': x[4],
})
self.write(json.dumps(tokens))
else:
self.write("false")
def loadDeck(self, uid, deck_id=0):
if deck_id != 0:
self.deck = []
self.deck_id = deck_id
s = Sql("mtg")
q = "SELECT name, decklist, sideboard "
q += "FROM decks "
q += "WHERE id = '" + str(deck_id) + "';"
rows = s.ex(q)
if (len(rows) > 0):
temp = rows[0]
self.name = temp[0]
if temp[1] != "":
x = json.loads(temp[1])
for a in x:
self.deck.append({
"uuid": str(uuid.uuid1()),
"card_id": a,
})
if temp[2] != "":
x = json.loads(temp[1])
for a in x:
self.sideboard.append({
"uuid": str(uuid.uuid1()),
"card_id": a,
})
return True
return False
def loadDeck(self, uid, deck_id=0):
if deck_id != 0:
self.deck = []
self.deck_id = deck_id
s = Sql("mtg")
q = "SELECT name, decklist, sideboard "
q += "FROM decks "
q += "WHERE id = '" + str(deck_id) + "';"
rows = s.ex(q)
if (len(rows) > 0):
temp = rows[0]
self.name = temp[0]
if temp[1] != "":
x = json.loads(temp[1])
for a in x:
self.deck.append({
"uuid": str(uuid.uuid1()),
"card_id": a,
})
if temp[2] != "":
x = json.loads(temp[1])
for a in x:
self.sideboard.append({
"uuid": str(uuid.uuid1()),
"card_id": a,
})
return True
return False
def get(self, id):
s = Sql("mtg")
q = "SELECT c.id, c.number, c.name, c.set_no, c.rarity, c.legendary, c.type, c.subtype, c.power, c.toughness, c.cost, c.cmc, c.text, c.flavor, c.illus, s.abrev "
q+= "FROM cards c "
q+= "JOIN sets s ON c.set_no = s.id "
q+= "WHERE c.id = '" + str(id) + "' "
q+= "LIMIT 1;"
rows = s.ex(q)
if len(rows) == 1:
c = {}
r = rows[0]
c["id"] = r[0]
c["number"] = r[1]
c["name"] = r[2]
c["set_no"] = r[3]
c["rarity"] = r[4]
c["legendary"] = r[5]
c["type"] = r[6]
c["subtype"] = r[7]
c["power"] = r[8]
c["toughness"] = r[9]
c["cost"] = r[10]
c["cmc"] = r[11]
c["text"] = r[12]
c["flavor"] = r[13]
c["illus"] = r[14]
c["abrev"] = r[15]
self.write(json.dumps(c))
self.write("")
def __init__(self, id, uuid="", owner=""):
self.tapped = False
self.parent = False
self.attached = False
self.counters = {}
s = Sql("mtg")
q = "SELECT c.id, c.number, c.name, c.set_no, c.rarity, c.legendary, c.type, c.subtype, c.power, c.toughness, c.cost, c.cmc, c.text, c.flavor, c.illus, s.abrev "
q+= "FROM cards c "
q+= "JOIN sets s ON c.set_no = s.id "
q+= "WHERE c.id = '" + str(id) + "' "
q+= "LIMIT 1;"
rows = s.ex(q)
if len(rows) == 1:
c = {}
r = rows[0]
self.id = r[0]
self.number = r[1]
self.name = r[2]
self.set_no = r[3]
self.rarity = r[4]
self.legendary = r[5]
self.type = r[6]
self.subtype = r[7]
self.power = r[8]
self.toughness = r[9]
self.cost = r[10]
self.cmc = r[11]
self.text = r[12]
self.flavor = r[13]
self.illus = r[14]
self.abrev = r[15]
self.uuid = uuid
self.owner = owner
def get(self, id):
s = Sql("mtg")
q = "SELECT c.id, c.number, c.name, c.set_no, c.rarity, c.legendary, c.type, c.subtype, c.power, c.toughness, c.cost, c.cmc, c.text, c.flavor, c.illus, s.abrev "
q += "FROM cards c "
q += "JOIN sets s ON c.set_no = s.id "
q += "WHERE c.id = '" + str(id) + "' "
q += "LIMIT 1;"
rows = s.ex(q)
if len(rows) == 1:
c = {}
r = rows[0]
c["id"] = r[0]
c["number"] = r[1]
c["name"] = r[2]
c["set_no"] = r[3]
c["rarity"] = r[4]
c["legendary"] = r[5]
c["type"] = r[6]
c["subtype"] = r[7]
c["power"] = r[8]
c["toughness"] = r[9]
c["cost"] = r[10]
c["cmc"] = r[11]
c["text"] = r[12]
c["flavor"] = r[13]
c["illus"] = r[14]
c["abrev"] = r[15]
self.write(json.dumps(c))
self.write("")
def get(self, deckid):
s = Sql("mtg")
q = "SELECT name, decklist, updateDate FROM decks WHERE id = '" + deckid + "' LIMIT 1;"
rows = s.ex(q)
if len(rows) == 1:
r = rows[0]
arr = json.loads(r[1])
c_lst = ",".join(str(x) for x in arr)
sql = "SELECT t.id AS t_id, "
sql += "t.size AS size, "
sql += "t.t_type AS t_type, "
sql += "l.img AS img, "
sql += "c.name AS card_name "
sql += "FROM token_lookup AS l "
sql += "JOIN tokens t ON t.id = l.token_id "
sql += "JOIN cards c ON c.id = l.card_id "
sql += "WHERE card_id IN (" + c_lst + ");"
toks = s.ex(sql)
if len(toks) > 0:
tokens = []
for x in toks:
tokens.append({
'id': x[0],
'size': x[1],
'type': x[2],
'img_src': x[3],
'card_name': x[4],
})
self.write(json.dumps(tokens))
else:
self.write("false")
def Add(self, notify=True):
s = Sql("woodhouse")
q = "INSERT INTO logs "
q += "(post_by, title, text) VALUES ("
q += "'" + self.post_by + "', "
q += "'" + self.title + "', "
q += "'" + self.text + "');"
if (notify):
pushover(message=self.text, title=self.post_by + ":" + self.title, token="pvPX3Ag69Iwt4E9dvpVpA5xfoQLaEw", user="******")
return s.ex_add(q)
def main():
sql = Sql()
mon_records = sql.read_one_monitor("1268059")
prices = []
dates = []
for mr in mon_records:
prices.append(mr.coupon_price)
dates.append(mr.update_time)
dates_order, prices_order = zip(*sorted(zip(dates, prices)))
plt.plot(dates_order, 'g*', [1, 4, 9, 16], 'ro')
plt.show()
def Add(self, notify=True):
s = Sql("woodhouse")
q = "INSERT INTO logs "
q += "(post_by, title, text) VALUES ("
q += "'" + self.post_by + "', "
q += "'" + self.title + "', "
q += "'" + self.text + "');"
if (notify):
pushover(message=self.text,
title=self.post_by + ":" + self.title,
token="pvPX3Ag69Iwt4E9dvpVpA5xfoQLaEw",
user="******")
return s.ex_add(q)
def get(self, uid):
s = Sql("mtg")
q = "SELECT d.id, d.usr, d.name, u.usr, u.uid "
q+= "FROM users u "
q+= "JOIN decks d ON u.usr = d.usr "
q+= "WHERE u.`uid` = '" + uid + "' "
q+= " ORDER BY d.id DESC;"
rows = s.ex(q)
if len(rows) > 0:
decks = []
for r in rows:
temp = {"id": r[0], "name": r[2]}
decks.append(temp)
self.write(json.dumps(decks))
self.write("")
def get(self, uid):
s = Sql("mtg")
q = "SELECT d.id, d.usr, d.name, u.usr, u.uid "
q += "FROM users u "
q += "JOIN decks d ON u.usr = d.usr "
q += "WHERE u.`uid` = '" + uid + "' "
q += " ORDER BY d.id DESC;"
rows = s.ex(q)
if len(rows) > 0:
decks = []
for r in rows:
temp = {"id": r[0], "name": r[2]}
decks.append(temp)
self.write(json.dumps(decks))
self.write("")
def getLogtime(self):
conn = Sql(path)
log = str(conn.get_last_log_timestamp())
if conn.get_last_log_timestamp() is None:
log = '-'
del conn
return log
def getData(self):
db_query = Sql(path) # Initialization(Constructor)
db_query.setCreateTable(sql_create_init_table) # Table Create
dbData = db_query.get_join_data()
last_update = db_query.get_last_log_timestamp(
) # Get latest log datetime in Table return type is datetime
del db_query
self.thread = threading.Thread()
self.thread.__init__(target=self.thread_progressbar(last_update),
args=())
self.thread.daemon = True
self.thread.start()
if self.allData is None:
self.allData = dbData
else:
if len(self.allData) == len(dbData):
return None
else:
sub = len(dbData) - len(self.allData)
self.allData = dbData
self.log_time = str(last_update)
return dbData[0:sub]
def get(self, id):
s = Sql("mtg")
q = "SELECT t.id, t.set_no, t.t_type, t.size, t.number, t.artist, l.img "
q+= "FROM tokens t "
q+= "JOIN token_lookup l ON t.id = l.token_id "
q+= "WHERE t.id = '" + str(id) + "' "
q+= "LIMIT 1;"
rows = s.ex(q)
if len(rows) == 1:
c = {}
r = rows[0]
c["id"] = r[0]
c["set"] = r[1]
c["type"] = r[2]
c["size"] = r[3]
c["number"] = r[4]
c["illus"] = r[5]
c["img_src"] = r[6]
self.write(json.dumps(c))
self.write("")
def get(self, id):
s = Sql("mtg")
q = "SELECT t.id, t.set_no, t.t_type, t.size, t.number, t.artist, l.img "
q += "FROM tokens t "
q += "JOIN token_lookup l ON t.id = l.token_id "
q += "WHERE t.id = '" + str(id) + "' "
q += "LIMIT 1;"
rows = s.ex(q)
if len(rows) == 1:
c = {}
r = rows[0]
c["id"] = r[0]
c["set"] = r[1]
c["type"] = r[2]
c["size"] = r[3]
c["number"] = r[4]
c["illus"] = r[5]
c["img_src"] = r[6]
self.write(json.dumps(c))
self.write("")
def __init__(self, id, uuid="", owner=""):
self.tapped = False
self.parent = False
self.attached = False
s = Sql("mtg")
q = "SELECT t.id, t.set_no, t.t_type, t.size, t.number, t.artist, l.img "
q+= "FROM tokens t "
q+= "JOIN token_lookup l ON t.id = l.token_id "
q+= "WHERE t.id = '" + str(id) + "' "
q+= "LIMIT 1;"
rows = s.ex(q)
if len(rows) == 1:
c = {}
r = rows[0]
self.id = r[0]
self.set = r[1]
self.type = r[2]
self.size = r[3]
self.number = r[4]
self.illus = r[5]
self.img_src = r[6]
self.uuid = uuid
self.owner = owner
def __init__(self, id, uuid="", owner=""):
self.tapped = False
self.parent = False
self.attached = False
s = Sql("mtg")
q = "SELECT t.id, t.set_no, t.t_type, t.size, t.number, t.artist, l.img "
q += "FROM tokens t "
q += "JOIN token_lookup l ON t.id = l.token_id "
q += "WHERE t.id = '" + str(id) + "' "
q += "LIMIT 1;"
rows = s.ex(q)
if len(rows) == 1:
c = {}
r = rows[0]
self.id = r[0]
self.set = r[1]
self.type = r[2]
self.size = r[3]
self.number = r[4]
self.illus = r[5]
self.img_src = r[6]
self.uuid = uuid
self.owner = owner
def OnDoubleClick(self, event):
item = self.treeview.selection()[0]
select_student_number = self.treeview.item(item).get('values')[1]
sql = Sql(path)
info = sql.get_student_data(select_student_number)
del sql
info_window = Toplevel()
info_window.title('학사 정보')
info_window.geometry('272x105')
info_window.resizable(False, False)
info_id = Label(info_window, text='학 번', width=15, bg='#bdbdbd')
student_id = Label(info_window, text=info[0][0], width=20, bg='white')
info_id.place(x=5, y=5)
student_id.place(x=120, y=5)
info_name = Label(info_window, text='성 명', width=15, bg='#bdbdbd')
student_name = Label(info_window,
text=info[0][1],
width=20,
bg='white')
info_name.place(x=5, y=30)
student_name.place(x=120, y=30)
info_major = Label(info_window, text='소속전공', width=15, bg='#bdbdbd')
student_major = Label(info_window,
text=info[0][3],
width=20,
bg='white')
info_major.place(x=5, y=55)
student_major.place(x=120, y=55)
info_phone = Label(info_window, text='전화번호', width=15, bg='#bdbdbd')
student_phone = Label(info_window,
text=info[0][2],
width=20,
bg='white')
info_phone.place(x=5, y=80)
student_phone.place(x=120, y=80)
info_window.mainloop()
def main():
MAX_PRICE = 9999999.00
crawler = Crawler()
# 需要时才运行一次
#cookies = crawler.login_jd()
sql = Sql()
with open("item_id_list.json", 'r') as load_f:
item_ids = json.load(load_f)
# 读商品信息数据库记录，更新item_lowest_prices和item_latest_prices
item_lowest_prices = []
item_latest_prices = []
for i in range(len(item_ids)):
info_item = sql.read_one_info(item_ids[i])
if info_item:
item_lowest_prices.append(info_item.coupon_price)
item_latest_prices.append(info_item.coupon_price)
else:
item_lowest_prices.append(MAX_PRICE)
item_latest_prices.append(MAX_PRICE)
bot = Bot()
my_friend = bot.friends().search('solo', sex=MALE)[0]
my_friend.send('小蒐开始工作!\n目标商品:{0}个;\n监控次数:{1}轮;\n间隔时长:{2}秒\n{3}'.format(
len(item_ids), CR_ITERATIONS, CR_SLEEP_SEC, datetime.datetime.now()))
n_error_time = 0
for i in range(CR_ITERATIONS):
crawler.load_cookies()
if not crawler.check_login():
my_friend.send('小蒐提示京东登陆失败! {0}'.format(datetime.datetime.now()))
return False
for j in range(len(item_ids)):
time.sleep(1)
item_raw = crawler.get_jd_rawitem(item_ids[j])
# 如果爬取的该条数据不完整
if not item_raw or not item_raw['coupon_price']:
n_error_time += 1
continue
if n_error_time > 10:
my_friend.send('小蒐提示连续读取信息失败后退出! \n{0}'.format(
datetime.datetime.now()))
return False
# 加入一条监控数据库记录
sql.add_one_monitor(item_raw)
# 破低价
if item_raw['coupon_price'] < item_lowest_prices[j]:
if item_lowest_prices[j] != MAX_PRICE:
my_friend.send(
'小蒐破最低价提示! {0}!\n原低价约{1:.2f}元;\n现低价约{2:.2f}元.\n{3}\n{4}\n{5}'
.format(item_raw['status'], item_lowest_prices[j],
item_raw['coupon_price'], item_raw['url'],
item_raw['title'], item_raw['update_time']))
# 更新item_lowest_prices
item_lowest_prices[j] = item_raw['coupon_price']
# 更新商品信息数据库记录
sql.add_update_one_info(item_raw)
# 降价
elif item_raw['coupon_price'] < item_latest_prices[j]:
if item_latest_prices[j] != MAX_PRICE:
my_friend.send(
'小蒐降价提示! {0}!\n上轮价约{1:.2f}元;\n现降为约{2:.2f}元,{2}.\n{3}\n{4}\n{5}'
.format(item_raw['status'], item_latest_prices[j],
item_raw['coupon_price'], item_raw['url'],
item_raw['title'], item_raw['update_time']))
item_latest_prices[j] = item_raw['coupon_price']
logging.info('Waiting {0}s to start {1} iteration ...'.format(
CR_SLEEP_SEC, i + 2))
my_friend.send('小蒐提示\n第{0}轮监控结束! \n{1}'.format(
i + 1, datetime.datetime.now()))
time.sleep(CR_SLEEP_SEC)
time.sleep(TIMEOUT_SLEEP_SEC)
continue
if json_stock['stock']['StockState'] == 33:
item_raw_dict['status'] = "stock_in"
elif json_stock['stock']['StockState'] == 34:
item_raw_dict['status'] = "stock_out"
crawling_phase4 = False
logging.info('Crawl SUCCESS: {}'.format(item_raw_dict))
return item_raw_dict
if __name__ == '__main__':
############# 下面代码作废 ##################
logging.basicConfig(level=logging.INFO)
crawler = Crawler()
#cookies = crawler.login_jd()
sql = Sql()
item_ids = ['8683310', '4624351', '1250262', '1120715', '100003671718']
item_lowest_prices = [9999999 for i in range(len(item_ids))]
for i in range(100):
crawler.load_cookies()
for j in range(len(item_ids)):
item_raw = crawler.get_jd_rawitem(item_ids[j])
sql.add_one_monitor(item_raw)
if item_raw['coupon_price'] < item_lowest_prices[j]:
item_lowest_prices[j] = item_raw['coupon_price']
logging.info('Waiting 300s for starting {0} iteration ...'.format(i +
2))
time.sleep(1800)
def main():
MAX_PRICE = 9999999.00
crawler = Crawler()
# 需要时才运行一次
#cookies = crawler.login_jd()
sql = Sql()
with open("item_id_list.json", 'r') as load_f:
item_ids = json.load(load_f)
# 读商品信息数据库记录，更新item_lowest_prices和item_latest_prices
item_lowest_prices = []
item_latest_prices = []
for i in range(len(item_ids)):
info_item = sql.read_one_info(item_ids[i])
if info_item:
item_lowest_prices.append(info_item.coupon_price)
item_latest_prices.append(info_item.coupon_price)
else:
item_lowest_prices.append(MAX_PRICE)
item_latest_prices.append(MAX_PRICE)
wxadb = WXadb()
wxadb.add_device("f51e6c3a")
pinyin = Pinyin()
wxadb.send_WX_msg(
'xso message>> target numbers:{0}; iteration times:{1}; interval seconds:{2} {3}'
.format(len(item_ids), CR_ITERATIONS, CR_SLEEP_SEC,
datetime.datetime.now()))
n_error_time = 0
for i in range(CR_ITERATIONS):
crawler.load_cookies()
if not crawler.check_login():
wxadb.send_WX_msg('xso warning>> JD login failed! {0}'.format(
datetime.datetime.now()))
return False
for j in range(len(item_ids)):
time.sleep(1)
item_raw = crawler.get_jd_rawitem(item_ids[j])
# 如果爬取的该条数据不完整
if not item_raw or not item_raw['coupon_price']:
n_error_time += 1
continue
if n_error_time > 10:
wxadb.send_WX_msg(
'xso warning>> several times failed and quit! {0}'.format(
datetime.datetime.now()))
return False
# 加入一条监控数据库记录
sql.add_one_monitor(item_raw)
# 破低价（有货）
if item_raw['coupon_price'] < item_lowest_prices[j]:
if item_lowest_prices[j] != MAX_PRICE and item_raw[
'status'] == 'stock_in':
wxadb.send_WX_msg(
'xso message>> {0} history lowest price:{1:.2f}yuan, current price:{2:.2f}yuan. {3} {4}'
.format(pinyin.get_pinyin(item_raw['title'],
''), item_lowest_prices[j],
item_raw['coupon_price'], item_raw['url'],
item_raw['update_time']))
# 更新item_lowest_prices
item_lowest_prices[j] = item_raw['coupon_price']
# 更新商品信息数据库记录
sql.add_update_one_info(item_raw)
# 降价
elif item_raw['coupon_price'] < item_latest_prices[j]:
if item_latest_prices[j] != MAX_PRICE and item_raw[
'status'] == 'stock_in':
wxadb.send_WX_msg(
'xso message>> {0} last price:{1:.2f}yuan, current price:{2:.2f}yuan. {3} {4}'
.format(pinyin.get_pinyin(item_raw['title'],
''), item_latest_prices[j],
item_raw['coupon_price'], item_raw['url'],
item_raw['update_time']))
item_latest_prices[j] = item_raw['coupon_price']
logging.info('Waiting {0}s to start {1} iteration ...'.format(
CR_SLEEP_SEC, i + 2))
wxadb.send_WX_msg('xso message>> {0} iteration finished. {1}'.format(
i + 1, datetime.datetime.now()))
time.sleep(CR_SLEEP_SEC)
import hashlib
from SQL import Sql
from pprint import pprint
s = Sql("mtg")
q = "SELECT id, email FROM users;"
rows = s.ex(q)
for r in rows:
uid = hashlib.md5(r[1]).hexdigest()
x = "UPDATE users SET `uid` = '" + uid + "' WHERE id = " + str(r[0]) + ";"
print x
s.ex(x)
run = {"a": printA, "b": printB}
def pause(secs):
print "Waiting %d seconds" % secs
for x in range(secs, 0, -1):
s = ""
for x in range(x):
s += "#####"
s += " %d" % x
print s
sleep(1)
if __name__ == "__main__":
s = Sql("mtg")
q = "SELECT d.id, d.usr, d.name, u.usr, u.uid "
q += "FROM users u "
q += "JOIN decks d ON u.usr = d.usr "
q += "WHERE u.`uid` = '01114ecbca5c52018c133ccfabd0db9c';"
print q
#t = test()
#t.run['a']()
#t.run['b']()
#g = GameServer()
#u = g.addPlayer("*****@*****.**", "Andy")
#pause(2)
#msg = { 'type': 'join', 'uid': u, 'deck_id': 60 }
#x = g.msg(json.dumps(msg))
