1. Introduction to SQL queries
The SQL query language can be used to do anything within the database.
2. SQL Statements
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.
3. Basic SQL querying
The general syntax of a select statement is select COLUMNS from TABLE CONDITIONS. For example, we can select the pop2008 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.
4. Basic SQL querying
We'll begin by importing create_engine and creating an engine. Next, we will 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 on the connection. This gives us an object we can use to fetch the results, which we assign to the variable results_proxy. Then we tell results_proxy to fetch all 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 that object that the execute method gave us.
5. ResultProxy vs ResultSet
That object is called a ResultProxy and it 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 the ResultProxy, we get a ResultSet that contains the actual data we asked for in the query. This separation between the ResultProxy and the ResultSet allows us to fetch as much or as little data as we desire. We'll explore this more in a later section. Let's learn how to work with a ResultSet.
6. Handling ResultSets
In this example we are using the results from the prior query. We'll start by getting the first_row of the results 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.
7. SQLAlchemy to build queries
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 assemble 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 way. SQLAlchemy also hides the difference between database types so 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.
8. SQLAlchemy querying
The first three steps of creating an engine and establishing a connection will be the same. After that, we will need to initialize our metadata and reflect the table as we did previously. Then in line 7, we build our select statement. Hang tight we'll talk more about this in a second. Finally, we execute the statement and fetch all the results.
9. SQLAlchemy select statement
The SQLAlchemy select 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, stmt equals select census will select all the columns of all 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.
10. Let's practice!
Now it's your turn 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.