使用sqlalchemy和pyodbc连接到SQL Server 2012

I'm trying to connect to a SQL Server 2012 database using SQLAlchemy (with pyodbc) on Python 3.3 (Windows 7-64-bit). I am able to connect using straight pyodbc but have been unsuccessful at connecting using SQLAlchemy. I have dsn file setup for the database access.

我正在尝试使用Python 3.3 (Windows 7-64位)上的SQLAlchemy(与pyodbc)连接到SQL Server 2012数据库。我可以使用直接的pyodbc连接，但是使用SQLAlchemy连接失败。我为数据库访问设置了dsn文件。

I successfully connect using straight pyodbc like this:

我成功地使用直的pyodbc连接:

con = pyodbc.connect('FILEDSN=c:\\users\\me\\mydbserver.dsn')

For sqlalchemy I have tried:

对于sqlalchemy，我尝试过:

import sqlalchemy as sa
engine = sa.create_engine('mssql+pyodbc://c/users/me/mydbserver.dsn/mydbname')

The create_engine method doesn't actually set up the connection and succeeds, but iIf I try something that causes sqlalchemy to actually setup the connection (like engine.table_names()), it takes a while but then returns this error:

create_engine方法实际上并没有设置连接和成功，但是如果我尝试一些导致sqlalchemy实际设置连接的东西(比如engine.table_names())，它需要一段时间，然后返回这个错误:

DBAPIError: (Error) ('08001', '[08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. (17) (SQLDriverConnect)') None None

DBAPIError:(错误)('08001'，'[08001][Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server不存在或访问被拒绝。(17)(SQLDriverConnect)”)都没有

I'm not sure where thing are going wrong are how to see what connection string is actually being passed to pyodbc by sqlalchemy. I have successfully using the same sqlalchemy classes with SQLite and MySQL.

我不确定哪里出了问题，如何查看sqlalchemy将哪些连接字符串实际传递给pyodbc。我已经成功地使用了与SQLite和MySQL相同的sqlalchemy类。

Thanks in advance!

提前谢谢!

4 个解决方案

#1

The file-based DSN string is being interpreted by SQLAlchemy as server name = c, database name = users.

基于文件的DSN字符串被SQLAlchemy解释为服务器名= c，数据库名=用户。

I prefer connecting without using DSNs, it's one less configuration task to deal with during code migrations.

我更喜欢不使用DSNs进行连接，这是在代码迁移期间较少处理的配置任务。

This syntax works using Windows Authentication:

此语法使用Windows身份验证:

engine = sa.create_engine('mssql+pyodbc://server/database')

Or with SQL Authentication:

或与SQL验证:

engine = sa.create_engine('mssql+pyodbc://user:password@server/database')

SQLAlchemy has a thorough explanation of the different connection string options here.

SQLAlchemy在这里对不同的连接字符串选项进行了全面的解释。

#2

I have an update info about the connection to MSSQL Server without using DSNs and using Windows Authentication. In my example I have next options: My local server name is "(localdb)\ProjectsV12". Local server name I see from database properties (I am using Windows 10 / Visual Studio 2015). My db name is "MainTest1"

我有一个关于连接到MSSQL服务器的更新信息，没有使用DSNs和使用Windows身份验证。在我的示例中，我有下一个选项:本地服务器名是“(localdb)\ProjectsV12”。我在数据库属性中看到的本地服务器名(我使用的是Windows 10 / Visual Studio 2015)。我的db名称是"MainTest1"

engine = create_engine('mssql+pyodbc://(localdb)\ProjectsV12/MainTest1?driver=SQL+Server+Native+Client+11.0', echo=True)

It is needed to specify driver in connection. You may find your client version in:

需要在连接中指定驱动程序。你可在以下网页找到你的客户版本:

control panel>Systems and Security>Administrative Tools.>ODBC Data Sources>System DSN tab>Add

控制面板>系统和安全>管理工具。b0 ODBC数据源>System DSN选项卡>添加

Look on SQL Native client version from the list.

从列表中查看SQL本机客户端版本。

#3

import urllib
params = urllib.quote_plus("DRIVER={SQL Server Native Client 10.0};SERVER=dagger;DATABASE=test;UID=user;PWD=password")

engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)

#4

Just want to add some latest information here: If you are connecting using DSN connections:

我想补充一些最新的信息:如果您正在使用DSN连接进行连接:

engine = create_engine("mssql+pyodbc://USERNAME:PASSWORD@SOME_DSN")

If you are connecting using Hostname connections:

如果您正在使用主机名连接进行连接:

engine = create_engine("mssql+pyodbc://USERNAME:PASSWORD@HOST_IP:PORT/DATABASENAME?driver=SQL+Server+Native+Client+11.0")

For more details, please refer to the "Official Document"

有关详情，请参阅“正式文件”

#1