Join us and get access to thousands of tutorials and a community of expert Pythonistas.

Unlock This Lesson

This lesson is for members only. Join us and get access to thousands of tutorials and a community of expert Pythonistas.

Unlock This Lesson

Hint: You can adjust the default video playback speed in your account settings.
Hint: You can set your subtitle preferences in your account settings.
Sorry! Looks like there’s an issue with video playback 🙁 This might be due to a temporary outage or because of a configuration issue with your browser. Please refer to our video player troubleshooting guide for assistance.

SQLite and SQLAlchemy in Python (Summary)

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

You’ve covered a lot of ground in this video course about databases, SQLite, SQL, and SQLAlchemy! You’ve used these tools to move data contained in flat files to an SQLite database, access the data with SQL and SQLAlchemy, and provide that data through a web server.

In this video course, you’ve learned:

  • Why a SQLite database can be a compelling alternative to flat-file data storage
  • How to normalize data to reduce data redundancy and increase data integrity
  • How to use SQLAlchemy to work with databases in an object-oriented manner
  • How to build a web application to serve a database to multiple users

Working with databases is a powerful abstraction for working with data that adds significant functionality to your Python programs and allows you to ask interesting questions of your data.

If you’d like to learn more about the concepts in this course, then check out:

Download

Sample Code (.zip)

 10.7 KB
Download

Course Slides (.pdf)

 1.3 MB

00:00 In the previous lesson, you learned about many-to-many relationships in the ORM. This lesson is the wrap-up. I’ll summarize the course and point you at some places for further investigation.

00:13 Databases provide a powerful way of storing data and relationship information in a single place. There are several different kinds of database, but the most common is a relational database.

00:23 Most relational databases use S-Q-L, or SQL, as the mechanism for managing the database itself, as well as the data within it. With SQL, you can create and manage your data structures, insert data, query data, and remove data. And of course, relationships themselves are just a special case of this data.

00:46 A very popular database engine is SQLite, which you’ve been using throughout this course to practice your database skills. It is file based rather than server based and comes with a handy command-line tool where you can get your SQL on.

01:02 And since this is a course in a Python place, you want some Python. There are many different libraries for doing database things. The one covered here was SQLAlchemy.

01:12 It provides an interface to many different relational databases and is made up of two main abstractions. Core abstracts away SQL interactions and can be used either in raw mode, allowing you to write SQL directly, or through function statements.

01:30 Queries in both of these methods return result objects, which can be access as lists and dictionaries.

01:38 The other abstraction that SQLAlchemy provides is an ORM, or object-relation mapping. This allows you to use Python classes to represent your data, where each row in a table can be mapped to an object instance. In this case, queries result in objects being returned, and you can define attributes on the objects to indicate the interobject relationships.

02:04 Along the way, you learned some common database concepts, like the use of a primary key to uniquely identify a row in the database, combined with a foreign key, which is a reference to a primary key in a different row.

02:16 This is how you indicate a one-to-many relationship. Or, with a secondary table made up of just foreign keys, you can create many-to-many relationships. You also saw how visualizing all of this can be helpful through the use of an ERD.

02:35 The first few places I’ll point you for further investigation are documentation for the tools Here’s SQLite and SQLAlchemy. Over at Khan, you can find a course on SQL if you want to learn more about how the ins and outs of this language work.

02:53 I’ve mentioned a couple of times that there are multiple libraries in Python for interacting with databases. This tutorial talks about several of them, including database-specific mechanisms.

03:04 If you want play some more with SQLite, this tutorial teaches you how to build a contacts book using the PyQT graphical library, with its own ORM, and SQLite as the address book storage mechanism.

03:19 Or if you want another SQLAlchemy project, this tutorial teaches you how to build a REST API front-end to a database using Flask and SQLAlchemy. If instead, you’d like to see a different ORM in action, there’s one built into Django.

03:35 This learning path has multiple tutorials teaching you Django, including its database abstractions.

03:44 Databases are a common tool in your programming tool kit. And there’s lots to learn. I hope this course helped you get started on your journey. Thanks for your attention.

FooledByCode on July 26, 2022

Your tutorials are as interesting as your talks on the Podcast, thanks for this course. My #1 take away is, that I shouldn’t had to be so hard on myself for not finding SQL interesting, ORM could have saved me.

Christopher Trudeau RP Team on July 27, 2022

I’m glad you’re enjoying both FooledByCode!

SQL is a powerful tool, but my procedural focused programmer’s brain doesn’t like it. I always find it a bit of a stretch mentally. Using ORMs is a big help (I live in Django-land a lot), but you definitely have to know their limitations.

Everything you do in the ORM is generating queries, so you want to at least have a sense of what you’re asking the system to do, otherwise you’ll end up beating your database to death.

As with all layers of abstraction, the power is helpful but comes at a cost. For me, I find the key is knowing when I need to ask somebody who is more knowledgeable in the database space than I.

Happy coding!

Become a Member to join the conversation.