### 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
修改監聽port口
```
vim /etc/postgresql/12/main/postgresql.conf
```
搜尋listen
```
/listen
```
開放任何port口
:::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
```
12.開通5432這個通道
```
sudo ufw allow 5432
```
#### 進入PostgreSQL
1.使用postgres user
`su - postgres`
2.啟動postgreSQL 伺服器
`psql`
3.更新postgressql的密碼
`\password postgres`
### PGadmin
1.先去下載PGadmin
2.建立與PostgreSQL連線
發現無法連線
到有sodo權限的帳號restart(rich)
```
pg_ctlcluster 12 main restart
```
再次檢查ip有沒有全開，就可以連線了!
建立資料庫和資料夾
設定id值(primary key)
#### 資料備援
要在使用者的介面輸入
將資料備援成一個檔案
```
pg_dump -U postgres -Fc database1 > test.file
```
將資料restore回資料庫
```
pg_restore -U postgres -d database1 test.file
```
刪除的資料被救回來了~
### 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
```
[原始檔下載](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
```
資料庫成功建立新欄位asd
降級2版
```
alembic downgrade -2
```
### 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)
```
資料沒有更新的話，先推一版註解掉的，再推想修改的
### 執行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()
```
創立清單
成功建立
模糊搜尋
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
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()
```
更新id2的name為dog
```
@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被刪除了!
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})
```
沒有令牌不予資訊
有令牌給予資訊
:::danger
!要注意引入的class所定義type
:::
Sign in with Wallet
Connect another wallet