【pyodbc】使用Python连接SQL Server查询处理数据

> 以前可以使用[pymssql](https://github.com/pymssql/pymssql)连接微软的SQL Server，但新版的Python不再支持，项目也不再维护。所以转而使用[pyodbc](https://github.com/mkleehammer/pyodbc) 首先MS SQL Server设置可以远程连接，然后分别在Windows和Linux进行连接，读取数据进行处理，最终定时将处理的数据发送到QQ群。 # SQL Server允许远程连接配置 ## 创建只读用户    ## 允许远程连接到此服务器 右键---属性  ## 设置TCP/IP   确定“微软SQL服务器网络选项协议”中的TCP/IP协议对服务器来说是有效的。再次检查是否已经可以执行远程链接。 设置监听IP为本机的  ## 防火墙允许 若“错误提醒对话框”依然弹出，我们需要进一步检查SQL服务器防火墙选项。 ## 允许RemoteAccessEnabled 右击数据库选择“方面”，将“RemoteAccessEnabled”属性设为“True”，点“确定”  ## 工具测试连接  # Windows连接 系统版本为Windows10-1909 ## 如何使用ODBC驱动 https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQL-Server-from-Windows Microsoft为SQL Server编写并分发了多个ODBC驱动程序： - {SQL Server} - released with SQL Server 2000 - {SQL Native Client} - released with SQL Server 2005 (also known as version 9.0) - {SQL Server Native Client 10.0} - released with SQL Server 2008 - {SQL Server Native Client 11.0} - released with SQL Server 2012 - {ODBC Driver 11 for SQL Server} - supports SQL Server 2005 through 2014 - {ODBC Driver 13 for SQL Server} - supports SQL Server 2005 through 2016 - {ODBC Driver 13.1 for SQL Server} - supports SQL Server 2008 through 2016 - {ODBC Driver 17 for SQL Server} - supports SQL Server 2008 through 2019 “SQL Server Native Client ...”和以前的驱动程序被弃用，不应该用于新的开发。 ## 安装pyodbc ```bash pip install pyodbc ``` ## 安装ODBC驱动程序 下载 [SQL 服务器的 Microsoft ODBC 驱动程序](https://docs.microsoft.com/en-us/sql/connect/python/pyodbc/step-1-configure-development-environment-for-pyodbc-python-development?view=sql-server-ver15) 安装完后查看驱动 ```python >>> import pyodbc >>> [x for x in pyodbc.drivers()] ['SQL Server', 'Microsoft Access Driver (*.mdb, *.accdb)', 'Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)', 'Microsoft Access Text Driver (*.txt, *.csv)', 'SQL Server Native Client 10.0', 'ODBC Driver 17 for SQL Server'] ``` ## Windows连接SQL Server Python Shell中测试 ```python import pyodbc driver = "{ODBC Driver 17 for SQL Server}" server = "10.168.2.252" # 数据库服务器名称或IP username = "donghuan" # 用户名 password = "***password***" # 密码 database = "myserver" # 数据库名称 port = 1433 # 连接到SQL Server conn = pyodbc.connect('DRIVER={};SERVER={};DATABASE={};UID={};PWD={}'.format(driver, server, database, username, password)) cursor = conn.cursor() cursor.execute("SELECT insname, insunit, hvalue, hvaluen FROM myserver.dbo.JfDevIns WHERE insname LIKE '%6楼机房%'") # 读取所有结果 all_data = cursor.fetchall() print(all_data) # 一行一行读取 # row = cursor.fetchone() # print(row) # while row: # row = cursor.fetchone() # print(row) # 关闭连接 conn.close() ``` # Linux连接 系统版本为Debian8 https://github.com/mkleehammer/pyodbc/wiki/Install https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQL-Server-from-Linux ## 安装ODBC驱动 微软提供了安装到各种基于Linux/UNIX的平台最新的ODBC驱动程序的说明，例如[Debian安装](https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15#debian17) ```bash (ITNest) root@PxeCtrlSys:/home/user/software# curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - # Debian8安装 (ITNest) root@PxeCtrlSys:/home/user/software# curl https://packages.microsoft.com/config/debian/8/prod.list > /etc/apt/sources.list.d/mssql-release.list (ITNest) root@PxeCtrlSys:/home/user/software# apt-get update (ITNest) root@PxeCtrlSys:/home/user/software# ACCEPT_EULA=Y apt-get install msodbcsql17 # 可选：用于bcp和sqlcmd (ITNest) root@PxeCtrlSys:/home/user/software# ACCEPT_EULA=Y apt-get install mssql-tools (ITNest) root@PxeCtrlSys:/home/user/software# echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile (ITNest) root@PxeCtrlSys:/home/user/software# echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc (ITNest) root@PxeCtrlSys:/home/user/software# source ~/.bashrc # 可选：用于unixODBC开发头文件（安装太慢了） (ITNest) root@PxeCtrlSys:/home/user/software# apt-get install unixodbc-dev # 可选：用于debian-slim发行版的kerberos库 (ITNest) root@PxeCtrlSys:/home/user/software# apt-get install libgssapi-krb5-2 ``` ## 驱动程序版本查看 驱动程序版本可以通过检查系统中的`odbcinst.ini`文件找到，该文件是 Microsoft 的 ODBC 驱动程序安装程序注册自己的位置。 ```bash (ITNest) root@PxeCtrlSys:/home/user/software# find / -name odbcinst.ini /usr/local/etc/odbcinst.ini /usr/lib/hive/lib/native/hiveodbc/Setup/odbcinst.ini /opt/microsoft/msodbcsql17/etc/odbcinst.ini /etc/odbcinst.ini (ITNest) root@PxeCtrlSys:/home/user/software# cat /etc/odbcinst.ini (ITNest) root@PxeCtrlSys:/home/user/software# cat /usr/local/etc/odbcinst.ini [ODBC Driver 17 for SQL Server] Description=Microsoft ODBC Driver 17 for SQL Server Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.5.so.2.1 UsageCount=1 ``` ## Linux使用DSN连接SQL Server ### 创建DSN配置文件 创建用于将[ODBC DSN（](https://support.microsoft.com/en-ca/help/966849/what-is-a-dsn-data-source-name)数据源名称）定义到数据库的临时文本文件，如下所示： ```bash (ITNest) root@PxeCtrlSys:/home/user/software# vim odbc_dsn.conf # 添加下面的内容 [MSSQLServerDatabase] Driver = ODBC Driver 17 for SQL Server Description = Connect to my SQL Server instance Trace = No Server = 10.168.2.252 ``` 如果不使用默认的端口1433，需要指定，使用`,`分隔 ```bash Server = 10.168.2.252,1433 ``` 在该文件中，按照上面的格式写，除非具有正确的驱动程序版本。在上面已经通过查看`odbcinst.ini`确定驱动程序版本。 > ！使用驱动程序名称与显示在方括号内的应完全相同。 ### 创建DSN 保存临时配置文件`odbc_dsn.conf`后，可以使用以下命令创建一个“System DSN”： **创建System DSN** ```bash # 在 /etc/odbc.ini 中注册SQL Server数据库DSN信息 (ITNest) root@PxeCtrlSys:/home/user/software# odbcinst -i -s -f odbc_dsn.conf -l # 检查DSN安装：应该包含一个名为[MSSQLServerDatabase]的部分 (ITNest) root@PxeCtrlSys:/home/user/software# cat /etc/odbc.ini # 如果没有内容就需要另外的方式 ``` **创建User DSN** ```bash # 在 ~/.odbc.ini 中注册SQL Server数据库DSN信息 (ITNest) root@PxeCtrlSys:/home/user/software# odbcinst -i -s -f odbc_dsn.conf -h # 检查DSN安装 (ITNest) root@PxeCtrlSys:/home/user/software# cat ~/.odbc.ini [MSSQLServerDatabase] Driver=ODBC Driver 17 for SQL Server Description=Connect to my SQL Server instance Trace=No Server=10.168.2.252 ``` 出现`[MSSQLServerDatabase]`的配置，表明生成成功 ### 连接SQL Server 使用python连接 ```python import pyodbc username = "donghuan" # 用户名 password = "***password***" # 密码 database = "myserver" # 数据库名称 port = 1433 conn = pyodbc.connect('DSN=MSSQLServerDatabase;UID={};PWD={};DATABASE={}'.format(username, password, database)) cursor = conn.cursor() cursor.execute("SELECT insname, insunit, hvalue, hvaluen FROM myserver.dbo.JfDevIns WHERE insname LIKE '%6楼机房%'") all_data = cursor.fetchall() print(all_data) conn.close() ``` ## Linux连接SQL Server 同“Windows连接SQL Server”一样，只要安装了`ODBC Driver 17 for SQL Server`都可以使用 ```python import pyodbc driver = "{ODBC Driver 17 for SQL Server}" server = "10.168.2.252" # 数据库服务器名称或IP username = "donghuan" # 用户名 password = "***password***" # 密码 database = "myserver" # 数据库名称 port = 1433 # 连接到SQL Server conn = pyodbc.connect('DRIVER={};SERVER={};DATABASE={};UID={};PWD={}'.format(driver, server, database, username, password)) cursor = conn.cursor() cursor.execute("SELECT insname, insunit, hvalue, hvaluen FROM myserver.dbo.JfDevIns WHERE insname LIKE '%6楼机房%'") # 读取所有结果 all_data = cursor.fetchall() print(all_data) # 关闭连接 conn.close() ``` # 定时监测动环温度 ## Python实现QQ发送 创建的名称为：`dh_sys_msg.py` ```bash """ 动环系统定义推送消息， 使用Linux定时任务，从动环主机上的SQL Server获取数据，取平均值发送QQ消息 """ import pyodbc class DonghuanAvg(object): def __init__(self, location): driver = "{ODBC Driver 17 for SQL Server}" server = "10.168.2.252" # 数据库服务器名称或IP username = "donghuan" # 用户名 password = "***password***" # 密码 database = "myserver" # 数据库名称 port = 1433 self.conn = pyodbc.connect('DRIVER={};SERVER={};DATABASE={};UID={};PWD={}'.format(driver, server, database, username, password)) self.cursor = self.conn.cursor() self.location = location def avg_t_and_h(self): self.cursor.execute(f""" SELECT insname, insunit, hvalue, hvaluen FROM myserver.dbo.JfDevIns WHERE insname LIKE '%{self.location}%' """) all_data = self.cursor.fetchall() # print(all_data) temperature = [] humidity = [] for data in all_data: if 0 < float(data[2]) < 100: # 处理检测的异常值，比如1楼机房温度1偶尔为3328.00℃ if '温度' in data[0]: temperature.append(data) if '湿度' in data[0]: humidity.append(data) # print(temperature, humidity) if len(temperature) != 0: avg_temperature = sum(float(v[2]) for v in temperature) / len(temperature) else: avg_temperature = 0 if len(humidity) != 0: avg_humidity = sum(float(v[2]) for v in humidity) / len(humidity) else: avg_humidity = 0 # print(avg_temperature, avg_humidity) self.conn.close() return round(avg_temperature, 1), round(avg_humidity, 1) def format_print(self): avg_temperature, avg_humidity = self.avg_t_and_h() return "{}平均温度：{}℃，平均湿度：{}%".format(self.location, avg_temperature, avg_humidity) # 运行时，向QQ群发送消息 def send_lyf_qq_group(msg): import requests try: requests.get('http://***qqbot_server_ip_port**/send_group_msg?group_id=***qq_group_id**', params={'message': str(msg).strip()}, timeout=1) except: pass if __name__ == '__main__': locations = [str(num) + "楼机房" for num in range(1, 7)] msg = '' for location in locations: obj = DonghuanAvg(location) msg += obj.format_print() + '\n' print(msg) send_lyf_qq_group(msg) ``` ## Linux定时消息 ```bash (ITNest) root@PxeCtrlSys:/home/user/software# crontab -e # 添加 # 动环系统顶是消息推送，每天10:10发送温湿度QQ消息 10 10 * * * /root/.pyenv/versions/ITNest/bin/python /home/user/ITNest/dh_sys_msg.py 2>&1 ``` 重启加载或重启cron ```bash (ITNest) root@PxeCtrlSys:/home/user/software# /etc/init.d/cron reload [ ok ] Reloading configuration files for periodic command scheduler: cron. ```

明人不说暗话 领红包就是支持！！！ 来都来了，再到页面底部看下呗~ ↓ ↓ ↓

本文地址： http://blog.starmeow.cn/detail/f718499c1c8cef6730f9fd03c8125cab/

很赞哦！ (0)