如何将本地获取jqdata的数据保存为sql数据库文件呢?
简单粗暴直接上代码,使用的是python自带的sqlite3数据库,比较轻便!
在这import jqdatasdk as jq
from sqlalchemy import create_engine #sqlalchemy是Python自带的与数据库联结的包,导入创建数据库联结的函数
import sqlite3 #导入python自带数据库sqlite3
import pandas as pd
jq.auth('admin','passpords')
ADDR = 'sqlite:///jqstockdata.db' # 使用sqlite,当前目录的jqstockdata.db作为数据库文件
engine = create_engine(ADDR) #创建jqstockdata.db文件
Stocks = list(jq.get_all_securities(['stock']).index) #获取所有股票代码
connect=sqlite3.connect('jqStockdata.db') #与数据库建立链接
def save(stocks): #构建函数
print("save code:",stocks) #打印数据保存成功的股票代码
try:
df = jq.get_price(stocks, start_date='start_date', end_date='end_data',frequency='1d',skip_paused=True,fq='pre') #获取股票数据
name =stocks #以股票代码为数据库表名
df.to_sql(name, conn2, if_exists='append') #将数据写入数据库
except:
print(code, " save failed") #打印保存失败的股票代码
for i in range(0, len(Stocks)):
save(Stocks[i])里插入代码片
数据保存好之后我们如何获取数据呢?也很简单,请看下面代码
mport sqlite3
import pandas as pd
conn=sqlite3.connect('jqstockdata.db')
df=pd.read_sql('select * from "{}"'.format('股票代码'),index_col = 'index',con= conn )
df.index= pd.to_datetime(df.index,format='%Y-%m-%d') #改变一下读取后的数据日期格式
具体效果我用研究模块直接展示一下
rom sqlalchemy import create_engine #注1 sqlalchemy是Python自带的与数据库联结的包,导入创建数据库联结的函数
import sqlite3
import pandas as pd
# import jqdatasdk as jq本地获取
#jq.auth('admin','passpords')
ADDR = 'sqlite:///jqstockdata.db' # 使用sqlite,当前目录的stock.db作为数据库文件
engine = create_engine(ADDR)
Stocks = list(get_all_securities(['stock']).index)
conn=sqlite3.connect('jqstockdata.db')
def save(stocks):
print("save code:",stocks)
try:
df = get_price(stocks, start_date='2018-10-29', end_date='2018-11-02',frequency='1d',skip_paused=True,fq='pre')
#本地获取改为jq.get_price
name =stocks
df.to_sql(name, conn, if_exists='append')
except:
print(stocks, " save failed")
for i in range(0, 15):
save(Stocks[i])
save code: 000001.XSHE
save code: 000002.XSHE
save code: 000004.XSHE
save code: 000005.XSHE
save code: 000006.XSHE
save code: 000007.XSHE
save code: 000008.XSHE
save code: 000009.XSHE
save code: 000010.XSHE
save code: 000011.XSHE
save code: 000012.XSHE
save code: 000014.XSHE
save code: 000016.XSHE
save code: 000017.XSHE
save code: 000018.XSHE
conn=sqlite3.connect('jqstockdata.db')
df=pd.read_sql('select * from "{}"'.format('000001.XSHE'),index_col = 'index',con= conn )
df.index= pd.to_datetime(df.index,format='%Y-%m-%d')
print(df)
open close high low volume money
index
2018-10-29 11.20 10.75 11.24 10.62 159162878.0 1.725932e+09
2018-10-30 10.78 10.90 11.08 10.73 150182786.0 1.641372e+09
2018-10-31 10.95 10.91 10.99 10.75 115268379.0 1.252157e+09
2018-11-01 10.99 10.83 11.05 10.76 154277632.0 1.679443e+09
2018-11-02 11.04 11.09 11.16 10.83 221292287.0 2.439179e+09