使用python或ironpython访问mssql最简单的方法是什么？

I've got mssql 2005 running on my personal computer with a database I'd like to run some python scripts on. I'm looking for a way to do some really simple access on the data. I'd like to run some select statements, process the data and maybe have python save a text file with the results.

我已经在我的个人计算机上运行了mssql 2005，我希望运行一些python脚本。我正在寻找一种方法来对数据进行一些非常简单的访问。我想运行一些select语句，处理数据并且可能让python保存带有结果的文本文件。

Unfortunately, even though I know a bit about python and a bit about databases, it's very difficult for me to tell, just from reading, if a library does what I want. Ideally, I'd like something that works for other versions of mssql, is free of charge and licensed to allow commercial use, is simple to use, and possibly works with ironpython.

不幸的是，尽管我对python有点了解并且对数据库有点了解，但是我很难通过阅读来判断一个库是否符合我的要求。理想情况下，我想要的东西适用于其他版本的mssql，是免费的，许可允许商业使用，使用简单，并可能与ironpython一起使用。

8 个解决方案

#1

I use SQL Alchemy with cPython (I don't know if it'll work with IronPython though). It'll be pretty familiar to you if you've used Hibernate/nHibernate. If that's a bit too verbose for you, you can use Elixir, which is a thin layer on top of SQL Alchemy. To use either one of those, you'll need pyodbc, but that's a pretty simple install.

我使用SQL Alchemy和cPython（我不知道它是否适用于IronPython）。如果您使用过Hibernate / nHibernate，那将会非常熟悉。如果这对你来说有点过于冗长，你可以使用Elixir，它是SQL Alchemy上的一个薄层。要使用其中任何一个，你需要pyodbc，但这是一个非常简单的安装。

Of course, if you want to write straight SQL and not use an ORM, you just need pyodbc.

当然，如果你想直接写SQL而不是使用ORM，你只需要pyodbc。

#2

Everyone else seems to have the cPython -> SQL Server side covered. If you want to use IronPython, you can use the standard ADO.NET API to talk to the database:

其他人似乎都覆盖了cPython - > SQL Server。如果要使用IronPython，可以使用标准ADO.NET API与数据库通信：

import clr
clr.AddReference('System.Data')
from System.Data.SqlClient import SqlConnection, SqlParameter

conn_string = 'data source=<machine>; initial catalog=<database>; trusted_connection=True'
connection = SqlConnection(conn_string)
connection.Open()
command = connection.CreateCommand()
command.CommandText = 'select id, name from people where group_id = @group_id'
command.Parameters.Add(SqlParameter('group_id', 23))

reader = command.ExecuteReader()
while reader.Read():
    print reader['id'], reader['name']

connection.Close()

If you've already got IronPython, you don't need to install anything else.

如果您已经有IronPython，则无需安装任何其他东西。

Lots of docs available here and here.

这里和这里提供了大量的文档。

#3

pyodbc comes with Activestate Python, which can be downloaded from here. A minimal odbc script to connect to a SQL Server 2005 database looks like this:

pyodbc附带Activestate Python，可以从这里下载。连接到SQL Server 2005数据库的最小odbc脚本如下所示：

import odbc

CONNECTION_STRING="""
Driver={SQL Native Client};
Server=[Insert Server Name Here];
Database=[Insert DB Here];
Trusted_Connection=yes;
"""

db = odbc.odbc(CONNECTION_STRING)
c = db.cursor()
c.execute ('select foo from bar')
rs = c.fetchall()
for r in rs:
    print r[0]

#4

I also successfully use pymssql with CPython. (With and without SQLAlchemy).

我也成功地使用CPython的pymssql。（有和没有SQLAlchemy）。

#5

http://adodbapi.sourceforge.net/ can be used with either CPython or IronPython. I have been very pleased with it.

http://adodbapi.sourceforge.net/可以与CPython或IronPython一起使用。我一直很满意。

#6

PyPyODBC (http://code.google.com/p/pypyodbc) works under PyPy, Ironpython and CPython.

PyPyODBC（http://code.google.com/p/pypyodbc）在PyPy，Ironpython和CPython下运行。

This article shows a Hello World sample of accessing mssql in Python.

本文展示了一个在Python中访问mssql的Hello World示例。

PyPyODBC has almostly same usage as pyodbc, as it can been seen as a re-implemenation of the pyodbc moudle. Because it's written in pure Python, it can also run on IronPython and PyPy.

PyPyODBC几乎与pyodbc一样使用，因为它可以被视为pyodbc模块的重新实现。因为它是用纯Python编写的，所以它也可以在IronPython和PyPy上运行。

Actually, when switch to pypyodbc in your existing script, you can do this:

实际上，在现有脚本中切换到pypyodbc时，您可以这样做：

#import pyodbc               <-- Comment out the original pyodbc importing line

import pypyodbc as pyodbc    # Let pypyodbc "pretend" the pyodbc

pyodbc.connect(...)          # pypyodbc has 99% same APIs as pyodbc

...

#7

I've used pymssql with standard python and liked it. Probably easier than the alternatives mentioned if you're just looking for basic database access.

我已经使用标准python的pymssql并喜欢它。如果您只是在寻找基本的数据库访问，可能比提到的替代方案更容易。

Sample code.

示例代码。

#8

If you are want the quick and dirty way with CPython (also works for 3.X python):

如果你想用CPython快速而肮脏的方式（也适用于3.X python）：

Install PYWIN32 after you install python http://sourceforge.net/projects/pywin32/files/pywin32/

安装python后安装PYWIN32 http://sourceforge.net/projects/pywin32/files/pywin32/

Import the following library: import odbc

导入以下库：import odbc

I created the following method for getting the SQL Server odbc driver (it is slightly different in naming depending on your version of Windows, so this will get it regardless):

我创建了以下获取SQL Server odbc驱动程序的方法（根据您的Windows版本命名略有不同，因此无论如何都会得到它）：

def getSQLServerDriver():
    key = winreg.OpenKey(winreg.HKEY_LOCAL_MACHINE, r"SOFTWARE\ODBC\ODBCINST.INI")
    sqlServerRegExp =  re.compile('sql.*server', re.I | re.S)

    try:
        for i in range(0, 2048):
            folder = winreg.EnumKey(key, i)
            if sqlServerRegExp.match(folder):
                return folder.strip()
    except WindowsError:
        pass

Note: if you use the above function, you'll need to also import these two libraries: winreg and re

注意：如果使用上述功能，则还需要导入这两个库：winreg和re

Then you use the odbc API 1 information as defined here: http://www.python.org/dev/peps/pep-0248/

然后使用此处定义的odbc API 1信息：http：//www.python.org/dev/peps/pep-0248/

Your connection interface string should look something like this (assuming you are using my above method for getting the ODBC driver name, and it is a trusted connection):

您的连接接口字符串应如下所示（假设您使用我的上述方法获取ODBC驱动程序名称，并且它是可信连接）：

dbString = "Driver={SQLDriver};Server=[SQL Server];Database=[Database Name];Trusted_Connection=yes;".replace('{SQLDriver}', '{' + getSQLServerDriver() + '}')

This method has many down sides. It is clumsy because of only supporting ODBC API 1, and there are a couple minor bugs in either the API or the ODBC driver that I've run across, but it does get the job done in all versions of CPython in Windows.

这种方法有很多不足之处。它是笨拙的，因为它只支持ODBC API 1，并且我运行的API或ODBC驱动程序中有一些小错误，但它确实在Windows的所有CPython版本中完成了工作。

#1