在Python中创建jTDS连接字符串 [英] Create a jTDS connection string in Python

问题描述

我正在尝试使用Python建立jTDS连接。

I'm trying to establish a jTDS connectivity using Python. Can anyone please help me in accomplishing that?

在SQL Server中，我通常使用以下连接字符串进行连接：

From the SQL Server, I generally connect using the below connection string:

jdbc:jtds:sqlserver://DBServer:port/DBInstance;useNTLMv2=true;domain=Domain

我正在尝试在Python中执行以下操作：

I'm trying to do as below in Python:

import pyodbc
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=DBName,port;'
                      'Database=database;'
                      'Truster_Connection=yes;'
                      'uid=user;'
                      'pwd=password;'
                )

cursor = conn.cursor()
cursor.execute('SELECT * from Table')

for row in cursor:
    print(row)

错误：

pyodbc.InterfaceError: ('28000', "[28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'USER'. (18456) (SQLDriverConnect); [28000] [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute (0); [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'USER'. (18456); [28000] [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute (0)"))

推荐答案

如果您专门尝试使用jTDS（而不是ODBC）连接到MS SQL Server，则可以使用jaydebeapi python软件包。

If you are specifically trying to connect to MS SQL Server using jTDS as opposed to ODBC, then you can use the jaydebeapi python package.

请参见以下代码（Python 3）：

See the following code (Python 3):

import sys
import jaydebeapi


def main():
    try:
        # jTDS Driver.
        driver_name = "net.sourceforge.jtds.jdbc.Driver"

        # jTDS Connection string.
        connection_url = "jdbc:jtds:sqlserver://<server_hostname>:<port>/<database_name>"

        # jTDS Connection properties.
        # Some additional connection properties you may want to use
        # "domain": "<domain>"
        # "ssl": "require"
        # "useNTLMv2": "true"
        # See the FAQ for details http://jtds.sourceforge.net/faq.html
        connection_properties = {
            "user": "username",
            "password": "password",
        }

        # Path to jTDS Jar
        jar_path = "<path_to>\\jtds-1.3.1.jar"

        # Establish connection.
        connection = jaydebeapi.connect(driver_name, connection_url, connection_properties, jar_path)
        cursor = connection.cursor()

        # Execute test query.
        cursor.execute("select 1 as test_connection")
        res = cursor.fetchall()
        if res:
            print(str(res))  # Should print [(1,)]

    except Exception as err:
        print(str(err))


if __name__ == "__main__":
    sys.exit(main())

在此之前，您需要完成以下操作：

Prior to this, you need to complete the following:

1. 从此处


2. 通过pip安装jaydebeapi或从此处


3. 从此处


4. 更新connection_url，connection_properties，jar_path。

1. Download and install JDK/JRE from here
2. pip install jaydebeapi or download from here
3. Download jtds from here
4. Update connection_url, connection_properties, jar_path.

这篇关于在Python中创建jTDS连接字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！