pymssql vs pyodbc

[Randy Syring]

Back when I started working with pymssql, pyodbc wasn't in very good shape.  However, Microsoft has just announced official support for pyodbc:

https://blogs.technet.microsoft.com/dataplatforminsider/2016/12/09/sql-server-python-whats-new/

• There are official pyodbc drivers from Microsoft for Linux: https://msdn.microsoft.com/en-us/library/hh568451(v=sql.110).aspx
• FreeTDS also provides an ODBC driver.
  

With that announcement, it seems we might be fighting an up-hill battle to keep pymssql maintained.  Most users will gravitate towards pyodbc as the "official" solution for mssql.  We already struggle to attract enough developer talent to keep pymssql updated as recent issue requests for a new release show.

Should we consider shutting down pymssql and pointing people to pyodbc?

Randy Syring
Husband | Father | Redeemed Sinner

"For what does it profit a man to gain the whole world
and forfeit his soul?" (Mark 8:36 ESV)

[Gord Thompson]

> Should we consider shutting down pymssql and pointing people to pyodbc?

I would have to say "yes". My main interest in pymssql was for accessing SQL Server from Linux. Now that Microsoft appears intent on supporting Linux clients I, like many others, will almost certainly standardize on pyodbc+MS_ODBC under both Windows and Linux. (I had already confirmed my preference for pyodbc+MS_ODBC on Windows clients.)

As you mentioned, FreeTDS offers an ODBC driver so pyodbc+FreeTDS_ODBC could be used to access SQL Server from

- Mac clients, or

- Linux clients for whom MS_ODBC_for_Linux might not be a good fit. (Microsoft may understandably not be inclined to support *absolutely every* Linux distribution, present and future.)

In addition to pyodbc's official support from Microsoft, and pyodbc fitting with Microsoft's strategy of promoting ODBC as the preferred access technology for SQL Server, pymssql would also (and may indeed already) have an increasingly difficult time maintaining user interest if it was perceived as "stagnating" in terms of bugfixes and new features.

So with heartfelt thanks to all of the pymssql contributors, past and present, I would agree that perhaps it really is time to wind the project down.

Gord

[Randy Syring]

I've decided to begin the process of shutting down pymssql in favor of pyodbc.  There is now an official proposal to discontinue the project:

https://github.com/pymssql/pymssql/issues/477

I appreciate the contributions of everyone who has committed to pymssql development over the years.  Especially Ramiro who has done almost all the work on pymssql over the past year and a half.  This proposal should not take away from that appreciation, I just believe with Microsoft now backing ODBC on Linux, we are going to be fighting an up-hill battle and we would be better served to put our time elsewhere and to point our users towards the supported connection library.

Randy Syring
Husband | Father | Redeemed Sinner

"For what does it profit a man to gain the whole world
and forfeit his soul?" (Mark 8:36 ESV)

--
You received this message because you are subscribed to the Google Groups "pymssql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pymssql+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Tim Allen]

Thanks to everyone for all of your efforts. Hopefully unifying behind a single solution will prove beneficial to everyone.

Warm regards,

Tim

[Randy Syring]

I've decided to begin the process of shutting down pymssql in favor of pyodbc. 

I'm sorry, that statement is not really accurate, it's too strong.  What I've decided is to make the proposal formal by creating the GitHub issue and requesting feedback on it.  Whether or not we discontinue pymssql and point people to pyodbc will come after we get feedback on the proposal.

My apologies for overstating the case and any resulting concern.

Randy Syring
Husband | Father | Redeemed Sinner

"For what does it profit a man to gain the whole world
and forfeit his soul?" (Mark 8:36 ESV)

[David Rueter]

I am concerned.  I would not want to see pymssql (and especially _mssql) shut down.

 

While pymssql has some limitations and rough edges, it is working reliably to provide access to MS SQL from my application whether the app runs on Windows or Linux, with no complex deployment involved.

 

I have not used pyodbc, but I did a quick google search and am concerned for a few reasons.

 

For example, Microsoft’s page https://msdn.microsoft.com/en-us/library/mt652092(v=sql.1).aspx (and pages linked there) seems to indicate no Mac support for pyodbc  (though mymssql offers this).  Additionally, it seems that downloading and installing a Microsoft driver is required—which at a minimum will greatly complicate deployment, but places our applications at risk to the whims of Microsoft’s decisions to support or not support specific platforms.

 

I like that Microsoft does seem to be embracing Linux now, and that their latest ODBC driver for SQL seems nice (with TLS 1.2 support, Azure support, etc:  https://blogs.technet.microsoft.com/dataplatforminsider/2016/10/25/odbc-driver-13-0-for-sql-server-linux-is-now-released/ )

 

But what about distros not listed?  What if I want to access SQL from Raspberry Pi or some other platform?  I suspect that making the Microsoft ODBC driver work would be difficult if not impossible.

 

Consider this:  Before 1999, Microsoft had ODBC support.  Then, about 1999 they encouraged us to switch from ODBC and go to OLEDB  (http://sqlmag.com/sql-server/ole-db-or-odbc ).  Since MS SQL 2005, Microsoft’s preferred means of connecting to MS SQL has been the  SQL Server Native Client:  they moved us away from OLEDB.  But then starting with SQL 2012, Microsoft discontinued the SQL Server Native Client (https://msdn.microsoft.com/en-us/library/ms131415.aspx ).  And now…I guess the shift is back to ODDBC .

 

This constant shifting is on Windows, Microsoft’s baby.  Do you think Microsoft will be any more consistent going forward on Linux?

 

TDS is not well understood and seems not to be fully documented…granted…but under the hood TDS has been (and will continue to be) a viable way to access SQL data.

 

FYI, my app requirements are simple…and actually I am only using _mssql:  I just need to establish a database connection in a thread, be able to persist that connection and later retrieve it from a different thread, be able to execute queries and stored procedures—including stored procedures that return multiple resultsets, and be able to retrieve resultsets as lists of dictionaries.  I don’t need or want any kind of ORM.

 

But I do want to have the freedom to build and distribute apps that can talk to MSSQL without having to be at risk that one day it will no longer possible due to another capricious Microsoft decision to chase after a shiny object, and without worrying about the fine print of licensing terms of installing and /or redistributing their latest client-side driver.

 

_mssql works well for this.  I have worked around limitations in a satisfactory way.  (Limitations have been things like:  I haven’t been able to make output parameters work, there have been some issues working with varbinary(MAX) data types, etc.)

 

I spent a lot of hours a few months back getting set up to build FreeTDS and pymssql.  I understand the complexities of developing and supporting this project.  I appreciate (and have not properly expressed my appreciation for) the hard work and investment made by the pymssq team.

 

But I’d ask you to carefully consider shutting down the project:  I think that pymssql is still relevant, timely, and needed.

 

Sincerely,

 

David Rueter

dru...@assyst.com

[Andrzej Kukuła]

Hi!

I've been involved in pymssql project in the past. I'd like to relate to the idea of shutting down the project and emails from this thread.

People do use pymssql. It is mentioned on Microsoft web site - for example [1][2]. As David said, it is much more portable than the alternatives. And it's fast. These are its distinguishing factors. It's surely imperfect, more on that later.

I would recommend not to delete it. That would be the worst decision that can be made. Leaving it idle is the second worst option, but allows people to continue using it, or fork it, or whatever. What I would do instead is to announce on github.com/pymssql/pymssql that the project is looking for maintainers and code contributors and pull requests. If possible, I would try to fix stuff and accept PRs with improvements and changes.

Remember that's it's an open source project that existed before you've volunteered to be the maintainers at some point of time. You have freedom to stop being interested in it, but it doesn't mean you should kill it. It's better to give it away to new maintainers. I'm sure we can find somebody who wants to and/or has resources to improve it. I would like to help as well, but I'm unable to devote my free time anymore. But if you plan to kill it, I'd like to take over the access to repositories, domain and hosting and maintenance scripts if possible. If the problems are related to financing hosting/domain then please PM me and we'll work something out.

I'd like to share some more thoughts. I know pymssql is not perfect. There are several reasons for that. The world was different when it started. There was no official documentation for TDS protocol, so FreeTDS was mostly done using reverse engineering, and it was in its early stages. Microsoft was still supporting DB-Library for C. There was no Cython. SQL Server's connectivity for Linux and/or Python was irrelevant for Microsoft. Linux was a cancer for Microsoft's CEO of the time [3]. Python was also irrelevant for Microsoft. Sybase was relevant. Development resources for pymssql project were limited. I'm sure we could think of more.

Right now everything's changed. We have complete TDS documentation [4]. We have native portable TDS implementation even in JavaScript [5]. FreeTDS is also more mature, although it still doesn't advertise Windows support on the front page. We have Git and GitHub and Markdown and lots of other stuff that makes things easier. pymssql is now much better, the great thing is that it's compatible with python 2 and 3 what was made with ease of Cython by Damien. It would need more work in order to bring support for features found in newer SQL Server versions and to fix some rough edges, but it does work right now for many people.

In the past I've also recommended couple of times switching to ODBC, because situation looked hopeless. Microsoft stopped supporting DB-Lib, introduced Native Client, which was Windows-only and said that it's the future. As you probably know, Native Client is already deprecated [6][7]. Thus David's concerns about ODBC may be valid. FreeTDS also had a lot of issues in the past. Now it's not the case anymore. TDS protocol is still developed and will remain primary low-level means of accessing SQL Server data.

If pymssql is to survive, it needs a roadmap. In the long term I would see pymssql version 3, partially incompatible with previous versions, fixing most problems of _mssql layer (parameter handling, formatting, stored procedure handling, exception handling, string and identifier quoting, new data types etc.). Ideally I would see a new, native, really cross-platform TDS implementation replacing FreeTDS, dedicated to SQL Server (with dropped support for Sybase). It should definitely be a long-term goal (I think it would be a separate project). It should be easier to create because of availability of official documentation. We then of course require manpower to implement that, it's a different story.

That's it for now, if you want more info or discuss something with me, please PM me.

Thank you very much for your contributions and for making pymssql better.

References:

[1]: https://msdn.microsoft.com/en-us/library/mt694094(v=sql.1).aspx

[2]: https://msdn.microsoft.com/en-us/library/mt763260(v=sql.1).aspx

[3]: http://www.theregister.co.uk/2001/06/02/ballmer_linux_is_a_cancer/

[4]: https://msdn.microsoft.com/en-us/library/dd304523.aspx

[5]: https://github.com/ttghr/tds

[6]: https://msdn.microsoft.com/en-us/library/hh967418.aspx

[7]: https://msdn.microsoft.com/en-us/library/cc280510.aspx

To unsubscribe from this group and stop receiving emails from it, send an email to pymssql+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "pymssql" group.

To unsubscribe from this group and stop receiving emails from it, send an email to pymssql+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "pymssql" group.

To unsubscribe from this group and stop receiving emails from it, send an email to pymssql+unsubscribe@googlegroups.com.

[Randy Syring]

I would recommend not to delete it.

I'm sorry I've been unclear on this topic.  My proposal is not to delete it.  The source code, released packages, would all remain as-is.  My proposal is that we should consider deprecating it in favor of pyodbc.  But, I'm not convinced that is the case, I just want to consider if we should do that.

Thanks for taking the time to give feedback.

Randy Syring
Husband | Father | Redeemed Sinner

"For what does it profit a man to gain the whole world
and forfeit his soul?" (Mark 8:36 ESV)

To unsubscribe from this group and stop receiving emails from it, send an email to pymssql+u...@googlegroups.com.

[Gabor Nyul]

Well, there is one thing where I prefer pymysql over pyodbc is the way pyodbc is handling the "normal" varchar data. The local characters are not displayed  correctly if the column is varchar. So I have to alter the columns to nvarchar which take twice as much space (and for tables over hundreds of millions of rows it counts).
With pymssql everything is fine (at least till now :-) )

[Randy Syring]

IMO, the sentiment has trended away from deprecating pymssql in favor of pyodbc. I don't believe any of the past maintainers were in favor. Michael Bayer also has strong feelings against it (see the GitHub issue) and given his extensive work with DBs b/c of SQLAlchemy, I have a hard time ignoring his recommendation. The other opinions towards pyodbc were rarely positive with the exception being that it was possibly more performant.

I'm withdrawing the proposal and will close the issue. However, I do think pymssql is badly in need of a maintainer who can put regular time into the project. I don't think it would need to be a lot, just regular.

Randy Syring
Husband | Father | Redeemed Sinner

"For what does it profit a man to gain the whole world
and forfeit his soul?" (Mark 8:36 ESV)