How to Convert SQL into Python Pandas? Part 1

In this series of articles, we will study how to convert SQL statements into Python Pandas syntax. We are using Google Colab to access to the database file stored in Google Drive and to run the python statements.

Access to DB

We upload the SQLite DB file named with “sample.db” under Data/db. The data we are using is SQLite Sample Database Chinook.

from google.colab import drive
drive.mount('/content/drive')

import sqlite3
import pandas as pd
import numpy as np

con = sqlite3.connect('/content/drive/MyDrive/Data/db/sample.db')
df_albums = pd.read_sql('select * from albums', con)
df_invoices = pd.read_sql('select * from invoices', con)
df_invoicesitem = pd.read_sql('select * from invoice_items', con)
df_tracks = pd.read_sql('select * from tracks', con)
df_invoicesitem2 = pd.read_sql('select * from invoice_items where 1=2', con)

Head/ Offset

-- Top 5 Rows
select * from albums LIMIT 5

# Top 5 Rows
df_albums.head(5)

-- Top 5 Rows starting from the 10th row
select * from albums LIMIT 5 OFFSET 10;

# Top 5 Rows starting from the 10th row
df_albums.drop(df_albums.head(10).index).head(5)
df_albums

Rename / Alias

-- Rename AlbumID as AlbumNum
select…