【tushare+python」自动更新股票信息的python代码

周日，终于忙完了本周的计划，突然想重新看一下我的python代码，上次有一个地方没有完成：实现从数据库中最后日期开始的增量更新。以至于每次写入都会有重复，不得不使用SQL的distinct语法去重。

1、python解决方案

刚才研究了一下，增加了两个函数：

def get_max_min_date(ts_code,table)

这个函数用来提取某个代码在对应数据表中的最大日期、最小日期

def dateadd(string,int=1):

这个函数用来把上述函数找到的最大日期增加1天，并改为标准格式 这样我就可以从数据库中的最大值的次日开始更新了，更新到today即可。

2、增加了一个python循环

只要是我的自定义列表的股票，都可以自动追加日期。 如果是新股票怎么办，通过一个判断将开始日期设置为2000年1月1日 if max_date != None: start_date=function_stock.dateadd(max_date) else: start_date=’20010101′ end_date = date.today().strftime(‘%Y%m%d’)

3、tableau分析

这是我的目的

附件：程序正文

import tushare as ts
from sqlalchemy import create_engine
import pandas as pd
import numpy as np
import pymysql
from datetime import date
import datetime
import function_stock
import pymysql
# 设置token
token = 'fb74de3b380**********************'
pro = ts.pro_api(token)
database='stock'

mystock= ['002589.SZ','000651.SZ','000241.SZ','000241.SZ']

#获得股票在数据库表的起始日期
for ts_code in mystock:
print('ts_code= %s'%(ts_code))
returns=function_stock.get_max_min_date(ts_code,table='s_daily')
# ts_code = returns[0]
max_date = returns[1]
min_date = returns[2]
# print("ts_code=%s,max_date=%s,min_date=%s, and next" % \
# (ts_code, max_date, min_date))
if max_date != None:
start_date=function_stock.dateadd(max_date)
# print(end_date)
else:
start_date='20010101'
end_date = date.today().strftime('%Y%m%d')
print('start date = %s, and end date = %s' %(start_date,end_date))
df = pro.query('daily', ts_code=ts_code, start_date=start_date, end_date=end_date)
print(ts_code,'daily',df.head(10))
function_stock.to_mysql(df,table='s_daily',database=database,type='append')
#
# #指定股票的大单交易
df = pro.moneyflow(ts_code=ts_code, start_date=start_date, end_date=end_date) # today =2020-5-8
print(ts_code,'monetyflow',df.head(10))
# # print(df)
function_stock.to_mysql(df,table='s_bigmoneyflow',database=database,type='append')
#
df = pro.daily_basic(ts_code=ts_code, start_date=start_date, end_date=end_date)
# print(ts_code,'daily_basic',df.head(10))
function_stock.to_mysql(df,table='s_daily_basic',database=database,type='append')

print(mystock)
# 获得交易股票清单，已经写入。
# data = pro.query('stock_basic', exchange='', list_status='L',
# fields='ts_code,symbol,name,area,industry,fullname,market,exchange,list_date,is_hs')
# print(data.head(5))
# function_stock.to_mysql(data,table='stock_basic',database=database,type='append')

另一个函数库代码

from sqlalchemy import create_engine
import datetime
import time
import pymysql
db = pymysql.connect("localhost", "root", "admin123", "stock")
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# SQL 查询语句
sql = "SELECT DISTINCT ts_code,MAX(trade_date),MIN(trade_date) FROM %s where ts_code = %s %\
#                   (table,ts_code)"
today = datetime.date.today()
def dateadd(string,int=1):
    date = datetime.datetime.strptime(string, '%Y%m%d')  # string  —— date
    # print(type(date))
    # int == 1
    next_start_date = (date + datetime.timedelta(days=int)).strftime('%Y%m%d')
    end_date = date.today().strftime('%Y%m%d')
    print('the next search date begin from %s to %s' % (next_start_date, end_date))
    # print(next_start_date,end_date)
    return next_start_date


def get_max_min_date(ts_code,table):
    db = pymysql.connect("localhost", "root", "admin123", "stock")
    #使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()
    print('open mysql')
    # SQL 查询语句
    sql = "SELECT DISTINCT ts_code,MAX(trade_date),MIN(trade_date) FROM stock.%s where ts_code = '%s'" % (table,ts_code)
    # print(sql)
    try:
        # 执行SQL语句
        cursor.execute(sql)
        # 获取所有记录列表
        results = cursor.fetchall()
        for row in results:
            ts_code= row[0]
            max_date = row[1]
            min_date = row[2]
            # 打印结果
            # print(results)
            print("ts_code=%s,max_date=%s,min_date=%s" %\
                  (ts_code,max_date, min_date))
        return ts_code,max_date, min_date
    except:
        print("Error: unable to fecth data")


def to_mysql(df, table, database,type):
    # 填写链接信息
    engine = create_engine("mysql+pymysql://root:admin123@127.0.01:3306/"+database)
    # ?+"?charset=utf8"
    # 开始写入
    df.to_sql(name=table, con=engine, if_exists=type, index=False, index_label=False)
    print(df.shape,'write done')


def to_db(df, table, database,type):
    alchemyEngine = create_engine('postgresql+psycopg2://postgres:admin@127.0.0.1/' + database, pool_recycle=3600);
    postgreSQLConnection = alchemyEngine.connect();
    print("       Opened database successfully")
    try:
        frame = df.to_sql(table, postgreSQLConnection, if_exists=type);
    except ValueError as vx:
        print(vx)
    except Exception as ex:
        print(ex)
    else:
        print(len(df), "     PostgreSQL Table %s has been created successfully." % table);
    finally:
        postgreSQLConnection.close();

日期：20200906 喜乐君