Skip to main content
 首页 » 编程设计

python之pyodbc DSN之在连接字符串中没有 UID 和 PWD 的情况下连接到 SQL Server

2024年12月31日16mengfanrong

我希望使用 pyodbc 通过 DSN 建立与 MS SQL 的连接。我看到的是我无法连接到数据库,除非我在连接字符串中指定用户名 (PID) 和密码 (PWD),如下所示:

conn_str = 'DSN=MYMSSQL;UID=sa;PWD=password' 

所以如果我使用 PID 和 PWD 它可以工作但是如果我把 PID 和 PWD 放在我的DSN 配置(MYMSSQL)并从 conn_str 中删除这两个属性然后它不起作用,下面是 DSN 配置:

[MYMSSQL] 
Description         = Test to SQLServer 
Driver              = FreeTDS 
Servername          = MYMSSQL 
UID                 = sa 
PWD                 = password 
Database            = tempdb 

从 pyodbc API 文档中观察,显然没有 UID 和 PWD 就无法做到这一点

def connect(p_str, autocommit=False, ansi=False, timeout=0, **kwargs): # real signature unknown; restored from __doc__ 
    """ 
    connect(str, autocommit=False, ansi=False, timeout=0, **kwargs) --> Connection 
 
    Accepts an ODBC connection string and returns a new Connection object. 
 
    **The connection string will be passed to SQLDriverConnect, so a DSN connection 
    can be created using:** 
 
      **cnxn = pyodbc.connect('DSN=DataSourceName;UID=user;PWD=password')** 
 
    To connect without requiring a DSN, specify the driver and connection 
    information: 
 
      DRIVER={SQL Server};SERVER=localhost;DATABASE=testdb;UID=user;PWD=password 
 
    Note the use of braces when a value contains spaces.  Refer to SQLDriverConnect 
    documentation or the documentation of your ODBC driver for details. 
 
    The connection string can be passed as the string `str`, as a list of keywords, 
    or a combination of the two.  Any keywords except autocommit, ansi, and timeout 
    (see below) are simply added to the connection string. 
 
      connect('server=localhost;user=me') 
      connect(server='localhost', user='me') 
      connect('server=localhost', user='me') 
 
    The DB API recommends the keywords 'user', 'password', and 'host', but these 
    are not valid ODBC keywords, so these will be converted to 'uid', 'pwd', and 
    'server'. 
 
    pass 

请您参考如下方法:

我处理过的 ODBC 驱动程序管理器(Windows 的内置 DM 和 Linux 上的 unixODBC)默默地忽略 UID= 和 PWD= 中的条目“系统 DSN”和“用户 DSN”定义。他们确实尊重“文件 DSN”定义中的那些条目,因此您可以在包含

[ODBC] 
Description         = Test to SQLServer 
Driver              = FreeTDS 
Servername          = MYMSSQL 
UID                 = sa 
PWD                 = password 
Database            = tempdb 

然后使用

conn_str = 'FILEDSN=/path/to/mymssql.dsn'