Pandas and The Hello World of SQL Queries!
Here, you'll take advantage of the power of pandas
to write the results of your SQL query to a DataFrame in one swift line of Python code!
You'll first import pandas
and create the SQLite 'Chinook.sqlite'
engine. Then you'll query the database to select all records from the Album
table.
Recall that to select all records from the Orders
table in the Northwind database, Hugo executed the following command:
df = pd.read_sql_query("SELECT * FROM Orders", engine)
This exercise is part of the course
Introduction to Importing Data in Python
Exercise instructions
- Import the
pandas
package using the aliaspd
. - Using the function
create_engine()
, create an engine for the SQLite databaseChinook.sqlite
and assign it to the variableengine
. - Use the
pandas
functionread_sql_query()
to assign to the variabledf
the DataFrame of results from the following query: select all records from the tableAlbum
. - The remainder of the code is included to confirm that the DataFrame created by this method is equal to that created by the previous method that you learned.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
# Import packages
from sqlalchemy import create_engine
import ____ as ____
# Create engine: engine
# Execute query and store records in DataFrame: df
df = pd.read_sql_query(____, ____)
# Print head of DataFrame
print(df.head())
# Open engine in context manager and store query result in df1
with engine.connect() as con:
rs = con.execute("SELECT * FROM Album")
df1 = pd.DataFrame(rs.fetchall())
df1.columns = rs.keys()
# Confirm that both methods yield the same result
print(df.equals(df1))