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

SQLite and SQLAlchemy in Python (Summary)

Christopher Trudeau
SQLite and SQLAlchemy in Python: Move 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)

 16.2 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.

Alain Rouleau on March 26, 2024

Thanks for the videos and, as per usual, always appreciated.

But anything related to objects, object oriented design, object oriented programming, object relational mapping, no matter what you call it. You know, OOD, OOP, ORM, really doesn’t matter. Objects are all about simplifying things, making things more simple, modelling the real world, writing less code. That’s the way we humans think. That’s the whole purpose of objects. You know, to make things easier.

So, me personally, I think the first three videos were great in terms of explaining SQLite and how to setup an actual SQL database. Tables, rows, syntax, you name it. Great examples. But, the rest, wow, pretty complicated. Lots of code.

Just create an object, you know, a person, and explain how to store that person in an SQLite datbase using an ORM like SQLAlchemy. Objects are suppose to be simple.

Become a Member to join the conversation.