Python DataBase.exeQuery方法代码示例

# 需要导入模块: import DataBase [as 别名]
# 或者: from DataBase import exeQuery [as 别名]
def show_report_price(code,startdate):
    df = ts.get_hist_data(code, start=startdate)
    df1 = df[::-1]
    highdate=''
    highprice=0
    lowdate=startdate
    lowprice=1000
    i = 0
    for each in df1.index:
        if df1['close'].iloc[i] > highprice:
            highprice = df1['close'].iloc[i]
            highdate = each
        i=i+1
    i = 0
    for each in df1.index:
        if each < highdate:
            if df1['close'].iloc[i] < lowprice:
                lowprice = df1['close'].iloc[i]
                lowdate = each
        i = i+1
    print highdate, highprice
    print lowdate, lowprice
    print (highprice-lowprice)/lowprice
    engine = create_engine("mssql+pyodbc://sa:[email protected]")
    conn, cur = db.connDB()
    cur = db.exeQuery(cur,"select * from tb_report where code = '%s' and reportdate <= '%s' and reportdate >= '%s' order by reportdate" % (code,highdate,lowdate))
    all = cur.fetchall()
    db.connClose(conn,cur)
    print len(all)

# 需要导入模块: import DataBase [as 别名]
# 或者: from DataBase import exeQuery [as 别名]
def show_report(code, startdate):
    df = ts.get_hist_data(code, start=startdate)
    df1 = df[::-1]
    x = df1.index
    xlist = x.tolist()
    y1 = df1['close']
    ylist1 = y1.tolist()
    engine = create_engine("mssql+pyodbc://sa:[email protected]")
    conn, cur = db.connDB()
    cur = db.exeQuery(cur,"select * from tb_report where code = '%s' and reportdate > '%s' order by reportdate" % (code,startdate))
    all = cur.fetchall()
    db.connClose(conn,cur)
    ylist2 = []
    num = 0
    for eachx in xlist:
        isin = 0
        for eachy2 in all:
            if eachy2[0] == eachx:
                isin = isin + 1
        if isin == 0:
            ylist2.append(num)
        else:
            num = num + isin
            ylist2.append(num)
    x = np.arange(0,len(xlist)).tolist()
    plt.plot(x, ylist1,label=code)
    plt.plot(x,ylist2)
    plt.legend()
    plt.savefig(code+'.jpg',dpi=200)
    # plt.show()
    result = {}
    result['close'] = ylist1
    result['report'] = ylist2
    resultfm = pd.DataFrame(result, index=xlist)
    return resultfm

# 需要导入模块: import DataBase [as 别名]
# 或者: from DataBase import exeQuery [as 别名]
def import_all_ticks(startdate, enddate):
    engine = create_engine("mssql+pyodbc://sa:[email protected]")
    conn, cur = db.connDB()
    cur = db.exeQuery(cur,'select * from tb_bas_stock')
    allname = cur.fetchall()
    for each in allname:
        print each[0], each[1]
        if startdate == 0:
            df = ts.get_hist_data(each[0],end=enddate)
        else:
            df = ts.get_hist_data(each[0],start=startdate,end=enddate)
        if df is not None:
            # df.to_csv('e:/stock/%s.csv' % each[0])
            try:
            # 第一次导入，数据库会报错，date类型要从text改为varchar..是主键不在能text上定义
                # 反序df,最新的日期在最后一个
                df1 = df[::-1]
                df1.to_sql(each[0], engine, if_exists='append')
                print each[0]
            except:
                print 'error ' + each[0] + each[1]

# 需要导入模块: import DataBase [as 别名]
# 或者: from DataBase import exeQuery [as 别名]
def pre_margin(code, startdate, enddate):
    conn, cur = db.connDB()
    cur = db.exeQuery(cur,'select * from tb_sz_margin_detail where stockcode = %s order by opDate' % code)
    allname = cur.fetchall()
    cur.close()
    conn.close()

    lrzye = []
    lrqye = []
    lopdate = []
    tstartdate = datetime.datetime.strptime(startdate,'%Y-%m-%d')
    tenddate = datetime.datetime.strptime(enddate,'%Y-%m-%d')
    for each in allname:
        opdate = datetime.datetime.strptime(each[9], '%Y-%m-%d')
        if opdate >= tstartdate and opdate <= tenddate:
            lopdate.append(each[9])
            lrzye.append(3*each[4]/100000000)
            lrqye.append(each[7]/100000000)

    df = ts.get_hist_data(code,start=startdate,end=enddate)
    df1 = df[::-1]

    #删除不正确的数据
    dindex = df1.index
    for each in dindex:
        if each in lopdate:
            pass
        else:
            df1 = df1.drop(each)

    yclose = df1['close']
    ylist = yclose.tolist()
    xlist = np.arange(0, len(lopdate)).tolist()
    plt.plot(xlist, ylist,label="close")
    # 用于对比 rzye rqye
    plt.plot(xlist,lrzye,label="rzye-rqye")
    plt.legend()
    plt.show()

# 需要导入模块: import DataBase [as 别名]
# 或者: from DataBase import exeQuery [as 别名]
import DataBase as db
from sqlalchemy import create_engine

engine = create_engine('mysql+pymysql://tradeuser:[email protected]/py_trade?charset=utf8')

# 取所有的股票基本面
# df = ts.get_stock_basics()
# df.to_csv('e:/s1.csv')
# df1.to_csv('e:/stockbas.csv', mode='a',header=True,index=True )
# 然后用SQL工具导入到 tb_stock_basics

#  https://mp.weixin.qq.com/s?__biz=MzAwOTgzMDk5Ng==&mid=2650833972&idx=1&sn=4de9f9ee81bc8bf85d1e0a4a8f79b0de&chksm=80adb30fb7da3a19817c72ff6f715ee91d6e342eb0402e860e171993bb0293bc4097e2dc4fe9&mpshare=1&scene=1&srcid=1106BPAdPiPCnj6m2Xyt5p2M#wechat_redirect
# 使用新的ts.get_k_data('000001', index=True)

conn, cur = db.connDB()
cur = db.exeQuery(cur,'select * from stock_basics')

# 修改表的date类型，改为varchar
allname = cur.fetchall()
for each in allname:
    if each[0] != 'stock_basics':
        print each[0]
        try:
            sta = db.exeQuery(cur,"ALTER TABLE `%s` CHANGE `date` `date` VARCHAR(25) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;" % each[0])
        except:
            pass


# 修改表,加主键
allname = cur.fetchall()
for each in allname:

# 需要导入模块: import DataBase [as 别名]
# 或者: from DataBase import exeQuery [as 别名]
reload(sys)
sys.setdefaultencoding('utf8')

# engine = create_engine('mysql+pymysql://tradeuser:[email protected]/py_trade?charset=utf8')
# engine = create_engine("mssql+pyodbc://sa:[email protected]")
# conn_info = 'DRIVER={SQL Server};DATABASE=%s;SERVER=%s;UID=%s;PWD=%s'% ('F10_PICK', '127.0.0.1\SQLEXPRESS', 'sa', '123456')
# conn=pyodbc.connect(conn_info)
# cur=conn.cursor()
# engine = create_engine('mysql+pymysql://tradeuser:[email protected]/py_trade1?charset=utf8')

# 取所有的股票基本面
# df = ts.get_stock_basics()
# df.to_csv('e:/s1.csv')
engine = create_engine("mssql+pyodbc://sa:[email protected]")
conn, cur = db.connDB()
cur = db.exeQuery(cur,'select * from tb_bas_stock')

# 修改表的date类型，改为varchar
allname = cur.fetchall()
for each in allname:
    if each[0] != 'stock_basics':
        print each[0]
        try:
            # my sql
            # sta = db.exeQuery(cur,"ALTER TABLE `%s` CHANGE `date` `date` VARCHAR(25) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;" % each[0])
            # sql server
            sta = db.exeQuery(cur,"ALTER TABLE [%s] alter COLUMN [date] VARCHAR(25) not null;" % each[0])
            cur.commit()
        except:
            pass

# 需要导入模块: import DataBase [as 别名]
# 或者: from DataBase import exeQuery [as 别名]
def pre_shareholder(before_datetime, startdate,topnum):
    conn, cur = db.connDB()
    # 取所有的股东人数下降最快的
    # if isIncr == False:
    #     cur = db.exeQuery(cur,'select top(%s) * from tb_shareholder' % topnum)
    # else:
    #     # 取所有的股东人数增加最快的
    #     cur = db.exeQuery(cur,'select top(%s) * from tb_shareholder_incr' % topnum)
    cur = db.exeQuery(cur,'select top(%s) * from tb_shareholder' % topnum)
    allname = cur.fetchall()
    cur.close()
    conn.close()
    df = ts.get_stock_basics()
    t_codes = []
    result ={}
    lstart = []
    llast =[]
    lmax = []
    lmin =[]
    lper =[]
    lname =[]
    lchange = []
    lrtbl = []
    ljgbl = []
    ljzd = []
    # result['bvalues'] = bvalues
    # result['avalues'] = avalues
    i = 0
    # 日期提前半年，进行比较,去除新股
    tt2 = time.strptime(before_datetime,'%Y-%m-%d')
    for each in allname:
        flag = True
        print each[0]
        # 刚刚上市的新股，不考虑
        if df.at[each[0],'timeToMarket'] != 0:
            timemarket = time.strptime(str(df.at[each[0],'timeToMarket']),'%Y%m%d')
            if timemarket > tt2:
                print each[0], 'new', timemarket
                flag = False
        else:
            print each[0], 'new'
            flag = False
        if flag == True:
            # 取历史数据
            df1 = ts.get_hist_data(each[0],start=startdate)
            if df1 is not None and len(df1) > 0:
                dmax = df1['close'].max()
                dstart = df1.loc[df1.index[-1],'close']
                dlast = df1.loc[df1.index[0],'close']
                dmin = df1['close'].min()
                dmean = df1['close'].mean()
                dper = (dmax - dstart) / dstart
                print each[0], dstart, dlast, dmax, dmin, dmean, dper,each[1]
                t_codes.append(each[0])
                lstart.append(dstart)
                llast.append(dlast)
                lmax.append(dmax)
                lmin.append(dmin)
                lper.append(dper)
                lname.append(each[1])
                lchange.append(each[3])
                lrtbl.append(each[4])
                ljgbl.append(each[5])
                ljzd.append(each[6])
            else:
                print each[0], 'stop'
    # 取指数变化，对比用
    df1 = ts.get_hist_data('sh',start=startdate)
    t_codes.append('sh')
    lname.append('sh')
    lchange.append('')
    lrtbl.append('')
    ljgbl.append('')
    ljzd.append('')
    get_date_maxmean(df1, lstart,llast,lmin,lmax,lper)
    df1 = ts.get_hist_data('sz',start=startdate)
    t_codes.append('sz')
    lname.append('sz')
    lchange.append('')
    lrtbl.append('')
    ljgbl.append('')
    ljzd.append('')
    get_date_maxmean(df1, lstart,llast,lmin,lmax,lper)
    df1 = ts.get_hist_data('hs300',start=startdate)
    t_codes.append('hs300')
    lname.append('hs300')
    lchange.append('')
    lrtbl.append('')
    ljgbl.append('')
    ljzd.append('')
    get_date_maxmean(df1, lstart,llast,lmin,lmax,lper)
    df1 = ts.get_hist_data('sz50',start=startdate)
    t_codes.append('sz50')
    lname.append('sz50')
    lchange.append('')
    lrtbl.append('')
    ljgbl.append('')
    ljzd.append('')
    get_date_maxmean(df1, lstart,llast,lmin,lmax,lper)
    df1 = ts.get_hist_data('zxb',start=startdate)
#.........这里部分代码省略.........