import sqlite3
connection = sqlite3.connect('chinook.db')
cursor = connection.cursor()
# list out tables -- .table does not work from Python sqlite3
cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
cursor.fetchall()
# get table information (column names, types, settings)
cursor.execute('PRAGMA table_info(artists);')
cursor.fetchall()
# SELECT the first 5 rows of artists
cursor.execute('SELECT * FROM artists LIMIT 5;')
cursor.fetchall()
# especially for longer queries, it helps to format them like this, with each SQL command on a separate line
query = """
SELECT *
FROM artists
LIMIT 5;
"""
cursor.execute(query)
cursor.fetchall()
# get table information (column names, types, settings)
cursor.execute('PRAGMA table_info(invoices);')
cursor.fetchall()
# save table column names in a list
cursor.execute('PRAGMA table_info(invoices);')
results = cursor.fetchall()
column_names = [r[1] for r in results]
column_names
cursor.execute('SELECT * FROM invoices LIMIT 5;')
cursor.fetchall()
# ORDER BY
cursor.execute('SELECT Total, InvoiceDate from invoices ORDER BY Total DESC LIMIT 5;')
cursor.fetchall()
# WHERE statement
cursor.execute('SELECT Total, BillingCountry from invoices WHERE BillingCountry == "Canada" LIMIT 5;')
cursor.fetchall()
# WHERE using an inserted argument
cursor.execute('SELECT Total, BillingCountry from invoices WHERE BillingCountry == ? LIMIT 5;', ('Canada',))
cursor.fetchall()
# LIKE command
cursor.execute('SELECT Total, BillingCountry from invoices WHERE BillingCountry LIKE "%can%" LIMIT 5;')
cursor.fetchall()
# GROUP BY statement
cursor.execute('SELECT SUM(Total), BillingCountry from invoices GROUP BY BillingCountry ORDER BY SUM(Total) DESC LIMIT 5;')
cursor.fetchall()
# examine column names for invoice_items table
cursor.execute('PRAGMA table_info(invoice_items);')
cursor.fetchall()
# examine a sample of the data
cursor.execute('SELECT * FROM invoice_items LIMIT 5;')
cursor.fetchall()
# aliases can be used to rename columns and tables
# according to some SQL style guides, it's not best practice to alias a table
cursor.execute('SELECT i.TrackID as tid, i.UnitPrice as up FROM invoice_items as i LIMIT 5;')
cursor.fetchall()
# DISTINCT
cursor.execute('SELECT DISTINCT UnitPrice FROM invoice_items;')
cursor.fetchall()
# JOIN
# get tracks that were purchased and combine with the country
query = """
SELECT invoices.BillingCountry, invoice_items.TrackId
FROM invoices
JOIN invoice_items
ON invoices.InvoiceId = invoice_items.InvoiceId
LIMIT 5;
"""
cursor.execute(query)
cursor.fetchall()
# get number of purchased tracks for each track by country, sorted by the top-most purchased
query = """
SELECT invoice_items.TrackId, COUNT(invoice_items.TrackId), invoices.BillingCountry
FROM invoices
JOIN invoice_items
ON invoices.InvoiceId = invoice_items.InvoiceId
GROUP BY invoices.BillingCountry
ORDER BY COUNT(invoice_items.TrackId) DESC
LIMIT 5;
"""
cursor.execute(query)
cursor.fetchall()
# multiple JOINs
query = """
SELECT tracks.Name, COUNT(invoice_items.TrackId), invoices.BillingCountry
FROM invoices
JOIN invoice_items
ON invoices.InvoiceId = invoice_items.InvoiceId
JOIN tracks
ON tracks.TrackId = invoice_items.TrackId
GROUP BY invoices.BillingCountry
ORDER BY COUNT(invoice_items.TrackId) DESC
LIMIT 5;
"""
cursor.execute(query)
cursor.fetchall()
# this same command as above can also be done with a subquery like this, but is easier with multiple joins
query = """
SELECT tracks.Name, invoice_merged.track_count, invoice_merged.BillingCountry
FROM
(SELECT ii.TrackId, COUNT(ii.TrackId) as track_count, i.BillingCountry
FROM invoices as i
JOIN invoice_items as ii
ON i.InvoiceId = ii.InvoiceId
GROUP BY BillingCountry) as invoice_merged
JOIN tracks
ON tracks.TrackId = invoice_merged.TrackId
ORDER BY track_count DESC
LIMIT 5;
"""
cursor.execute(query)
cursor.fetchall()
# be sure to close the connection when done
connection.close()
No comments:
Post a Comment