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:

No. Overview keyword
1 Flask-SQLAlchemy development Flask-SQLAlchemy, psycopg2
2 PostgreSQL settings psql, Flask-Migrate

Execution environment

environment Ver.
macOS Catalina 10.15.2
Python 3.7.3
Flask-Migrate 2.5.2
Flask-SQLAlchemy 2.4.1
Flask 1.1.1
psycopg2 2.8.4
requests 2.22.0

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);
    

Recommended Posts

Create a web service in Flask-SQLAlchemy + PostgreSQL
Launch multiple instances with Postgresql 11.x
[Python] Create multiple directories
Create a web service in Flask-SQLAlchemy + PostgreSQL
Create a web service with Docker + Flask
Create a web server in Go language (net/http) (2)
Heppoko develops web service in a week # 2 Domain search
Create a function in Python
Create a dictionary in Python
Create a web server in Go language (net / http) (1)
Create a CSV reader in Flask
Create a DI Container in Python
Create a binary file in Python
Create a SlackBot service on Pepper
Create a Kubernetes Operator in Python
Create a random string in Python
Create a LINE Bot in Django
[Python / Django] Create a web API that responds in JSON format
Create a JSON object mapper in Python
Create a simple web app with flask
Create a Python-GUI app in Docker (PySimpleGUI)
[GPS] Create a kml file in Python
Launch a simple password-protected search service in 5 minutes
Create a Vim + Python test environment in 1 minute
Create a GIF file using Pillow in Python
Create an executable file in a scripting language
I want to create a window in Python
Create a standard normal distribution graph in Python
How to create a JSON file in Python
Create a virtual environment with conda in Python
Create a web map using Python and GDAL
Steps to develop a web application in Python
Create a custom search command in Splunk (Streaming Command)
Create a simple momentum investment model in Python
Create a new page in confluence with Python
Create a datetime object from a string in Python (Python 3.3)
Create a package containing global commands in Python
How to create a Rest Api in Django
Until you create a new app in Django
Create a MIDI file in Python using pretty_midi
Create a loop antenna pattern in Python in KiCad
[Docker] Create a jupyterLab (python) environment in 3 minutes!
Create a data collection bot in Python using Selenium
Released a web service for scoring handwriting using DeepLearning
Looking back on creating a web service with Django 1
[LINE Messaging API] Create a rich menu in Python
Create a plugin to run Python Doctest in Vim (2)
Create a plugin to run Python Doctest in Vim (1)
Looking back on creating a web service with Django 2
Create a fake Minecraft server in Python with Quarry
Create a Django schedule
Create SpatiaLite in Python
dict in dict Makes a dict a dict
Create a Bootable LV
Create a Python environment
Create a slack bot
Create a web application that recognizes numbers with a neural network
Create a local scope in Python without polluting the namespace
Create a list in Python with all followers on twitter
Created a reading record book in conjunction with PostgreSQL in Flask
Create a web surveillance camera with Raspberry Pi and OpenCV
[Django3] Display a web page in Django3 + WSL + Python virtual environment
Create a child account for connect with Stripe in Python