In order to connect to a database, we'll need to create an engine. One use for the engine is to build the database instance with our table declarations. The other use for the engine is to manage the data from a session, which we'll look at later. Here's a script that we can use to build a database:

from sqlalchemy import create_engine 
engine = create_engine('sqlite:///./p2_c11_blog2.db', echo=True) 
Base.metadata.create_all(engine) 

When we create an Engine instance, we use a URL-like string that names the vendor product and provides all the additional parameters required to create the connection to that database. In the case of SQLite, the connection is a filename. In the case of other database products, there might be server host names and authentication credentials.

Once we have the engine, we've done some fundamental metadata operations. We've shown you create_all(), which builds all of the tables. We might also perform a drop_all(), which will drop all of the tables, losing all the data. We can, of course, create or drop an individual schema item too.

If we change a table definition during software development, it will not automatically mutate the SQL table definition. We need to explicitly drop and rebuild the table. In some cases, we might want to preserve some operational data, leading to potentially complex surgery to create and populate new table(s) from old table(s).

The echo=True option writes log entries with the generated SQL statements. This can be helpful to determine whether the declarations are complete and create the expected database design. Here's a snippet of the output that is produced:

CREATE TABLE "BLOG" (
    id INTEGER NOT NULL, 
    title VARCHAR, 
    PRIMARY KEY (id)
)

CREATE TABLE "TAG" (
    id INTEGER NOT NULL, 
    phrase VARCHAR, 
    PRIMARY KEY (id), 
    UNIQUE (phrase)
)

CREATE TABLE "POST" (
    id INTEGER NOT NULL, 
    title VARCHAR, 
    date DATETIME, 
    rst_text TEXT, 
    blog_id INTEGER, 
    PRIMARY KEY (id), 
    FOREIGN KEY(blog_id) REFERENCES "BLOG" (id)
)

CREATE TABLE "ASSOC_POST_TAG" (
    "POST_ID" INTEGER, 
    "TAG_ID" INTEGER, 
    FOREIGN KEY("POST_ID") REFERENCES "POST" (id), 
    FOREIGN KEY("TAG_ID") REFERENCES "TAG" (id)
)

This shows SQL that the CREATE TABLE statements were created based on our class definitions. This can be helpful to see how the ORM definitions are implemented in a database. 

Once the database has been built, we can create, retrieve, update, and delete objects. In order to work with database objects, we need to create a session that acts as a cache for the ORM-managed objects.

We'll see how to manipulate objects with the ORM layer in the next section.