Connecting to SQL Server 2012 using sqlalchemy and pyodbc

Question

I'm trying to connect to a SQL Server 2012 database using SQLAlchemy (with pyodbc) on Python 3.3 (Windows 7-64-bit). I am able to connect using straight pyodbc but have been unsuccessful at connecting using SQLAlchemy. I have dsn file setup for the database access.

I successfully connect using straight pyodbc like this:

con = pyodbc.connect('FILEDSN=c:\\users\\me\\mydbserver.dsn')

For sqlalchemy I have tried:

import sqlalchemy as sa engine = sa.create_engine('mssql+pyodbc://c/users/me/mydbserver.dsn/mydbname')

The create_engine method doesn't actually set up the connection and succeeds, but iIf I try something that causes sqlalchemy to actually setup the connection (like engine.table_names()), it takes a while but then returns this error:

DBAPIError: (Error) ('08001', '[08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. (17) (SQLDriverConnect)') None None

I'm not sure where thing are going wrong are how to see what connection string is actually being passed to pyodbc by sqlalchemy. I have successfully using the same sqlalchemy classes with SQLite and MySQL.

I found the SQLAlchemy documentation to be woefully inadequate on this topic. Improvements there are warranted. — Maddenker
– Maddenker, Commented Oct 4, 2019 at 18:10

Bryan · Accepted Answer · 2018-06-05 13:01:07Z

74

The file-based DSN string is being interpreted by SQLAlchemy as server name = c, database name = users.

I prefer connecting without using DSNs, it's one less configuration task to deal with during code migrations.

This syntax works using Windows Authentication:

engine = sa.create_engine('mssql+pyodbc://server/database')

Or with SQL Authentication:

engine = sa.create_engine('mssql+pyodbc://user:password@server/database')

SQLAlchemy has a thorough explanation of the different connection string options here.

edited Jun 5, 2018 at 13:01

answered Apr 1, 2013 at 20:08

Bryan

17.7k7 gold badges59 silver badges81 bronze badges

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

5 Comments

Brad Campbell Over a year ago

Thanks. The SQL Server instance is the only one that is not on the machine I am working on so I wasn't sure if there was something funny going on here. Just to expand a little on the stings you listed (since sql server instances are apparently named) - sa.create_engine('mssql+pyodbc://[machinename]\\[servername]/[database]')

marr75 Over a year ago

They don't have to be named. It's actually easier to connect to and use a sql server instance configured as a "default instance". Named instances are required when you will be hosting multiple sql server instances on one server.

BallpointBen Over a year ago

That link is broken. I think this is a suitable replacement: docs.sqlalchemy.org/en/latest/core/engines.html#database-urls

Sebastin Ignacio Camilla Trinc Over a year ago

engine = sa.create_engine('mssql+pyodbc://user:password@server/database') <br> you have to add this ?driver=SQL+Server+Native+Client+11.0 so final will be engine = sa.create_engine('mssql+pyodbc://user:password@server/database?driver=SQL+Server+Native+Client+11.0')

Alex Deft Over a year ago

@Bryan In reference to the synax you mentioned engine = sa.create_engine('mssql+pyodbc://user:password@server/database'), what if the password has @ in it? what can I do? Its being parsed wrongly by alchemy. EDIT: found the solution in the docs link, there is option to pass string to pyodbc directly to abvoid misinterpretations.

Vadim Shkaberda · Accepted Answer · 2018-08-22 14:52:14Z

In Python 3 you can use function quote_plus from module urllib.parse to create parameters for connection:

import urllib params = urllib.parse.quote_plus("DRIVER={SQL Server Native Client 11.0};" "SERVER=dagger;" "DATABASE=test;" "UID=user;" "PWD=password") engine = sa.create_engine("mssql+pyodbc:///?odbc_connect={}".format(params))

In order to use Windows Authentication, you want to use Trusted_Connection as parameter:

params = urllib.parse.quote_plus("DRIVER={SQL Server Native Client 11.0};" "SERVER=dagger;" "DATABASE=test;" "Trusted_Connection=yes")

In Python 2 you should use function quote_plus from library urllib instead:

params = urllib.quote_plus("DRIVER={SQL Server Native Client 11.0};" "SERVER=dagger;" "DATABASE=test;" "UID=user;" "PWD=password")

This may require special attention to the character encoding, e.g. add ".encode('utf-8')"

Andrii · Accepted Answer · 2016-04-20 14:42:29Z

I have an update info about the connection to MSSQL Server without using DSNs and using Windows Authentication. In my example I have next options: My local server name is "(localdb)\ProjectsV12". Local server name I see from database properties (I am using Windows 10 / Visual Studio 2015). My db name is "MainTest1"

engine = create_engine('mssql+pyodbc://(localdb)\ProjectsV12/MainTest1?driver=SQL+Server+Native+Client+11.0', echo=True)

It is needed to specify driver in connection. You may find your client version in:

control panel>Systems and Security>Administrative Tools.>ODBC Data Sources>System DSN tab>Add

Look on SQL Native client version from the list.

In my case?driver=SQL+Server+Native+Client+11.0 was the bit of string I was looking for. Was too slack to find my old code :D
@SupunDeSilva this is exactly what I needed myself. Thank you so much. I wish I could have upvoted your comment and andrew 's answer 10000 times.
Note, other driver locations on *NIX systems work well also. On OSX I used driver=/usr/local/lib/libtdsodbc.so

ssword · Accepted Answer · 2018-03-27 18:46:42Z

9

Just want to add some latest information here: If you are connecting using DSN connections:

engine = create_engine("mssql+pyodbc://USERNAME:PASSWORD@SOME_DSN")

If you are connecting using Hostname connections:

engine = create_engine("mssql+pyodbc://USERNAME:PASSWORD@HOST_IP:PORT/DATABASENAME?driver=SQL+Server+Native+Client+11.0")

For more details, please refer to the "Official Document"

edited Mar 27, 2018 at 18:46

answered Mar 27, 2018 at 18:28

ssword

94510 silver badges13 bronze badges

4 Comments

shshnk Over a year ago

This answer helped me but just adding that if someone is mentioning port as well then it should be given as hostname,port and not as hostname:port. mssql expects a comma and not colon.

ssword Over a year ago

@shshnk, I have checked both hostname,port and hostname:port on my mac, both worked.

shshnk Over a year ago

I am surprised that : worked. For sql server even if you see here stackoverflow.com/questions/5294721/… . The answer is to use a ,. I am using ODBC+Driver+17+for+SQL+Server if that makes any difference.

kishea Over a year ago

@shshnk the comma is automatically translated somehow, its considered as like part of hostname i believe thats why both solutions work

Davide Fiocco · Accepted Answer · 2021-02-12 17:40:28Z

I did different and worked like a charm.

First you import the library:

import pandas as pd from sqlalchemy import create_engine import pyodbc

Create a function to create the engine

def mssql_engine(user = os.getenv('user'), password = os.getenv('password') ,host = os.getenv('SERVER_ADDRESS'),db = os.getenv('DATABASE')): engine = create_engine(f'mssql+pyodbc://{user}:{password}@{host}/{db}?driver=SQL+Server') return engine

Create a variable with your query

query = 'SELECT * FROM [Orders]'

Execute the Pandas command to create a Dataframe from a MSSQL Table

df = pd.read_sql(query, mssql_engine())

Teja Goud Kandula · Accepted Answer · 2021-04-20 19:10:47Z

import pyodbc import sqlalchemy as sa engine = sa.create_engine('mssql+pyodbc://ServerName/DatabaseName?driver=SQL+Server+Native+Client+11.0',echo = True)

This works with Windows Authentication.

Collectives™ on Stack Overflow

Connecting to SQL Server 2012 using sqlalchemy and pyodbc

6 Answers 6

5 Comments

2 Comments

4 Comments

4 Comments

First you import the library:

Create a function to create the engine

Create a variable with your query

Execute the Pandas command to create a Dataframe from a MSSQL Table

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

5 Comments

2 Comments

4 Comments

4 Comments

First you import the library:

Create a function to create the engine

Create a variable with your query

Execute the Pandas command to create a Dataframe from a MSSQL Table

Comments

Comments

Linked

Related