---
# System prepended metadata

title: '2024-12-10'
tags: [GCP]

---

# 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)
    );
    ```

* 準備程式碼

    ![image](https://hackmd.io/_uploads/r1U4L4aNyx.png)

    **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'
    ```
    ![image](https://hackmd.io/_uploads/HkRkMSp4yg.png)

    **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 .
    ```
    ![image](https://hackmd.io/_uploads/Bk8StVpNkl.png)
    ![image](https://hackmd.io/_uploads/HymFtEpEye.png)

* testing
    ![image](https://hackmd.io/_uploads/H1THzHpNkl.png)

* result
    ![image](https://hackmd.io/_uploads/SJyBMrpVJe.png)

## 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
    ```
    **部屬完**
    ![image](https://hackmd.io/_uploads/HJlBBIp4kg.png)
    **連過去看**
    ![image](https://hackmd.io/_uploads/ry58SUT4ke.png)

### 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
    ```
    
