Unable to connect to MSSQL Server from Python on Windows

Question

I'm unable to connect to SQL Server from Python (3.4.4 64 bit) on Windows 10. This is what I did:

1. I found this nice library.
2. Then, I followed this page and installed FreeTDS
3. After that I installed pymssql with this command: easy_install pymssql
4. In SQL Server Network Configuration I enabled Named Pipes and TCP/IP for my SQLEXPRESS instance

So, at this moment I can run SQL Server and connect to my database, using SQL Server Management Studio. When I log in, I use DESKTOP-1JA5E9F\SQLEXPRESS as Server Name, sa as Login and 123 as Password. Besides, in Python shell I can import pymssql like:

>>> import pymssql

It does not raise any error. However, I can not connect to my database instance. I tried dozens attempts like:

 conn = pymssql.connect(host=r'DESKTOP-1JA5E9F\SQLEXPRESS', 
                        user=r'sa', password=r'123', database=r'reestr')

The code above ^^^ never completes (I see just blinking _ in the shell, that is blinking for ever). I also tried this:

conn = pymssql.connect(host=r'SQLEXPRESS', user=r'sa', password=r'123', database=r'reestr')

This results in pymssql.InterfaceError: Connection to the database failed for an unknown reason.. I tried this:

conn=pymssql.connect(host=r'SQLEXPRESS:1433',user=r'sa',password=r'123', database=r'reestr')

It also results in the same error message. So, I if anybody knows those magic voodoo spells that can do the trick, you are welcome.

Answer 1

Acording to the docs, there's no host keyword arg anymore, but server. Also it should be server name, not instance name, or full instance name (with server name). See connect() description , and examples of name construction for Connection class.

In your case server name is DESKTOP-1JA5E9F, also . and (local) should work since you do it all on local machine; your named instance name is SQLEXPRESS.

Try like these:

import pymssql

#for instance with known name 'SQLEXPRESS'
conn = pymssql.connect(server=r'DESKTOP-1JA5E9F\SQLEXPRESS', 
                       user=r'sa', password=r'123', database=r'reestr')
#on localhost this should work too
conn = pymssql.connect(server=r'.\SQLEXPRESS', 
                       user=r'sa', password=r'123', database=r'reestr')

#for default instance with port taken from freetds.conf
#(this probably won't work for your case, because you use named instance
#instead of default instance, which is named MSSQLSERVER)
conn = pymssql.connect(server=r'DESKTOP-1JA5E9F', user=r'sa', password=r'123',    
                       database=r'reestr')

#for instance on known port '1433'
conn = pymssql.connect(server=r'DESKTOP-1JA5E9F:1433', user=r'sa', password=r'123',
                       database=r'reestr')

If this won't help, test the connection with tsql as described in the docs, e.g.:

tsql -H DESKTOP-1JA5E9F -p 1433 -U sa -P 123 -D reestr

or if you have freetds.conf:

tsql -S 'DESKTOP-1JA5E9F\SQLEXPRESS' -U sa -P 123 -D reestr