# -*- coding:utf-8 -*-
# @Time：2022/1/11 9:06
# @Author：
# @File:pyodbc_op_mssql.py
# @Sofeware :PyCharm
#!/usr/bin/env python
# -*- coding: utf-8 -*-
'''
sm 中 s 表示 select 查询语句 ; m 表示 data manipulation language 数据操作语言 即 insert、 update 或 delete
一、若 sm=0：select some 返回字典 'SELECT top 3 * FROM F_T_DZ order by 交易日期 desc '
如：[{'序号': 'DZ2021123000001', '操作主体': '浙江中石油', '期货公司': '东证期货', '交易日期': '20211230', '交易所': '上期所', '品种': 'bu', '合约': 'bu2206', '买卖': 'S', '投机保值': 'S', '成交价': 3290.0, '手数': 23.0, '成交额': 756700.0, '开平': 'O', '手续费': 76.47, '平仓盈亏': 0.0, '权利金收支': '0', '成交序号': '347184'},
{'序号': 'DZ2021123000002', '操作主体': '浙江中石油', '期货公司': '东证期货', '交易日期': '20211230', '交易所': '上期所', '品种': 'bu', '合约': 'bu2206', '买卖': 'S', '投机保值': 'S', '成交价': 3290.0, '手数': 63.0, '成交额': 2072700.0, '开平': 'O', '手续费': 209.47, '平仓盈亏': 0.0, '权利金收支': '0', '成交序号': '347185'},
{'序号': 'DZ2021123000003', '操作主体': '浙江中石油', '期货公司': '东证期货', '交易日期': '20211230', '交易所': '上期所', '品种': 'bu', '合约': 'bu2206', '买卖': 'S', '投机保值': 'S', '成交价': 3290.0, '手数': 1.0, '成交额': 32900.0, '开平': 'O', '手续费': 3.32, '平仓盈亏': 0.0, '权利金收支': '0', '成交序号': '347186'}]
二、若 sm=1：select 返回元组
如：[('DZ2021123000001', '浙江中石油', '东证期货', '20211230', '上期所', 'bu', 'bu2206', 'S', 'S', 3290.0, 23.0, 756700.0, 'O', 76.47, 0.0, '0', '347184')]
三、若 sm=2: select structure 返回 数据表字段名
如：['序号', '操作主体', '期货公司', '交易日期', '交易所', '品种', '合约', '买卖', '投机保值', '成交价', '手数', '成交额', '开平', '手续费', '平仓盈亏', '权利金收支', '成交序号']
四、若 sm=3: select 返回 DataFrame
如：
序号 操作主体 期货公司 交易日期 交易所 ... 开平 手续费 平仓盈亏 权利金收支 成交序号
0 DZ2021123000001 浙江中石油 东证期货 20211230 上期所 ... O 76.47 0.0 0 347184
[1 rows x 17 columns]
五、# 若sm=8：(insert、 update 或 delete） 返回变更的记录条数
如果是 DataFrame 数据 要存入mssql 数据库 按如下方式 拼接 sql 语句 ：
for row in range(df.shape[0]):
sql = ' insert into F_S(tradedate, exchange, contract, settlementprice) values('
for col in range(df.shape[1]):
if isinstance(df.iloc[row, col], float):
sql = sql + str(df.iloc[row, col]) + ',' 数值型 字段转换 成 字符 才可链接
else:
sql = sql + '\'' + df.iloc[row, col].strip() + '\',' 字符型 加引号
sql = sql[0:-1] + ')'
count = excel_query(sql, 8) # 将 DataFrame 终内容 存入数据库
六、# 若sm=9：获取数据库服务器中 用户数据表名称 及其字段名称
如：
[['Trans_DZ', ['BS', 'DeptName', 'Exchange', 'Fee', 'FutureCompany', 'ID', 'Instrument', 'Lots', 'OC', 'Product', 'RealizedPL', 'TransDate', 'TransPrice', 'Turnover']],
['F_F_DZ', ['操作主体', '持仓保证金', '当日结存', '盯市盈亏', '交割货款', '可用资金', '平仓盈亏', '期初结存', '期货公司', '期间出金', '期间入金', '期末权益', '日期', '手续费', '应追加保证金']],
['F_T_DZ', ['操作主体', '成交额', '成交价', '成交序号', '合约', '交易日期', '交易所', '开平', '买卖', '品种', '平仓盈亏', '期货公司', '权利金收支', '手数', '手续费', '投机保值', '序号']],
['F_P_DZ', ['保证金占用', '操作主体', '持仓盈亏', '盯市盈亏', '合约', '交易所', '今结算价', '买价', '买手', '卖价', '卖手', '品种', '期货公司', '日期', '投机保值', '昨结算价']]]
'''
import pandas as pd
import pyodbc
from share import SI
pd.set_option('display.width', None)
pd.set_option('display.max_rows', None)
pd.set_option('display.max_columns', None)
pd.set_option('display.max_colwidth', None)
pd.set_option('display.float_format', '{:,.2f}'.format)
class MSSQL:
"""
封装pyodbc
"""
def __init__(self, host, user, pwd, db='master', charset='utf8'):
self._host = host.strip().replace('\'', '')
self._user = user.strip().replace('\'', '')
self._pwd = pwd.strip().replace('\'', '')
self._db = db.strip().replace('\'', '')
self._charset = charset
# print(host, user, self._pwd, db)
def __get_connect(self):
"""
得到连接信息
返回: conn.cursor()
"""
if not self._db:
# raise(NameError, "没有设置数据库信息")
SI.alarmMessageBox("没有设置数据库信息")
exit()
conn_info = 'DRIVER={SQL Server};DATABASE=%s;SERVER=%s;UID=%s;PWD=%s' % (self._db, self._host, self._user, self._pwd)
self.conn = pyodbc.connect(conn_info, charset=self._charset)
# print(self.conn)
cur = self.conn.cursor()
if not cur:
SI.alarmMessageBox("连接数据库失败")
exit()
else:
return cur
def __exec_query(self, sql):
"""
执行查询语句
返回的是一个包含tuple的list，list的元素是记录行，tuple的元素是每行记录的字段
调用示例：
ms = MSSQL(host="localhost",user="sa",pwd="123456",db="PythonWeiboStatistics")
resList = ms.ExecQuery("SELECT id,NickName FROM WeiBoUser")
for (id,NickName) in resList:
print str(id),NickName
"""
cur = self.__get_connect()
cur.execute(sql)
resList = cur.fetchall()
#查询完毕后必须关闭连接
self.conn.close()
return resList
def exec_query_tuple(self, sql): # 返回数据表 元组
"""结果集以元组返回"""
return self.__exec_query(sql)
def exec_query_structurelist(self, sql): # 返回数据表 各字段名 列表
cur = self.__get_connect()
cur.execute(sql)
title = [i[0] for i in cur.description]
return title
def exec_query_dict(self, sql): # 返回数据表 字典
result = []
for row in self.__exec_query(sql):
result.append(dict([(desc[0], row[index]) for index, desc in enumerate(row.cursor_description)]))
return result
def get_DataFrame(self, sql): # 返回数据表 DataFrame
cur = self.__get_connect()
# print(cur)
''' 方法1：'''
df = pd.read_sql(sql, self.conn)
''' 方法2：'''
# cur.execute(sql)
# data = cur.fetchall() # self.conn
# columnsDes = cur.description # 获取连接对象的描述信息
# columnsNames = [columnsDes[i][0] for i in range(len(columnsDes))]
# df = pd.DataFrame([list(i) for i in data], colunms=columnsNames)
return df
def get_systable(self):
cur = self.__get_connect()
cur.execute("select name from sysobjects where xtype='U'") # 执行sql语句,获取数据库中的用户表表名, 及其字段名称
rowList = cur.fetchall()
tableList = []
for row in rowList:
tableList.append(row[0])
# print('tableList(%d):\n%s\n' % (len(tableList), pprint.pformat(tableList, indent=4)))
tablefieldList = []
for tabName in tableList:
# print('table %s ...' % tabName)
sql = "select name from syscolumns where id = object_id('%s')"
sql = sql % (tabName)
cur.execute(sql)
rowList = cur.fetchall()
fieldList = list()
for row in rowList:
fieldList.append(row[0])
tablefieldList.append([tabName, fieldList])
return tablefieldList
def exec_DML(self, sql):
"""
执行非查询语句
调用示例：
cur = self.__GetConnect()
cur.execute(sql)
self.conn.commit()
self.conn.close()
如果 sql 是 存储过程执行方法 ，没有返回结果
cur.execute(’EXEC P_PROCDUREName param1,param2,%D,%s' %(p1_int,p2_str))
print(cur.rowcount) #可以得到存储过程影响的行数
如果有返回结果集，需要添加 set nocount on , 也可以在存储过程中加入
rows = cur.execute('set nocount on; EXEC P_PROCDUREName param1,param2,%D,%s' %(p1_int,p2_str)).fetchall()
print(cur.rowcount, rows)
无论可种方式，都要在最后才
cur.commit()
"""
cur = self.__get_connect()
try:
rows = cur.execute(sql).rowcount
self.conn.commit()
return rows
except Exception as e:
print(e)
return None
self.conn.close()
# def dftomssql(self, df, tablename): # 将 DataFrame 存入数据表
# address = 'mssql+pymssql://' + self._user + ':' + self._pwd + '@' + self._host + '/' + self._db
# engine = create_engine(address, connect_args={'charset': 'utf8'}, echo=True) # 初始化数据库连接
# if df.shape[0] > 0:
# print(" running Write to sqlserver...")
# df.to_sql(tablename, engine, if_exists='append', index=False)
# print("Write to sqlserver successfully!")
# if __name__ == '__main__':
def mssql_query(sql, sm):
# 以下 两种方法 都可以读到 文本文件 的 内容
# print(SI.server, SI.dbuser, SI.psw, SI.database)
# exit()
conn = MSSQL(SI.server, SI.dbuser, SI.psw, SI.database, 'GBK')
# sql = 'SELECT top 1 holddate FROM F_SP order by holddate desc '
# sql = ''' insert into F_T_DZ(序号, 操作主体, 期货公司, 交易日期, 交易所, 品种, 合约, 买卖, 投机保值, 成交价, 手数, 成交额, 开平, 手续费, 平仓盈亏, 权利金收支, 成交序号)
# # values('DZ2021123100001', '浙江中石油','东证期货', '20211231', '上期所', 'bu', 'bu2206', 'S', 'S', 3500.0, 30.0, 1050000, 'O', 76.47, 100, 0.0 ,'347185') '''
if sm == 0:
return conn.exec_query_dict(sql)
elif sm == 1:
return conn.exec_query_tuple(sql)
elif sm == 2:
return conn.exec_query_structurelist(sql)
elif sm == 3:
return conn.get_DataFrame(sql)
# print(conn.get_DataFrame(sql))
elif sm == 8:
return conn.exec_DML(sql)
elif sm == 9:
return conn.get_systable()
if __name__ == '__main__':
# df = mssql_query('''select * from F_TF where tradedate like '2022-03%' ''', 3)
# print(df.head(500))
# -*- coding: utf-8 -*-
import os
out = os.system('telnet 127.0.0.1 1433') # 25端口号
print(out) # 输出进程
# out = os.system('tasklist|findstr "3316"') # 3316进是程
# print(out) # 输出程序名字
# out = os.system('taskkill /f /t /im MESMTPC.exe') # MESMTPC.exe程序名字
# print(out) # ```
浙公网安备 33010602011771号