import pymysql,pandas,sys,re
from datetime import date, timedelta, datetime
from dateutil.relativedelta import relativedelta

sql = """select 
	p.po_idx,
    p.po_title,
    p.po_context,
    p.mrkt_titl_nm,
    p.po_keyword,
    p.gods_cate_cd,
    c.cate_kwrd,
    p.po_maker,
    (select group_concat(concat(o.oValue1,":",o.oValue2,":",o.oValue3,":",o.oValue4)) 
		from jasonapp019.po_list_option as o
        where o.po_idx = p.po_idx
        group by po_idx) as `options`,
    p.po_regdate 
from jasonapp019.po_list as p,
	jasonapp019.gods_cate as c
where 
	p.po_type="9" AND p.po_soldout = "0" AND p.po_status = "0" 
    -- and p.po_regdate between "2022-07-29 00:00:00" and "2022-08-01 00:00:00"
    and p.gods_cate_cd = c.cate_cd
order by p.po_regdate desc"""

db = pymysql.connect(host='10.20.10.91', port=3306, user='jason_da', password='da@0417', db='extr')
cursor = db.cursor(pymysql.cursors.DictCursor)

cursor.execute(sql)
data = cursor.fetchall()
db.close()

df = pandas.DataFrame(data)

df.fillna("")

df.to_excel("./po_keywords.xlsx")