pymssql:与数据库的连接仅有时有效 [英] pymssql: Connection to the database only works sometimes

问题描述

我正在尝试使用 Python 的 pymssql 连接到 Azure SQL 服务器.问题是以下脚本有效，但只是有时，其他时候我会收到此错误:

<块引用>

_mssql.MSSQLDatabaseException: (20002, b'DB-Lib 错误消息 20002，严重性 9: Adaptive Server 连接失败 ')

这是我正在使用的脚本:

导入pymssqlconn = pymssql.connect(server='x', user='x', password='x', database='x')游标 = conn.cursor()cursor.execute('SELECT * FROM customers');行 = cursor.fetchone()而行:打印 (str(row[0]) + " " + str(row[1]) + " " + str(row[2]))行 = cursor.fetchone()

如果有人能告诉我为什么上面的脚本只在某些时候有效，而在其他时候我会收到Adaptive Server 连接失败"错误，这将对我有很大帮助.

解决方案

我查看了这些旧线程

出于向后兼容性的原因，FreeTDS 将此版本别名为 7.1，但由于未来的兼容性问题，应该避免这种情况.请参阅下面关于过时版本的说明.

如果你在 linux 上使用 pymssql 和 FreeTDS，我认为你需要检查/etc/freetds/路径下的配置文件freetds.conf".

这是我对下面的 Azure SQL Server 的配置:

#一个典型的微软服务器[例如Server70]主机 = .database.windows.net端口 = 1433tds 版本 = 7.3

您可以尝试使用freetds工具'tsql'命令'tsql -H .database.windows.net -U Username -D DatabaseName -p 1433 - 来测试与Azure SQL服务器的连接P 密码' .

最好的问候.

I'm trying to connect to Azure SQL server using Python's pymssql. The problem is that the following script works but only sometimes, the other times I get this error:

_mssql.MSSQLDatabaseException: (20002, b'DB-Lib error message 20002, severity 9: Adaptive Server connection failed ')

This is the script I'm using:

import pymssql
conn = pymssql.connect(server='x', user='x', password='x', database='x')
cursor = conn.cursor()
cursor.execute('SELECT * FROM customers');
row = cursor.fetchone()
while row:
    print (str(row[0]) + " " + str(row[1]) + " " + str(row[2]))
    row = cursor.fetchone()

It would help me greatly if someone can tell me why this above script works only sometimes and rest of the times I get the "Adaptive Server connection failed" error.

解决方案

I reviewed these SO old threads Read from the server failed when trying to connect to sql-azure from tsql and What is TDS Protocol Version 8.0 and why should I use it?. The issue seems to be caused by using the wrong version of FreeTDS.

I found the key at the page of FreeTDS offical website http://www.freetds.org/faq.html#Does.FreeTDS.support.Microsoft.servers.

There is a table of versions of the TDS protocol by product http://www.freetds.org/userguide/choosingtdsprotocol.htm.

FreeTDS will alias this version to 7.1 for backwards compatibility reasons, but this should be avoided due to future compatibility concerns. See note below on obsolete versions.

If you use pymssql with FreeTDS on linux, I think you need to check the configuration file "freetds.conf" at the path /etc/freetds/.

This is my configuration for Azure SQL Server below:

# A typical Microsoft server
[egServer70]
        host = <database_name>.database.windows.net
        port = 1433
        tds version = 7.3

You can try to test the connection to Azure SQL server by using freetds tool 'tsql' to command 'tsql -H <database_name>.database.windows.net -U Username -D DatabaseName -p 1433 -P Password' .

Best Regards.

这篇关于pymssql:与数据库的连接仅有时有效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！