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, Oct 04 '19 at 18:10

Bryan · Accepted Answer · 2018-06-05T13:01:07.900

70

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 05 '18 at 13:01

answered Apr 01 '13 at 20:08

Bryan

17,112
7
57
80

4

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]')` – Brad Campbell Apr 02 '13 at 18:04
1

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. – marr75 May 05 '14 at 18:12
That link is broken. I think this is a suitable replacement: http://docs.sqlalchemy.org/en/latest/core/engines.html#database-urls – BallpointBen Jun 04 '18 at 22:24
3

`engine = sa.create_engine('mssql+pyodbc://user:password@server/database')`
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')` – Sebastin Ignacio Camilla Trinc Jun 21 '20 at 11:32
@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. – Alex Deft Jan 04 '22 at 01:29

score 50 · Answer 2 · edited Aug 22 '18 at 14:52

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')" — Turanga1, Nov 01 '22 at 20:55

score 28 · Answer 3 · answered Apr 20 '16 at 14:42

28

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.

answered Apr 20 '16 at 14:42

Andrew

735
1
8
19

This is the only one that worked for me. Many thanks! – Barka Feb 20 '18 at 04:42
2

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 – Supun De Silva May 03 '20 at 00:06
@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. – Chicken Sandwich No Pickles May 26 '20 at 20:02
Note, other driver locations on *NIX systems work well also. On OSX I used `driver=/usr/local/lib/libtdsodbc.so` – Peter Apr 08 '21 at 21:58

ssword · Answer 4 · 2018-03-27T18:46:42.993

8

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 '18 at 18:46

answered Mar 27 '18 at 18:28

ssword

905
10
13

1

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. – shshnk Nov 18 '19 at 09:19
@shshnk, I have checked both `hostname,port` and `hostname:port` on my mac, both worked. – ssword Nov 18 '19 at 19:16
1

I am surprised that `:` worked. For sql server even if you see here https://stackoverflow.com/questions/5294721/how-to-specify-a-port-number-in-sql-server-connection-string . The answer is to use a `,`. I am using `ODBC+Driver+17+for+SQL+Server` if that makes any difference. – shshnk Nov 19 '19 at 04:56
@shshnk the comma is automatically translated somehow, its considered as like part of hostname i believe thats why both solutions work – kishea Nov 19 '19 at 08:28

score 2 · Answer 5 · answered Apr 20 '21 at 19:10

2

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.

answered Apr 20 '21 at 19:10

Teja Goud Kandula

1,462
13
26

score 1 · Answer 6 · edited Feb 12 '21 at 17:40

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())

Connecting to SQL Server 2012 using sqlalchemy and pyodbc

6 Answers6

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

Linked