If you’d like a laugh, then check out xkcd: Exploits of a Mom.
SQLAlchemy: Core Text
00:00 In the previous lesson, I introduced you to writing SQL to manipulate a relational database. In this lesson, I’ll show you how to use SQLAlchemy to accomplish the same things in your Python code.
00:13 SQLAlchemy is a popular third-party library for accessing and managing databases. It is made up of two parts: Core and the ORM. The core is a Python abstraction of SQL, where the ORM uses object-oriented modeling to represent database rows.
00:31 One of the advantages of using a library like SQLAlchemy is it abstracts away the connection mechanisms. You initialize the SQLAlchemy engine the same way, regardless of what kind of database you’re connecting to.
The path part indicates where the database is. For a database that used a server, this would have a hostname and a port. Because SQLite uses files, this is a file reference. I’m connecting to the
books.db file in the
data/ subdirectory. The engine factory uses lazy loading, so the connection hasn’t been established yet.
echo parameter turns on SQL echoing. Everything I do through this connection will print out the SQL equivalent. It’ll be a bit noisy, but for our purposes, you’ll be able to see exactly what is going on.
02:17 I mentioned in the first lesson that SQLAlchemy is going through an adaptation phase heading for the 2.0 release. The 1.4 version supports both the old dialect and the new dialect of SQLAlchemy.
result.all() returns a list of tuples, and the database I’m using was populated with two authors in the previous lesson, good old Isaac and Pearl. All right, let’s make some changes now. To do an insert, I’ll need to execute a query.
Like before, I’m using a text object, so I can put in raw SQL. There’s something new here though. Notice that in the
VALUES clause of the
INSERT, I’m using
:ln. The colon indicates that these are placeholder values.
By passing in a list of dictionaries, I am able to insert more than one author at a time. Let me just close the call … and you see a few things. First off, you get the
INSERT debug info showing
"Stephen"—I speak as if they’re close friends—being inserted. Next, because I didn’t capture the result of the
.execute(), The REPL shows a
These all get grouped together as part of the database. Calling
.commit() closes the transaction off and makes all the changes permanent. So what are transactions good for? Well, if you get partway through a group of queries, you can undo all of them by rolling back the transaction. To demonstrate that, I’ll start by inserting another author.
but this time, instead of calling
.all(), I’ll iterate over the
result object. Each item in the
result object iterator corresponds to a row in the database, or it does this time because that was the kind of
select I did.
I can access the columns in a result by using the columns’ names:
row.first_name, for example. Note that the
result object is temporal. Accessing the items in it consumes them. That’s why calling
.all() now shows an empty list.
08:08 This might take a little getting used to, but it’s actually a good thing because it means you can iterate partway through a result, do something in your code, and then go back and not have to remember where you were. This becomes particularly important when you start to get into things like pagination and larger sets of data.
08:35 Although I like to think of the results of a SQL query as being a row in the database, this is a faulty mental abstraction. Oftentimes that is exactly what you’re getting back, but really you’re creating a dataset with specific names.
.mappings() method on the
result object returns the rows as dictionaries instead of tuples. This can be useful if you wanted a dictionary format anyhow—for example, if you were going to serialize this data to JSON.
WHERE is saying that I’m looking for authors whose last name is greater than capital
"C". Because I used a parameter to define this, I could actually have passed in the dictionary
"ln":"C" at runtime.
There’s the result of the query. Don’t forget that this is doing string comparison. Something that starts with capital
"Clancy", and is bigger than a single letter, like
"Clancy", is actually greater than just capital
Since you specify the paramaters to a statement as part of the
.execute() method, that means you can pre-prepare a statement and reuse it. Here, I’ve kept the text object in a variable called
12:28 I have a very important warning about writing raw SQL. You might be tempted to think, Hey, this is all text. I can just concatenate or use f-strings! And that would be a very bad idea. Doing it this way leaves you open to what’s called an SQL injection attack.
If I pass in a
last_name here that has a semicolon and then, say, a
DELETE statement, you’d be running both your
INSERT and my
DELETE, and that’s probably not what you want. Parameters solve this problem.
13:21 You’d think us coders would learn, but no, the wrong way is still pretty common. The OWASP Top 10 vulnerabilities list dropped SQL injection to spot three in 2021, which is down from spot one in previous years.
Become a Member to join the conversation.