Skip to main content

Introduction to Databases in Python

In this course, you'll learn the basics of relational databases and how to interact with them.

Start Course for Free
4 Hours20 Videos66 Exercises92,837 Learners

Create Your Free Account

GoogleLinkedInFacebook

or

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.

Loved by learners at thousands of companies

Course Description

In this course, you'll learn the basics of using SQL with Python. This will be useful because databases are ubiquitous and data scientists, analysts, and engineers must interact with them constantly. The Python SQL toolkit SQLAlchemy provides an accessible and intuitive way to query, build, and write to essential databases, including SQLite, MySQL, and PostgreSQL.
  1. 1

    Basics of Relational Databases

    Free

    In this chapter, you’ll get acquainted with the fundamentals of relational databases and the relational model for database management. You will learn how to connect to a database and interact with it by writing basic SQL queries, both in raw SQL as well as SQLAlchemy, which provides a Pythonic way of interacting with databases.

    Play Chapter Now
    Introduction to Databases
    50 xp
    Relational model
    50 xp
    Connecting to your database
    50 xp
    Engines and connection strings
    100 xp
    Autoloading Tables from a database
    100 xp
    Viewing Table details
    100 xp
    Introduction to SQL queries
    50 xp
    Selecting data from a Table: raw SQL
    100 xp
    Selecting data from a Table with SQLAlchemy
    100 xp
    Handling a ResultSet
    100 xp
    Congratulations!
    50 xp
  2. 2

    Applying Filtering, Ordering and Grouping to Queries

    In this chapter, you will build on your database knowledge by writing more nuanced queries that allow you to filter, order, and count your data—all within the Pythonic framework provided by SQLAlchemy.

    Play Chapter Now
    Filtering and targeting data
    50 xp
    Connecting to a PostgreSQL database
    100 xp
    Filter data selected from a Table - Simple
    100 xp
    Filter data selected from a Table - Expressions
    100 xp
    Filter data selected from a Table - Advanced
    100 xp
    Ordering query results
    50 xp
    Ordering by a single column
    100 xp
    Ordering in descending order by a single column
    100 xp
    Ordering by multiple columns
    100 xp
    Counting, summing, and grouping data
    50 xp
    Counting distinct data
    100 xp
    Count of records by state
    100 xp
    Determining the population sum by state
    100 xp
    SQLAlchemy and pandas for visualization
    50 xp
    ResultsSets and pandas DataFrames
    100 xp
    From SQLAlchemy results to a plot
    100 xp
  3. 3

    Advanced SQLAlchemy Queries

    In this chapter, you will learn to perform advanced—and incredibly useful—queries that enable you to interact with your data in powerful ways.

    Play Chapter Now
    Calculating values in a query
    50 xp
    Connecting to a MySQL database
    100 xp
    Calculating a difference between two columns
    100 xp
    Determining the overall percentage of women
    100 xp
    SQL relationships
    50 xp
    Automatic joins with an established relationship
    100 xp
    Joins
    100 xp
    More practice with joins
    100 xp
    Working with hierarchical tables
    50 xp
    Using alias to handle same table joined queries
    100 xp
    Leveraging functions and group_bys with hierarchical data
    100 xp
    Handling large ResultSets
    50 xp
    Working on blocks of records
    100 xp
  4. 4

    Creating and Manipulating your own Databases

    In the previous chapters, you interacted with existing databases and queried them in different ways. Now, you will learn how to build your own databases and keep them updated.

    Play Chapter Now
    Creating databases and tables
    50 xp
    Creating tables with SQLAlchemy
    100 xp
    Constraints and data defaults
    100 xp
    Inserting data into a table
    50 xp
    Inserting a single row
    100 xp
    Inserting multiple records at once
    100 xp
    Loading a CSV into a table
    100 xp
    Updating data in a table
    50 xp
    Updating individual records
    100 xp
    Updating multiple records
    100 xp
    Correlated updates
    100 xp
    Deleting data from a database
    50 xp
    Deleting all the records from a table
    100 xp
    Deleting specific records
    100 xp
    Deleting a table completely
    100 xp
  5. 5

    Putting it all together

    Bring together all of the skills you acquired in the previous chapters to work on a real-life project. From connecting to a database and populating it, to reading and querying it.

    Play Chapter Now
    Census case study
    50 xp
    Setup the engine and metadata
    100 xp
    Create the table to the database
    100 xp
    Populating the database
    50 xp
    Reading the data from the CSV
    100 xp
    Load data from a list into the Table
    100 xp
    Querying the database
    50 xp
    Determine the average age by population
    100 xp
    Determine the percentage of population by gender and state
    100 xp
    Determine the difference by state from the 2000 and 2008 censuses
    100 xp
    Congratulations!
    50 xp

Datasets

Census (CSV)Census (SQLite)Employees (SQLite)

Collaborators

Hugo Bowne-Anderson

Prerequisites

Intermediate Python
Jason Myers HeadshotJason Myers

Co-Author of Essential SQLAlchemy and Software Engineer

Jason Myers is a software engineer and author. His area of expertise is in developing data analytics platforms. He has also written the Essential SQLAlchemy book, co-authored with Rick Copeland, that introduces you to working with relational databases in Python.
See More

What do other learners have to say?

    Join over 11 million learners and start Introduction to Databases in Python today!

    Create Your Free Account

    GoogleLinkedInFacebook

    or

    By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.