Customizing the Hello World of SQL Queries
Congratulations on executing your first SQL query! Now you're going to figure out how to customize your query in order to:
- Select specified columns from a table;
- Select a specified number of rows;
- Import column names from the database table.
Recall that Hugo performed a very similar query customization in the video:
engine = create_engine('sqlite:///Northwind.sqlite')
with engine.connect() as con:
rs = con.execute("SELECT OrderID, OrderDate, ShipName FROM Orders")
df = pd.DataFrame(rs.fetchmany(size=5))
df.columns = rs.keys()
Packages have already been imported as follows:
from sqlalchemy import create_engine
import pandas as pd
The engine has also already been created:
engine = create_engine('sqlite:///Chinook.sqlite')
The engine connection is already open with the statement
with engine.connect() as con:
All the code you need to complete is within this context.
This exercise is part of the course
Introduction to Importing Data in Python
Exercise instructions
- Execute the SQL query that selects the columns
LastName
andTitle
from theEmployee
table. Store the results in the variablers
. - Apply the method
fetchmany()
tors
in order to retrieve 3 of the records. Store them in the DataFramedf
. - Using the
rs
object, set the DataFrame's column names to the corresponding names of the table columns.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
# Open engine in context manager
# Perform query and save results to DataFrame: df
with engine.connect() as con:
rs = ____
df = pd.DataFrame(____)
df.columns = ____
# Print the length of the DataFrame df
print(len(df))
# Print the head of the DataFrame df
print(df.head())