Connecting to Microsoft SQL server using Python

Question

I am trying to connect to SQL through python to run some queries on some SQL databases on Microsoft SQL server. From my research online and on this forum the most promising library seems to be pyodbc. So I have made the following code

import pyodbc
conn = pyodbc.connect(init_string="driver={SQLOLEDB}; server=+ServerName+; 
database=+MSQLDatabase+; trusted_connection=true")
cursor = conn.cursor()

and get the following error

Traceback (most recent call last):
  File "C:\Users...\scrap.py", line 3, in <module>
    conn = pyodbc.connect(init_string="driver={SQLOLEDB}; server=+ServerName+; database=+MSQLDatabase+; trusted_connection=true")
pyodbc.Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

I have looked at the folowing posts and tried changing my driver to {sql server} and have connected using ODBC links before in SAS, which is partially what my above code is based on, so don't think I need to install anything else.

pyodbc.Error: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)')

Pyodbc - "Data source name not found, and no default driver specified"

Thanks

Crystalloid

Here are some pics for newbies.  – Nosegay

For anyone looking to determine what python package they should use with SQL Server and seeing the variety of packages in these answers, note that Microsoft says "There are several python SQL drivers available. However, Microsoft places its testing efforts and its confidence in pyodbc driver." – Celibate

Answer 1

This is how I do it...

import pyodbc 
cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
                      "Server=server_name;"
                      "Database=db_name;"
                      "Trusted_Connection=yes;")


cursor = cnxn.cursor()
cursor.execute('SELECT * FROM Table')

for row in cursor:
    print('row = %r' % (row,))

Relevant resources:

• https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQL-Server-from-Windows

• http://blogs.msdn.com/b/cdndevs/archive/2015/03/11/python-and-data-sql-server-as-a-data-source-for-python-applications.aspx

Moss

Do not forget to e.g. conda install -c anaconda pyodbc / pip install pyodbc – Azote

In the not too distant future, that should now be pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=server.lan,1433;DATABASE=database;UID=user;PWD=pass'); after following these instruction (or their non-linux versions) learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/… – Poland

Answer 2

Minor addition to what has been said before. You likely want to return a dataframe. This would be done as

import pypyodbc 
import pandas as pd

cnxn = pypyodbc.connect("Driver={SQL Server Native Client 11.0};"
                        "Server=server_name;"
                        "Database=db_name;"
                        "uid=User;pwd=password")
df = pd.read_sql_query('select * from table', cnxn)

Chancelor

I am a bit confused by this. If I am running my python code on a linux server (say, red hat 7) I just need to write driver="sql driver version name.." and not the PATH on the server to a driver? I always had trouble connecting to mssql server from python in the past. – Evvy

@Evvy I think that the driver is something stored in pypyodbc. – Chancelor

Answer 3

In data source connections between a client and server there are two general types: ODBC which uses a DRIVER and OLEDB which uses a PROVIDER. And in the programming world, it is a regular debate as to which route to go in connecting to data sources.

You are using a provider, SQLOLEDB, but specifying it as a driver. As far as I know, neither the pyodbc nor pypyodbc modules support Window OLEDB connections. However, the adodbapi does which uses the Microsoft ADO as an underlying component.

Below are both approaches for your connection parameters. Also, I string format your variables as your concatenation did not properly break quotes within string. You'll notice I double the curly braces since it is needed in connection string and string.format() also uses it.

# PROVIDER
import adodbapi
conn = adodbapi.connect("PROVIDER=SQLOLEDB;Data Source={0};Database={1}; \
       trusted_connection=yes;UID={2};PWD={3};".format(ServerName,MSQLDatabase,username,password))
cursor = conn.cursor()

# DRIVER
import pyodbc
conn = pyodbc.connect("DRIVER={{SQL Server}};SERVER={0}; database={1}; \
       trusted_connection=yes;UID={2};PWD={3}".format(ServerName,MSQLDatabase,username,password))
cursor = conn.cursor()

Rois

Thanks for the explanation and code I got the driver one to work. Although I had to get rid of the .format(...) and put the variables in the proper places. What was the format meant to do? – Crystalloid

You need to install adodbapi to use OLEDB connection. And string format is the recommended way to pass variables into a string rather than using the + operator. The curly braces with numbers are placeholders which format() fills in accordingly. You can even pass in lists and tuples using format(). Your original code did not break string and variables by quotes, so + was considered part of string. – Rois

While this answer is great and helped me to get the issue resolved. whoever is trying to do it remember you may get an exception if you set trusted connection= yes and enter the UID/pwd in the same connection string. This is a either/or combination & when you use trusted connection your NT/system credential is used for authentication even if you are explicitly mentioning UID/PWD. – Magdalenmagdalena

Answer 4

I Prefer this way ... it was much easier

http://www.pymssql.org/en/stable/pymssql_examples.html

conn = pymssql.connect("192.168.10.198", "odoo", "secret", "EFACTURA")
cursor = conn.cursor()
cursor.execute('SELECT * FROM usuario')

Peradventure

This project has been discontinued: github.com/pymssql/pymssql – Weldonwelfare

BUT! as of August 2020 it is no longer depreciated. you can see the repo is active again: github.com/pymssql/pymssql – Anaclinal

Answer 5

Following Python code worked for me. To check the ODBC connection, I first created a 4 line C# console application as listed below.

Python Code

import pandas as pd
import pyodbc 
cnxn = pyodbc.connect("Driver={SQL Server};Server=serverName;UID=UserName;PWD=Password;Database=My_DW;")
df = pd.read_sql_query('select TOP 10 * from dbo.Table WHERE Patient_Key > 1000', cnxn)
df.head()

Calling a Stored Procedure

 dfProcResult = pd.read_sql_query('exec dbo.usp_GetPatientProfile ?', cnxn, params=['MyParam'] )

C# Program to Check ODBC Connection

    static void Main(string[] args)
    {
        string connectionString = "Driver={SQL Server};Server=serverName;UID=UserName;PWD=Password;Database=My_DW;";
        OdbcConnection cn = new OdbcConnection(connectionString);
        cn.Open();
        cn.Close();
    }

Electronics

Answer 6

Try using pytds, it works throughout more complexity environment than pyodbc and more easier to setup.

I made it work on Ubuntu 18.04

Ref: https://github.com/denisenkom/pytds

Example code in documentation:

import pytds
with pytds.connect('server', 'database', 'user', 'password') as conn:
    with conn.cursor() as cur:
        cur.execute("select 1")
        cur.fetchall()

Chaffee

Thank you. Works like a charm without any complex setup. – Virile

I spent a long time trying to make pyodbc and pymssql work on M1 MacBook Pro and still failed. pytds just works directly! Thanks for sharing! – Poaceous

Answer 7

here's the one that works for me:

from sqlalchemy import create_engine
import urllib
import pandas

conn_str = (
r'Driver=ODBC Driver 13 for SQL Server;'
r'Server=DefinitelyNotProd;'
r'Database=PlayPen;'
r'Trusted_Connection=Yes;')

quoted_conn_str = urllib.parse.quote_plus(conn_str)
engine = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(quoted_conn_str))
sqlcmd = """select * from information_schema.tables"""
df = pd.read_sql(sqlcmd, engine)

Korikorie

Answer 8

Try with pymssql: pip install pymssql

import pymssql

try:
    conn = pymssql.connect(server="host_or_ip", user="your_username", password="your_password", database="your_db")
    cursor = conn.cursor()
    cursor.execute ("SELECT @@VERSION")
    row = cursor.fetchone()
    print(f"\n\nSERVER VERSION:\n\n{row[0]}")
    cursor.close()
    conn.close()
except Exception:
    print("\nERROR: Unable to connect to the server.")
    exit(-1)

Output:

SERVER VERSION:

Microsoft SQL Server 2016 (SP2-CU14) (KB4564903) - 13.0.5830.85 (X64)
        Jul 31 2020 18:47:07
        Copyright (c) Microsoft Corporation
        Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

The connection can also be checked from the terminal, with a single line of code with sqlcmd. See syntax.

╔═════════╦═════════════════════════════════════════╗
║ Command ║               Description               ║
╠═════════╬═════════════════════════════════════════╣
║   -S    ║ [protocol:]server[instance_name][,port] ║
║   -U    ║ login_id                                ║
║   -p    ║ password                                ║
║   -Q    ║ "cmdline query" (and exit)              ║
╚═════════╩═════════════════════════════════════════╝

sqlcmd -S "host_or_ip"  -U "your_username" -p -Q "SELECT @@VERSION"

output:

Password:    your_password



--------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2016 (SP2-CU14) (KB4564903) - 13.0.5830.85 (X64) 
        Jul 31 2020 18:47:07 
        Copyright (c) Microsoft Corporation
        Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)


(1 rows affected)

Network packet size (bytes): 4096
1 xact[s]:
Clock Time (ms.): total         1  avg   1.00 (1000.00 xacts per sec.)

Hood

Answer 9

I tried to connect sql server in following ways and those worked for me.

To connect using windows authentication

import pyodbc

conn = pyodbc.connect('Driver={SQL Server};Server='+servername+';Trusted_Connection=yes;Database='+databasename+';')
cursor = conn.cursor()
cursor.execute("Select 1 as Data")

To use sql server authentication I used following code.

import pyodbc

conn = pyodbc.connect('Driver={SQL Server};Server='+servername+  ';UID='+userid+';PWD='+password+';Database='+databasename) 
cursor1 = conn.cursor()
cursor1.execute("SELECT 1 AS DATA")

Mccreary

Answer 10

My version. Hope it helps.

import pandas.io.sql
import pyodbc
import sys

server = 'example'
db = 'NORTHWND'
db2 = 'example'

#Crear la conexión
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=' + server +
                      ';DATABASE=' + db +
                      ';DATABASE=' + db2 +
                      ';Trusted_Connection=yes')
#Query db
sql = """SELECT [EmployeeID]
      ,[LastName]
      ,[FirstName]
      ,[Title]
      ,[TitleOfCourtesy]
      ,[BirthDate]
      ,[HireDate]
      ,[Address]
      ,[City]
      ,[Region]
      ,[PostalCode]
      ,[Country]
      ,[HomePhone]
      ,[Extension]
      ,[Photo]
      ,[Notes]
      ,[ReportsTo]
      ,[PhotoPath]
  FROM [NORTHWND].[dbo].[Employees] """
data_frame = pd.read_sql(sql, conn)
data_frame

Dimpledimwit

Answer 11

This is how I had done it.

import pyodbc



connection = pyodbc.connect("DRIVER={SQL Server Native Client 10.0};"
                            
                            "SERVER=server_name;"
                            
                            "DATABASE=database_name;"
                            
                            "UID=user_id_of_database;"
                            
                            "PWD=password_of_database;")


cursor = connection.cursor()

cursor.execute('SELECT * FROM Table')


Always make sure you had specified the correct Driver. You can check your Driver by following the steps given below.

1. Open the Windows Control Panel.
2. Open the Administrative Tools folder.
3. Double-click Data Sources (ODBC) to open the ODBC Data Source Administrator window.
4. Click the Drivers tab

Listen

Answer 12

An alternative approach would be installing Microsoft ODBC Driver 13, then replace SQLOLEDB with ODBC Driver 13 for SQL Server

Regards.

Chemosynthesis

Answer 13

I found up-to-date resources here: Microsoft | SQL Docs | Python SQL Driver

There are these two options explained including all the prerequisites needed and code examples: Python SQL driver - pyodbc (tested & working) Python SQL driver - pymssql

Prowel

Hi - Welcome to Stack Overflow - you should aim to address the question with some ideas(new ideas in this case) - Some of your own code or a new approach. Then use some links in order to provide more aid or back up your solution. You shouldn't just post some links. – Dreyfus