Quickly build Apache + Pipenv + Flask + SQLAlchemy with Sakura VPS

Introduction

A memo to build a Python web server on Sakura VPS (CentOS7) Apache → gunicorn → Pipenv → Flask + SQLAlchemy (bonus phpMyAdmin) Build a web server that handles DB in the shortest time from server instance creation with Python.

This article is completely for beginners, but the minimum required knowledge is

--Basic usage of vi --How to connect ssh from client

It is about.

I hope it will be useful for server construction and Python beginners (myself).

** This article is for building a test web server in the shortest time, so security etc. are not considered **

Eventually install the following:

• CentOS7
• Apache2.4
• Python3.8.0
• Pipenv
• gunicorn
• Flask
• SQLAlchemy
• PhpMyAdmin7.2

** Let's say it quickly. ** **

OS installation

OS installation with the following settings from the Sakura VPS administrator screen Various settings> OS installation

--Standard OS

• CentOS7 x86_64 --Administrative user name: root --New admin user password: ** Create root password ** --Do not use startup scripts --Packet filter
  • [x] SSH
  • [x] Web
  • [x] FTP

Log in to the console

Log in as root from the Sakura VPS console or client ssh connection When logging in from a mac client

Offending ECDSA key in /Users/'username'/.ssh/known_hosts:1

Delete known_hosts in ~ / .ssh

MySQL installation and configuration

Refer to the Sakura VPS manual https://www.sakura-vps.net/centos7-setting-list/mysql-settings-for-sakura-vps-centos7

** Delete MariaDB **

$ yum remove -y mariadb-libs
$ rm -rf /var/lib/mysql

*** Addition of official MySQL repository ***

$ rpm -Uvh http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

** Install MySQL **

$ yum -y install mysql-community-server

** Version check **

$ mysqld --version

** MySQL startup and auto-start settings **

$ systemctl start mysqld.service
$ systemctl enable mysqld.service

** Check MySQL initial password ** In MySQL5.7, the initial password is generated at the first startup, so check the initial password with the following command.

• The (password) part is the initial password. Save it for re-entry after this

$ cat /var/log/mysqld.log | grep password
[Note] A temporary password is generated for root@localhost:(password)

** MySQL security settings **

[root@ ~]# mysql_secure_installation
 
Securing the MySQL server deployment.
 
Enter password for user root:"Enter the initial password"
 
The existing password for the user account root has expired. Please set a new password.
 
New password:"Enter new password (including alphabetic characters, numbers and symbols)"
 
Re-enter new password:"Re-enter password for confirmation"
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.
 
Estimated strength of the password: 100
Change the password for root ? ((Press y|Y for Yes, any other key for No) :y "Enter y (* 1)"
 
New password:"Enter the same password set above"
 
Re-enter new password:"Re-enter password for confirmation"
 
Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) :y "Enter y to confirm that the set password is acceptable"
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
 
Remove anonymous users? (Press y|Y for Yes, any other key for No) :y "Enter y to confirm that you can delete the anonymous user"
Success.
 
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
 
Disallow root login remotely? (Press y|Y for Yes, any other key for No) :y "Enter y to confirm that you can not log in as root remotely"
Success.
 
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
 
Remove test database and access to it? (Press y|Y for Yes, any other key for No) :y "Enter y to confirm that you can delete the test database"
 - Dropping test database...
Success.
 
 - Removing privileges on test database...
Success.
 
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
 
Reload privilege tables now? (Press y|Y for Yes, any other key for No) :y "Enter y to confirm whether to read the permission table now and enable the setting"
Success.
 
All done!

** Change MySQL settings (my.cnf) **

$ vi /etc/my.cnf

Add the highlight below to your my.cnf file.

"Character-set-server = utf8" ⇒ Specify UTF8 as the character code used in the MySQL database. "Default_password_lifetime = 0" ⇒ From MySQL 5.7, the password expiration date of the user is 360 days by default, so set this expiration date to 0 to disable it.

Restart MySQL for the settings to take effect.

$ systemctl restart mysqld.service

** Creating a database ** Login to MySQL

$ mysql -u root -p

Create test_db

mysql>CREATE DATABASE test_db;
mysql>SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_db            |
+--------------------+

Log out of MySQL

mysql> QUIT;

Apache installation

yum installation

$ yum install httpd

Auto start setting

$ systemctl enable httpd.service

Apache startup

$ systemctl start httpd

Check from the browser http://'サイトのアドレス'

Install pyenv and python

** Yum install the required packages **

$ yum install gcc bzip2 bzip2-devel openssl openssl-devel readline readline-devel
$ yum update -y
$ yum groupinstall "Development Tools"
$ yum install libffi-devel
$ yum install mariadb-devel

** pyenv installation ** Click here for the location as you can pass through

$ cd /usr/local/

Clone pyenv from git

$ git clone https://github.com/pyenv/pyenv.git ./pyenv
$ mkdir -p ./pyenv/versions ./pyenv/shims

PATH setting

$ echo 'export PYENV_ROOT="/usr/local/pyenv"' | sudo tee -a /etc/profile.d/pyenv.sh
$ echo 'export PATH="${PYENV_ROOT}/shims:${PYENV_ROOT}/bin:${PATH}"' | sudo tee -a /etc/profile.d/pyenv.sh
$ source /etc/profile.d/pyenv.sh

Check the version of pyenv

$ pyenv --version

** Install Python 3.8.0 ** Somehow install with options for wsgi

$ env PYTHON_CONFIGURE_OPTS="--enable-shared" pyenv install 3.8.0

Version switching

$ pyenv global 3.8.0
$ pyenv rehash

Verification

$ python --version
Python 3.8.0

Build a virtual environment with pipenv

** Install **

$ pip install --upgrade pip
$ pip install pipenv

** Create virtual environment ** Create "test" project

$ mkdir -p /var/www/app/test
$ cd /var/www/app/test
$ pipenv install

Launch pipenv shell

$ pipenv shell

Installation confirmation

$ python --version

** Install the package used this time ** The folder name is displayed on the left side during pipenv shell

(test) $ pipenv install gunicorn
(test) $ pipenv install flask
(test) $ pipenv install sqlalchemy
(test) $ pipenv install mysqlclient

Apache settings and Flask app creation

Proxy requests received by Apache to gunicorn

$ vi /etc/httpd/conf/httpd.conf

Add to last line

httpd.conf

ProxyPass /test http://localhost:8000/

apache restart

$ systemctl restart httpd.service

http request is proxied to port 8000 You will be able to access your Flask application from your browser.

** Create .py file **

Create two Flask startup and sql .py files in / var / www / app / test.

$ vi app.py

app.py

from flask import Flask
from testsql import *
import datetime

app = Flask(__name__)

@app.route("/")
def hello():
	return "Hello World! "

@app.route("/insert")
def insert():
	#If there is no table, the CREATE TABLE statement is executed
	Base.metadata.create_all(bind=ENGINE)

	data = TestTable()
	ret = data.create_date.strftime("%Y-%m-%d %H:%M:%S")
	session.add(data)
	session.commit()
	session.close()

	return "insert data "+ret

@app.route("/select")
def select():
	#Get the latest data
	res = session.query(TestTable).order_by(desc(TestTable.create_date)).all()

	ret = ""

	print("ret len "+str(len(res)))

	if 0 < len(res):
		ret = str(res[0].create_date)

	return "select "+ret


if __name__ == "__main__":
	#Web server launch
	app.run(debug=True)

Next, create a file that handles the DB

$ vi testsql.py

testsql.py

from sqlalchemy import *
from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Float, DateTime, Text
from datetime import datetime, timedelta, timezone

#mysql DB settings
DATABASE = 'mysql://%s:%s@%s/%s?charset=utf8' % (
	"root",             #user
“First mysql password you wrote down”,         #password
	"localhost",        # host ip
	"test_db", #DB name
)

ENGINE = create_engine(
	DATABASE,
	encoding = "utf-8",
	echo=False #If True, SQL will be output each time it is executed
)

#Creating a Session
session = scoped_session(
			sessionmaker(
				autocommit = False,
				autoflush = True,
				bind = ENGINE))

#Base model creation
Base = declarative_base()
Base.query = session.query_property()

#Inherit the base model to define the model class
class TestTable(Base):
	__tablename__ = 'tbl_test'

	id          = Column('id', Integer, primary_key = True)

	create_date = Column('create_date', DateTime)

	def __init__(self):
		#Time zone generation
		JST = timezone(timedelta(hours=+9), 'JST')

		self.create_date = datetime.now(JST)

Start gunicorn

gunicorn [file name]: Start with [variable name](with reload option by changing file)

(test) $ gunicorn --reload app:app

http:'site address' / test Check the display with

http:'site address' / test / insert Write to database with

http:'site address' / test / select Returns the data written to the database in (date and time)

In gunicorn log at / select ret len [number of records inserted] Is displayed

gunicorn finished

(test) $ pkill gunicorn

I didn't do it this time Start gunicorn when the server starts Reference: https://qiita.com/sti320a/items/c196387d405272cce46e

[Bonus] phpMyAdmin

For those who want to touch with DB related GUI

** Install PHP from remi repository **

$ rpm -Uvh http://rpms.famillecollet.com/enterprise/remi-release-7.rpm
$ yum install --enablerepo=remi,remi-php71 php php-devel php-mbstring php-mysqlnd php-pdo php-gd

** phpMyAdmin installation and configuration ** Reference: https://knowledge.sakura.ad.jp/9006/

Installation

$ yum --enablerepo=remi-php72 install phpmyadmin

Access settings

$ vi /etc/httpd/conf.d/phpMyAdmin.conf

Add "Require all granted" to the highlights below. With this setting, you can access phpMyAdmin from the outside.

apache restart

$ systemctl restart httpd

http://'サイトのアドレス'/phpmyadmin/

at the end

I'm doing a lot of research to build a web server with Python

--FTP installation --Prohibit root connection and password connection with ssh --Adding a user for ssh and a user for ftp --Add the user to the root group so that you can run the sudo command

This area was common to almost all articles.

I stumbled on a part that was not the original purpose and took time to investigate, so I ended up starting over from the beginning ... Because there were quite a lot of things Build quickly anyway, omitting extra work and explanations I tried to focus on that.

Furthermore, the difference between pyenv and pipenv in the Python environment Where the virtual environment is created and how it works gunicorn ap server mechanism etc. When I started to investigate, it became deeper, and at the same time I became interested, I gradually moved away from the purpose ...

This article + selenium etc. was used to automate the process A site that collects manga that can be read with each manga app Manga Ikki Operates. Please take a look here if you like.