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
Top comments (0)