Attempt to connect to MS SQL Server with sql alchemy throwing error that user is empty string

Question

Trying to connect to a MS SQL Server database with odbc and sqlalchemy to write a pandas.DataFrame to the database and getting an error that seems to imply that the user being used is an empty string (when one is provided the the connection string). Why would this be?

The relevant code snippet is:

import sqlalchemy

# setup db connection
server = 'myserver' 
database = 'mydb'
table = 'mytable'
username = 'username' 
password = 'password' 
cnxn_str = 'DSN=MyMSSQLServer;DATABASE='+database+';UID='+username+';PWD='+password+';MultipleActiveResultSets=True;'

engine = sqlalchemy.create_engine("mssql+pyodbc:///?odbc_connect=%s" % cnxn_str)

# testing connection
cnxn = engine.connect()
...

and the (abridged) error message thrown is:

---------------------------------------------------------------------------
InterfaceError                            Traceback (most recent call last)
<ipython-input-207-6807f223c9bb> in <module>()
     15 
     16 #Sample select query
---> 17 cnxn = engine.connect()
     18 result = connection.execute("SELECT @@version;")
     19 for row in result:

/home/mapr/anaconda2/lib/python2.7/site-packages/sqlalchemy/engine/base.py in connect(self, **kwargs)
   2016         """
   2017 
-> 2018         return self._connection_cls(self, **kwargs)
   2019 
   2020     def contextual_connect(self, close_with_result=False, **kwargs):
...
...
/home/mapr/anaconda2/lib/python2.7/site-packages/sqlalchemy/engine/strategies.py in connect(connection_record)
     95                         if connection is not None:
     96                             return connection
---> 97                 return dialect.connect(*cargs, **cparams)
     98 
     99             creator = pop_kwarg('creator', connect)

/home/mapr/anaconda2/lib/python2.7/site-packages/sqlalchemy/engine/default.py in connect(self, *cargs, **cparams)
    383 
    384     def connect(self, *cargs, **cparams):
--> 385         return self.dbapi.connect(*cargs, **cparams)
    386 
    387     def create_connect_args(self, url):

InterfaceError: (pyodbc.InterfaceError) ('28000', u"[28000] [unixODBC][Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Login failed for user ''. (18456) (SQLDriverConnect)")

What is also weird to me is that this same connection string works successfully earlier in the code when used to read from the same sql server, but in that case using cnxn = pyodbc.connect(cnxn_str) to establish connection (would do the same here, but according to the docs I need to use sqlalchemy if I want to write to anything other than sqlite3).

Can anyone explain why this is happening and how to fix it? Thanks.

Answer 1

Try to do it in a more "SQL Alchemy" way:

# pyodbc
engine = sqlalchemy.create_engine('mssql+pyodbc://{}:{}@MyMSSQLServer'.format(username, password ))

or

# pymssql
engine = sqlalchemy.create_engine('mssql+pymssql://{}:{}@{}:{}/{}'.format(username, password, server, port, database))