import tushare as ts
import MySQLdb

from config import CONFIG_DB 
from config import CONFIG_TS

db = MySQLdb.connect(CONFIG_DB['host'], CONFIG_DB['user'], CONFIG_DB['password'], CONFIG_DB['database'], charset='utf8')
pro = ts.pro_api(CONFIG_TS['secret'])

# limit 2000 rows per request
df = pro.new_share(**{
    "start_date": 20000101,
    "end_date": 20071231,
#    "start_date": 20080101,
#    "end_date": 20111231,
#    "start_date": 20120101,
#    "end_date": 20131231,
#    "start_date": 20140101,
#    "end_date": 20151231,
#    "start_date": 20160101,
#    "end_date": 20171231,
#    "start_date": 20180101,
#    "end_date": 20191231,
#    "start_date": 20200101,
#    "end_date": 20220629,
    "limit": "",
    "offset": ""
}, fields=[
    "ts_code",
    "sub_code",
    "name",
    "ipo_date",
    "issue_date",
    "amount",
    "market_amount",
    "price",
    "pe",
    "limit_amount",
    "funds",
    "ballot"
])

cursor = db.cursor()
loop = 0
for row in df.itertuples():
    sql = "insert into t_ods_hs_base_ipo(ts_code, sub_code, name, ipo_date, issue_date, \
            amount, market_amount, price, pe, limit_amount, funds, ballot) \
             values('%s', '%s', '%s', '%s', '%s', %f, %f, %f, %f, %f, %f, %f ) \
             on duplicate key update sub_code= '%s', name='%s', ipo_date='%s', issue_date='%s', \
             amount=%f, market_amount=%f, price=%f, pe=%f, limit_amount=%f, funds=%f, ballot=%f " % \
             (getattr(row, 'ts_code'), getattr(row, 'sub_code'), getattr(row, 'name'), getattr(row, 'ipo_date')
             , getattr(row, 'issue_date'), float(getattr(row, 'amount')), float(getattr(row, 'market_amount'))
             , float(getattr(row, 'price')), float(getattr(row, 'pe')), float(getattr(row, 'limit_amount'))
             , float(getattr(row, 'funds')), float(getattr(row, 'ballot'))
             , getattr(row, 'sub_code'), getattr(row, 'name'), getattr(row, 'ipo_date')
             , getattr(row, 'issue_date'), float(getattr(row, 'amount')), float(getattr(row, 'market_amount'))
             , float(getattr(row, 'price')), float(getattr(row, 'pe')), float(getattr(row, 'limit_amount'))
             , float(getattr(row, 'funds')), float(getattr(row, 'ballot')))
    try:
        cursor.execute(sql)
        db.commit()
        loop = loop+1
    except Exception as ex:
        print (ex)
        db.rollback()
        break

print("task complete! total rows: %d" % loop)
db.close()



