- The hostname-based connection format for SQL Server when using

pyodbc will no longer specify a default "driver name", and a warning
is emitted if this is missing.  The optimal driver name for SQL Server
changes frequently and is per-platform, so hostname based connections
need to specify this. DSN-based connections are preferred.
fixes #3182

2 files changed, 50 insertions, 58 deletions

diff --git a/lib/sqlalchemy/connectors/pyodbc.py b/lib/sqlalchemy/connectors/pyodbc.py
index ef72c8049..907e4d353 100644
--- a/lib/sqlalchemy/connectors/pyodbc.py
+++ b/lib/sqlalchemy/connectors/pyodbc.py
@@ -26,7 +26,7 @@ class PyODBCConnector(Connector):
     supports_native_decimal = True
     default_paramstyle = 'named'
 
-    # for non-DSN connections, this should
+    # for non-DSN connections, this *may* be used to
     # hold the desired driver name
     pyodbc_driver_name = None
 
@@ -75,10 +75,21 @@ class PyODBCConnector(Connector):
                 if 'port' in keys and 'port' not in query:
                     port = ',%d' % int(keys.pop('port'))
 
-                connectors = ["DRIVER={%s}" %
-                              keys.pop('driver', self.pyodbc_driver_name),
-                              'Server=%s%s' % (keys.pop('host', ''), port),
-                              'Database=%s' % keys.pop('database', '')]
+                connectors = []
+                driver = keys.pop('driver', self.pyodbc_driver_name)
+                if driver is None:
+                    util.warn(
+                        "No driver name specified; "
+                        "this is expected by PyODBC when using "
+                        "DSN-less connections")
+                else:
+                    connectors.append("DRIVER={%s}" % driver)
+
+                connectors.extend(
+                    [
+                        'Server=%s%s' % (keys.pop('host', ''), port),
+                        'Database=%s' % keys.pop('database', '')
+                    ])
 
             user = keys.pop("user", None)
             if user:
diff --git a/lib/sqlalchemy/dialects/mssql/pyodbc.py b/lib/sqlalchemy/dialects/mssql/pyodbc.py
index 1c75fe1ff..445584d24 100644
--- a/lib/sqlalchemy/dialects/mssql/pyodbc.py
+++ b/lib/sqlalchemy/dialects/mssql/pyodbc.py
@@ -12,74 +12,57 @@
     :connectstring: mssql+pyodbc://<username>:<password>@<dsnname>
     :url: http://pypi.python.org/pypi/pyodbc/
 
-Additional Connection Examples
--------------------------------
+Connecting to PyODBC
+--------------------
 
-Examples of pyodbc connection string URLs:
+The URL here is to be translated to PyODBC connection strings, as
+detailed in `ConnectionStrings <https://code.google.com/p/pyodbc/wiki/ConnectionStrings>`_.
 
-* ``mssql+pyodbc://mydsn`` - connects using the specified DSN named ``mydsn``.
-  The connection string that is created will appear like::
+DSN Connections
+^^^^^^^^^^^^^^^
 
-    dsn=mydsn;Trusted_Connection=Yes
+A DSN-based connection is **preferred** overall when using ODBC.  A
+basic DSN-based connection looks like::
 
-* ``mssql+pyodbc://user:pass@mydsn`` - connects using the DSN named
-  ``mydsn`` passing in the ``UID`` and ``PWD`` information. The
-  connection string that is created will appear like::
+    engine = create_engine("mssql+pyodbc://scott:tiger@some_dsn")
 
-    dsn=mydsn;UID=user;PWD=pass
-
-* ``mssql+pyodbc://user:pass@mydsn/?LANGUAGE=us_english`` - connects
-  using the DSN named ``mydsn`` passing in the ``UID`` and ``PWD``
-  information, plus the additional connection configuration option
-  ``LANGUAGE``. The connection string that is created will appear
-  like::
-
-    dsn=mydsn;UID=user;PWD=pass;LANGUAGE=us_english
-
-* ``mssql+pyodbc://user:pass@host/db`` - connects using a connection
-  that would appear like::
+Which above, will pass the following connection string to PyODBC::
 
-    DRIVER={SQL Server};Server=host;Database=db;UID=user;PWD=pass
-
-* ``mssql+pyodbc://user:pass@host:123/db`` - connects using a connection
-  string which includes the port
-  information using the comma syntax. This will create the following
-  connection string::
-
-    DRIVER={SQL Server};Server=host,123;Database=db;UID=user;PWD=pass
+    dsn=mydsn;UID=user;PWD=pass
 
-* ``mssql+pyodbc://user:pass@host/db?port=123`` - connects using a connection
-  string that includes the port
-  information as a separate ``port`` keyword. This will create the
-  following connection string::
+If the username and password are omitted, the DSN form will also add
+the ``Trusted_Connection=yes`` directive to the ODBC string.
 
-    DRIVER={SQL Server};Server=host;Database=db;UID=user;PWD=pass;port=123
+Hostname Connections
+^^^^^^^^^^^^^^^^^^^^
 
-* ``mssql+pyodbc://user:pass@host/db?driver=MyDriver`` - connects using a
-  connection string that includes a custom ODBC driver name.  This will create
-  the following connection string::
+Hostname-based connections are **not preferred**, however are supported.
+The ODBC driver name must be explicitly specified::
 
-    DRIVER={MyDriver};Server=host;Database=db;UID=user;PWD=pass
+    engine = create_engine("mssql+pyodbc://scott:tiger@myhost:port/databasename?driver=SQL+Server+Native+Client+10.0")
 
-If you require a connection string that is outside the options
-presented above, use the ``odbc_connect`` keyword to pass in a
-urlencoded connection string. What gets passed in will be urldecoded
-and passed directly.
+.. versionchanged:: 1.0.0 Hostname-based PyODBC connections now require the
+   SQL Server driver name specified explicitly.  SQLAlchemy cannot
+   choose an optimal default here as it varies based on platform
+   and installed drivers.
 
-For example::
+Other keywords interpreted by the Pyodbc dialect to be passed to
+``pyodbc.connect()`` in both the DSN and hostname cases include:
+``odbc_autotranslate``, ``ansi``, ``unicode_results``, ``autocommit``.
 
-    mssql+pyodbc:///?odbc_connect=dsn%3Dmydsn%3BDatabase%3Ddb
+Pass through exact Pyodbc string
+^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
 
-would create the following connection string::
+A PyODBC connection string can also be sent exactly as specified in
+`ConnectionStrings <https://code.google.com/p/pyodbc/wiki/ConnectionStrings>`_
+into the driver using the parameter ``odbc_connect``.  The delimeters must be URL escaped, however,
+as illustrated below using ``urllib.quote_plus``::
 
-    dsn=mydsn;Database=db
+    import urllib
+    params = urllib.quote_plus("DRIVER={SQL Server Native Client 10.0};SERVER=dagger;DATABASE=test;UID=user;PWD=password")
 
-Encoding your connection string can be easily accomplished through
-the python shell. For example::
+    engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)
 
-    >>> import urllib
-    >>> urllib.quote_plus('dsn=mydsn;Database=db')
-    'dsn%3Dmydsn%3BDatabase%3Ddb'
 
 Unicode Binds
 -------------
@@ -243,8 +226,6 @@ class MSDialect_pyodbc(PyODBCConnector, MSDialect):
 
     execution_ctx_cls = MSExecutionContext_pyodbc
 
-    pyodbc_driver_name = 'SQL Server'
-
     colspecs = util.update_copy(
         MSDialect.colspecs,
         {