使用了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 i* "   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()