Use SQL in Python after Connecting with a Database

Python / April 20, 2020

In this guide, I’ll show you how to use SQL in Python after connecting to a database. I’ll review a simple example using MS Access, but similar concepts would apply if you connect Python to other databases, such as in the case of Oracle, or SQL Server.

The Example

To start, let’s say that you created a table in Access. For demonstration purposes, I created a table in Access, where:

  • The table name is: tracking_sales
  • The database name is: testdb

This is how the tracking_sales table would look like in Access:

Table in MS Access

The Goal

Suppose that you want to derive the revenue for each record by performing a simple multiplication:

revenue = (units_ordered) * (product_price_per_unit)

There are two options you can go about it:

  • Either perform the calculations in MS Access; or
  • Apply the calculations in Python

Let’s now review the second option of applying the calculations in Python. To accomplish this goal, we will use SQL in Python.

Steps to Apply SQL in Python

(1) First, you’ll need to connect Python to MS Access using the Pyodbc package.

You can use the code below to connect Python to Access. Note that you’ll need to change the path name to the location where your Access database is stored on your computer:

import pyodbc
 
conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\Ron\Desktop\Test\testdb.accdb;')
cursor = conn.cursor()

(2) Once you established the connection with Access, you’ll be able to start writing the SQL in Python to get your desired results. In our example, we will use SQL to calculate the revenue.

Here is the complete Python code:

import pyodbc
 
conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\Ron\Desktop\Test\testdb.accdb;')
cursor = conn.cursor()

cursor.execute(
'''select
client_id,
client_first_name,
client_last_name,
units_ordered,
product_price_per_unit,
product_name,
((units_ordered) * (product_price_per_unit)) AS revenue
from tracking_sales''')
    
for row in cursor.fetchall():
    print (row)

(3) Run the code in Python. You’ll notice that the revenue is calculated for each record (at the last column created):

Use SQL in Python after Connecting with a Database

(4) But what if you want to get the total revenue?

In this case, the total revenue would be the sum of all revenues across all the individual records.

Here is the syntax that you can use:

import pyodbc
 
conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\Ron\Desktop\Test\testdb.accdb;')
cursor = conn.cursor()

cursor.execute(
'''select
sum(((units_ordered) * (product_price_per_unit))) AS total_revenue
from tracking_sales''')
    
for row in cursor.fetchall():
    print (row)

When you run the code in Python, you’ll get the total revenue of 4350.

Now what if you want to connect additional tables using joins?

Using Joins

Let’s say that you have a second table in Access called shipping_country:

Table MS Access

You can then connect the tracking_sales table to the shipping_country table using the client_id field. You’ll need to make sure that the client_id field has the same format under both tables.

And this is the full Python code:

import pyodbc
  
conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\Ron\Desktop\Test\testdb.accdb;')
cursor = conn.cursor()
 
cursor.execute(
'''select
ts.client_id,
ts.client_first_name,
ts.client_last_name,
ts.units_ordered,
ts.product_price_per_unit,
ts.product_name,
((ts.units_ordered) * (ts.product_price_per_unit)) AS revenue,
sc.country
from tracking_sales ts
left join shipping_country sc on ts.client_id = sc.client_id 
''')
     
for row in cursor.fetchall():
    print (row)

Once you run the Python code, you’ll see the country name at the end of each record:

SQL in Python