SQLite and SQLAlchemy in Python (Overview)

Christopher Trudeau
SQLite and SQLAlchemy in Python: Move Your Data Beyond Flat Files Christopher Trudeau 02:44
Recommended Tutorial Course Slides (.pdf) Sample Code (.zip) Ask a Question

All programs process data in one form or another, and many need to be able to save and retrieve that data from one invocation to the next. Python, SQLite, and SQLAlchemy give your programs database functionality, allowing you to store data in a single file without the need for a database server.

You can achieve similar results using flat files in any number of formats, including CSV, JSON, XML, and even custom formats. Flat files are often human-readable text files—though they can also be binary data—with a structure that can be parsed by a computer program. You’ll explore using SQL databases and flat files for data storage and manipulation and learn how to decide which approach is right for your program.

In this video course, you’ll learn how to use:

  • Flat files for data storage
  • SQL to improve access to persistent data
  • SQLite for data storage
  • SQLAlchemy to work with data as Python objects
Download

Sample Code (.zip)

 16.2 KB
Download

Course Slides (.pdf)

 1.3 MB

00:00 Welcome to Data Management with Python and SQLAlchemy. My name is Christopher, and I will be your guide. This course is all about interacting with databases in Python through the third-party library, SQLAlchemy.

00:14 In this course, you’ll learn about S-Q-L, also known as SQL. The language used to interact with most relational databases and then dive into SQLAlchemy, a library for using SQL concepts in Python.

00:27 SQLAlchemy breaks down into two parts: core, which allows you to write SQL statements directly or use functions to do the same. And the second part, the ORM, an Object-Relational Mapping implementation that lets you treat rows in a database as objects.

00:46 The code in this course was tested with Python 3.12, SQLAlchemy 2.0.23, and SQLite 3.37. Note that there have been major changes in SQLAlchemy since the previous 1.4 and even more changes from the previous 1.0 version.

01:08 If you aren’t using 2.0, you are going to run into problems with the examples in this course.

01:15 What is software besides a set of rules for manipulating data? And oftentimes you want that data to persist between execution of your program or to be passed between programs.

01:27 You may have used a text file, also known as a flat file data representation for storing data. These can be useful, but often have limitations.

01:36 A database provides a more robust way of representing data objects and their interrelations. There are actually different kinds of databases, but the most common is what is called a relational database.

01:48 If someone just says database to you, they probably mean the relational kind. Most relational databases support the Structured Query Language or a dialect of it for managing the contents of that database.

02:02 SQLite is a popular self-contained, single file database engine. One of the beauties of it, especially if you’re just learning databases, is that it doesn’t require a server.

02:12 It has all the power of a database, except it’s local to your file system. And seeing as you’re here at Real Python, the question probably is: Great, but how do I use it with Python? Yep. Well, there are actually many libraries that can help you interact with databases.

02:28 I’ll be covering SQLAlchemy, a third-party library with several different styles of interaction.

02:36 Next up, I’ll introduce you to data storage through the use of CSV files. Gotta walk before you can run.

nordin on March 25, 2024

It looks like the course slides have not been updated in that they refer to older versions of python, sqlalchemy, etc. than what is shown in this video segment.

Chris Bailey RP Team on March 25, 2024

Hi nordin, I’ve updated the slides pdf. Thanks for letting us know.

Become a Member to join the conversation.