Forum Export: Modeling Database Tables with SQLAlchemy Using Python

Databases are great for storing and allowing us to query information, but the default shape and SQL are not always the best options for people within our organization. If we know the schema of our database tables, we can create classes to model our data and then massage the data into other shapes that are more helpful for specific use cases. In this hands-on lab, we’ll create a library to connect to our forum database using SQLAlchemy and then create a few models to represent forum posts and comments.

Learning Objectives

Successfully complete this lab by achieving the following learning objectives:

Set Up a Project and Virtualenv with Pipenv

To set up our project, we’re going to create a new directory with an internal directory of the same name (forum) to hold our Python package:

$ mkdir -p forum/forum
$ cd forum

We’ll also need to add an __init__.py to the internal forum directory to mark it as a package:

$ touch forum/__init__.py

Next, let’s make sure that pipenv is installed and use it to create our virtualenv, then install SQLAlchemy and psycopg2-binary so that we can interact with a PostgreSQL database:

$ pip3.7 install --user -U pipenv
...
$ pipenv --python python3.7 install SQLAlchemy psycopg2-binary
...

Once the virtualenv is created, we should activate it while working on this project:

$ pipenv shell
(forum) $

Lastly, we should create a setup.py so that our library can be installed. A quick and easy way to do this is to use the setup.py for Humans:

(forum) $ curl -O https://raw.githubusercontent.com/navdeep-G/setup.py/master/setup.py

Now that we have a setup.py, we’ll need to change some of the metadata and add our dependencies to the REQUIRED list:

setup.py (partial)

# Package meta-data.
NAME = 'forum'
DESCRIPTION = 'A model library for accessing an internal forum database'
URL = 'https://github.com/me/forum'
EMAIL = 'me@example.com'
AUTHOR = 'Awesome Soul'
REQUIRES_PYTHON = '>=3.6.0'
VERSION = '0.1.0'

# What packages are required for this module to be executed?
REQUIRED = ['SQLAlchemy', 'psycopg2-binary']

Define the `Post` and `Comment` Classes in a `models` Module

Our library only needs to provide a few classes that we can then use with SQLAlchemy in ad-hoc scripts. To do this, we’ll use the declarative_base from SQLAlchemy. Here’s the schema for our posts and comments database tables:

create table posts (
    id SERIAL PRIMARY KEY,
    body TEXT NOT NULL,
    author_name VARCHAR(50) NOT NULL,
    created_on TIMESTAMP NOT NULL DEFAULT NOW()
);
create table comments (
    id SERIAL PRIMARY KEY,
    post_id INTEGER REFERENCES posts(id),
    comment TEXT NOT NULL,
    sentiment VARCHAR(10) NOT NULL,
    commenter_name VARCHAR(50) NOT NULL,
    created_on TIMESTAMP NOT NULL DEFAULT NOW()
);

With this knowledge, let’s create our models.py within our forum package:

forum/models.py

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Text, TIMESTAMP, ForeignKey
from sqlalchemy.orm import relationship

Base = declarative_base()

class Post(Base):
    __tablename__ = "posts"

    id = Column(Integer, primary_key=True)
    body = Column(Text, nullable=False)
    author_name = Column(String(50), nullable=False)
    created_on = Column(TIMESTAMP)

    comments = relationship("Comment", back_populates="post")

class Comment(Base):
    __tablename__ = "comments"

    id = Column(Integer, primary_key=True)
    post_id = Column(Integer, ForeignKey("posts.id"))
    comment = Column(Text, nullable=False)
    sentiment = Column(String(10), nullable=False)
    commenter_name = Column(String(50), nullable=False)
    created_on = Column(TIMESTAMP)

    post = relationship("Post", back_populates="comments")

Now we can install our library on any machine that needs to interact with the database, and these classes will make working with the data easier. We’re not going to add any configuration logic to our helper; the user will be expected to know how to generate a SQLAlchemy engine and a session.

Utilize the Library from REPL

Before we call this portion of the application completed, we’re going to ensure that we can interact with the database the way that we think that we should be able to. Let’s install our package using pip install -e . (with our virtualenv started):

(forum) $ pip install -e .

Now let’s open up a REPL, create an engine and a session, and load in some Post and Comment objects to ensure that the library is working as expected. For the engine, you’ll need to use the username of admin, a password of password, the public IP address of the database server, port 80, and a database name of forum.

(forum) $ python
>>> from sqlalchemy import create_engine
>>> from sqlalchemy.orm import sessionmaker
>>> from forum.models import Post, Comment
>>> engine = create_engine("postgresql://admin:password@PUBLIC_IP:80/forum")
>>> Session = sessionmaker(bind=engine)
>>> session = Session()
>>> posts = session.query(Post).limit(10).all()
>>> post = posts[0]
>>> post.__dict__
{'_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x1057ae210>, 'body': 'Voluptatem voluptatem eius numquam neque magnam.', 'id': 1, 'created_on': datetime.datetime(2019, 7, 31, 19, 9, 28, 730416), 'author_name': 'Nelson Schacht', 'comments': [<forum.models.Comment object at 0x1057bda10>, <forum.models.Comment object at 0x1057bdad0>]}
>>> post.comments[0].__dict__
{'_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x1057bd9d0>, 'comment': 'Aliquam sed dolor numquam non. Quiquia velit etincidunt est ipsum. Numquam tempora etincidunt velit sed quisquam. Etincidunt ipsum amet etincidunt adipisci ut modi. Numquam aliquam velit dolorem quisquam dolorem voluptatem. Dolor velit quiquia sit etincidunt eius aliquam. Est magnam aliquam eius est consectetur tempora. Quaerat modi quiquia adipisci modi quaerat tempora quisquam. Sit neque sit sed quisquam porro dolore. Labore dolorem tempora eius adipisci ipsum adipisci.', 'id': 36, 'commenter_name': 'James Chavez', 'sentiment': 'postitive', 'post_id': 1, 'created_on': datetime.datetime(2019, 7, 31, 19, 9, 28, 956082)}

We were successfully able to query our database and populate Post and Comment objects.

Forum Export: Modeling Database Tables with SQLAlchemy Using Python

About this Hands-on Lab

Learning Objectives

Additional Resources

What are Hands-on Labs

Newsletter