CODEWITHSUNDEEP
postgresqlsearchflask-sqlalchemygetattrfuzzy
cy163
cy163

Reputation: 1

failed to get ALL satisfied records when retrieving postgresql database by db.session.query(TaskResultItems).filter(Dynamic_conditions).all()

I am stucked for days for this. I need to retrieve a postgresql DB table to find all records which contain a Chinese character '电' in fwmc field. I was doing a fuzzy search. However, I always failed to get ALL satisfied records. Actually I got only 1 record, while there are 3 records containing Chinese character '电' in the table. What I have tried is described as follows. Thanks for your help in advance.

# back end flask code

import logging
from sqlalchemy.sql import text
from flask_wtf.csrf import CSRFProtect
from flask import Flask, request, session, url_for, current_app, render_template, request, jsonify
from sqlalchemy import or_, and_
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SECRET_KEY'] = 'you never guess'
app.config['SQLALCHEMY_DATABASE_URI'] ="postgresql://cy163:[email protected]/yifan"
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = True
app.config['SQLALCHEMY_ECHO'] = True
db = SQLAlchemy(app)


handler = logging.StreamHandler()
handler.setLevel(logging.DEBUG)
formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
handler.setFormatter(formatter)


app.logger.addHandler(handler)



class TaskResultItems(db.Model):
    __tablename__ = 'taskresultitems'
    taskid = db.Column(db.String(19), primary_key= True, nullable=False)
    row_no = db.Column(db.Integer, nullable=True, autoincrement=True)
    fwmc  =  db.Column(db.String(60))
    model  =  db.Column(db.String(20))



@app.route('/')
def index():

    return render_template('index5.html')

@app.route('/search', methods=['POST','get'])
def search():
    dynamic_value = '电'
    Ziduanming = 'fwmc'

    Dynamic_conditions = getattr(TaskResultItems, Ziduanming).like(f"%{dynamic_value}%") #"%" + dynamic_value2 + "%"

    ## Way1: wrong
    results = db.session.query(TaskResultItems).filter(Dynamic_conditions).all()
    ## Way2: wrong
    results6 = db.session.query(TaskResultItems).filter(TaskResultItems.fwmc.like('%{}%'.format(dynamic_value))).all()

    ## Way3: OK!
    abc = '%' + dynamic_value + '%'
    results2 = db.session.execute(text('SELECT * FROM taskresultitems WHERE fwmc LIKE :fwmc'), {'fwmc': abc}).fetchall()

    db.session.close()
    return '1234567890'
if __name__ == '__main__':
    app.run(debug=True)

<!--front end html code -->
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title12345</title>

        <style type="text/css">


        .mymy-table {
          border: none;
        }

        .my-table {
          width: 640px;
          height: 300px;
          border: 1px solid black;
        }
        table {
            width: 640px;
            border-collapse: collapse;
            margin-top: 20px;
        }
        .td-1{
            width: 5%;
        }
        .th-1{
            width: 5%;
        }

        th, td {
            border: 1px solid black;
            padding: 2px;
            text-align: left;
        }
        th {
            background-color: #f2f2f2;
        }
        span{
            font-size: 14px;
            font-family:"微软雅黑";
            color: rgb(0, 0, 0);
        }

    input[type="text"] {
        background-color: #f2f2f2;
        border: 1px solid #a7a5a5;
        height: 28px;
        border-radius: 6px;
        padding: 0px 0px;
    }
    .big-font {
        font-size: 28px;
    }
    .white-text {
       color: white;
    }

    .green-text {
       color: #ffffff;
    }



    select {
       height: 28px;
       width: 80px;

       background-color: #f2f2f2;
       border: 1px solid #a7a5a5;
       border-radius: 6px;
       padding: 0px 0px;
    }

      select option {
          height: 22px;
          font-size: 12px;
      }

      .centered-container {
        display: flex;
        justify-content: center;
        align-items: center;
        height: 100vh;
      }

      .centered-content {
        width: 50%;
        padding: 20px;
        border: 1px solid #000;
      }



        *{margin:0;padding:0;}
        body{font-size: 12px;font-family:"微软雅黑";color: rgb(10, 60, 41);background: #f2f2f2;}
        img{border:0;}


        /*h_nav start*/
        .header .h_top .h_nav{width:950px;height:70px;float: left;color:#fff;list-style:none;}
        .header .h_top .h_nav ul li{list-style:none;float:left;}
        .header .h_top .h_nav ul li a{text-decoration:none;display:block;line-height:70px;padding: 0 20px 0 20px;
            color:#fff;font-size:24px;margin:0 5px 0 5px;}
        .header .h_top .h_nav ul li a:hover{background:#323744;}

        /*end h_nav*/
    </style>
</head>
<body>
<div >    <form id="myForm"  method="POST" action="/search">
<div style="display: flex; justify-content: center; align-items: center; ">

      <table  id="conditionsTable">
        <tr>
          <td><button type="button" onclick="addCondition()">Add Condition</button><
          </td>
          <td  class="td-1">
              <select id="sel_FWCPSX_id" name="sel_FWCPSX_name"  onchange="updateInputs4('xxx', this.value)" ><!--sel_FWCPSX-->
                  <option value="select"  style="display:none" selected>select/option>
                  <option value="fwmc">fwmc</option>
              </select>
          </td>
          <td  id="xxx"><input type="text" id="first_Editbox_id" name="first_Editbox_name" class="search-input" placeholder="Enter search condition"></td>
          <td>
              <select  class="select1" id="second_AndOr_id" name="second_AndOr_name" >
                  <option value="and">and </option>
                  <option value="or"> or</option>
              </select>

          </td>
          <td><input type="text" id="second_Editbox_id" name="second_Editbox_name" class="search-input" placeholder="Enter search condition"></td>
          <td><button type="submit" onclick="submitForm()">Submit</button></td>
        </tr>
      </table>

</div>

</form>
</div>
</body>
<script>
    let conditionCount = 0;

    function submitForm() {
      document.getElementById('myForm').submit();
    }



</script>
</html>

schema of taskresultitems table

enter image description here

records in taskresultitems table

enter image description here

What I am expecting is finding ALL 3 records by using

way 1
results = db.session.query(TaskResultItems).filter(Dynamic_conditions).all()

instead of using

## Way3: 
    abc = '%' + dynamic_value + '%'

results2 = db.session.execute(text('SELECT \* FROM taskresultitems WHERE fwmc LIKE :fwmc'), {'fwmc': abc}).fetchall()

Upvotes: 0

Views: 22

Answers (0)

Related Questions