Filtering your database records using SQL's WHERE
You can now execute a basic SQL query to select records from any table in your database and you can also perform simple query customizations to select particular columns and numbers of rows.
There are a couple more standard SQL query chops that will aid you in your journey to becoming an SQL ninja.
Let's say, for example that you wanted to get all records from the Customer table of the Chinook database for which the Country is 'Canada'. You can do this very easily in SQL using a SELECT statement followed by a WHERE clause as follows:
SELECT * FROM Customer WHERE Country = 'Canada'
In fact, you can filter any SELECT statement by any condition using a WHERE clause. This is called filtering your records.
In this interactive exercise, you'll select all records of the Employee table for which 'EmployeeId' is greater than or equal to 6.
Packages are already imported as follows:
import pandas as pd
from sqlalchemy import create_engine
Query away!
This is a part of the course
“Introduction to Importing Data in Python”
Exercise instructions
- Complete the argument of
create_engine()so that the engine for the SQLite database'Chinook.sqlite'is created. - Execute the query that selects all records from the
Employeetable where'EmployeeId'is greater than or equal to6. Use the>=operator and assign the results tors. - Apply the method
fetchall()torsin order to fetch all records inrs. Store them in the DataFramedf. - Using the
rsobject, 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.
# Create engine: engine
engine = create_engine(____)
# Open engine in context manager
# Perform query and save results to DataFrame: df
with engine.connect() as con:
rs = con.execute(____)
df = pd.DataFrame(____)
df.columns = ____
# Print the head of the DataFrame df
print(df.head())This exercise is part of the course
Introduction to Importing Data in Python
Learn to import data into Python from various sources, such as Excel, SQL, SAS and right from the web.
In this chapter, you'll learn how to extract meaningful data from relational databases, an essential skill for any data scientist. You will learn about relational models, how to create SQL queries, how to filter and order your SQL records, and how to perform advanced queries by joining database tables.
Exercise 1: Introduction to relational databasesExercise 2: Pop quiz: The relational modelExercise 3: Creating a database engine in PythonExercise 4: Creating a database engineExercise 5: What are the tables in the database?Exercise 6: Querying relational databases in PythonExercise 7: The Hello World of SQL Queries!Exercise 8: Customizing the Hello World of SQL QueriesExercise 9: Filtering your database records using SQL's WHEREExercise 10: Ordering your SQL records with ORDER BYExercise 11: Querying relational databases directly with pandasExercise 12: Pandas and The Hello World of SQL Queries!Exercise 13: Pandas for more complex queryingExercise 14: Advanced querying: exploiting table relationshipsExercise 15: The power of SQL lies in relationships between tables: INNER JOINExercise 16: Filtering your INNER JOINExercise 17: Final ThoughtsWhat is DataCamp?
Learn the data skills you need online at your own pace—from non-coding essentials to data science and machine learning.