使用 sqlalchemy 和 pyodbc 连接到 SQL Server 2012 [英] Connecting to SQL Server 2012 using sqlalchemy and pyodbc

问题描述

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

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.

我像这样使用直接 pyodbc 成功连接:

I successfully connect using straight pyodbc like this:

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

对于 sqlalchemy 我已经尝试过:

For sqlalchemy I have tried:

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

create_engine 方法实际上并没有建立连接并成功，但是如果我尝试一些导致 sqlalchemy 实际设置连接的操作(例如 engine.table_names())，它需要一段时间，然后返回此错误:

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:

DBAPIError: (Error) ('08001', '[08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server 不存在或访问被拒绝.(17) (SQLDriverConnect)') 无无

我不确定哪里出错了，就是如何查看 sqlalchemy 实际传递给 pyodbc 的连接字符串.我已经成功地在 SQLite 和 MySQL 中使用了相同的 sqlalchemy 类.

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 将基于文件的 DSN 字符串解释为 server name = c, database name = users.

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

我更喜欢在不使用 DSN 的情况下进行连接，这是在代码迁移期间要处理的配置任务少.

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

此语法适用于 Windows 身份验证:

This syntax works using Windows Authentication:

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

或者使用 SQL 身份验证:

Or with SQL Authentication:

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

SQLAlchemy 对不同的连接字符串选项有详尽的解释 此处.

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

这篇关于使用 sqlalchemy 和 pyodbc 连接到 SQL Server 2012的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！