import pymysql
import time
import datetime
import pprint
from collections import defaultdict

#今天时间
today = datetime.date.today()
#print(today)
#昨天时间
yesterday = today - datetime.timedelta(days=1)
#print(yesterday)

#得到昨天开始时间戳
def get_start_time():
	return int(time.mktime(time.strptime(str(yesterday), '%Y-%m-%d')))
#print(get_start_time())
#得到昨天结束时间戳
def get_end_time():
	return (int(time.mktime(time.strptime(str(today), '%Y-%m-%d'))) - 1)

#print(get_end_time())

conn = (pymysql.connect(host='192.168.0.13',user = 'root',
		password='root',database='copyright_for_crawler',
		use_unicode=True,charset='utf8'))
cur = conn.cursor()
#sql = "SELECT * FROM copyright_for_crawler.kanjian_ddex_album_release_feedback;"
sql_two = ("SELECT id,dsp_id,cw_url,cw_timestamp FROM copyright_for_crawler.kanjian_ddex_album_release_feedback WHERE cw_timestamp BETWEEN %s AND %s;"
			% (get_start_time(), get_end_time()))

cur.execute(sql_two)
results = cur.fetchall()
#print(results)
album_dict = defaultdict(dict)

for row in results:
	#cw_timestamp = row[3]
	#print(cw_timestamp)

	#if (cw_timestamp >= get_start_time() and cw_timestamp <= get_end_time()):
	if row[2]:
		album_id =row[0]
		#print(album_id)
		if album_id not in album_dict:
			album_dict[row[0]]["cw_url"] = row[2]
			album_dict[row[0]]["dsp_id"] = row[1]

		#pprint.pprint(album_dict)


#pprint.pprint(dict(album_dict))
sql_re = "SELECT id,name FROM copyright_for_crawler.kanjian_ddex_album;"
cur.execute(sql_re)
results_re = cur.fetchall()
for row in results_re:
	album_id_re = row[0]
	if album_id_re in album_dict:
		album_dict[row[0]]["name"] = row[1]

#pprint.pprint(album_dict)

sql_ree = "SELECT id, name FROM copyright_for_crawler.kanjian_ddexdsp;"
cur.execute(sql_ree)
results_ree = cur.fetchall()
for row in results_ree:
	dsp_id = row[0]
	for key in album_dict:
		if dsp_id == album_dict[key]["dsp_id"]:
			album_dict[key]["dsp_id"] = row[1]

pprint.pprint(album_dict)