在Python中使用SQLAlchemy连接到Azure数据库 [英] Connecting to an Azure database using SQLAlchemy in Python

问题描述

我正在尝试使用Python中的SQLAlchemy连接到Azure数据库.

我的代码如下:

  engine_azure = \create_engine('mssql + pyodbc://{服务器管理员登录}:{密码} @ {服务器名称} .database.windows.net:1433/{AdventureWorksLT}'，echo = True) 

我收到以下消息:

  C:\ ProgramData \ Anaconda3 \ lib \ site-packages \ sqlalchemy \ connectors \ pyodbc.py:92:SA警告:未指定驱动程序名称；使用无DSN的连接时，PyODBC可以预期到这一点未指定驱动程序名称；" 

然后我运行以下代码:

  print(engine_azure.table_names()) 

我收到以下消息:

  DBAPIError:(pyodbc.Error)('01S00'，'[01S00] [Microsoft] [ODBC驱动程序管理器]无效的连接字符串属性(0)(SQLDriverConnect)') 

解决方案

连接字符串存在2个问题:

1. 按照

   对于连接字符串，您可以通过转到azure门户获取它->您的数据库->连接字符串(在这种情况下，请选择ODBC):

   I am trying to connect to an Azure database using SQLAlchemy in Python.

   My code is the following:

   engine_azure = \
   create_engine('mssql+pyodbc://{Server admin login}:{password}@{Server name}.database.windows.net:1433/{AdventureWorksLT}', echo=True)
   

   I get the following message:

   C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\connectors\pyodbc.py:92: SAWarning: No driver name specified; this is expected by PyODBC when using DSN-less connections
     "No driver name specified; "
   

   Then I run the following code:

   print(engine_azure.table_names())
   

   I get the following message:

   DBAPIError: (pyodbc.Error) ('01S00', '[01S00] [Microsoft][ODBC Driver Manager] Invalid connection string attribute (0) (SQLDriverConnect)')
   

   解决方案

   There are 2 issues with your connection string:

   1. As per the SQLAlchemy documentation: The delimeters must be URL escaped when using a pass-through exact pyodbc string.

   2. And you do not specify the sql driver name either.

   You can use the code below, which works fine at my side:

   import pyodbc
   from sqlalchemy import create_engine
   import urllib
   
   params = urllib.parse.quote_plus \ # urllib.parse.quote_plus for python 3
   (r'Driver={ODBC Driver 13 for SQL Server};Server=tcp:yourDBServerName.database.windows.net,1433;Database=dbname;Uid=username@dbserverName;Pwd=xxx;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;')
   conn_str = 'mssql+pyodbc:///?odbc_connect={}'.format(params)
   engine_azure = create_engine(conn_str,echo=True)
   
   print('connection is ok')
   print(engine_azure.table_names())
   

   Test result:

   And for the connection string, you can get it by going to azure portal -> your database -> connection strings(select the ODBC in this case):

   这篇关于在Python中使用SQLAlchemy连接到Azure数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！