pyodbc / sqlalchmey / sqlserver

pyodbc.py

"""
I had some difficulties to connect to a local SQL Server 17 test database using sqlalchemy.

I made it work using these resources:
  - setup the sql server db: https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker?view=sql-server-2017
  - setup the python drivers (it's so painful...): https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017
  - SqlAlchemy documentation: http://docs.sqlalchemy.org/en/latest/dialects/mssql.html#connecting-to-pyodbc
 
Here are some of the errors that I encountered:

- pyodbc.OperationalError: ('HYT00', u'[HYT00] [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')
- DBAPIError: (pyodbc.Error) ('IM012', '[IM012] [unixODBC][Driver Manager]DRIVER keyword syntax error (0) (SQLDriverConnect)') (Background on this error at: http://sqlalche.me/e/dbapi)

"""

# Solution A: Using exact Pyodbc string
# -------------------------------------

import urllib.parse
from sqlalchemy import create_engine
connectString = 'Driver={ODBC Driver 17 for SQL Server};Server=localhost,1433;uid=SA;pwd=<YourNewStrong!Passw0rd>;Database=TestDB'
url = urllib.parse.quote(connectString)
engine = create_engine("mssql+pyodbc:///?odbc_connect=" + url)
engine.execute('select * from Inventory').fetchall()


# Solution B: Using Hostname Connections (not preferred - says the sqlalchemy doc)
# --------------------------------------

engine = create_engine("mssql+pyodbc://SA:<YourNewStrong!Passw0rd>@localhost:1433/TestDB?driver=ODBC+Driver+17+for+SQL+Server")
engine.execute('select * from Inventory').fetchall()

Extremely helpful, thank you.

Thank you so much!!

Thank you so much. It works for me.

Awesome. Thanks!

Excellent post, and thank you. My problem was not the same as yours. I had been using a script for many years implementing solution B already. On a new laptop I was encountering the DBAPIError DRIVER keyword syntax error. I was able to successfully run this script on the old laptop and a citrix VM. Changing from solution B to solution A corrected my issue. I can now run the revised script on all three systems without issue.

last line solved my problem. thanks mate