Use SQL in Python after Connecting with a Database

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

In a previous post, I explained how to connect Python to MS Access. Once you created such a connection, you may want to apply SQL in Python to further manage your data.

The Example

To start, let’s say that you have the following table in Access:

And this is how the table would look like in Access, where the table name is tracking_sales (and the database name is testdb):

The Goal

You may 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

We will 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) The first thing that you’ll need to do is to create a connection between Python and MS Access.

You can use this Python code for the connection (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\Doron E\Desktop\Test\testdb.accdb;')
cursor = conn.cursor()

(2) Once you established the connection with Access, the fun part begins!

You can now 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 Python code:

import pyodbc
 
conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\Doron E\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) Press F5 to run the code in Python. You’ll notice that the revenue is calculated for each record (at the last column created):

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

In our case, that 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\Doron E\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)

(5) And when you run the code in Python, you’ll get the total revenue:

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:

You can then connect the tracking_sales table with 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 (I used the number format for that field 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\Doron E\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: