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

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)

Collectives™ on Stack Overflow

SQL Server connection - Works in pyodbc, but not SQLAlchemy

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related