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.
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.
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.
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.
.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
.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
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.
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.
Become a Member to join the conversation.