原生数据库查询 

# -*- 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)  # ```
posted @   冀未然  阅读(9)  评论(0)  编辑  收藏  举报
（评论功能已被禁用）
相关博文：
· SQL万能语句-经典操作
· java 数据库编程----JDBC连接Sql Server数据库
· 数据库原生查询
· MySQL
· 《MySql 学习笔记》
阅读排行：
· 分享4款.NET开源、免费、实用的商城系统
· 全程不用写代码，我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了
点击右上角即可分享
微信分享提示