SQL Server connection - Works in pyodbc, but not SQLAlchemy

Question

This is a fairly common question but even using the answers on SO like here but I still can't connect.

When I setup my connection to pyodbc I can connect with the following:

cnxn = pyodbc.connect('DRIVER={SQL Server Native Client 11.0};SERVER=ip,port;DATABASE=db;UID=user;PWD=pass')
cursor = cnxn.cursor()

cursor.execute("some select query")
for row in cursor.fetchall():
    print(row)

and it works.

However to do a .read_sql() in pandas I need to connect with sqlalchemy.

I have tried with both hosted connections and pass-through pyodbc connections like the below:

quoted = urllib.parse.quote_plus('DRIVER={SQL Server Native Client 11.0};Server=ip;Database=db;UID=user;PWD=pass;Port=port;')
engine = sqlalchemy.create_engine('mssql+pyodbc:///?odbc_connect={}'.format(quoted))
engine.connect()

I have tried with both SERVER=ip,port format and the separate Port=port parameter like above but still no luck.

The error I'm getting is Login failed for user 'user'. (18456)

Any help is much appreciated.

Which edition of sqlserver you have installed? I have been facing many connection related issues and then I realized the culprit was the edition. — mad_
– mad_, Commented Jul 20, 2018 at 12:43

RobJan · Accepted Answer · 2018-07-20 10:12:45Z

1

I assume that you want to create a DataFrame so when you have a cnxn you can pass it to Pandas read_sql_query function. Example:

cnxn = pyodbc.connect('your connection string')
query = 'some query'

df = pandas.read_sql_query(query, conn)

answered Jul 20, 2018 at 10:12

RobJan

1,4411 gold badge17 silver badges19 bronze badges

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

Collectives™ on Stack Overflow

SQL Server connection - Works in pyodbc, but not SQLAlchemy

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related