Learn to query databases with raw SQL and SQLAlchemy. The tutorial covers fetching rows, accessing columns, and building select statements with SQLAlchemy's core API, showing how both approaches yield identical results from the US Census dataset.
Chapters
The SQL query language can be used to do anything within the database. It can select data, insert new data, update existing data, and delete it. It can also be used to create, alter, and delete tables and columns. The first SQL statement we'll learn is the select statement, which provides the basic method of extracting information from your database. The general syntax of a select statement is select columns from tables with conditions. For example, we can select the POP 2008 column from the people table. We can also select all the columns from the people table with an asterisk. Let's execute this last query. We'll begin by importing Create engine and creating an engine. Next, we'll establish a connection by using the Connect method on the engine. Then we can define our select statement and pass it to the Execute method of the connection. This gives us an object that we can use to fetch the results, which we'll be assigning to the variable results proxy. Then we can tell resultsproxy to fetch all of the results via the fetchall method. Now, results will contain all of the data from our people table. Let's pause for just a moment and look at the object that the execute method gave us. That object is called a result proxy and can be used in a variety of different ways to get the data returned by our query. When we use a fetch method such as fetchall on a result proxy, we get a result set that contains the actual data we asked for in the query. This separation between the result proxy and the result set allows us to fetch as much or as little data as we desire, and we'll explore this more in a later section. Let's learn how to work with the result set. In this example, we're using the results from the prior query. We'll start by getting the first row of the results by using an index of 0. By printing the first row, we can see the data it contains. If we want to know what columns are in the row, we can find out by using the keys method. Finally, we print the value of the state column from the first row by using the column name as an attribute on the row object. In the prior query example, we wrote a normal SQL statement as a string. However, manipulating a string to build more complex statements can be very overwhelming. The beauty of SQLAlchemy is that it allows us to symbol these complex statements in a Pythonic way. Pythonic refers to code that adheres to the idioms of Python's common guidelines and expresses its intent in a highly readable manner. SQLAlchemy also hides the difference between database types so that we can focus on the data we want to work with instead of the differences we might encounter in each database type. Let's rebuild the same query using SQLAlchemy. The first three steps of creating an engine and establishing a connection will be the same. After that, we need to initialize our metadata and reflect the table as we did in the previous section. Then, in line seven, we build our select statement. Now hang tight. We'll talk more about this in a second. Finally, we execute the statement and fetch all the results. The SQLAlchemySelect statement works the same as a SQL select statement and in its most basic form takes a list of column or table objects. For example, statement SelectListOfCensus will select all of the columns of all of the rows in the census table. SQLAlchemy generates the same SQL statement we wrote by hand. We can see that by using the print function on the statement, which outputs select from census. Now it's your turn to write several SQL queries, both as raw SQL and in the more Pythonic way using SQLAlchemy. We'll be continuing to use the US Census database. Let's practice.
