DB-API interface to Microsoft SQL Server for Python. (new Cython-based version)
A simple database interface for Python that builds on top of FreeTDS to provide a Python DB-API (PEP-249) interface to Microsoft SQL Server.
Detailed information on pymssql is available on the website:
New development is happening on GitHub at:
https://github.com/pymssql/pymssql
There is a Google Group for discussion at:
https://groups.google.com/forum/?fromgroups#!forum/pymssql
Can you take a minute and fill out this survey to help us prioritize development tasks?
https://www.surveymonkey.com/s/KMQ8BM5
Attention!
Windows users: You need to download and install additional DLLs
pymssql version 2.1.2 includes a change in the official Windows binaries: FreeTDS isn’t statically linked as it happened up to release 2.1.1, as that FreeTDS copy lacked SSL support.
Please see http://pymssql.org/en/latest/freetds.html#windows for futher details.
We are trying to find a balance between security and convenience and will be evaluating the situation for future releases. Your feedback is greatly welcome.
Custom message handlers (GH-139)
The DB-Library API includes a callback mechanism so applications can provide functions known as message handlers that get passed informative messages sent by the server which then can be logged, shown to the user, etc.
_mssql now allows you to install your own message handlers written in Python. See the _msssql examples and reference sections of the documentation for more details.
Thanks Marc Abramowitz.
Compatibility with Azure
It is now possible to transparently connect to SQL Server instances accessible as part of the Azure cloud services.
Note
If you need to connect to Azure make sure you use FreeTDS 0.91 or newer.
Customizable per-connection initialization SQL clauses (both in pymssql and _mssql) (GH-97)
It is now possible to customize the SQL statements sent right after the connection is established (e.g. 'SET ANSI_NULLS ON;'). Previously it was a hard-coded list of queries. See the _mssql.MSSQLConnection documentation for more details.
Thanks Marc Abramowitz.
Added ability to handle instances of uuid.UUID passed as parameters for SQL queries both in pymssql and _mssql. (GH-209)
Thanks Marat Mavlyutov.
Allow using SQL Server autocommit mode from pymssql at connection opening time. This allows e.g. DDL statements like DROP DATABASE to be executed. (GH-210)
Thanks Marat Mavlyutov.
Documentation: Explicitly mention minimum versions supported of Python (2.6) and SQL Server (2005).
Incremental enhancements to the documentation.
Handle errors when calling Stored Procedures via the .callproc() pymssql cursor method. Now it will raise a DB-API DatabaseException; previously it allowed a _mssql.MSSQLDatabaseException exception to surface.
Fixes in tds_version _mssql connections property value
Made it work with TDS protocol version 7.2. (GH-211)
The value returned for TDS version 7.1 is still 8.0 for backward compatibility (this is because such feature got added in times when Microsoft documentation labeled the two protocol versions that followed 7.0 as 8.0 and 9.0; later it changed them to 7.1 and 7.2 respectively) and will be corrected in a future release (2.2).
PEP 249 compliance (GH-251)
Added type constructors to increase compatibility with other libraries.
Thanks Aymeric Augustin.
pymssql: Made handling of integer SP params more robust (GH-237)
Check lower bound value when convering integer values from to Python to SQL (GH-238)
Sphinx-based documentation (GH-149)
Read it online at http://pymssql.org/
Thanks, Ramiro Morales!
See:
“Green” support (GH-135)
Lets you use pymssql with cooperative multi-tasking systems like gevent and have pymssql call a callback when it is waiting for a response from the server. You can set this callback to yield to another greenlet, coroutine, etc. For example, for gevent, you could do:
def wait_callback(read_fileno):
gevent.socket.wait_read(read_fileno)
pymssql.set_wait_callback(wait_callback)
The above is useful if you’re say, running a gunicorn server with the gevent worker. With this callback in place, when you send a query to SQL server and are waiting for a response, you can yield to other greenlets and process other requests. This is super useful when you have high concurrency and/or slow database queries and lets you use less gunicorn worker processes and still handle high concurrency.
Better error messages.
E.g.: For a connection failure, instead of:
pymssql.OperationalError: (20009, ‘Net-Lib error during Connection refused’)
the dberrstr is also included, resulting in:
pymssql.OperationalError: (20009, ‘DB-Lib error message 20009, severity 9:nUnable to connect: Adaptive Server is unavailable or does not existnNet-Lib error during Connection refusedn’)
See: * https://github.com/pymssql/pymssql/pull/151
In the area of error messages, we also made this change:
execute: Raise ColumnsWithoutNamesError when as_dict=True and missing column names (GH-160)
because the previous behavior was very confusing; instead of raising an exception, we would just return row dicts with those columns missing. This prompted at least one question on the mailing list (https://groups.google.com/forum/?fromgroups#!topic/pymssql/JoZpmNZFtxM), so we thought it was better to handle this explicitly by raising an exception, so the user would understand what went wrong.
See: * https://github.com/pymssql/pymssql/pull/160 * https://github.com/pymssql/pymssql/pull/168
Performance improvements
You are most likely to notice a difference from these when you are fetching a large number of rows.
Reworked row fetching (GH-159)
There was a rather large amount of type conversion occuring when fetching a row from pymssql. The number of conversions required have been cut down significantly with these changes. Thanks Damien, Churchill!
See: * https://github.com/pymssql/pymssql/pull/158 * https://github.com/pymssql/pymssql/pull/159
Modify get_row() to use the CPython tuple API (GH-178)
This drops the previous method of building up a row tuple and switches to using the CPython API, which allows you to create a correctly sized tuple at the beginning and simply fill it in. This appears to offer around a 10% boost when fetching rows from a table where the data is already in memory. Thanks Damien, Churchill!
MSSQLConnection: Add with (context manager) support (GH-171)
This adds with statement support for MSSQLConnection in the _mssql module – e.g.:
with mssqlconn() as conn:
conn.execute_query("SELECT @@version AS version")
We already have with statement support for the pymssql module. See:
Allow passing in binary data (GH-179)
Use the bytesarray type added in Python 2.6 to signify that this is binary data and to quote it accordingly. Also modify the handling of str/bytes types checking the first 2 characters for b‘0x’ and insert that as binary data. See:
Add support for binding uuid.UUID instances to stored procedures input params (GH-143) Thanks, Ramiro Morales!
See: * https://github.com/pymssql/pymssql/pull/143 * https://github.com/pymssql/pymssql/commit/1689c83878304f735eb38b1c63c31e210b028ea7
The version number is now stored in one place, in pymssql_version.h This makes it easier to update the version number and not forget any places, like I did with pymssql 2.0.1
Improved support for using py.test as test runner (GH-183)
Improved PEP-8 and pylint compliance
GH-142 (“Change how *.pyx files are included in package”) - this should prevent pymssql.pyx and _mssql.pyx from getting copied into the root of your virtualenv. Thanks, @Arfrever!
GH-145 (“Prevent error string growing with repeated failed connection attempts.”)
See:
GH-151 (“err_handler: Don’t clobber dberrstr with oserrstr”)
GH-152 (“_mssql.pyx: Zero init global last_msg_* vars”) See: https://github.com/pymssql/pymssql/pull/152
GH-177 (“binary columns sometimes are processed as varchar”) Better mechanism for pymssql to detect that user is passing binary data.
buffer overflow fix (GH-182)
Return uniqueidentifer columns as uuid.UUID objects on Python 3
See ChangeLog for older history…
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
