本地获取jqdata的日资金量数据保存为sql数据库

使用了dataframe自动生成sql的方法，避免了繁琐的拼接sql

import os

import sys

import json

import jqdatasdk as jq

import pandas as pd

import mssql

jq.auth("xxxxxxxxxxx","xxxxxx")

ms = mssql.MSSQL(host="127.0.0.1",user="new",pwd="xxx",db="pqdata")

 

# 获取多只股票在某一日期的资金流入

dfstock=jq.get_all_securities(["stock"])

stocks = list(jq.get_all_securities(['stock']).index)

 

sqlall=""

ls=jq.get_trade_days(start_date="2019-3-19", end_date="2019-3-19", count=None)

i=0

for d in ls:

i=i+1

df=jq.get_money_flow(stocks, end_date=d, count=1)

sql=ms.getsqlbydf(df,"t_zijinliu")

ms.ExecNonQuery(sql)

 

sqlserver操作类

import pymssql

 

class MSSQL:

def __init__(self,host,user,pwd,db):

self.host = host

self.user = user

self.pwd = pwd

self.db = db

 

def getsqlbydf(self,df,tablename):

ls=df.columns.values.tolist()

sql="insert into " + tablename + "("

for c in ls:

sql=sql+c + ","

sql=sql.rstrip(',') + ") values ("

sqlall=""

for index,row1 in df.iterrows():

sql1=sql

for c in ls:

sql1=sql1 + "'" + str(row1[c]) + "',"

sql1=sql1.rstrip(',') + ")"

sqlall=sqlall + sql1 + ";"

return sqlall

def __GetConnect(self):

if not self.db:

raise(NameError,"not conn")

self.conn = pymssql.connect(host=self.host,user=self.user,password=self.pwd,database=self.db,charset="utf8")

cur = self.conn.cursor()

if not cur:

raise(NameError,"conn fail")

else:

return cur

 

def ExecQuery(self,sql):

cur = self.__GetConnect()

cur.execute(sql)

resList = cur.fetchall()

 

self.conn.close()

return resList

 

def ExecNonQuery(self,sql):

cur = self.__GetConnect()

cur.execute(sql)

self.conn.commit()

self.conn.close()