CODEWITHSUNDEEP
pythonsql-serverpandassqlalchemypyodbc
swifty
swifty

Reputation: 1282

SQL Server connection - Works in pyodbc, but not SQLAlchemy

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.

Upvotes: 0

Views: 403

Answers (1)

RobJan
RobJan

Reputation: 1441

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)

Upvotes: 1

Related Questions