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.
Basics of Relational DatabasesFree
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.Introduction to Databases50 xpRelational model50 xpConnecting to your database50 xpEngines and connection strings100 xpAutoloading Tables from a database100 xpViewing Table details100 xpIntroduction to SQL queries50 xpSelecting data from a Table: raw SQL100 xpSelecting data from a Table with SQLAlchemy100 xpHandling a ResultSet100 xpCongratulations!50 xp
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.Filtering and targeting data50 xpConnecting to a PostgreSQL database100 xpFilter data selected from a Table - Simple100 xpFilter data selected from a Table - Expressions100 xpFilter data selected from a Table - Advanced100 xpOrdering query results50 xpOrdering by a single column100 xpOrdering in descending order by a single column100 xpOrdering by multiple columns100 xpCounting, summing, and grouping data50 xpCounting distinct data100 xpCount of records by state100 xpDetermining the population sum by state100 xpSQLAlchemy and pandas for visualization50 xpResultsSets and pandas DataFrames100 xpFrom SQLAlchemy results to a plot100 xp
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.Calculating values in a query50 xpConnecting to a MySQL database100 xpCalculating a difference between two columns100 xpDetermining the overall percentage of women100 xpSQL relationships50 xpAutomatic joins with an established relationship100 xpJoins100 xpMore practice with joins100 xpWorking with hierarchical tables50 xpUsing alias to handle same table joined queries100 xpLeveraging functions and group_bys with hierarchical data100 xpHandling large ResultSets50 xpWorking on blocks of records100 xp
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.Creating databases and tables50 xpCreating tables with SQLAlchemy100 xpConstraints and data defaults100 xpInserting data into a table50 xpInserting a single row100 xpInserting multiple records at once100 xpLoading a CSV into a table100 xpUpdating data in a table50 xpUpdating individual records100 xpUpdating multiple records100 xpCorrelated updates100 xpDeleting data from a database50 xpDeleting all the records from a table100 xpDeleting specific records100 xpDeleting a table completely100 xp
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.Census case study50 xpSetup the engine and metadata100 xpCreate the table to the database100 xpPopulating the database50 xpReading the data from the CSV100 xpLoad data from a list into the Table100 xpQuerying the database50 xpDetermine the average age by population100 xpDetermine the percentage of population by gender and state100 xpDetermine the difference by state from the 2000 and 2008 censuses100 xpCongratulations!50 xp
Jason MyersSee More
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.