import pandas as pd
df=pd.read_csv('passwd.txt',dtype={'port':int},sep='|')
(host,port,user,passwd)=df.iloc[0]
import sqlalchemy as sa
import os
import pandas as pd
import MySQLdb
conn=MySQLdb.connect(host=host,user=user,passwd=passwd,db='test',port=port,charset="utf8")

hsgt_stat_cumsum_df = pd.read_sql('select * from hsgt_stat_cumsum_df;', con=conn)
#hsgt_stat_cumsum_df.tail(4)

import matplotlib
from pylab import mpl
matplotlib.rcParams['axes.unicode_minus']=False
myfont = matplotlib.font_manager.FontProperties(fname='C:/Windows/Fonts/msyh.ttf')  
mpl.rcParams['axes.unicode_minus'] = False 
mpl.rcParams['font.sans-serif'] = ['SimHei']  
import matplotlib.pyplot as plt
fig = plt.figure(figsize =(12,6))
ax1 = fig.add_subplot(111)
plt.xticks(rotation=25)
ax1.plot( pd.to_datetime(hsgt_stat_cumsum_df['date'].tolist()), hsgt_stat_cumsum_df['close'].tolist(),color="red",label=u'sz'  ) #linestyle="--",
ax1.set_title( u"HS300&北上净资金" ,fontsize =12)
ax2 = ax1.twinx()
ax2.plot( pd.to_datetime( hsgt_stat_cumsum_df['date'].tolist()),hsgt_stat_cumsum_df['north_cumsum'].tolist(),color="blue",label=u'北上资金累计资金')# color='red' , label=u'质押数量'
ax1.set_ylabel(u'HS300指数',fontsize =12)
ax2.set_ylabel(u'北上累积资金')
ax1.legend( loc="upper left",fontsize =12)
ax2.legend( loc="upper center",fontsize =12)

import copy
tmp_df=copy.deepcopy( hsgt_stat_cumsum_df )
tmp_df.index=tmp_df['date']   #data[['indexPrice','meanPrice']].plot(figsize=(14,5),linewidth=2)
tmp_df[['close','meanPrice']].plot(figsize=(14,8),linewidth=2,legend=True,title=u'hs300股价 & 北上资金持仓成本') #[tmp_df['date']>'2015-01-01']
plt.figure(2)
tmp_df['accumShare'].plot(figsize=(14,3),color='red',title=u'累计份额(参考 百万)',linewidth=2,legend=True)

inustry_in_df = pd.read_sql('select * from inustry_in_df;', con=conn)
inustry_in_df

inustry_out_df = pd.read_sql('select * from inustry_out_df;', con=conn)
inustry_out_df

recent_stks_trad_top_df = pd.read_sql('select * from recent_stks_trad_top_df;', con=conn)
recent_stks_trad_top_df['JME']=recent_stks_trad_top_df['JME']/100000000
recent_stks_trad_top_df['MRJE']=recent_stks_trad_top_df['MRJE']/100000000
recent_stks_trad_top_df['MCJE']=recent_stks_trad_top_df['MCJE']/100000000
recent_stks_trad_top_df['CJJE']=recent_stks_trad_top_df['CJJE']/100000000
recent_stks_trad_top_df[['date','stk','stkname','JME','MRJE','MCJE','CJJE']].sort(['date','JME'],ascending=False).rename(columns={'JME':u'净买入额','MRJE':u'买入金额','MCJE':u'卖出金额','CJJE':u'成交金额'}).head(20)

conn.close()

from IPython.display import display
from IPython.display import HTML
import IPython.core.display as di

# This line will hide code by default when the notebook is exported as HTML
di.display_html('''<script>
    $(function() {
        String.prototype.startWith=function(str){     
            var reg=new RegExp("^"+str);     
            return reg.test(this);        
        }  

        String.prototype.endWith=function(str){     
            var reg=new RegExp(str+"$");     
            return reg.test(this);        
        }
        
        if ($("body.notebook_app").length == 0) {
            $(".input_area").empty().toggle();
            $(".prompt").empty().toggle();
            $(".output_stderr").empty().toggle();
            $("pre").each(function(idx, val){
                if(this.innerHTML.startWith('&')){
                    $(this).empty().toggle();
                }
            })
            $(".code_cell").css("padding", '0px');
            $(".code_cell").css("border", '0px');
            
            $("#notebook-container").css("padding", '0px');
            $("div.output_subarea").css("min-width", '100%');
        }
    });
</script>''', raw=True)