Statement of Completion#0df04655
Sushil Udaya Kumar has successfully completed this project.
Beginning SQL: Basic Selection Statements
easy
Learn how to query SQL From Python
Resolution
Activities
1. Our first SQLite query¶
First we import sqlite3, which is included with Python
In [1]:
import sqlite3
Now we'll connect to the database. Note that SQLite is a Database that stores information in single files, so connecting to it is extremely simple. We have the file Chinook.sqlite in the local hard drive.
In [2]:
!ls -lh Chinook.sqlite
-rw-r--r-- 1 root root 1.1M Mar 1 2024 Chinook.sqlite
Establish connection:
In [13]:
conn = sqlite3.connect("Chinook.sqlite")
Create a cursor:
In [14]:
cursor = conn.cursor()
Execute the query in the cursor:
In [5]:
cursor.execute("SELECT * FROM Artist LIMIT 5")
Out[5]:
<sqlite3.Cursor at 0x7e7501092d40>
Now we can get the results, in this case, we'll just get ALL the results produced by the query:
In [6]:
cursor.fetchall()
Out[6]:
[(1, 'AC/DC'), (2, 'Accept'), (3, 'Aerosmith'), (4, 'Alanis Morissette'), (5, 'Alice In Chains')]
fetchone and fetchmany¶
Notice we're not using a limit in this query
In [7]:
cursor.execute("SELECT * FROM Artist")
Out[7]:
<sqlite3.Cursor at 0x7e7501092d40>
In [15]:
cursor.execute("SELECT * FROM Customer")
Out[15]:
<sqlite3.Cursor at 0x7e7501092ec0>
In [ ]:
In [8]:
cursor.fetchone()
Out[8]:
(1, 'AC/DC')
In [9]:
cursor.fetchone()
Out[9]:
(2, 'Accept')
In [10]:
cursor.fetchmany(3)
Out[10]:
[(3, 'Aerosmith'), (4, 'Alanis Morissette'), (5, 'Alice In Chains')]
In [11]:
cursor.close()
In [12]:
conn.close()
Activities¶
1) Pull the CustomerID, FirstName, LastName, Country and Email from ALL Customers¶
In [19]:
all_customers = cursor.execute("select CustomerID, FirstName, LastName, Country, Email from Customer")
In [20]:
all_customers.fetchall()
Out[20]:
[(1, 'Luís', 'Gonçalves', 'Brazil', 'luisg@embraer.com.br'), (2, 'Leonie', 'Köhler', 'Germany', 'leonekohler@surfeu.de'), (3, 'François', 'Tremblay', 'Canada', 'ftremblay@gmail.com'), (4, 'Bjørn', 'Hansen', 'Norway', 'bjorn.hansen@yahoo.no'), (5, 'František', 'Wichterlová', 'Czech Republic', 'frantisekw@jetbrains.com'), (6, 'Helena', 'Holý', 'Czech Republic', 'hholy@gmail.com'), (7, 'Astrid', 'Gruber', 'Austria', 'astrid.gruber@apple.at'), (8, 'Daan', 'Peeters', 'Belgium', 'daan_peeters@apple.be'), (9, 'Kara', 'Nielsen', 'Denmark', 'kara.nielsen@jubii.dk'), (10, 'Eduardo', 'Martins', 'Brazil', 'eduardo@woodstock.com.br'), (11, 'Alexandre', 'Rocha', 'Brazil', 'alero@uol.com.br'), (12, 'Roberto', 'Almeida', 'Brazil', 'roberto.almeida@riotur.gov.br'), (13, 'Fernanda', 'Ramos', 'Brazil', 'fernadaramos4@uol.com.br'), (14, 'Mark', 'Philips', 'Canada', 'mphilips12@shaw.ca'), (15, 'Jennifer', 'Peterson', 'Canada', 'jenniferp@rogers.ca'), (16, 'Frank', 'Harris', 'USA', 'fharris@google.com'), (17, 'Jack', 'Smith', 'USA', 'jacksmith@microsoft.com'), (18, 'Michelle', 'Brooks', 'USA', 'michelleb@aol.com'), (19, 'Tim', 'Goyer', 'USA', 'tgoyer@apple.com'), (20, 'Dan', 'Miller', 'USA', 'dmiller@comcast.com'), (21, 'Kathy', 'Chase', 'USA', 'kachase@hotmail.com'), (22, 'Heather', 'Leacock', 'USA', 'hleacock@gmail.com'), (23, 'John', 'Gordon', 'USA', 'johngordon22@yahoo.com'), (24, 'Frank', 'Ralston', 'USA', 'fralston@gmail.com'), (25, 'Victor', 'Stevens', 'USA', 'vstevens@yahoo.com'), (26, 'Richard', 'Cunningham', 'USA', 'ricunningham@hotmail.com'), (27, 'Patrick', 'Gray', 'USA', 'patrick.gray@aol.com'), (28, 'Julia', 'Barnett', 'USA', 'jubarnett@gmail.com'), (29, 'Robert', 'Brown', 'Canada', 'robbrown@shaw.ca'), (30, 'Edward', 'Francis', 'Canada', 'edfrancis@yachoo.ca'), (31, 'Martha', 'Silk', 'Canada', 'marthasilk@gmail.com'), (32, 'Aaron', 'Mitchell', 'Canada', 'aaronmitchell@yahoo.ca'), (33, 'Ellie', 'Sullivan', 'Canada', 'ellie.sullivan@shaw.ca'), (34, 'João', 'Fernandes', 'Portugal', 'jfernandes@yahoo.pt'), (35, 'Madalena', 'Sampaio', 'Portugal', 'masampaio@sapo.pt'), (36, 'Hannah', 'Schneider', 'Germany', 'hannah.schneider@yahoo.de'), (37, 'Fynn', 'Zimmermann', 'Germany', 'fzimmermann@yahoo.de'), (38, 'Niklas', 'Schröder', 'Germany', 'nschroder@surfeu.de'), (39, 'Camille', 'Bernard', 'France', 'camille.bernard@yahoo.fr'), (40, 'Dominique', 'Lefebvre', 'France', 'dominiquelefebvre@gmail.com'), (41, 'Marc', 'Dubois', 'France', 'marc.dubois@hotmail.com'), (42, 'Wyatt', 'Girard', 'France', 'wyatt.girard@yahoo.fr'), (43, 'Isabelle', 'Mercier', 'France', 'isabelle_mercier@apple.fr'), (44, 'Terhi', 'Hämäläinen', 'Finland', 'terhi.hamalainen@apple.fi'), (45, 'Ladislav', 'Kovács', 'Hungary', 'ladislav_kovacs@apple.hu'), (46, 'Hugh', "O'Reilly", 'Ireland', 'hughoreilly@apple.ie'), (47, 'Lucas', 'Mancini', 'Italy', 'lucas.mancini@yahoo.it'), (48, 'Johannes', 'Van der Berg', 'Netherlands', 'johavanderberg@yahoo.nl'), (49, 'Stanisław', 'Wójcik', 'Poland', 'stanisław.wójcik@wp.pl'), (50, 'Enrique', 'Muñoz', 'Spain', 'enrique_munoz@yahoo.es'), (51, 'Joakim', 'Johansson', 'Sweden', 'joakim.johansson@yahoo.se'), (52, 'Emma', 'Jones', 'United Kingdom', 'emma_jones@hotmail.com'), (53, 'Phil', 'Hughes', 'United Kingdom', 'phil.hughes@gmail.com'), (54, 'Steve', 'Murray', 'United Kingdom', 'steve.murray@yahoo.uk'), (55, 'Mark', 'Taylor', 'Australia', 'mark.taylor@yahoo.au'), (56, 'Diego', 'Gutiérrez', 'Argentina', 'diego.gutierrez@yahoo.ar'), (57, 'Luis', 'Rojas', 'Chile', 'luisrojas@yahoo.cl'), (58, 'Manoj', 'Pareek', 'India', 'manoj.pareek@rediff.com'), (59, 'Puja', 'Srivastava', 'India', 'puja_srivastava@yahoo.in')]
2) Pull the Total from all Invoices from USA¶
In [ ]:
totals_usa = ...
3) Which is the most common value for Total?¶
In [ ]:
2. Querying MySQL¶
In [31]:
import pymysql.cursors
In [32]:
HOST = "<ENTER THE IP ADDRESS OF THE `mysql` DEVICE HERE>"
USER = "datawars"
DATABASE = "sakila"
PASSWORD = ""
PORT = 3306
In [36]:
conn = pymysql.connect(
host="192.159.132.2", user=USER, port=PORT,
password=PASSWORD, database=DATABASE)
conn
Out[36]:
<pymysql.connections.Connection at 0x7e74df490910>
In [37]:
cursor = conn.cursor()
In [38]:
cursor.execute("SELECT * FROM film LIMIT 5")
Out[38]:
5
In [39]:
cursor.fetchall()
Out[39]:
((1,
'ACADEMY DINOSAUR',
'A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies',
2006,
1,
None,
6,
Decimal('0.99'),
86,
Decimal('20.99'),
'PG',
'Deleted Scenes,Behind the Scenes',
datetime.datetime(2006, 2, 15, 5, 3, 42)),
(2,
'ACE GOLDFINGER',
'A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China',
2006,
1,
None,
3,
Decimal('4.99'),
48,
Decimal('12.99'),
'G',
'Trailers,Deleted Scenes',
datetime.datetime(2006, 2, 15, 5, 3, 42)),
(3,
'ADAPTATION HOLES',
'A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Factory',
2006,
1,
None,
7,
Decimal('2.99'),
50,
Decimal('18.99'),
'NC-17',
'Trailers,Deleted Scenes',
datetime.datetime(2006, 2, 15, 5, 3, 42)),
(4,
'AFFAIR PREJUDICE',
'A Fanciful Documentary of a Frisbee And a Lumberjack who must Chase a Monkey in A Shark Tank',
2006,
1,
None,
5,
Decimal('2.99'),
117,
Decimal('26.99'),
'G',
'Commentaries,Behind the Scenes',
datetime.datetime(2006, 2, 15, 5, 3, 42)),
(5,
'AFRICAN EGG',
'A Fast-Paced Documentary of a Pastry Chef And a Dentist who must Pursue a Forensic Psychologist in The Gulf of Mexico',
2006,
1,
None,
6,
Decimal('2.99'),
130,
Decimal('22.99'),
'G',
'Deleted Scenes',
datetime.datetime(2006, 2, 15, 5, 3, 42)))
Using Dictionaries¶
In [43]:
conn = pymysql.connect(
host="192.159.132.2", user=USER, port=PORT,
password=PASSWORD, database=DATABASE,
cursorclass=pymysql.cursors.DictCursor)
conn
Out[43]:
<pymysql.connections.Connection at 0x7e74df32fe50>
In [41]:
cursor = conn.cursor()
In [42]:
cursor.execute("SELECT * FROM film LIMIT 5")
Out[42]:
5
In [ ]:
cursor.fetchall()
Activities¶
4) Pull all films with a Rental Rate of $0.99¶
In [44]:
conn = pymysql.connect(
host="192.159.132.2", user=USER, port=PORT,
password=PASSWORD, database=DATABASE,
cursorclass=pymysql.cursors.DictCursor)
cursor = conn.cursor()
cursor.execute("SELECT film_id, title, rental_rate, length FROM film WHERE rental_rate = 0.99")
films_under_a_buck = cursor.fetchall()
5) Pull all the films¶
In [46]:
conn = pymysql.connect(
host="192.159.132.2", user=USER, port=PORT,
password=PASSWORD, database=DATABASE,
cursorclass=pymysql.cursors.DictCursor)
cursor = conn.cursor()
cursor.execute("SELECT film_id, title, rental_rate, length FROM film")
all_films = cursor.fetchall()
6) Calculate price_per_minute¶
In [ ]:
films_ppm = ...
3. Querying SQL from Pandas¶
In [ ]:
import pandas as pd
In [ ]:
conn = pymysql.connect(
host=HOST, user=USER, port=PORT,
password=PASSWORD, database=DATABASE)
In [ ]:
film_df = pd.read_sql("SELECT * FROM film", conn, index_col='film_id')
film_df.head()
