python之如何在短时间内使用 python 从 Ubuntu 16.04 连接到 SQL Server 2016

我正在尝试使用 python 从 Ubuntu 16.04 虚拟机将数据写入 SQL Server 2016 Express 数据库。将通过 FreeTDS、UnixODBC、pyodbc 建立连接。

该程序通过 TCP 套接字获取一串传感器数据，对其进行格式化并将其写入(新的或现有的)表中。这必须每 x 秒发生一次。为此，我使用了 this answer .

该代码表现出一种奇怪的行为，我尝试并搜索了几个小时。它有时有效，有时无效。我认为问题出在 connection = pyodbc.connect(conn_str) 上，因为通常代码成功执行一次，然后卡在这一行。几分钟后出现错误:

connection = pyodbc.connect(conn_str) 
Error: ('08S01', '[08S01] [unixODBC][FreeTDS][SQL Server]Unable to connect: Adaptive Server is unavailable or does not exist (20009) (SQLDriverConnect)') 

也许问题与打开的连接有关，但它应该在脚本结束时关闭。 你有解决方案吗？如何使用 try/except 函数捕获此类错误？请询问我是否应该提供更多信息。

预先感谢您的帮助!

这是代码:

import sys 
import pyodbc 
import socket 
import re 
from threading import Timer 
import time 
 
# Timer (see link above) 
class InfiniteTimer(): 
 
def pi_to_sql(): 
    dsn = "sqlserverdatasource" 
    user = "User" 
    password = "Password" 
    database = "MSSQL_DB" 
 
    conn_str = "DSN=%s;UID=%s;PWD=%s;DATABASE=%s;" % (dsn, user, password, database) 
    connection = pyodbc.connect(conn_str) 
    cursor = connection.cursor() 
 
    # Create TCP/IP socket (client) 
    sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM) 
 
    # Create connection with socket server (insert IP of server) 
    server_address = ('XXX.XXX.XXX.XXX', 10000) 
    sock.connect(server_address) 
 
    # Sending order to server 
    to_send = "send data" 
    sock.sendall(to_send) 
 
    # Receiving data (string, e.g. "DS18B20;28FF97FF841605C0;32.4375") 
    data = sock.recv(1024) 
 
    # Filter data using regex 
    data_list = re.findall(r"(?P<name>\w{6,});(?P<address>[0-9A-F]{16,});(?P<value>-?\d+\.?\d+)",data,re.M) 
 
    # Closing socket 
    sock.close() 
 
    sensor_cnt = len(data_list) 
 
    i = 0 
 
    # Write sensor data to new or existing table using a loop 
    while i<=(sensor_cnt-1): 
        s_address = data_list[i][1] 
        s_value = data_list[i][2] 
        act_datetime = time.strftime("%Y%m%d %H:%M:%S") 
        table_name = "sensor_" + s_address 
 
        sql_command = """IF (NOT EXISTS (SELECT * 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'dbo' 
AND  TABLE_NAME = '{0:s}')) 
BEGIN 
CREATE TABLE dbo.{0:s} 
( 
Timestamp DATETIME2(0), 
S_Value FLOAT 
) 
END 
INSERT INTO dbo.{0:s} VALUES 
( 
'{1:s}','{2:s}' 
)""".format(table_name,act_datetime,s_value) 
 
        try: 
            cursor.execute(sql_command) 
            connection.commit() 
 
        except pyodbc.ProgrammingError: 
            print("error") 
            connection.close() 
 
        i = i+1 
 
    cursor.close() 
    connection.close() 
 
    print("done") 
 
# Calling timer class to execute code every 5s 
t = InfiniteTimer(5, pi_to_sql) 
t.start() 

tsql -C(我使用了来自 FreeTDS 的最新稳定版 .tar.gz)

Compile-time settings (established with the "configure" script) 
                        Version: freetds v1.00.27 
         freetds.conf directory: /usr/local/etc 
 MS db-lib source compatibility: no 
    Sybase binary compatibility: no 
                  Thread safety: yes 
                  iconv library: yes 
                    TDS version: 7.4 
                          iODBC: no 
                       unixodbc: yes 
          SSPI "trusted" logins: no 
                       Kerberos: no 
                        OpenSSL: no 
                         GnuTLS: no 
                           MARS: no 

/etc/odbc.ini

[sqlserverdatasource] 
driver = /usr/local/lib/libtdsodbc.so 
server = XXX.XXX.XXX.XXX 
port = 61521 
database = MSSQL_DB 
tds_version = 7.4 

isql -v sqlserverdatasource 用户密码

+---------------------------------------+ 
| Connected!                            | 
|                                       | 
| sql-statement                         | 
| help [tablename]                      | 
| quit                                  | 
|                                       | 
+---------------------------------------+ 
SQL>  

请您参考如下方法:

This thread让我想到用我拥有管理员权限的另一个版本(SQL Server 2014)在本地服务器上测试代码。所以我确保对于每个列出的 IP 地址，Active 和 Enabled 都是 Yes。它现在正在工作。不幸的是，我不能确切地说出问题是什么(如果它取决于 SQL Server 16 本身或配置或不兼容)。我希望这句话可能对某些人有所帮助。