MS SQL Server backend using pyodbc.
|Reported by:||Owned by:||nobody|
|Component:||Database layer (models, ORM)||Version:||master|
|Severity:||Keywords:||mssql pyodbc database backend feature|
|Cc:||filip.wasilewski@…||Triage Stage:||Design decision needed|
|Has patch:||yes||Needs documentation:||no|
|Needs tests:||no||Patch needs improvement:||no|
Note: The code is partially based on the #5062 ticket with several fixes, modifications
and improvements applied.
- Uses pyodbc (http://pyodbc.sourceforge.net) as the database driver. This seems to be the most mature SQL Server driver for Python and is still maintained, which is a big plus compared to other SQL Server drivers like adodbapi and pymssql. Pyodbc is also used by SQLAlchemy for SQL Server connections.
- Native Unicode support. Every string that goes in is stored as Unicode,
and every string that goes out of the database is returned as Unicode.
No conversion to/from intermediate encodings takes place, so things like
CharFiledworks just like expected.
- Limit/offset supported in SQL Server 2005 and SQL Server 2000. Yay!
- Both Windows Authentication (Integrated Security) and SQL Server Authentication supported.
- Passes most of the model test cases. The tests that don't work out of
the box are:
- empty: Corner-case. As far as I know SQL Server 2005 does not support that. There seems to be little use of it at all.
- lookup: Regular expressions are not supported out of the box. Only simple wildcard matching with %, _ and  character classes.
- serializers: Forward references cause foreign key constraint violation.
- Tested with SQL Server 2005 Express SP2 and SQL Server 2000 SP4, Python 2.5 and Windows XP.
- SQL Server collation support. I have added a collation parameter to the
Fieldconstructor to see how this will work.
- Is case sensitive/insensitive comparison using explicit collations a sensible thing?
- Introspection not tested yet.
- Support for ODBC specific configuration -- see module doc in base.py for DATABASE_SETTINGS options.
- SQL Server 2005 specific:
ntextis used for storing
TextFielddata. This allows for exact matching (
=) on this field type. (Unicode text parameters are passed to the SQL Server as
nvarchars, but the comparison of
ntextis not supported.)
nvarchar(max)type is not supported on previous SQL Server versions and some additional magic may be required. For now the exact matching on
TextFieldswill not work in older versions.