周日,终于忙完了本周的计划,突然想重新看一下我的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 喜乐君