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.

MaxU - stand with Ukraine · Accepted Answer · 2017-12-08 00:52:23Z

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))

answered Dec 8, 2017 at 0:52

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

lampShadesDrifter Over a year ago

Thanks, that worked. But what was wrong with what I was already doing? If sql alchemy was using the underlying pyodbc, why did it have problems when using the same connection string as a direct call to pyodbc.connect()?

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related