mysql数据库操作

连接mysql

from sqlalchemy import create_engine
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import pymssql
from scipy.interpolate import interp1d

from datetime import timedelta
#正常显示画图时出现的中文和负号
from pylab import mpl
mpl.rcParams['font.sans-serif'] = ['SimHei']
mpl.rcParams['axes.unicode_minus'] =False
#显示所有列
pd.set_option('display.max_columns',None)
#显示所有行
pd.set_option('display.max_rows',None)


#连接数据库
conn = pymssql.connect(host = '.',user ='sa',password = 'test',database = 'databasename',charset ='utf8')
engine = create_engine('mssql+pymssql://sa:test@127.0.0.1/databasename')

数据存入

#保存数据
df.to_sql(name = 'sheetname',con=engine,if_exists='append',index=False)

获取数据

#获取数据
sql = 'select * from stock_data '
df0 = pd.read_sql(sql,conn)

tushare获取股票数据并存入

#获取数据
def get_data(code,start,end):
    #设置token
    token=''
    pro = ts.pro_api(token)
    df = pro.daily(ts_code =code,adj='qfq',start_date=start,end_date=end)
    return df

#上传数据
def insert_sql(data,db_name,if_exists='append'):
    try:
        data.to_sql(db_name,engine,index=False,if_exists= if_exists)
    except:
        pass

更新数据

#更新数据
def updata_sql(start,end,db_name):
    for code in get_coe():
        data=get_data(code,start,end)
        insert_sql(data,db_name)
    print(f'{start}:{end}期间数据已成功更新')

数据可视化 

#数据可视化
def plot_data(df):
    count_ = data_info.groupby('trade_date')['ts_code'].count()
    attr = count_.index
    v1 = count_.values
    bar = Bar()
    bar.add('',at,v1,is_splitline_show=False,linewidth=2)
    return bar

筛选股票池

#筛选数据.制作股票池
def get_new_code(date):
    df0  = pro.stock_basic(exchange = '',list_status='L')
    df1 =  pro.daily(trade_date = date)
    df = pd.merge(df0,df1,on='ts_code')
    #print(df.head())
    df.info()
#剔除2017年以后上市的新股   
    df = df[df['list_date'].apply(int)<20200101]
    #剔除st
    df = df[-df['name'].apply(lambda x:x.startswith('*ST'))]
    #剔除动态市盈率为负
    df = df[df.change>0]
    codes = df.ts_code.values
    return codes

查找指定股票

def find_stock(date):
    print(get_new_code(date))
    engine = create_engine('mssql+pymssql://sa:test@127.0.0.1/pos')
    df_all_data=pd.read_sql('stock_data',engine)
    f_code =[]
    for code in  get_new_code(date):
        try:
            data = df_all_data.loc[df_all_data.ts_code==code].copy()
            data.index= pd.to_datetime(data.trade_date)
            data=data.sort_index()
            data['ma_20'] = ta.MA(data.close,timeperiod=20)
            if data.iloc[-1]['close'] > data.iloc[-1]['ma_20']:
                print(code)
                f_code.append(code)
        except:
            pass
    return f_code