Python Connect To SQLServer Express

On windows 7 running python 3.4.  Trying to connect to SQLServer Express with the connection:

conn = pymssql.connect('SQLEXPRESS', 'sa', 'master', 'tempdb')


C:\Python34>python sqlserver.py
Traceback (most recent call last):
  File "pymssql.pyx", line 630, in pymssql.connect (pymssql.c:10097)
  File "_mssql.pyx", line 1887, in _mssql.connect (_mssql.c:20477)
  File "_mssql.pyx", line 632, in _mssql.MSSQLConnection.__init__ (_mssql.c:6169
)
_mssql.MSSQLDriverException: Connection to the database failed for an unknown re
ason.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "sqlserver.py", line 7, in <module>
    conn = pymssql.connect('SQLEXPRESS', 'sa', 'master', 'tempdb')
  File "pymssql.pyx", line 639, in pymssql.connect (pymssql.c:10246)
pymssql.InterfaceError: Connection to the database failed for an unknown reason.
xoxomosAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
According to this page http://pymssql.sourceforge.net/examples_pymssql.php , you are missing quite a bit in your connect statement at the very least.  This http://pymssql.org/en/latest/ appears to be the current page.
xoxomosAuthor Commented:
from os import getenv
import pymssql
server = getenv("PYMSSQL_TEST_SERVER")
user = getenv("PYMSSQL_TEST_USERNAME")
password = getenv("PYMSSQL_TEST_PASSWORD")
#conn = pymssql.connect(server, user, password, "tempdb")
conn = pymssql.connect(server="SQLEXPRESS",  user="sa", password="master", database="tempdb")
cursor = conn.cursor()
..
.
.C:\Python34>python sqlserver.py
Traceback (most recent call last):
  File "pymssql.pyx", line 630, in pymssql.connect (pymssql.c:10097)
  File "_mssql.pyx", line 1887, in _mssql.connect (_mssql.c:20477)
  File "_mssql.pyx", line 632, in _mssql.MSSQLConnection.__init__ (_mssql.c:6169
)
_mssql.MSSQLDriverException: Connection to the database failed for an unknown re
ason.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "sqlserver.py", line 7, in <module>
    conn = pymssql.connect(server="SQLEXPRESS", user="sa", password="master", da
tabase="tempdb")
  File "pymssql.pyx", line 639, in pymssql.connect (pymssql.c:10246)
pymssql.InterfaceError: Connection to the database failed for an unknown reason.


That freetds generated a directory of it's own c:\freetds-0.95rc2.
Is it necessary for it's bin directory to be in PATH?
xoxomosAuthor Commented:
Changed the server from SQLEXPRESS to localhost.  Getting slightly different errors.

C:\Python34>python sqlserver.py
Traceback (most recent call last):
  File "pymssql.pyx", line 630, in pymssql.connect (pymssql.c:10097)
  File "_mssql.pyx", line 1887, in _mssql.connect (_mssql.c:20477)
  File "_mssql.pyx", line 631, in _mssql.MSSQLConnection.__init__ (_mssql.c:6156
)
  File "_mssql.pyx", line 1612, in _mssql.maybe_raise_MSSQLDatabaseException (_m
ssql.c:16426)
_mssql.MSSQLDatabaseException: (20009, b'DB-Lib error message 20009, severity 9:
\nUnable to connect: Adaptive Server is unavailable or does not exist\nNet-Lib e
rror during Unknown error (10035)\n')

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "sqlserver.py", line 7, in <module>
    conn = pymssql.connect(server="localhost", user="sa", password="master", dat
abase="tempdb")
  File "pymssql.pyx", line 636, in pymssql.connect (pymssql.c:10178)
pymssql.OperationalError: (20009, b'DB-Lib error message 20009, severity 9:\nUna
ble to connect: Adaptive Server is unavailable or does not exist\nNet-Lib error
during Unknown error (10035)\n')

C:\Python34>
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Dave BaldwinFixer of ProblemsCommented:
How did you get FreeTDS to rub under Windows?  Usually, you have to install SQL Native Client for your version of SQL Server Express.  The advantage of that is that you can test it in the ODBC manager to make sure your connections and credentials are working.
xoxomosAuthor Commented:
SQL Native Client was just there.   I'm sure I did not do anything to install it.
Dave BaldwinFixer of ProblemsCommented:
SQL Native Client is installed with SQL Server Express.  But that is only on the machine that SQL Server is installed on.  If you were running on another machine trying to make a remote connection, you would have to install it on the remote machine.

Did you create a DSN to test your credentials with the SQL Native Client in the ODBC manager?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
xoxomosAuthor Commented:
In this case the machine is local, the desktop.  Yes, the ODBC manager says there is a successful connection.
xoxomosAuthor Commented:
Looking in the module

pymssql.pyx  line 573

try:
        conn = _mssql.connect(server, user, password, charset, database,  appname, port)
Here there are seven parameters to be passes
however the example shows only four parameters
conn = pymssql.connect(host='134.154.228.217', user='sa', password='master', database='master')
xoxomosAuthor Commented:
Had to get sqlserver listening on 1433 instead of some number fifty-thousand port.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Python

From novice to tech pro — start learning today.