# 1/17課程

[1/19 課程筆記連結](https://hackmd.io/y5h8x86xQYS4-xlJdtyTdQ?both)

## 大綱
- 簡介 PostgreSQL
- 下載 PostgreSQL 並透過 PGAdmin 操作
- Python Alembic 版控

## 需預先下載的軟體
- [Vscode](https://code.visualstudio.com/download)
- [PGAdmin](https://www.pgadmin.org/download/pgadmin-4-windows/)
- [今天上課講義](https://docs.google.com/presentation/d/1a8D5zRzx9109b2oVNPVwJw7LWSdglUvV/edit?usp=sharing&ouid=101247442856896968889&rtpof=true&sd=true)
- [今天上課講義2](https://www.canva.com/design/DAFp31PYLYQ/WeWxwnzmYmgjDJI3E80lJQ/edit?utm_content=DAFp31PYLYQ&utm_campaign=designshare&utm_medium=link2&utm_source=sharebutton)
- [上課用程式碼](https://drive.google.com/file/d/1Q94HpV2KlddNXj9YTJ9oUSzE1suuY3BX/view?usp=drive_link)
- Python3.10

requirements.txt

aniso8601==9.0.1
apispec==5.2.2
certifi==2023.5.7
charset-normalizer==3.2.0
click==8.1.3
Flask==2.1.2
flask-apispec==0.11.4
Flask-JWT-Extended==4.4.1
Flask-RESTful==0.3.9
idna==3.4
itsdangerous==2.1.2
Jinja2==3.1.2
MarkupSafe==2.1.2
marshmallow==3.18.0
marshmallow-enum==1.5.1
packaging==24.2
pip==24.3.1
PyJWT==2.6.0
python-dotenv==1.0.1
pytz==2022.1
requests==2.31.0
setuptools==75.6.0
six==1.16.0
urllib3==1.26.16
webargs==8.2.0
Werkzeug==2.2.2

psycopg2-binary==2.9.3
alembic==1.10.2
SQLAlchemy==1.4.37
Flask-SQLAlchemy==2.5.1
SQLAlchemy-Utils==0.38.2

- Ubuntu24.04

## Q&A
`pip install -i https://test.pypi.org/simple/ psycopg2-binary==2.9.3 --force-reinstall --no-cache-dir`
網路上找到這個方法有成功，但這個方式有什麼要注意的嗎?

## 上課會用到網頁
- [ChatGPT](https://chat.openai.com/)
- [API User專案下載網址](https://drive.google.com/file/d/1Q94HpV2KlddNXj9YTJ9oUSzE1suuY3BX/view?usp=drive_link)

## 補充
- [UTM](https://techblog.shippio.io/how-to-run-an-ubuntu-22-04-vm-on-m1-m2-apple-silicon-9554adf4fda1)

## 上半堂(要用sudo權限在api_rest_user資料夾底下進行)

apt-get install curl ca-certificates python3-pip python3-venv libpq-dev gnupg2 wget vim ufw net-tools -y


sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'


wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -


apt-get update

apt-get -y install postgresql-12

apt list --installed | grep postgresql

vim /etc/postgresql/12/main/pg_hba.conf


找到 replication
/replication // 找 replication 的關鍵字

壓 enter // 確定要找這個

按 n // 跳到下一個符合關鍵字的地方

o // 跟 i  一樣是寫入、但是是寫下一行

加上 host    all    all     0.0.0.0/0       md5

vim /etc/postgresql/12/main/postgresql.conf

/listen  // 找 listen 的關鍵字

壓 enter // 確定要找這個

加上  listen_addresses = '*'

確認外部連線
netstat -ano | grep 5432

pg_isready 

pg_ctlcluster 12 main start
pg_ctlcluster 12 main restart

ufw allow 5432

su - postgres  // 更換成 postgres 的 user

psql  // 進到 postgresql 操作介面
 
\password postgres  // 更換 postgres 的密碼

建立 server group

建立 server

填入連線資訊

建立 database

建立 table

進入 {自己取的database 的名稱} > Schemas > Tables (感恩！！！)
## 建立 table 的 column

### 建立 id

### 建立 name

## 讀取 table

新增資料

存擋

## 其他
再次進到 table 做設定

[模擬 DB 資料遺失]
資料備份

切換回 postgres

`pg_dump -U postgres -Fc class > post.dump`


將資料移除

移除後進行儲存（save）

[將資料回復]
`pg_restore -U postgres -d class post.dump`

## 下半堂

### Alembic 相關指令
`alembic --help`
`pip install alembic`

alembic.ini

sqlalchemy.url = postgresql://{username}:{password}@{ip}:{port}/{db}

ip: 127.0.0.1 -> python 程式碼 以及 postgresql 都在虛擬機中
ip: {VM ip}   -> postgresql 在虛擬機中 + python 程式碼在 window


sqlalchemy.url = postgresql://postgres:postgres@192.168.70.3:5432/class

model.py

from sqlalchemy import Column, Integer, String, Boolean, DateTime
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()



class User(db.Model):
    id = Column(Integer, primary_key=True)
    name = Column(String(45))
    account = Column(String(45))
    password = Column(String(100))
    birth = Column(String(100))
    note = Column(String(45))

`alembic revision --autogenerate -m 'init'`
`alembic upgrade head`

## 下半堂程式碼

api.py

from flask import Flask
from flask_restful import Api
from apispec import APISpec
from flask_apispec.extension import FlaskApiSpec
from apispec.ext.marshmallow import MarshmallowPlugin
from flask_jwt_extended import JWTManager
from model import db

from resource.user import Users, SingleUser
from resource.login import Login

# Flask init
app = Flask(__name__)

# FlaskRestFul init
api = Api(app)

# DB setting
app.config["SQLALCHEMY_DATABASE_URI"] = (
    "postgresql://postgres:postgres@192.168.70.3:5432/class"
)
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
app.config["SQLALCHEMY_ENGINE_OPTIONS"] = {
    "pool_pre_ping": True, #檢查連線是否有效，避免使用失效的連線。
    "pool_recycle": 60, #設定連線的最大使用時間，防止連線長時間閒置而被資料庫切斷。
    "pool_timeout": 300, #設定等待可用連線的最大時間，防止長時間阻塞。
    "pool_size": 20, #設定連線池的最大連線數量，控制資料庫連線的併發數。
}


# Swagger

## JWT swagger setting
app.config["JWT_SECRET_KEY"] = "secret_key"
security_definitions = {
    "bearer": {
        "type": "apiKey",
        "in": "header",
        "name": "Authorization",
    }
}

app.config.update(
    {
        "APISPEC_SPEC": APISpec(
            title="Awesome Projectdfdfssdd",
            version="v1",
            # JWT token setting
            securityDefinitions=security_definitions,
            plugins=[MarshmallowPlugin()],
            openapi_version="2.0.0",
        ),
        "APISPEC_SWAGGER_URL": "/swagger/",  # URI to access API Doc JSON
        "APISPEC_SWAGGER_UI_URL": "/swagger-ui/",  # URI to access UI of API Doc
    }
)

# Swagger init
docs = FlaskApiSpec(app)

# Route
api.add_resource(Users, "/users")
docs.register(Users)
api.add_resource(SingleUser, "/user/<int:id>")
docs.register(SingleUser)
api.add_resource(Login, "/login")
docs.register(Login)


if __name__ == "__main__":
    # DB setting
    db.init_app(app)
    db.app = app
    # JWT init
    jwt = JWTManager().init_app(app)
    app.run(host="0.0.0.0", port="10009", debug=True, use_reloader=True)

user.py

import json
from flask_apispec import MethodResource, marshal_with, doc, use_kwargs
from flask_jwt_extended import jwt_required
from model import User, db


from . import user_route_model
import util


class Users(MethodResource):
    # GET_ALL
    @doc(description="Get Users info.", tags=["User"])
    @marshal_with(user_route_model.UserGetResponse, code=200)
    def get(self, name):
        return util.success()

    # Create User
    @doc(description="Get Users info.", tags=["User"])
    @use_kwargs(user_route_model.UserPostSchema, location="form")
    @marshal_with(user_route_model.UserPostResponse, code=201)
    def post(self, name, birth, note, account, password):
        row = User(
            name=name,
            birth=birth,
            note=note,
            account=account,
            password=password,
        )
        db.session.add(row)
        db.session.commit()
        return util.success(status_code=201)


class SingleUser(MethodResource):
    # Get single by id
    @doc(description="Get Single Users info.", tags=["User"])
    @marshal_with(user_route_model.UserSingleGetResponse, code=200)
    def get(self):
        return util.success()

    @doc(description="Update Single Users info.", tags=["User"])
    @use_kwargs(user_route_model.UserPutSchema, location="form")
    @marshal_with(user_route_model.UserPutResponse, code=200)
    def put(self, id, name, birth, note, account, password):
        return util.success()

    @doc(
        description="Delete Single Users info.",
        tags=["User"],
        security=[{"bearer": []}],
    )
    @marshal_with(user_route_model.UserSingleDeleteResponse, code=204)
    @jwt_required()
    def delete(self, id):
        return util.success(status_code=204)

user_route_model.py

from marshmallow import Schema, fields


# Request
class UserPutSchema(Schema):
    name = fields.Str(doc="name", example="string", required=True)
    birth = fields.Str(doc="birth", example="string", required=True)
    note = fields.Str(doc="note", example="string", required=True)
    account = fields.Str(doc="account", example="string", required=True)
    password = fields.Str(doc="password", example="string", required=True)


class UserPostSchema(Schema):
    name = fields.Str(doc="name", example="string", required=True)
    birth = fields.Str(doc="birth", example="string", required=True)
    note = fields.Str(doc="note", example="string", required=True)
    account = fields.Str(doc="account", example="string", required=True)
    password = fields.Str(doc="password", example="string", required=True)


# Response
class UserGetResponse(Schema):
    message = fields.Str(example="success")
    datatime = fields.Str(example="1970-01-01T00:00:00.000000")
    data = fields.List(fields.Dict())


class UserSingleGetResponse(Schema):
    message = fields.Str(example="success")
    datatime = fields.Str(example="1970-01-01T00:00:00.000000")
    data = fields.Dict()


class UserPostResponse(Schema):
    message = fields.Str(example="success")


class UserPutResponse(Schema):
    message = fields.Str(example="success")


class UserSingleDeleteResponse(Schema):
    message = fields.Str(example="success")