The Playground Database

I'm using a database from sqlitetutorial.net and SQLite Browser to play with the queries. From the database, I will only use one table for this post, let's use only the Albums table here.

For The Python playground, I'm using Jupyter Notebook by Anaconda. And to import a table into Pandas DataFrame, we just need to export a table into csv file and this could be done using SQLite Browser, or you can get it from my gist.

Or if you know Google Colab then It's great alternative for Jupyter Notebook.

Preparing the Dataframe

Let's assume the albums.csv file is in the same directory as your python or ipynb file, then we can load it into pandas dataframe like this:

import pandas as pd

albums_df = pd.read_csv("albums.csv")

Basic SQL Queries into Pandas Dataframe

For Part 1, I will only cover SELECT, WHERE, LIMIT and ORDER BY of SQL in DataFrame syntax.

Select All Columns and Rows

SELECT * 
FROM albums

albums_df

Select One Column

SELECT Title 
FROM albums

albums_df[['Title']]

Select More Columns

SELECT Title, ArtistId 
FROM albums

albums_df[['Title', 'ArtistId']]

Filtering with One Condition

SELECT * 
FROM albums
WHERE Title = 'The Best Of Van Halen, Vol. I'

albums_df[albums_df['Title'] == 'The Best Of Van Halen, Vol. I']

Filtering With More Conditions

SELECT * 
FROM albums
WHERE ArtistId = 2 AND AlbumId = 3

albums_df[(albums_df['ArtistId'] == 2) & (albums_df['AlbumId'] == 3) ]

Filtering With More Values

SELECT * 
FROM albums
WHERE ArtistId IN (8, 9, 10)

albums_df[albums_df['ArtistId'].isin([8,9,10])]

Search on a String

In Between:

SELECT * 
FROM albums
WHERE Title LIKE '%The%'

albums_df[albums_df['Title'].str.contains('The')]

Starts With:

SELECT * 
FROM albums
WHERE Title LIKE 'The%'

albums_df[albums_df['Title'].str.contains('^The')]

Ends With:

SELECT * 
FROM albums
WHERE Title LIKE '% Hits'

albums_df[albums_df['Title'].str.contains(' Hits$')]

Limit The First 10 Rows

SELECT * 
FROM albums
LIMIT 10

albums_df[0:10]

or

albums_df.head(10)

Sort by a Column

Ascending:

SELECT *
FROM albums
ORDER BY Title ASC

albums_df.sort_values(['Title'], ascending=True)

Descending:

SELECT *
FROM albums
ORDER BY Title DESC

albums_df.sort_values(['Title'], ascending=False)

To the Part 2