0

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.

swifty
  • 1,182
  • 1
  • 15
  • 36
  • 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_ Jul 20 '18 at 12:43

1 Answers1

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)
RobJan
  • 1,351
  • 1
  • 14
  • 18