flaskでajax CRUD

## dataが5件数越えたら、5件ずつ表示しページを折り返す

```php:
total_pages = math.ceil(len(users)/5)
    users_data = users[(int(page_num)-1)*int(5): (int(page_num)-1)*int(5)+ int(5)]
    users_data = user_schema.dump(users_data, many=True)
```

## pagenation用　ルーティング

```php:
@app.route("/loaddata", methods = ['POST'])
def lodadata():
    data = request.get_json()
    page_num = data['page_num']
    users = User.query.filter_by().all()
    total_pages = math.ceil(len(users)/5)
    users_data = users[(int(page_num)-1)*int(5): (int(page_num)-1)*int(5)+ int(5)]
    users_data = user_schema.dump(users_data, many=True)
    data = {"users": users_data, "total_pages": total_pages, "page_num":page_num}
    return custom_response(data, 200)

def custom_response(res, status_code):
    return Response(mimetype="application/json",response=json.dumps(res),status=status_code)
```

## 1件データ取得、データなかったら、エラー表示(エラーハンドリングをつけ、例外を自動で防ぐ)

```php:
first_or_404()
```

## get_json

```php:
htmlで受け取ったjsonデータを、flaskデータに加工
```

### return Response(mimetype="application/json",response=json.dumps(res),status=status_code)

```php:
flaskデータをjsonデータにする
```

## flask 更新

```php:
   データを送り、送った最初のデータを取得し、
   最初取得したデータを取得データに変える
   
      sno = data['id']
      name = data['name']
      city = data['city']
      user = User.query.filter_by(sno=sno).first()
      user.name = name
      user.city = city
      db.session.commit()
```

## union(dataCity).all()

```php:
  調べた時、カラム2つを縦に一覧で表示したい時、使う
     dataName =  User.query.filter(User.name.like(search))
    dataCity =  User.query.filter(User.city.like(search))
    data = dataName.union(dataCity).all()
```

## schema.dump

```php:
  dataが見つかったら、Flaskをajaxに渡すため
  JSONに加工、加工してajaxに渡す
  dataはスキーマに入ってるから、user_schema使う
  
  r_data = user_schema.dump(data,many=True)
```

## user_schema.dump(users_data, many=True)

```php:
  many=True : dataのカラムが何個もある時、必ず使う
```

## 修正箇所

```php:
current_ssession  = db.session.object_session(user)
    current_ssession.delete(user)

    db.session.delete(user)
    db.session.commit()
```

### app.py

```php:
rom flask import Flask, render_template, request, json, Response
from flask_sqlalchemy import SQLAlchemy
from marshmallow import fields, Schema
import math
from flask_migrate import Migrate

app = Flask(__name__)
app.secret_key = 'super-secret-key'

app.config['SQLALCHEMY_DATABASE_URI'] = "sqlite:///fajax.db"

app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True

db = SQLAlchemy(app)

migrate = Migrate(app, db)

class User(db.Model):
    '''set structure of database'''
    sno = db.Column(db.Integer, primary_key=True,)
    name = db.Column(db.String(80), nullable=False)
    city = db.Column(db.String(80), nullable=False)

class UserSchema(Schema):
  sno = fields.Int(dump_only=True)
  name = fields.Str(required=True)
  city = fields.Str(required=True)

user_schema = UserSchema()

@app.route("/")
def home():
    return render_template('index.html')

@app.route("/loaddata", methods = ['POST'])
def lodadata():
    data = request.get_json()
    page_num = data['page_num']
    users = User.query.filter_by().all()
    total_pages = math.ceil(len(users)/5)
    users_data = users[(int(page_num)-1)*int(5): (int(page_num)-1)*int(5)+ int(5)]
    users_data = user_schema.dump(users_data, many=True)
    data = {"users": users_data, "total_pages": total_pages, "page_num":page_num}
    return custom_response(data, 200)

def custom_response(res, status_code):
    return Response(mimetype="application/json",response=json.dumps(res),status=status_code)

@app.route("/insertdata", methods = ['POST'])
def insertdata():
    data = request.get_json()
    name = data['name']
    city = data['city']
    user = User(name=name, city=city)
    db.session.add(user)
    db.session.commit()
    return  "1"

@app.route("/updatedata", methods = ['POST'])
def updatedata():
    data = request.get_json()
    sno = data['id']
    name = data['name']
    city = data['city']
    user = User.query.filter_by(sno=sno).first()
    user.name = name
    user.city = city
    db.session.commit()
    return  "1"

@app.route("/deletedata", methods = ['POST'])
def deletedata():
    data = request.get_json()
    sno = data['id']
    user = User.query.filter_by(sno=sno).first_or_404()
    current_ssession  = db.session.object_session(user)
    current_ssession.delete(user)
    current_ssession.commit()
    return  "1"

@app.route("/searchdata")
def searchdata():
    search_query = request.args.get('q')
    search = "%{0}%".format(search_query)
    dataName =  User.query.filter(User.name.like(search))
    dataCity =  User.query.filter(User.city.like(search))
    data = dataName.union(dataCity).all()
    if data == []:
        return "0"
    else:
        r_data = user_schema.dump(data,many=True)
        return custom_response(r_data, 200) 

if __name__ == "__main__":
    app.run(debug=True)
```

### index.html

```php:
<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <meta http-equiv="X-UA-Compatible" content="ie=edge">
  <title>Flask & Ajax CRUD</title>
  <link rel="stylesheet" href="../static/css/style.css">
</head>
<body>
  <table id="main" border="0" cellspacing="0">
    <tr>
      <td id="header">
        <h1>Flask REST API CRUD</h1>

        <div id="search-bar">
          <label>Search :</label>
          <!-- <input type="text" id="search" onkeyup="load_search()" autocomplete="off"> -->
          <input type="text" id="search" autocomplete="off">
        </div>
      </td>
    </tr>
    <tr>
      <td id="table-form">
        <form id="addForm">
          Name : <input type="text" name="sname" id="sname">
          City : <input type="text" name="scity" id="scity">
          <input type="submit" id="save-button" value="Save">
        </form>
      </td>
    </tr>
    <tr>
      <td id="table-data">
        <table width="100%" cellpadding="10px" >
          <tr>
            <th width="40px">Id</th>
            <th>Name</th>
            <th width="150px">City</th>
            <th width="60px">Edit</th>
            <th width="70px">Delete</th>
          </tr>
          <tbody id="load-table">

          </tbody>
        </table>
        
        <div id="pagination">
          <button class="paginate-btn" id="previous">Previous</button>
        </div>
      </td>
    </tr>
  </table>

  <div id="error-message" class="messages"></div>
  <div id="success-message" class="messages"></div>

  <!-- Popup Modal Box for Update the Records -->
  <div id="modal">
    <div id="modal-form">
      <h2>Edit Form</h2>
      <form action="" id="edit-form">
      <table cellpadding="10px" width="100%">
        <tr>
          <td width="90px">First Name</td>
          <td><input type="text" name="sname" id="edit-name">
              <input type="text" name="sid" id="edit-id" hidden="">
          </td>
        </tr>
        <tr>
          <td>City</td>
          <td><input type="text" name="scity" id="edit-city"></td>
        </tr>
        <tr>
          <td></td>
          <td><input type="button" id="edit-submit" value="Update"></td>
        </tr>
      </table>
      </form>
      <div id="close-btn">X</div>
    </div>
  </div>

<script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
<script type="text/javascript">
  //Fetch All Records
  $(document).ready(function(){

    // load data---------------------------------------------
    function loaddata(page){
      if (page == undefined){
        page = 1
      }
      $("#load-table").html('');
      $.ajax({
        url : "/loaddata",
        // type : "GEt",
        type : "POST",
        data : JSON.stringify({page_num :page}),
        dataType : "json",
        contentType : "application/json; charset=utf-8",
        success : function(data){
          $.each(data.users, function(key, value){
            $("#load-table").append('<tr>'+
                '<td class="center">'+ value.sno +'</td>'+
                '<td>'+ value.name +'</td>'+
                '<td>'+ value.city +'</td>'+
                '<td class="center"><button class="edit-btn" id="edit" data-id="'+ value.sno +'" data-name="'+ value.name +'" data-city="'+ value.city +'">Edit</button></td>'+
                '<td class="center"><button class="delete-btn" id="delete" data-id="'+ value.sno +'">Delete</button></td>'+
            '</tr>');
          });
          $("#pagination").html('');
          if (data.page_num == 1){
            $("#pagination").append('<button class="paginate-btn disable-link" id="previous">Previous</button>');
          }else{
            $("#pagination").append('<button class="paginate-btn" id="previous">Previous</button>');
          }
          
          for (let i = 1; i <= data.total_pages; i++) {
            if (i == data.page_num){
              $("#pagination").append("<a class='active' id="+i+" href=''>"+i+"</a>");
            }else{
              $("#pagination").append("<a class='' id="+i+" href=''>"+i+"</a>");
            }
          }
          if (data.page_num == data.total_pages){
            $("#pagination").append('<button class="paginate-btn disable-link" id="next">Next</button>');
          }else{
            $("#pagination").append('<button class="paginate-btn" id="next">Next</button>');
          }
        }
      });
    }
    loaddata();

    // paginate data------------------------------------------
    $(document).on("click","#pagination a",function(e) {
      e.preventDefault();
      var page_id = $(this).attr("id");

      loaddata(page_id);
    })

    $(document).on("click", "#previous", function(e){
      e.preventDefault();
      var page_id = Number($(".active").attr("id")) - 1 ;

      loaddata(page_id);
    })

    $(document).on("click", "#next", function(e){
      e.preventDefault();
      var page_id = Number($(".active").attr("id")) + 1;

      loaddata(page_id);
    })

    // show message-------------------------------------------
    function message(msg, status) {
      if (status == true){
        $('#error-message').hide();
        $('#success-message').slideDown();
        $('#success-message').html(msg);
        setTimeout(function(){
          $('#success-message').slideUp();
        }, 4000);
      }else{
        $('#error-message').slideDown();
        $('#success-message').hide();
        $('#error-message').html(msg);
        setTimeout(function(){
          $('#error-message').slideUp();
        }, 4000);
      }
    }
  
    $('#message').hide();

    // insert data---------------------------------------------
    $('#save-button').on("click",function(e){
      e.preventDefault();
      var name = $('#sname').val();
      var city = $('#scity').val();

      if ( name == "" || city == "") {
          message('All fields are required !', false);
      }else{
        $.ajax({
          url : "/insertdata",
          type : "POST",
          data : JSON.stringify({name : name, city : city}),
          dataType : "json",
          contentType : "application/json; charset=utf-8",
          success : function(data){
              if (data == 1) {
                loaddata();
                $('#addForm').trigger("reset");
                message('保存されたよ !', true);
              }else{
                alert("Can't Save Record.");
              }
          }
        });
      }
    });

    // delete data---------------------------------------------
    $(document).on("click","#delete", function(e){

      if (confirm("Are you sure you want to delete this data!")) {
         
        var name_id = $(this).data("id");

        $.ajax({
          url : "/deletedata",
          type : "POST",
          data : JSON.stringify({id : name_id}),
          dataType : "json",
          contentType : "application/json; charset=utf-8",
          success : function(data){
              if (data == 1){
                loaddata();
                message('Data has been Deleted !', true);
              }else{
                alert("Can't Delete Record.");
              }
          }
        });
      }
    });

    // update data---------------------------------------------
    $(document).on("click","#edit", function(e){
      $('#modal').show();
      var id = $(this).data("id");
      var name = $(this).data("name");
      var city = $(this).data("city");

      $('#edit-id').val(id);
      $('#edit-name').val(name);
      $('#edit-city').val(city);
    });

    $(document).on("click", "#close-btn", function(e){
      $('#modal').hide();
    });

    //Update Record------------------------------------------
    $(document).on("click", "#edit-submit", function(e){
        e.preventDefault();

        var name_id =  $('#edit-id').val();
        var name = $('#edit-name').val();
        var city = $('#edit-city').val();

        if (name == "" || city == "") {
          message('All fields are required !', false);
        }else {
          $.ajax({
            url : "/updatedata",
            type : "POST",
            data : JSON.stringify({id : name_id, name : name, city : city}),
            dataType : "json",
            contentType : "application/json; charset=utf-8",
            success : function(data){
                if (data == 1) {
                  loaddata();
                  message('Data has been Saved  !', true);
                  $('#modal').hide();
                }else{
                  alert("Can't Save Record.");
                }
            }
          });
        }
    });

    //Live Search Record------------------------------
    $(document).on("keyup", "#search", function(e){
      var search = $('#search').val();
      if (search == ''){
        loaddata();
        return false;
      }else{
      $("#load-table").html('');
      $.ajax({
        url : "/searchdata?q=" + search,
        type : "GET",
        success : function(data){
          if (data == 0){
            $("#load-table").html('<tr><td colspan="6" align="center"><h3>No record found</h3></tr></td>');
          }else{
            $.each(data, function(key, value){
              $("#load-table").append('<tr>'+
                  '<td class="center">'+ value.sno +'</td>'+
                  '<td>'+ value.name +'</td>'+
                  '<td>'+ value.city +'</td>'+
                  '<td class="center"><button class="edit-btn" id="edit" data-id="'+ value.sno +'" data-name="'+ value.name +'" data-city="'+ value.city +'">Edit</button></td>'+
                  '<td class="center"><button class="delete-btn" id="delete" data-id="'+ value.sno +'">Delete</button></td>'+
              '</tr>');
            });
          }
        }
      });
      }
    });

});
  
</script>
</body>
</html>
```



引用 : https://jobcode.jp/flask-mvt-model/
https://zenn.dev/ikaro1192/books/999b71a570cb89024716/viewer/beb222e301d2119669cc
https://qiita.com/tarosuke777000/items/391b0291faae45974be1
https://github.com/jakbin/flask-ajax