Skip to content

Contact sales

By filling out this form and clicking submit, you acknowledge our privacy policy.
  • Labs icon Lab
  • A Cloud Guru
Google Cloud Platform icon

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.

Google Cloud Platform icon

Path Info

Clock icon Intermediate
Clock icon 1h 0m
Clock icon Aug 26, 2019

Contact sales

By filling out this form and clicking submit, you acknowledge our privacy policy.

Table of Contents

  1. Challenge

    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 to the internal forum directory to mark it as a package:

    $ touch forum/

    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 so that our library can be installed. A quick and easy way to do this is to use the for Humans:

    (forum) $ curl -O

    Now that we have a, we'll need to change some of the metadata and add our dependencies to the REQUIRED list: (partial)

    # Package meta-data.
    NAME = 'forum'
    DESCRIPTION = 'A model library for accessing an internal forum database'
    URL = ''
    EMAIL = '[email protected]'
    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']
  2. Challenge

    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 (
        body TEXT NOT NULL,
        author_name VARCHAR(50) NOT NULL,
    create table comments (
        post_id INTEGER REFERENCES posts(id),
        comment TEXT NOT NULL,
        sentiment VARCHAR(10) NOT NULL,
        commenter_name VARCHAR(50) NOT NULL,

    With this knowledge, let's create our within our forum package:


    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(""))
        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.

  3. Challenge

    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.

The Cloud Content team comprises subject matter experts hyper focused on services offered by the leading cloud vendors (AWS, GCP, and Azure), as well as cloud-related technologies such as Linux and DevOps. The team is thrilled to share their knowledge to help you build modern tech solutions from the ground up, secure and optimize your environments, and so much more!

What's a lab?

Hands-on Labs are real environments created by industry experts to help you learn. These environments help you gain knowledge and experience, practice without compromising your system, test without risk, destroy without fear, and let you learn from your mistakes. Hands-on Labs: practice your skills before delivering in the real world.

Provided environment for hands-on practice

We will provide the credentials and environment necessary for you to practice right within your browser.

Guided walkthrough

Follow along with the author’s guided walkthrough and build something new in your provided environment!

Did you know?

On average, you retain 75% more of your learning if you get time for practice.

Start learning by doing today

View Plans