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)