SQLAlchemy: Core Statements

00:00 In the previous lesson, you used SQLAlchemy to run raw SQL commands on your database. In this lesson, you’ll learn more about the core part of SQLAlchemy and use functions to achieve the same results. The next step is to move further into Python land and think even less about the SQL. Doing this, amongst other things, forces you to use parameterized calls, which if you’ll recall my rant in the previous lesson, is good for you but is also helpful in that you now get Python error messages when you mess up instead of the rather cryptic SQL equivalents.

00:36 The SQLAlchemy statements are still closely tied to SQL. Instead of doing SELECT with raw text, you’ll calling a .select() method. Let’s go into the REPL and play with some.

00:53 You start out the same as before, with an engine …

01:10 and a connection. And now this where different. SQLAlchemy can keep a representation of your database in its objects to make your easier. You get at this representation through the use of metadata objects. Let me just do an import.

01:35 And I’ve constructed a metadata object.

01:48 Wow, that was a lot of debug. Let me just scroll back here. I did this to grab info about the author_table. I did that by constructing a new Table object, passing the metadata object in, and using the autoload parameter, telling it to create the Table object based on the existing table.

02:07 That massive amount of log information is SQLAlchemy asking the database what it needs to know about the author_table so it can construct the metadata.

02:18 The Table object can be used to do other things, like create a new table, but in this case, the autoload is telling it to use an existing table to get the info.

02:28 I’ll use this moving forward in my queries. Let me scroll back down. And that’s what the author_table looks like. You can see info on each of the columns in the table being represented here.

02:44 Let’s use this to get some data. First off, I’ve imported a select statement.

03:05 And instead of using text and constructing a raw SQL statement, I’ve used the select object to structure a query. The select object takes a table object as an argument, and then the .where() method on it filters the query.

03:21 The .where() clause accepts any kind of Python comparison statement. SQLAlchemy knows how to translate these into the corresponding SQL. Note the use of the table object to filter on the first_name.

03:35 The .c attribute on the table object contains all of the columns on the table. This .where() clause is looking for all the Stephens in the first_name column of the author_table. I’m storing all of that inside of a stmt variable.

03:52 Not lot of info here, but if I print it out or convert it to a string, it shows the underlying SQL query that would get run. Like before, I call .execute() on the statement.

04:15 And that is my result set. As you can see, this is just another way of forming your SQL queries. The advantage here is that Python and SQLAlchemy are enforcing how you do that.

04:26 If you’re using a fancy IDE with type hints and auto-completion, that will help you even further. A SELECT without a WHERE clause automatically populates all the columns from the table in the query.

04:53 Alternatively, I can pass in the names of columns instead of the name of the table, in this case skipping the primary key in the result. You can compile these statements for efficiency and reuse.

05:15 Let me do that with this statement. And this is the query inside of the compiled statement. Notice that it used parameters for the WHERE clause.

05:35 You can examine the parameters that are attached to the compiled statement … And the results should be what you expected.

06:04 The WHERE clause is capable of taking multiple arguments to further refine your query. Of course, in this case, it resulted in the same output.

06:21 All right, how about adding some stuff? I’ve imported the insert method …

06:41 and the pattern is similar to the select. Create a statement, specifying the table being inserted into, and this time, instead of a WHERE clause, you have the attributes being inserted.

06:54 There’s the corresponding SQL … the compiled version, the parameters … and after I ran the insert(), doing a select() will show the new data.

07:22 Like with the raw equivalent, you can create multiple rows at a time.

07:46 Providing this list of dictionaries will create two new authors. Don’t forget all this is still inside a transaction. I should probably be committing more often.

08:07 And there’s the result. There are now seven authors in our database. You can probably guess where this is going.

08:36 Similar to before, I create a statement, specifying a .where() clause for my update and saying what the new values will be. Executed it … and Stephen’s name has changed. Rolling this back will put things back to where they were at my last commit.

09:09 See, undid the nickname. Not sure how Mr. King would feel about that. One more pattern for you: deleting things. Sing along with me. Import … statement …

09:35 execute. It’s like one of those follow-the-bouncing-ball things, but without the melody or music. And as the delete took effect, poor old Alex isn’t in our library anymore.

09:52 I’m going to roll it back. I like Alex.

10:05 And there you have it. I’m back to seven authors. That’s it for the core part of SQLAlchemy. Next up, I’ll show you the object-oriented way, using the ORM.