---
# System prepended metadata

title: PostgreSQL入門

---

### PostgreSQL入門
[上課講義](https://dv106.ntc.im/index.php/apps/files/files?dir=/DV106&openfile=110015)

[參考資料](https://hackmd.io/KKCrq3MUSd66s9Pp2SCPKw)

1.建立ubuntu_sever環境
2.用最高權限操作
```
sudo su
```
3.更新Ubuntu系統上的套件及安裝相依性套件
```
apt-get update && apt-get install -y lsb-release libpqxx-dev protobuf-compiler gnupg2 wget vim ufw
```
4.建立postgreSQL的檔案設定資料夾
```
sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
```
5.Import 資料夾的signing key
```
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
```
6.再次更新軟件
```
apt-get update
```
7.下載postgresql 12
```
apt-get -y install postgresql-12
```
8.更新設定檔 (pg_hba.conf) 允許遠端連線
```
vim /etc/postgresql/12/main/pg_hba.conf
```
搜尋replication
```
/replication
```
開放任何ip
![image](https://hackmd.io/_uploads/SJUo3XbRT.png)
修改監聽port口
```
vim /etc/postgresql/12/main/postgresql.conf
```
搜尋listen
```
/listen
```
開放任何port口
![image](https://hackmd.io/_uploads/Hyks67bAp.png)

:::success
vim 快捷鍵:

換頁
ctrl + b : back 往後一頁

ctrl + f : front 往後一頁

頭尾
shift + g | G : 鼠標到文章最後

gg : 鼠標到文章最前面

$ : 該行的行尾

^ : 該行的行首

刪除
x : 刪除鼠標後的一個字

6x : 刪除鼠標後的六個字

X : 刪除鼠標前的一個字

6X : 刪除鼠標前的六個字

dd : 刪除鼠標所在的那行

6dd : 刪除鼠標所在的後六行(包含該行)

複製 & 貼上
6yw : 複製鼠標所在的6個字

yy : 複製鼠標所在那行

6yy : 複製鼠標所在後六行(包含該行)

p : 貼上

其他
u : 回復上一次操作（可連續）

ctrl + g : 列出鼠標所在行號

6 + shift + g : 跳至第6行

/關鍵字 : 尋找關鍵字, 按下 n 到下一個

:%s/old_word/new_word/g : 取代所有的old_word to new_word
:::

10.啟動postgreSQL & 重新啟動postgreSQL
```
pg_ctlcluster 12 main start
pg_ctlcluster 12 main restart
```
11.確認postgreSQL狀態
```
pg_isready
```
![image](https://hackmd.io/_uploads/BJW80Q-0T.png)

12.開通5432這個通道
```
sudo ufw allow 5432
```
![image](https://hackmd.io/_uploads/S1atAmbC6.png)

#### 進入PostgreSQL

1.使用postgres user
`su - postgres`
2.啟動postgreSQL 伺服器
`psql`
3.更新postgressql的密碼
`\password postgres`

![image](https://hackmd.io/_uploads/HJMSES-0a.png)

### PGadmin

1.先去下載PGadmin
2.建立與PostgreSQL連線
![image](https://hackmd.io/_uploads/HyU_S4WRa.png)
發現無法連線
![image](https://hackmd.io/_uploads/Bk1l8N-C6.png)

![image](https://hackmd.io/_uploads/SkmBIE-Aa.png)
到有sodo權限的帳號restart(rich)
```
pg_ctlcluster 12 main restart
```
再次檢查ip有沒有全開，就可以連線了!
![image](https://hackmd.io/_uploads/BynRq4-06.png)
建立資料庫和資料夾
![image](https://hackmd.io/_uploads/BkV3ySWRT.png)
設定id值(primary key)
![image](https://hackmd.io/_uploads/BkETkBZ0T.png)
![image](https://hackmd.io/_uploads/Hk4KeSbAa.png)

#### 資料備援
要在使用者的介面輸入
![image](https://hackmd.io/_uploads/r1iIfBW06.png)
將資料備援成一個檔案
```
pg_dump -U postgres -Fc database1 > test.file
```
將資料restore回資料庫
```
pg_restore -U postgres -d database1 test.file
```
刪除的資料被救回來了~
![image](https://hackmd.io/_uploads/Hkv9GSZ06.png)


### Alembic

安裝環境
1.Vscode
2.PGAdmin
3.Python3.10
4.在windos裝:
* psycopg2-binary
* alembic==1.8.0
* Flask-SQLAlchemy==2.5.1
* SQLAlchemy==1.4.37
* SQLAlchemy-Utils==0.38.2
* pip3 install -r requirements.txt
* pip3 install Werkzeug==2.2.2
5.Ubuntu22.04

初始化alembic
```
alembic init alembic
```
![image](https://hackmd.io/_uploads/ByKu2wbCp.png)

[原始檔下載](https://drive.google.com/file/d/1GGcKeESmxu8UE-mj7orj4EBW19Ufcflb/view?pli=1)

加上一些設定檔
alembic.ini
```
sqlalchemy.url =  postgresql://postgres:postgres@10.167.218.124:5432/database2
```

app.py
```
app.config["SQLALCHEMY_DATABASE_URI"] = "postgresql://postgres:postgres@10.167.218.124:5432/database2"
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
app.config["SQLALCHEMY_ENGINE_OPTIONS"] = {
    "pool_pre_ping": True,
    "pool_recycle": 60,
    "pool_timeout": 300,
    "pool_size": 20,
}
```

在app.py旁新增<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))
    gender = Column(Boolean)
    account = Column(String(45))
    password = Column(String(100))
    birth = Column(DateTime)
    note = Column(String(45))
    create_at = Column(DateTime)
```

在<alembic/env.py>引進model.py並設定target_metadata(資料庫來源)
```
from model import db   
target_metadata = db.metadata
```
刪掉
`target_metadata = None`


### 執行版控
建立執行版本三
```
alembic revision --autogenerate -m 'my_model_v3'
```
升級至head(最新版本)[有點類似push到資料庫]
```
alembic upgrade head
```
版號歷史確認
```
alembic historys
```
![image](https://hackmd.io/_uploads/Hk2_9tZ0p.png)

![image](https://hackmd.io/_uploads/rkeTkcW0T.png)
資料庫成功建立新欄位asd
![image](https://hackmd.io/_uploads/BJnC1cbRT.png)
降級2版
```
alembic downgrade -2
```
![image](https://hackmd.io/_uploads/rk5-mcWC6.png)


### ORM
[參考講義](https://hackmd.io/5zOmxKw7SzWuhWtGpO2TwQ)

app.py
```
from model import db #新增的db setting
```

user.py
```
from model import User as ModelUser,db as model_db #新增的db setting

修改post

# POST
    @doc(description='Create User.', tags=['User'])
    @use_kwargs(user_router_model.UserPostSchema, location="form")
    @marshal_with(user_router_model.UserCommonResponse, code=201)
    # @jwt_required()
    def post(self, **kwargs):
        user = ModelUser(**kwargs)
        model_db.session.add(user)
        model_db.session.commit()

        return util.success() 
```

user.py
```
修改class User

class User(db.Model):
    id = Column(Integer, primary_key=True)
    name = Column(String(45))
    gender = Column(String(45))
    account = Column(String(45))
    password = Column(String(100))
    birth = Column(String(45))
    note = Column(String(45))
    create_at = Column(DateTime)
    update_at = Column(DateTime)
```
資料沒有更新的話，先推一版註解掉的，再推想修改的
![image](https://hackmd.io/_uploads/rkA-x1VRT.png)

### 執行swager-ui
http://127.0.0.1:10009/swagger-ui/

user.py
先註記掉jwt token的問題
```
 # @jwt_required()
```

```
class Users(MethodResource):
    # GET_ALL
    @doc(description='Get Users info.', tags=['User'], security=security_params)
    @use_kwargs(user_router_model.UserGetSchema, location="query")
    @marshal_with(user_router_model.UserGetResponse, code=200)
    @jwt_required()
    def get(self, **kwargs):
        name = kwargs.get("name")
        
        if name is None:
            rows = ModelUser.query.all()
        else:
            rows = ModelUser.query.filter(ModelUser.name.ilike(f"%{name}%")).all()

        user_info = [{
            "id": row.id,
            "name": row.name,
            "account": row.account,
            "gender": row.gender,
            "create_at": row.create_at
        } for row in rows] 

        return util.success(user_info)

    # POST
    @doc(description='Create User.', tags=['User'])
    @use_kwargs(user_router_model.UserPostSchema, location="form")
    @marshal_with(user_router_model.UserCommonResponse, code=201)
    # @jwt_required()
    def post(self, **kwargs):
        user = ModelUser(**kwargs)
        model_db.session.add(user)
        model_db.session.commit()

        return util.success() 
```        
創立清單
![image](https://hackmd.io/_uploads/B1yaNW4Ra.png)
成功建立
![image](https://hackmd.io/_uploads/BJct8bN0T.png)

模糊搜尋
![image](https://hackmd.io/_uploads/SkrkHbE0a.png)


user.py
```
class User(MethodResource):
    @doc(description='Get Single user info.', tags=['User'], security=security_params)
    @marshal_with(user_router_model.UserGetResponses, code=200)
    # @jwt_required()
    def get(self, id):
        row = ModelUser.query.filter_by(id=id).first()
        if row is None:
            return util.success({})
        ret = [{
            "id": row.id,
            "name": row.name,
            "account": row.account,
            "gender": row.gender,
            "create_at": row.create_at
        }]
        return util.success(ret)

    @doc(description='Update User info.', tags=['User'])
    @use_kwargs(user_router_model.UserPatchSchema, location="form")
    @marshal_with(user_router_model.UserCommonResponse, code=201)
    def patch(self, id, **kwargs):
        db, cursor = db_init()
        user = {
            'name': kwargs.get('name'),
            'account': kwargs.get('account'),
            'password': kwargs.get('password'),
            'gender': kwargs.get('gender'),
            'birth': kwargs.get('birth') or '1900-01-01',
            'note': kwargs.get('note')
        }

        query = []
        for key, value in user.items():
            if value is not None:
                query.append(f"{key} = '{value}'")
        query = ",".join(query)

        sql = """
            UPDATE `test`.`member`
            SET {}
            WHERE id = {};
        """.format(query, id)

        result = cursor.execute(sql)
        db.commit()
        db.close()
        if result == 0:
            return util.failure()

        return util.success()
```
user_router_model.py
```
# Response
class UserGetResponses(Schema):
    message = fields.Str(example="success")
    datatime = fields.Str(example="1970-01-01T00:00:00.000000")
    data = fields.List(fields.Dict())

class UserGetResponse(Schema):
    message = fields.Str(example="success")
    datatime = fields.Str(example="1970-01-01T00:00:00.000000")
    data = fields.Dict()
``` 
找到特定id
![image](https://hackmd.io/_uploads/Bykya-VAa.png)

user.py
```
 @doc(description='Update User info.', tags=['User'])
    @use_kwargs(user_router_model.UserPatchSchema, location="form")
    @marshal_with(user_router_model.UserCommonResponse, code=201)
    def patch(self, id, **kwargs):
        user_obj = ModelUser.query.filter_by(id=id)
        print(user_obj)
        if user_obj.first() is None:
            return util.success()

        values = {k: v for k, v in kwargs.items() if v is not None}
        user_obj.update(values)
        model_db.session.commit()
        return util.success()
```    
![image](https://hackmd.io/_uploads/ByULtME0a.png)
更新id2的name為dog
![image](https://hackmd.io/_uploads/ry31qGN0T.png)

```
    @doc(description='Delete User info.', tags=['User'])
    @marshal_with(None, code=204)
    def delete(self, id):
        user_obj = ModelUser.query.filter_by(id=id)
        print(user_obj)
        if user_obj.first() is None:
            return util.success()

        user_obj.delete()
        model_db.session.commit()
```
id:2 的dog被刪除了!
![image](https://hackmd.io/_uploads/HkI0szNR6.png)

login
```
class Login(MethodResource):
    @doc(description='User Login', tags=['Login'])
    @use_kwargs(user_router_model.LoginSchema, location="form")
    # @marshal_with(user_router_model.UserGetResponse, code=200)
    def post(self, **kwargs):
        row = ModelUser.query.filter_by(
            account=kwargs["account"], password=kwargs["password"]
        ).first()

        if row is None:
            return util.failure("Account or password is wrong")

        token = get_access_token({"name": row.name, "account": row.account})
        return util.success({"token": token})
```        
![image](https://hackmd.io/_uploads/rJlT3wXVA6.png)
![image](https://hackmd.io/_uploads/HJz0PmVRp.png)
![image](https://hackmd.io/_uploads/H1MAKXN0p.png)
沒有令牌不予資訊
![image](https://hackmd.io/_uploads/rktnIN4Cp.png)
有令牌給予資訊
![image](https://hackmd.io/_uploads/SJYn9V4Ap.png)
:::danger
!要注意引入的class所定義type
:::
![image](https://hackmd.io/_uploads/BJ1zsNN06.png)


