Use SQLAlchemy to create cache tables rather than raw psycopg2 SQL statements

[JamesKunstle]

SQLAlchemy implements a class-based metadata (table) definition interface that looks like the following:

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()

students = Table(
   'students', meta, 
   Column('id', Integer, primary_key = True), 
   Column('name', String), 
   Column('lastname', String),
)
meta.create_all(engine)

which emits the following SQL:

CREATE TABLE students (
   id INTEGER NOT NULL,
   name VARCHAR,
   lastname VARCHAR,
   PRIMARY KEY (id)
)

However, if the table already exists, SQLAlchemy won't actually run the statement. Instead, it'll skip creating the table altogether. This is more user-friendly than using raw SQL where we have to define:

CREATE TABLE name IF NOT EXISTS ...

and use postgres types. SQLAlchemy lets the user think in terms of python types and handles the Postgres dialect conversion on its own.

Users can still create tables with raw SQL, but with enough good examples, I think using SQLAlchemy is much easier to use and debug for an incoming user than SQL's DDL language.