pymssql 与 pyodbc 与 adodbapi 与... [英] pymssql versus pyodbc versus adodbapi versus...

问题描述

我对数据库、执行 SQL 查询等比较陌生.目前我专门将 MS SQL Server 用于我的数据库(这是我们在工作中使用的)，但希望我的问题的答案可以比只针对那个特定的数据库.

I am relatively new to databases, performing SQL queries, etc. At the moment I am exclusively using MS SQL Server for my DBs (that's what we use at my work), but hopefully answers to my question can be more general than just for that particular DB.

我使用 DB 接口应用程序本身(在本例中为 SQL Server Management Studio)完成了大部分工作，但我也通过 python 脚本完成了一些工作，其中一些作为 cron 作业运行，其中一些是网站，以便用户可以向数据库提交查询.

I do a good chunk of my work using the DB interface app itself (in this case, SQL Server Management Studio), but I also do some work through python scripts, some of which run as cron jobs, some of which are websites such that users can submit queries into the DB.

我一直在使用 pymssql 模块来查询 &从python写入数据库.但是我遇到了一些烦人的问题.(似乎 pymssql 不能使用临时表，而且我在自动提交"方面也遇到了麻烦，但这可能是我自己的错.)

I have been using the pymssql module for querying into & writing into DBs from python. But there are a few annoying problems that I'm running into. (It seems pymssql cannot use temporary tables, and also I've had troubles with "autocommit", but that's likely my own fault.)

因此，我对此一无所知，我只是想知道我应该朝哪个方向发展.据我所知，我可以:

So being ignorant to much of this, I'd just like a flavor of what direction I should go. As far as I can tell, I could either:

• 使用直接与 MS SQL 服务器对话的 pymssql
• 使用pyodbc，需要安装FreeTDS &首先是 unixODBC，然后设置它们
• 使用 adodbapi 模块，我不确定这里需要什么要求.
• 另一种方法??

问题是，我不完全明白 ODBC 的作用.这似乎是某种身份验证中介"，有点像中间人，以确保用户就是他/她所说的人.而且我对 ADO 的了解更少.

Thing is, I don't FULLY get what ODBC does. It seems like some sort of "authentification intermediary", sort of like a middle man to ensure that the user is who he / she says. And I have even less understanding of ADO.

虽然 pymssql 似乎是最简单的方法，但它似乎也是人们不太看好的一种方法，它最终会被弃用.我宁愿学习正确"的方法，因为我几乎才刚开始接触这些东西.

While pymssql seems the easiest way to go, it also seems the one that folks think less highly of, and that it will eventually be deprecated. I'd rather learn the "right" way since I am pretty much just starting out with this stuff.

所以我正在寻找一些关于让 python 与 MS SQL Server 对话以及与数据库对话的最佳方式的知识渊博的意见.我会将最佳"定义为对于那些会用它做中等复杂事情的人来说是最好的.(虽然我才刚刚开始，但我最终必须要提高很多.)

So I'm looking for some knowledgeable opinions regarding the best way to get python to talk to MS SQL Server, and to talk to DBs in general. I would define "best" as the best for someone who will do moderately complex stuff with it. (While I am just getting started, it's something that I'll eventually have to ramp up on quite a bit.)

谢谢！迈克

推荐答案

pyodbc(推荐)

我几个月前开始使用这个模块，到目前为止没有任何抱怨.开发社区似乎比 pymssql 更活跃.到目前为止，我没有安装问题(32 位 Win XP 和 64 位 Server 2008 R2).您将拥有额外的优势，即您不仅限于 MS SQL Server.

I've started using this module a few months ago and have no complaints so far. The development community seems more active than pymssql. I had no installation issues so far (32bit Win XP and 64bit Server 2008 R2). You'll have the additional advantage that you are not limited to MS SQL Servers.

您可能会发现:http://www.lfd.uci.edu/~gohlke/pythonlibs/ 如果您在 Windows 上安装，则很有帮助.

You might find: http://www.lfd.uci.edu/~gohlke/pythonlibs/ helpful if you're installing on windows.

pymssql

我使用 1.x 版本已经有大约 2.5 年的时间了.我在大型对象的大小限制方面遇到了一些小问题.我在多线程时也遇到了很多问题(如果关闭我不得不关闭).1.x 基于已弃用的 MS 库.但是，最新版本是 2.x，不再使用这些已弃用的库.相反，它使用 FreeTDS(如 pyodbc).听起来不错，但我还没试过.

I've worked with the 1.x version for about 2.5 years. I had some minor problems with the size limitations for large objects. I also ran into massive problems when multi-threading (to the point I had to turn if off). 1.x was based on deprecated MS libraries. However, the recent version is 2.x and does not use these deprecated libraries any longer. Instead it uses FreeTDS (like pyodbc). It sounds good, but I haven't tried it yet.

adodbapi

大约一年前，我尝试切换出 pymssql 并用 adodbapi 替换它，并且一些与 pymssql 一起工作正常的查询由于没有明显原因而失败.我很快又放弃了adodbapi.

I've tried switching out pymssql and replacing it with adodbapi about a year ago and some of the queries which worked fine with pymssql failed for no obvious reason. I quickly abandoned adodbapi again.

这篇关于pymssql 与 pyodbc 与 adodbapi 与...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！