import jinja2
import codecs
import os
import dataset

db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/yodb?charset=utf8mb4')

fname=os.path.abspath(__file__)
elmts=fname.split(os.path.sep)
path2=os.path.sep.join(elmts[0:-1])
keysdir=path2+os.path.sep
print(keysdir)


templateLoader = jinja2.FileSystemLoader(searchpath=keysdir)
templateEnv = jinja2.Environment(loader=templateLoader)
TEMPLATE_FILE = 'test.tmpl'
template = templateEnv.get_template(TEMPLATE_FILE)


#SELECT distinct ts_word FROM trending_searches ORDER BY ts_date DESC limit 21;
cursor=db.query('SELECT distinct ts_word FROM trending_searches ORDER BY ts_date DESC limit 21;')
display={}
idx=1
for c in cursor:
    display['a'+str(idx)]=c['ts_word']
    idx+=1
display['client']='1'
display['email']='jeweiliang@gmail.com'


outputText = template.render(display=display) 

fw=codecs.open('c:/tmp/final.html','w','utf-8')
fw.write(outputText)
fw.close()

os.startfile('c:/tmp/final.html')