owned this note
owned this note
Published
Linked with GitHub
# 2024-12-10
## SQL
**延續上周的SQL**
* 建立一個要使用的資料庫
```
gcloud sql databases create testdb --instance=mydb, -i mydb
```
* 建立一個要使用的資料表
```
gcloud sql connect mydb --user=root
```
```sql=
use testdb;
CREATE TABLE info (
id INT NOT NULL AUTO_INCREMENT,
firstname VARCHAR(20),
lastname VARCHAR(20),
age VARCHAR(3),
collegename VARCHAR(150),
PRIMARY KEY (id)
);
```
* 準備程式碼
**main.py**
```python=
import sqlalchemy
#connection name we noted earlier
connection_name = "lofty-entropy-436602-n8:mydb" # 到SQL查看 (下方有圖)
#database name
db_name = "testdb"
db_user = "root"
db_password = "admin1234"
driver_name = 'mysql+pymysql'
query_string = dict({"unix_socket": "/cloudsql/{}".format(connection_name)})
def writeToSql(request):
#You can change this to match your personal details
stmt = sqlalchemy.text("INSERT INTO info ( firstname, lastname, age, collegename) values ('Sagadevan', 'Kounder', '21', 'XYZ College')")
db = sqlalchemy.create_engine(
sqlalchemy.engine.url.URL(
drivername=driver_name,
username=db_user,
password=db_password,
database=db_name,
query=query_string,
),
pool_size=5,
max_overflow=2,
pool_timeout=30,
pool_recycle=1800
)
try:
with db.connect() as conn:
conn.execute(stmt)
print("Insert successful")
except Exception as e:
print ("Some exception occured" + e)
return 'Error: {}'.format(str(e))
return 'ok'
```
**requirements.txt**
```
SQLAlchemy==1.3.12
PyMySQL==0.9.3
```
* 透過Cloud Shell 部屬 Cloud Function
```
gcloud functions deploy writeToSql --region=asia-east1 --entry-point writeToSql --runtime python310 --trigger-http --allow-unauthenticated --no-gen2 --source .
```
* testing
* result
## GAE
### 簡單範例
* 新的資料夾、檔案
```bash=
$ mkdir forflask
$ cd forflask/
$ touch app.yaml main.py requirements.txt
$ ls
app.yaml main.py requirements.txt
```
* main.py
```python=
from flask import Flask
app = Flask(__name__)
@app.route("/")
def hello():
return "Hello, World! 2024/12/16"
if __name__ == "__main__":
app.run(debug=True)
```
* requirements.txt
```
flask
```
* app.yaml
```yaml=
runtime: python39
service: myflask # 從沒部署過的話這邊要設default
```
* 上傳
```
$ gcloud app deploy
```
**部屬完**
**連過去看**
### IRIS
* 新的資料夾、檔案
```
$ touch app.yaml client.py main.py requirements.txt train_model.py
$ ls
app.yaml client.py main.py requirements.txt train_model.py
```
* train_model.py
```python=
# -*- coding: utf-8 -*-
import pickle
from sklearn import datasets
from sklearn.model_selection import train_test_split
from sklearn import tree
# simple demo for traing and saving model
iris=datasets.load_iris()
x=iris.data
y=iris.target
#labels for iris dataset
labels ={
0: "setosa",
1: "versicolor",
2: "virginica"
}
x_train, x_test, y_train, y_test = train_test_split(x, y, test_size=.25)
classifier=tree.DecisionTreeClassifier()
classifier.fit(x_train,y_train)
predictions=classifier.predict(x_test)
#export the model
model_name = 'model.pkl'
print("finished training and dump the model as {0}".format(model_name))
pickle.dump(classifier, open(model_name,'wb'))
```
* main.py
```python=
import pickle
from flask import Flask, request, jsonify
app = Flask(__name__)
# Load the model
model = pickle.load(open('model.pkl', 'rb'))
labels = {
0: "versicolor",
1: "setosa",
2: "virginica"
}
@app.route("/", methods=["GET"])
def index():
"""Basic HTML response."""
body = (
"<html>"
"<body style='padding: 10px;'>"
"<h1>Welcome to my Flask API</h1>"
"</body>"
"</html>"
)
return body
@app.route('/api', methods=['POST'])
def predict():
# Get the data from the POST request.
data = request.get_json(force = True)
predict = model.predict(data['feature'])
return jsonify(predict[0].tolist())
if __name__ == '__main__':
app.run(debug = True, host = '0.0.0.0', port=8080)
```
* requirements.txt
```
scikit-learn
flask
```
* client.py
```python=
# -*- coding: utf-8 -*-
import requests
# 先在本地(沒部屬上去的情況)測試
url = 'http://127.0.0.1:8080/api'
feature = [[5.8, 4.0, 1.2, 0.2]]
labels ={
0: "setosa",
1: "versicolor",
2: "virginica"
}
r = requests.post(url,json={'feature': feature})
print(labels[r.json()])
```
* app.yaml
```yaml
runtime: python312
service: iris-predict
```
* 跑模型
```bash=
$ ls
app.yaml client.py main.py requirements.txt train_model.py
$ python train_model.py
finished training and dump the model as model.pkl
$ ls
app.yaml client.py main.py model.pkl requirements.txt train_model.py
```
* 上傳前測試
**main**
```bash
$ python main.py
* Serving Flask app 'main'
* Debug mode: on
WARNING: This is a development server. Do not use it in a production deployment. Use a production WSGI server instead.
* Running on all addresses (0.0.0.0)
* Running on http://127.0.0.1:8080
* Running on http://10.88.0.3:8080
Press CTRL+C to quit
* Restarting with stat
* Debugger is active!
* Debugger PIN: 108-328-539
* Detected change in '/home/f131406298/foriris/client.py', reloading
* Restarting with stat
* Debugger is active!
* Debugger PIN: 108-328-539
```
**client**
```bash
$ python client.py
setosa #成功
```
* 部署
```
gcloud app deploy
```
* 修改client.py
```
url = 'https://iris-predict-dot-lofty-entropy-436602-n8.ue.r.appspot.com/api'
```
* 再執行
```bash
$ python client.py
setosa
```
Google Drive
Gist
Clipboard
Sign in with Wallet
