SQL Query into Pandas DataFrame - Part 1

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

37

Categories:

SqlPython

Tags:

SqlPythonDatascienceSqlite

This website collects cookies to deliver better user experience

SQL Query into Pandas DataFrame - Part 1