Microsoft Azure数据仓库和SqlAlchemy

本文介绍了Microsoft Azure数据仓库和SqlAlchemy的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试使用python的sqlalchemy库连接到Microsoft Azure数据仓库。 并收到以下错误：

I am trying to use python's sqlalchemy library for connecting to microsoft azure data warehouse. and receiving the following error:

(pyodbc.Error) ('HY000', '[HY000] [Microsoft][ODBC SQL Server Driver][SQL Server]Client driver version is not supported. (46722) (SQLDriverConnect); [HY000] [Microsoft][ODBC SQL Server Driver][SQL Server]Client driver version is not supported. (46722)')

我的Windows连接代码：

my code for windows connection:

import sqlalchemy user_name = 'userName' password = 'password' uri = 'sqlServerName' db_name = 'SQLDBName' db_prefix = 'mssql+pyodbc://' db_driver = '{SQL Server}' connection_string = "{db_prefix}{user_name}:{password}@{uri}/{db_name}?Driver={driver}".format( db_prefix=db_prefix, user_name=user_name, password=password, uri=uri, db_name=db_name, driver=db_driver) engine = sqlalchemy.engine.create_engine(connection_string, echo=echo, pool_size=20, max_overflow=100) engine.connect() # throws the error

谢谢！

推荐答案

根据您的代码，看来该问题是由使用错误的 Driver = { SQL Server} 。

According to your code, it seems that the issue was caused by using the incorrect Driver={SQL Server}.

在Azure门户上，可以按照如下图所示的步骤获取连接字符串。

On Azure portal, you can get the connection string via follow the steps as the figure below.

正确的odbc驱动程序名称应为 Driver = {用于SQL Server的ODBC驱动程序13} 。同时，请遵循教程为您的当前环境安装正确版本的 pyodbc 3.1.1 。

The correct odbc driver name should be Driver={ODBC Driver 13 for SQL Server}. Meanwhile, please follow the tutorial to install the correct version 3.1.1 of pyodbc for your current environment.

这是我的测试代码，如下所示。

Here is my testing code as below.

import sqlalchemy connection_string = "mssql+pyodbc://<user>@<server-host>:<password>@<server-host>.database.windows:1433/<database>?driver=ODBC+Driver+13+for+SQL+Server" engine = sqlalchemy.engine.create_engine(connection_string) engine.connect()

或

import sqlalchemy import urllib params = urllib.quote_plus("Driver={ODBC Driver 13 for SQL Server};Server=<server-host>.database.windows,1433;Database=<database>;Uid=<user>@<server-host>;Pwd=<password>;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;") engine = sqlalchemy.engine.create_engine("mssql+pyodbc:///?odbc_connect=%s" % params) engine.connect()

我遇到异常 sqlalchemy.exc.ProgrammingError：（pyodbc.ProgrammingError）（'42000'， 此版本不支持[42000] [Microsoft] [用于SQL Server的ODBC驱动程序13] [SQL Server]目录视图'dm_exec_sessions'。 （104385）（SQLExecDirectW））当我运行上面的代码时，但这似乎并不影响工作。

I got an exception sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Catalog view 'dm_exec_sessions' is not supported in this version. (104385) (SQLExecDirectW)") when I ran the codes above, but it seems not affect working.

我测试下面的代码

import pyodbc cnxn = pyodbc.connect("Driver={ODBC Driver 13 for SQL Server};Server=<server-host>.database.windows,1433;Database=<database>;Uid=<user>@<server-host>;Pwd=<password>;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;") cursor = cnxn.cursor() cursor.execute("select @@VERSION") row = cursor.fetchone() if row: print row

输出：

(u'Microsoft Azure SQL Data Warehouse - 10.0.8529.1 Jan 13 2017 22:49:03 Copyright (c) Microsoft Corporation', )