SQLAlchemy's Engine object connects Python to any database using a connection string. The library can automatically reflect existing tables into Python objects, exposing their columns and types without writing schema code by hand.
Chapters
In the Python world, there are several great tools that we can use when working with databases. One of those is SQLAlchemy that we'll be using throughout this course. SQLAlchemy will allow us to generate SQL queries by writing Python code. You should still consider learning how to write queries in SQL as well. SQLAlchemy has two main components. The part we will be focusing on is often referred to as the core part of SQLAlchemy. It's really focused around the relational model of the database. Additionally, there is the object relational model, or ORM part of SQLAlchemy that is really focused around data models and classes that you as a programmer create. There are many different types of databases, and each database type has its own quirks and unique capabilities. You'll commonly find SQLite, Postgres, SQL, MySQL, Microsoft SQL Server, and Oracle. When working with data, SQLAlchemy provides a way to operate across all these database types in a consistent manner. To connect to a database, we need a way to talk to it, and an engine provides that common interface. To create an engine, we import the Create Engine function from SQLAlchemy. We then use the CreateEngine function and supply it a connection string that provides the details needed to connect to a database. Finally, once we have an engine, we're ready to make a connection using the connect method on the engine. It's worth noting that SQLalchemy won't actually make the connection until we give it some work to execute. So to review, an engine is the common interface to the database, which requires a connection string to provide the details used to find and connect to the database. Before we go any further, let's talk a bit more about connection strings. In their simplest form, they tell us what kind of database we are talking to and how we should access it. In this example, you can see that we're using the SQLite database driver and the database file named CensusNYC SQLite, which is in the current directory. Now that we have an engine and a connection, we need to know what tables are in the database. We'll start again by importing the CreateEngine function and creating an engine to our database. Finally, we can use the table names method of an engine, which returns a list of tables. Once we know what table we want to work on, we need a way to access that table with Python. To do that, we're going to use a handy process called reflection, which reads the database and builds a table object that we can use in our code. We have already created our engine so we begin by importing the metadata and table objects needed for reflection. The metadata object is a catalog that stores database information such as tables so that we don't have to keep looking them up to reflect the table. We initialize a metadata object. Next we use the SQLAlchemy table object and provide the table name we got earlier from the table names method. We also supply our metadata instance and then instruct it to auto load the table using the engine. Finally, we can use the function repr to view the details of our table that we stored as census. This allows us to see the names of the columns such as the state and sex columns along with their types such as varchar and integer. This process of reflection may seem like a bit of overhead, but it will make understanding your databases and extracting information from them far easier downstream. Now it's your turn to practice writing connection strings and connecting to databases and reflecting tables. Then we'll be back here writing our first SQL queries.
