How to take HTML user input and query it via Python & SQL?

Question

Is there a way to take user input from HTML, and use python to run the input through to a SQL database? Does the input need to be parsed? I want the the user to be able to type in a store name, and for it to return relevant rows

def search():
    store_search = request.form.get("store")
    if request.method == "POST":
        if not store_search:
            return "please type in a store!"
        else:
            c = conn.cursor()
            c.execute("SELECT * FROM stores WHERE store_name= 'store_search'")
            rows = c.fetchall()
            for eachRow in rows:
                return row
    else:
return render_template("search.html")

HTML:

{% extends "main_page.html" %}
{% block title %}
Search
{% endblock %}

{% block main %}
    <form action="{{ url_for('search') }}" method="post">
        <fieldset>
            <div class="form-group">
                <input autocomplete="off" autofocus class="form-control" 
                 name="store" placeholder="store" type="text"/>
            </div>
            <div class="form-group">
                <button class="btn btn-default" type="submit">search</button>
            </div>
            <div class="page-header">
                <h1>{{ store }}</h1>
            </div>
        </fieldset>
    </form>
{% endblock %}

Ajax1234 · Accepted Answer · 2018-05-09 03:10:19Z

0

You can create a simple app in flask that receives user input and scans the items returned from a SELECT query in sqlite3:

First, create the user form. You can use ajax with jquery for a dynamic response:

In search.html:

<html>
 <head>
   <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
 </head>
 <body> 
    <input type='text' name='query' id='query'>
    <button type='button' id='search'>Search</button>
    <div id='results'></div>
 </body>
  <script> 
    $(document).ready(function() {
     $('#search').click(function() {
       var text = $('#query').val();
        $.ajax({
        url: "/search",
        type: "get",
        data: {query: text},
        success: function(response) {
        $("#results").html(response.html);
       },
       error: function(xhr) {
        //Do Something to handle error
       }
     });
   });
  });
  </script>
</html>

In app.py

import flask
import sqlite3
app = flask.Flask(__name__)

@app.route('/')
def home():
  return "<a href='/search'>Search</a>"

@app.route('/search')
def search():
   term = flask.request.args.get('query')
   possibilities = [i for [i] in sqlite3.connect('filename.db').cursor().execute("SELECT * FROM stores") if term.lower() in i.lower()]
   return flask.jsonify({'html':'<p>No results found</p>' if not possibilities else '<ul>\n{}</ul>'.format('\n'.join('<li>{}</li>'.format(i) for i in possibilities))})

answered May 9, 2018 at 3:10

Ajax1234

71.7k9 gold badges67 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Emily Over a year ago

Thank you so much!! Would this be able to work for MySQL too?

Ajax1234 Over a year ago

@Emily Glad to help! It will work with MySql, all you need to do is connect to your database which is running on your server, and then run INSERT and SELECT queries similar to sqlite3. See here and here

Emily Over a year ago

thank you again! one last question, how do I display the filtered row inside the #results div?

Ajax1234 Over a year ago

@Emily The code above completes that task. In the route, a simple list is created which is passed as a jsonified object to the ajax response function. Let me know how this works.

coder96 Over a year ago

@Ajax1234 hello :) how do you combine the html and python script? If I run the python script will not give me errors but when I activate the web page it doesn't give any result from the database. Instead of sqlite3 I am using PostgreSQL. Please help.

Collectives™ on Stack Overflow

How to take HTML user input and query it via Python & SQL?

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related