SQL wildcards in PYMYSQL search

I am struggling to put together a simple SQL wildcard search for my Flask application.

I am using an HTML form field to run a simple mysql query “WHERE CustomerName LIKE ‘a%'”- https://www.w3schools.com/sql/sql_wildcards.asp – However I am unable to get the right positioning for the search place holder…

here the HTML form field (working fine)

    form method="GET" action>
    <div class="form-group">
        <div class="col-sm-3">
            <input type="text" placeholder="Material Name" name="Material_Name" action=/search class="form-control">
        </div>
    </div>

<div class="form-group">
    <div class="col-sm-2">
        <input type="submit" value="SEARCH" class="btn btn-primary btn-block">
    </div>
</div>

The python route

@app.route('/wegowo')
def wegowo():
    material_namex = request.args.get('Material_Name','')
    try:
        tabledata = DB.wego_wo(material_namex)
    except Exception as e:
        print(e)
        tabledata = None
    return render_template('wegowo.html', tabledata=tabledata)

and finally the database helper function

def wego_wo(self,material_namex):
        connection = self.connect()
        MLN = "%s" % material_namex
        try:
            query = "SELECT id, material, cas, category FROM wegomats WHERE material LIKE '%s' ORDER BY cas" %(MLN);
            with connection.cursor(pymysql.cursors.DictCursor) as cursor:
            cursor.execute(query)
        return cursor.fetchall()
    finally:
        connection.cursor

The error seems to be around the structure of the LIKE ‘%s’, since the query works fine when using an ‘=’ statement.

Thank you in advance for any help.

Answer

After quite some trying I fount the solution to this problem: the ‘%’ wildcard operators need to be placed as follows:

MLN = "%s" % '%' + material_namex + '%'

the rest of the code is correct

Advertisement

Answer