使用了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()
作者:nittystone
来源:CSDN
原文:https://blog.csdn.net/nittystone/article/details/88686578
版权声明:本文为博主原创文章,转载请附上博文链接!