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

作者: 吾星喵 | 来源:发表于2020-04-02 22:07 被阅读0次

更多内容请点击 我的博客 查看，欢迎来访。

以前可以使用pymssql连接微软的SQL Server，但新版的Python不再支持，项目也不再维护。所以转而使用pyodbc

首先MS SQL Server设置可以远程连接，然后分别在Windows和Linux进行连接，读取数据进行处理，最终定时将处理的数据发送到QQ群。

SQL Server允许远程连接配置

创建只读用户

BLOG_20200402_220015_39

BLOG_20200402_220020_55

BLOG_20200402_220026_79

允许远程连接到此服务器

右键---属性

BLOG_20200402_220033_76

设置TCP/IP

BLOG_20200402_220040_30

BLOG_20200402_220046_70

确定“微软SQL服务器网络选项协议”中的TCP/IP协议对服务器来说是有效的。再次检查是否已经可以执行远程链接。

设置监听IP为本机的

BLOG_20200402_220056_14

防火墙允许

若“错误提醒对话框”依然弹出，我们需要进一步检查SQL服务器防火墙选项。

允许RemoteAccessEnabled

右击数据库选择“方面”，将“RemoteAccessEnabled”属性设为“True”，点“确定”

BLOG_20200402_220106_43

工具测试连接

BLOG_20200402_220115_98

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

pip install pyodbc

安装ODBC驱动程序

下载 SQL 服务器的 Microsoft ODBC 驱动程序

安装完后查看驱动

>>> 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中测试

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安装

(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 驱动程序安装程序注册自己的位置。

(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（数据源名称）定义到数据库的临时文本文件，如下所示：

(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，需要指定，使用,分隔

Server      = 10.168.2.252,1433

在该文件中，按照上面的格式写，除非具有正确的驱动程序版本。在上面已经通过查看odbcinst.ini确定驱动程序版本。

！使用驱动程序名称与显示在方括号内的应完全相同。

创建DSN

保存临时配置文件odbc_dsn.conf后，可以使用以下命令创建一个“System DSN”：

创建System DSN

# 在 /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

# 在 ~/.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连接

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都可以使用

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

"""
动环系统定义推送消息，
使用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定时消息

(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

(ITNest) root@PxeCtrlSys:/home/user/software# /etc/init.d/cron  reload
[ ok ] Reloading configuration files for periodic command scheduler: cron.