【PYTHON】Sqlite3

 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()