Create a web service in Flask-SQLAlchemy + PostgreSQL

Create a web service in Flask-SQLAlchemy + PostgreSQL

Introduction

We will introduce it using the sample application (Feedback).

`Although it is an article on Mac environment, the procedure is the same for Windows environment. Please read and try the environment-dependent part. ``

Purpose

After reading this article to the end, you will be able to:

Execution environment

Source code

I think that understanding will deepen if you read while actually following the implementation contents and source code. Please use it by all means.

GitHub

Related articles

-Create a RESTful web service in Flask

0. Development environment configuration

tree.sh

/
├── app
│   ├── __init__.py
│   ├── config.py
│   ├── feedback
│   │   ├── __init__.py
│   │   ├── common/
│   │   ├── models
│   │   │   ├── __init__.py
│   │   │   └── feedback.py
│   │   ├── static/
│   │   ├── templates/
│   │   └── views/
│   ├── run.py
│   └── tests/
└── instance
     ├── postgresql.py
     ├── sqlite3.py
     └── config.py

1. Flask-Development of SQLAlchemy

Package installation

1. Install the package.

   ~$ pip install Flask-Migrate
   ~$ pip install Flask-SQLAlchemy
   ~$ pip install Flask
   ~$ pip install psycopg2
   

2. If you get an error when installing psycopg2, run the command with environment variables (macOS + venv environment).

   ~$ xcode-select --install
   ~$ env LDFLAGS="-I/usr/local/opt/openssl/include -L/usr/local/opt/openssl/lib" pip install psycopg2
   

SQLAlchemy settings

1. Set the config of the development environment.

   """instance/config.py
   """
   
   from instance.postgresql import SQLALCHEMY_DATABASE_URI as DATABASE_URI
   
   DEBUG = True
   # SECRET_KEY is generated by os.urandom(24).
   SECRET_KEY = '\xf7\xf4\x9bb\xd7\xa8\xdb\xee\x9f\xe3\x98SR\xda\xb0@\xb7\x12\xa4uB\xda\xa3\x1b'
   STRIPE_API_KEY = ''
   
   SQLALCHEMY_DATABASE_URI = DATABASE_URI
   SQLALCHEMY_TRACK_MODIFICATIONS = True
   SQLALCHEMY_ECHO = True
   

2. Set up PostgreSQL.

   """instance/postgresql.py
   """
   
   SQLALCHEMY_DATABASE_URI = 'postgresql+psycopg2://{user}:{password}@{host}/{name}'.format(**{
       'user': 'nsuhara',
       'password': 'nsuhara',
       'host': '127.0.0.1',
       'name': 'db.postgresql'
   })
   

3. Set up SQLite3 (bonus).

   """instance/sqlite3.py
   """
   
   import os
   
   SQLALCHEMY_DATABASE_URI = 'sqlite:///{host}/{name}'.format(**{
       'host': os.path.dirname(os.path.abspath(__file__)),
       'name': 'db.sqlite3'
   })
   

Creating a Model

1. Create an instance of SQLAlchemy.

   """app/feedback/models/__init__.py
   """
   
   from flask_sqlalchemy import SQLAlchemy
   
   db = SQLAlchemy()
   
   
   def init():
       """init
       """
       db.create_all()
   

2. Create a Model by inheriting the SQLAlchemy class (db.Model).

   """app/feedback/models/feedback.py
   """
   
   from datetime import datetime
   
   from feedback.models import db
   
   
   class Feedback(db.Model):
       """Feedback
       """
       __tablename__ = 'feedback'
   
       id = db.Column(db.Integer, primary_key=True, autoincrement=True)
       service = db.Column(db.String(255), nullable=False)
       title = db.Column(db.String(255), nullable=False)
       detail = db.Column(db.String(255), nullable=False)
       created_date = db.Column(
           db.DateTime, nullable=False, default=datetime.utcnow)
   
       def __init__(self, service, title, detail):
           self.service = service
           self.title = title
           self.detail = detail
   
       def to_dict(self):
           """to_dict
           """
           return {
               'id': self.id,
               'service': self.service,
               'title': self.title,
               'detail': self.detail,
               'created_date': self.created_date
           }
   

2. PostgreSQL settings

This is an example of executing Homebrew.

Service confirmation

1. Check the service.

   ~$ brew services list
   

   Name       Status  User    Plist
   postgresql started nsuhara /Users/nsuhara/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
   

Service start / end

1. Start the service.

   ~$ brew services start postgresql
   

2. End the service.

   ~$ brew services stop postgresql
   

Check the database

1. Check the database.

`Three databases are created by default. Also, the Mac username is set as Owner. ``

```procedure.sh
~$ psql -l
```

```result.sh
                            List of databases
    Name      |  Owner  | Encoding | Collate | Ctype |  Access privileges
--------------+---------+----------+---------+-------+---------------------
postgres      | nsuhara | UTF8     | C       | C     |
template0     | nsuhara | UTF8     | C       | C     | =c/nsuhara         +
              |         |          |         |       | nsuhara=CTc/nsuhara
template1     | nsuhara | UTF8     | C       | C     | =c/nsuhara         +
              |         |          |         |       | nsuhara=CTc/nsuhara
```

Database connection / disconnection

1. Connect to the database.

   ~$ psql -h "<host_name>" -p <port_number> -U "<role_name>" -d "<database_name>"
   

   ~$ psql -h "127.0.0.1" -p 5432 -U "nsuhara" -d "postgres"
   

2. Disconnect the database.

   postgresql=# \q
   

Creating a role (user)

1. Connect to the database.

2. Confirm the role (user).

   postgresql=# \du
   

                                   List of roles
   Role name |                         Attributes                         | Member of
   ----------+------------------------------------------------------------+-----------
   nsuhara   | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
   

3. Create a role (user).

   postgresql=# CREATE ROLE "<role_name>" LOGIN PASSWORD "password";
   

   postgresql=# CREATE ROLE "nsuhara" LOGIN PASSWORD "nsuhara";
   

4. Delete the role (user).

   postgresql=# DROP ROLE "<role_name>";
   

   postgresql=# DROP ROLE "nsuhara";
   

Creating a database

1. Connect to the database.

2. Check the database.

   postgresql=# \l
   

                           List of databases
       Name      |  Owner  | Encoding | Collate | Ctype |  Access privileges
   --------------+---------+----------+---------+-------+---------------------
   db.postgresql | nsuhara | UTF8     | C       | C     |
   postgres      | nsuhara | UTF8     | C       | C     |
   template0     | nsuhara | UTF8     | C       | C     | =c/nsuhara         +
                 |         |          |         |       | nsuhara=CTc/nsuhara
   template1     | nsuhara | UTF8     | C       | C     | =c/nsuhara         +
                 |         |          |         |       | nsuhara=CTc/nsuhara
   

3. Create a database.

   postgresql=# CREATE DATABASE "<database_name>" OWNER "<role_ name>";
   

   postgresql=# CREATE DATABASE "db.postgresql" OWNER "nsuhara";
   

4. Delete the database.

   postgresql=# DROP DATABASE "<database_name>";
   

   postgresql=# DROP DATABASE "db.postgresql";
   

Database migration

1. Set Flask environment variables.

2. Migrate the database.

   ~$ flask db init
   ~$ flask db migrate
   ~$ flask db upgrade
   

Delete record

1. Connect to the database.

2. Delete the record.

   postgresql=# delete from <table_name>;
   

3. Initialize automatic numbering.

   postgresql=# select setval ('<table_name>_id_seq', 1, false);