Statement of Completion#978834bf
Eddy Unamed has successfully completed this project.
Beginning SQL: Basic Selection Statements
easy
Learn how to query SQL From Python
Resolution
Activities
Project.ipynb
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 [3]:
conn = sqlite3.connect("Chinook.sqlite")
Create a cursor:
In [4]:
cursor = conn.cursor()
Execute the query in the cursor:
In [5]:
cursor.execute("SELECT * FROM Artist LIMIT 5")
Out[5]:
<sqlite3.Cursor at 0x79759f2d2cc0>
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 0x79759f2d2cc0>
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 [13]:
conn = sqlite3.connect("Chinook.sqlite")
In [14]:
cursor = conn.cursor()
In [16]:
cursor.execute (
"""SELECT CustomerID, FirstName, LastName, Country, Email
FROM Customer""")
Out[16]:
<sqlite3.Cursor at 0x79759f2d2d40>
In [17]:
all_customers = cursor.fetchall()
In [19]:
cursor.close()
In [20]:
conn.close()
2) Pull the Total from all Invoices from USA¶
In [21]:
conn = sqlite3.connect("Chinook.sqlite")
In [22]:
cursor = conn.cursor()
In [42]:
cursor.execute (
"""SELECT Total
FROM Invoice
WHERE BillingCountry LIKE 'USA'""")
Out[42]:
<sqlite3.Cursor at 0x79759f2d2ac0>
In [43]:
totals_usa= cursor.fetchall()
In [44]:
tt=[]
for t in totals_usa:
tt.append (t[0])
totals_usa = tt
In [45]:
totals_usa
Out[45]:
[13.86, 0.99, 1.98, 1.98, 3.96, 5.94, 13.86, 3.96, 5.94, 8.91, 5.94, 8.91, 0.99, 1.98, 1.98, 8.91, 13.86, 0.99, 1.98, 1.98, 3.96, 15.86, 0.99, 1.98, 1.98, 3.96, 5.94, 13.86, 1.98, 3.96, 5.94, 8.91, 13.86, 5.94, 8.91, 0.99, 1.98, 8.91, 0.99, 1.98, 1.98, 3.96, 8.91, 18.86, 0.99, 1.98, 1.98, 3.96, 5.94, 13.86, 1.98, 3.96, 5.94, 13.86, 5.94, 8.91, 0.99, 1.98, 8.91, 0.99, 1.98, 1.98, 3.96, 10.91, 23.86, 1.99, 3.98, 3.98, 7.96, 11.94, 13.86, 1.98, 1.98, 3.96, 5.94, 13.86, 3.96, 5.94, 8.91, 0.99, 5.94, 8.91, 0.99, 1.98, 1.98, 8.91, 13.86, 0.99, 1.98, 1.98, 3.96]
3) Which is the most common value for Total?¶
In [48]:
A=0
B=0
for t in totals_usa:
if
--------------------------------------------------------------------------- AttributeError Traceback (most recent call last) Cell In[48], line 1 ----> 1 A= totals_usa.unique() 2 A AttributeError: 'list' object has no attribute 'unique'
2. Querying MySQL¶
In [50]:
import pymysql.cursors
In [51]:
HOST = "192.199.125.3"
USER = "datawars"
DATABASE = "sakila"
PASSWORD = ""
PORT = 3306
In [52]:
conn = pymysql.connect(
host=HOST, user=USER, port=PORT,
password=PASSWORD, database=DATABASE)
conn
Out[52]:
<pymysql.connections.Connection at 0x79759f2eca10>
In [53]:
cursor = conn.cursor()
In [54]:
cursor.execute("SELECT * FROM film LIMIT 5")
Out[54]:
5
In [55]:
cursor.fetchall()
Out[55]:
((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 [56]:
conn = pymysql.connect(
host=HOST, user=USER, port=PORT,
password=PASSWORD, database=DATABASE,
cursorclass=pymysql.cursors.DictCursor)
conn
Out[56]:
<pymysql.connections.Connection at 0x79759c077050>
In [57]:
cursor = conn.cursor()
In [58]:
cursor.execute("SELECT * FROM film LIMIT 5")
Out[58]:
5
In [59]:
cursor.fetchall()
Out[59]:
[{'film_id': 1,
'title': 'ACADEMY DINOSAUR',
'description': 'A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies',
'release_year': 2006,
'language_id': 1,
'original_language_id': None,
'rental_duration': 6,
'rental_rate': Decimal('0.99'),
'length': 86,
'replacement_cost': Decimal('20.99'),
'rating': 'PG',
'special_features': 'Deleted Scenes,Behind the Scenes',
'last_update': datetime.datetime(2006, 2, 15, 5, 3, 42)},
{'film_id': 2,
'title': 'ACE GOLDFINGER',
'description': 'A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China',
'release_year': 2006,
'language_id': 1,
'original_language_id': None,
'rental_duration': 3,
'rental_rate': Decimal('4.99'),
'length': 48,
'replacement_cost': Decimal('12.99'),
'rating': 'G',
'special_features': 'Trailers,Deleted Scenes',
'last_update': datetime.datetime(2006, 2, 15, 5, 3, 42)},
{'film_id': 3,
'title': 'ADAPTATION HOLES',
'description': 'A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Factory',
'release_year': 2006,
'language_id': 1,
'original_language_id': None,
'rental_duration': 7,
'rental_rate': Decimal('2.99'),
'length': 50,
'replacement_cost': Decimal('18.99'),
'rating': 'NC-17',
'special_features': 'Trailers,Deleted Scenes',
'last_update': datetime.datetime(2006, 2, 15, 5, 3, 42)},
{'film_id': 4,
'title': 'AFFAIR PREJUDICE',
'description': 'A Fanciful Documentary of a Frisbee And a Lumberjack who must Chase a Monkey in A Shark Tank',
'release_year': 2006,
'language_id': 1,
'original_language_id': None,
'rental_duration': 5,
'rental_rate': Decimal('2.99'),
'length': 117,
'replacement_cost': Decimal('26.99'),
'rating': 'G',
'special_features': 'Commentaries,Behind the Scenes',
'last_update': datetime.datetime(2006, 2, 15, 5, 3, 42)},
{'film_id': 5,
'title': 'AFRICAN EGG',
'description': 'A Fast-Paced Documentary of a Pastry Chef And a Dentist who must Pursue a Forensic Psychologist in The Gulf of Mexico',
'release_year': 2006,
'language_id': 1,
'original_language_id': None,
'rental_duration': 6,
'rental_rate': Decimal('2.99'),
'length': 130,
'replacement_cost': Decimal('22.99'),
'rating': 'G',
'special_features': 'Deleted Scenes',
'last_update': datetime.datetime(2006, 2, 15, 5, 3, 42)}]
Activities¶
4) Pull all films with a Rental Rate of $0.99¶
In [60]:
conn = pymysql.connect(
host=HOST, user=USER, port=PORT,
password=PASSWORD, database=DATABASE,
cursorclass=pymysql.cursors.DictCursor)
conn
Out[60]:
<pymysql.connections.Connection at 0x79759e9cbc50>
In [62]:
cursor = conn.cursor()
In [63]:
cursor.execute(
"""SELECT film_id, title, rental_rate, length
FROM film
WHERE rental_rate = 0.99""")
Out[63]:
341
In [ ]:
In [ ]:
In [64]:
films_under_a_buck = cursor.fetchall()
5) Pull all the films¶
In [ ]:
In [ ]:
In [ ]:
In [66]:
cursor.execute(
"""SELECT film_id, title, rental_rate, length
FROM film
""")
Out[66]:
1000
In [67]:
all_films = cursor.fetchall()
6) Calculate price_per_minute¶
In [ ]:
cursor.execute(
"""SELECT title
FROM film
""")
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()
