PyODBC with MSSQL and Pandas

PYODBC is an open source Python module that makes accessing ODBC databases simple. It implements the DB API 2.0 specification but is packed with even more Pythonic convenience.

Source: https://github.com/mkleehammer/pyodbc

pip install pyodbc

In This code example checks the available driver that ends up with “SQL Server”.

import pyodbc

driver_name = ‘’

driver_names = [x for x in pyodbc.drivers() if x.endswith(‘SQL Server’)]

if driver_names:

drivername = drivernames[0]

if driver_name:

connstr = ‘DRIVER={}; …’.format(drivername)

print(conn_str)

# then continue with …

# pyodbc.connect(conn_str)

# … etc.

else:

print(‘(No suitable driver found. Cannot connect.)’)

For this next example will be using SQL Server to demonstrate the use of PyODBC.

Below shows the sample table we use name as “Student table”.

Using the table above, will fetch the record using For loop.

import pyodbc

# Initialize the SQL SERVER Connection

conn = pyodbc.connect(‘Driver={SQL Server};’

‘Server=(local);’

‘Database=PyODBC-master;’

‘Uid=sa;’

‘Pwd=12345;’

‘Trusted_Connection=yes;’

‘Connection Timeout=30;’)

cursor = conn.cursor()

cursor.execute(‘SELECT * from [PyODBC-master].[dbo].[Student]’)

rows = cursor.fetchall()

for row in rows:

print(row)

You can also combine with pandas then export as csv from the example below.

pip install pandas

import pandas as pd

import pyodbc

# Initialize the SQL SERVER Connection

conn = pyodbc.connect(‘Driver={SQL Server};’

‘Server=(local);’

‘Database=PyODBC-master;’

‘Uid=sa;’

‘Pwd=12345;’

‘Trusted_Connection=yes;’

‘Connection Timeout=30;’)

cursor = conn.cursor()

sqlquery = pd.readsql_query(‘SELECT * from [PyODBC-master].[dbo].[Student]’,conn)

print(sql_query)

print(type(sql_query))

sqlquery.tocsv(“all_data.csv”,index=False)

Or you can also use PyODBC to export in excel as well with pandas.

pip install openpyxl

import pyodbc

import pandas as pd

# Initialize the SQL SERVER Connection

conn = pyodbc.connect(‘Driver={SQL Server};’

‘Server=(local);’

‘Database=PyODBC-master;’

‘Uid=sa;’

‘Pwd=12345;’

‘Trusted_Connection=yes;’

‘Connection Timeout=30;’)

tableResult = pd.read_sql(‘SELECT * from [PyODBC-master].[dbo].[Student]’, conn)

# Copy to Clipboard

#df=pd.DataFrame(tableResult)

#df.to_clipboard(index=False,header=True)

# Or create a Excel file with the results

df=pd.DataFrame(tableResult)

df.toexcel(“FileExample.xlsx”,sheetname=’Results’)

Now a days, PyODBC was already a widely use, also you can use that to any database as well. Feel free to try.

Thank you!