Connecting to Microsoft SQL server using Python

Question

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

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)

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

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

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

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