# 1/19課程
[1/17 課程筆記連結](https://hackmd.io/ksnC7xUkQUqy2ge2Ce2e5Q)
## 大綱
- Python ORM 練習
- 寫出透過 ORM 跟 DB 溝通的API
## 需預先下載的軟體
- [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/1TA-3uMFT7ApbTH4R6mP47ksCIasEpwe_/view?usp=drive_link)
- Python3.10
## Q&A
Q1: 網路上找到這個方法有成功，但這個方式有什麼要注意的嗎?
A1: 非常好，找到一個厲害的參數可以直接做比對，實驗過後確實可以偵測出形態的差異，
但一樣要注意如果欄位裡面已經有值，在做 upgrade head 的時候會有失敗的風險，
e.g., birth(str): 2020/01/01 -> birth(datetime)
建議還是對做欄位的值做轉移的動作會必較安全～
**BTW: 找到這個方法的人來找我，我給一張星巴克禮卷！**
**因為沒人承認，所以我先給班代，如果之後有想承認的人幫我去找班代拿**
**如果幾天後都沒人承認的話，就班代自行吸收**
Q2:
## 補充
Mac UTM PGADMIN
PGADMIN: https://www.pgadmin.org/download/pgadmin-4-macos/
Ubuntu 24.04(list): https://old-releases.ubuntu.com/releases/24.04/
chormedriver for ARM:
https://stackoverflow.com/questions/38732822/compile-chromedriver-on-arm
- Error Message:
[解決方法](https://ithelp.ithome.com.tw/articles/10028377)
- 註: 使用系統管理員權限開啟 Powershell/ Visual Studio後才輸入指令
### SQL injection 的攻擊
不輸入正確的帳號密碼，還是能正常登入
透過 SQL 語法的漏洞，繞過檢查
warm.py
```
from selenium import webdriver
service = webdriver.ChromeService(executable_path="/home/john/nice/chromedriver_folder/chromedriver")
driver = webdriver.Chrome(service = service)
# 配置 Chromium 的執行路徑
#options = webdriver.ChromeOptions()
#options.binary_location = "/home/byron/chromedriver" # Chromium 的安裝路徑
# 初始化 WebDriver，並指向 ChromeDriverManager
# service = Service(ChromeDriverManager().install())
#driver = webdriver.Chrome(service=service, options=options)
# driver = webdriver.Chrome("/home/john/nice/chormedriver")
# 打開一個網站以測試
driver.get("https://www.google.com")
print(driver.title)
# 關閉瀏覽器
driver.quit()
```
output
```
(myenv) byron@server:~$ python warm_test.py
Traceback (most recent call last):
File "/home/byron/warm_test.py", line 12, in <module>
driver = webdriver.Chrome("/home/byron/chromedriver")
File "/home/byron/myenv/lib/python3.10/site-packages/selenium/webdriver/chrome/webdriver.py", line 45, in __init__
super().__init__(
File "/home/byron/myenv/lib/python3.10/site-packages/selenium/webdriver/chromium/webdriver.py", line 50, in __init__
if finder.get_browser_path():
File "/home/byron/myenv/lib/python3.10/site-packages/selenium/webdriver/common/driver_finder.py", line 47, in get_browser_path
return self._binary_paths()["browser_path"]
File "/home/byron/myenv/lib/python3.10/site-packages/selenium/webdriver/common/driver_finder.py", line 56, in _binary_paths
browser = self._options.capabilities["browserName"]
AttributeError: 'str' object has no attribute 'capabilities'
```
## 上半堂課程程式碼
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"])
@use_kwargs(user_route_model.UserGetSchema, location="query") # new
@marshal_with(user_route_model.UserGetResponse, code=200)
def get(self, **kwargs):
name = kwargs.get("name")
if name:
members = User.query.filter(User.name.ilike(f"%{name}%")).all()
else:
members = User.query.all()
user_info = [
{
"id": member.id,
"name": member.name,
"account": member.account,
"birth": member.birth,
"note": member.note,
}
for member in members
]
return util.success(user_info)
# # 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, id):
member = User.query.filter_by(id=id).first()
if member is None:
return util.failure({"message": "User not found"})
member_info = {
"id": member.id,
"name": member.name,
"account": member.account,
"birth": member.birth,
"note": member.note,
}
return util.success(member_info)
@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, **kwargs):
member_info = User.query.filter_by(id=id).first()
if member_info is None:
return util.failure({"message": "User not found"})
kwargs = {k: v for k, v in kwargs.items() if v is not None or v != ""}
User.query.filter(User.id == id).update(kwargs)
db.session.commit()
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):
User.query.filter_by(id=id).delete()
db.session.commit()
return util.success(status_code=204)
```
user_route_model.py
```
from marshmallow import Schema, fields
# Request
class UserGetSchema(Schema):
name = fields.Str(example="string")
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")
```
args x kwargs
`*args`:
- 取得沒帶入 key 值得參數
- type是 tuple
`**kwargs`:
- 取得有帶入 key 值得參數
- type是 dict
```
def nice(*args, **kwargs):
print(f"args: {args}")
print(f"kwargs: {kwargs}")
# print("-----------------")
# print("Only args")
# nice(1, 2, 3)
# print("-----------------")
# print("-----------------")
# print("Only kwargs")
# nice(a=1, b=2, c=3)
# print("-----------------")
print("-----------------")
print("kwargs and args")
nice(1, 2, c=3)
print("-----------------")
```
dict .items()
```
my_dict = {1: "a", 2: "b"}
for k, v in my_dict.items():
print(f"key: {k}, value: {v}")
```
class property 的補充
```
class MyClass:
birth_year = 1992
@property
def age(self):
return 2025 - self.birth_year
my_cls = MyClass()
print(my_cls.birth_year)
print(my_cls.age)
```
## 下半堂
### 補充
點開 vscode 設定
搜尋
`Preferences: Open User Settings`
找到 defaultformatter 並改成 black
找到 format on save 並把他勾起來
.gitignore template
https://github.com/github/gitignore
## 程式碼
login.py
```
from flask_jwt_extended import create_access_token
from flask_apispec import MethodResource, marshal_with, doc, use_kwargs
import util
from resource import login_router_model
from datetime import timedelta
from model import User
class Login(MethodResource):
@doc(description="User Login", tags=["Login"])
@use_kwargs(login_router_model.LoginSchema, location="form")
@marshal_with(login_router_model.LoginResponse, code=200)
def post(self, **kwargs):
account, password = kwargs.get("account"), kwargs.get("password")
member = User.query.filter(
User.account == account, User.password == password
).first()
if member is not None:
token = create_access_token(
identity={"account": account}, expires_delta=timedelta(days=1)
)
return util.success(token)
else:
return util.failure("Account or password is wrong")
```
model.py
```
from sqlalchemy import Column, Integer, String, Boolean, DateTime, ForeignKey
from flask_sqlalchemy import SQLAlchemy
class Organization(db.Model):
id = Column(Integer, primary_key=True)
name = Column(String(50))
owner_id = Column(Integer, ForeignKey(User.id, ondelete="SET NULL"), nullable=True)
@property
def owner(self):
if self.owner_id is not None:
member_info = User.query.get(self.owner_id)
return {
"name": member_info.name,
"account": member_info.account,
}
return {}
```
organization.py
```
from flask_apispec import MethodResource, marshal_with, doc, use_kwargs
import util
from . import organization_route_model
from model import Organization, db, User
####### API Action #########
class Organizations(MethodResource):
# GET_ALL
@doc(description="Get Organization info.", tags=["Organization"])
@use_kwargs(organization_route_model.OrganGetSchema, location="query")
@marshal_with(organization_route_model.OrganGetResponse, code=200)
def get(self, **kwargs):
filter_name = kwargs.get("name")
if filter_name:
organizations = Organization.query.filter(
Organization.name.ilike(f"%{filter_name}%")
).all()
else:
organizations = Organization.query.all()
organization_info = [
{
"id": organization.id,
"name": organization.name,
"owner_id": organization.owner_id,
}
for organization in organizations
]
return util.success(organization_info)
# POST
@doc(description="Create Organization.", tags=["Organization"])
@use_kwargs(organization_route_model.OrganPostSchema, location="form")
@marshal_with(organization_route_model.OrganCommonResponse, code=201)
def post(self, **kwargs):
organization = Organization(**kwargs)
db.session.add(organization)
db.session.commit()
return util.success()
class SingleOrganization(MethodResource):
@doc(description="Get Single organ info.", tags=["Organization"])
@marshal_with(organization_route_model.SingleOrganGetResponse, code=200)
def get(self, id):
organ = Organization.query.filter_by(id=id).first()
if organ is None:
return util.failure({"message": "Organization not found"})
organ_info = {
"id": organ.id,
"name": organ.name,
"owner_id": organ.owner_id,
"owner": organ.owner,
}
return util.success(organ_info)
@doc(description="Update User info.", tags=["Organization"])
@use_kwargs(organization_route_model.OrganPatchSchema, location="form")
@marshal_with(organization_route_model.OrganCommonResponse, code=201)
def patch(self, id, **kwargs):
organ_info = Organization.query.filter_by(id=id).first()
if organ_info is None:
return util.failure({"message": "Organization not found"})
kwargs = {k: v for k, v in kwargs.items() if v is not None or v != ""}
owner_id = kwargs.get("owner_id")
if kwargs.get("owner_id") is not None:
member_info = User.query.filter_by(id=owner_id).first()
if member_info is None:
return util.failure({"message": "Member not found"})
Organization.query.filter(Organization.id == id).update(kwargs)
db.session.commit()
return util.success()
@doc(description="Delete Organ info.", tags=["Organization"])
@marshal_with(None, code=204)
def delete(self, id):
Organization.query.filter_by(id=id).delete()
db.session.commit()
return util.success()
```
organization_route_model.py
```
from marshmallow import Schema, fields
# Schema
class OrganGetSchema(Schema):
name = fields.Str(example="string")
class OrganPostSchema(Schema):
name = fields.Str(doc="name", example="string", required=True)
class OrganPatchSchema(Schema):
name = fields.Str(doc="name", example="string")
owner_id = fields.Int(doc="owner_id", example="string")
# Response
class OrganGetResponse(Schema):
message = fields.Str(example="success")
datatime = fields.Str(example="1970-01-01T00:00:00.000000")
data = fields.List(fields.Dict())
class SingleOrganGetResponse(Schema):
message = fields.Str(example="success")
datatime = fields.Str(example="1970-01-01T00:00:00.000000")
data = fields.Dict()
class OrganCommonResponse(Schema):
message = fields.Str(example="success")
```
api.py
```
api.add_resource(Organizations, "/organizations")
docs.register(Organizations)
api.add_resource(SingleOrganization, "/organization/<int:id>")
docs.register(SingleOrganization)
```
Sign in with Wallet
Connect another wallet