Forum Export: Modeling Database Tables with SQLAlchemy Using Python

Hands-On Lab

 

Photo of Keith Thompson

Keith Thompson

DevOps Training Architect II in Content

Length

01:00:00

Difficulty

Intermediate

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.

What are Hands-On Labs?

Hands-On Labs are scenario-based learning environments where learners can practice without consequences. Don't compromise a system or waste money on expensive downloads. Practice real-world skills without the real-world risk, no assembly required.

Forum Export: Modeling Database Tables with SQLAlchemy

Introduction

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.

Connect to the Lab

Option 1: Connect with the Visual Studio (VS) Code Editor

  1. Open your terminal application, and run the following command:
    ssh cloud_user@PUBLIC_IP_ADDRESS
  2. Enter yes at the prompt.
  3. Enter your cloud_user password at the prompt.
  4. Run exit to close the connection.
  5. Run the following command:
    ssh-copy-id cloud_user@PUBLIC_IP
  6. Enter your password at the prompt.
  7. Open Visual Studio Code.
  8. In the seach bar at the top, enter cloud_user@PUBLIC_IP.
  9. Once you've connected, click the square Extensions icon in the left sidebar.
  10. Under Local - Installed, scroll down to Python and click Install on SSH.
  11. Click Reload to make the changes take effect.

Option 2: Connect with Your Local Machine

  1. Open your terminal application, and run the following command (remember to replace PUBLIC_IP with the public IP you were provided on the lab instructions page):
    ssh cloud_user@PUBLIC_IP
  2. Type yes at the prompt.
  3. Enter your cloud_user password at the prompt.

Set Up a Project and Virtualenv with Pipenv

  1. Create a new directory named forum with an internal directory of the same name.
    mkdir -p forum/forum
  2. Change to the forum directory.
    cd forum
  3. Make the internal forum directory a package.
    touch forum/__init__.py
  4. Install Pipenv.
    pip3.7 install --user -U pipenv
  5. Create a virtualenv, and install SQLAlchemy and psycopg2-binary as dependencies.
    pipenv --python python3.7 install SQLAlchemy psycopg2-binary
  6. Activate the virtualenv.
    pipenv shell
  7. Add setup.py for Humans.
    curl -O https://raw.githubusercontent.com/navdeep-G/setup.py/master/setup.py
  8. Edit the NAME and DESCRIPTION parameters of the setup.py metadata, and add the dependencies we installed to the REQUIRED list.

    # 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

  1. Create the models.py within the 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("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")

Utilize the Library from REPL

  1. With the virtualenv started, install the package.
    pip install -e .
  2. Load Python.
    python
  3. Open 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("postgres://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)}

Conclusion

Congratulations, you've successfully completed this hands-on lab!