How to use JavaScript/Ajax and Python to edit local SQL database?

Question

I've developed the front-end of a web site using JavaScript.

I have a button that when it's clicked retrieves an integer value from another object on the website.

I have a local SQL database. How do I make SQL calls when this button is clicked?

I've seen answers with JavaScript/Ajax and PHP, but I don't want to use PHP.

Using JavaScript only, can I open a local SQL db and make SQL calls? If yes, how?

If I can't do this using JavaScript only, can I use JavaScript/Ajax and Python? If yes, please post a reference or simple example.

Answer 1

Technically it is possible to connect to a database using client-side JavaScript. However, as others have already pointed out, doing so is considered to be really bad practice for a few reasons - the most important of them being security.

When the browser is asked to load a certain page it will go ahead and ask the server for the content that is found at that URL. This means HTML files, external JavaScript files, images, stylesheets and other resources that are needed in order for the page to get rendered. Since the user has access to the these files, especially your JavaScript code, it means he has access to your database credentials. You could try to minify/obfuscate the code, but that won't make it any safer.

If you do understand all the consequences and for some reason you still want to do it, take a look at this example.

The best way to go at it is to have a server handle the interaction with the database. Depending on your language of choice, you could build that part of the application using PHP (among with an HTTP server such as Apache), Java, Python and so on. You could even use JavaScript (see Node.js).

Since you asked for a Python snippet, here's one that connects to a MySQL database and creates an HTTP server that listens for connections on port 8080. For each GET request it will receive, the script will query the database and send back the result as text/html.

#!/usr/bin/python
import MySQLdb
from BaseHTTPServer import BaseHTTPRequestHandler, HTTPServer

PORT_NUMBER = 8080

class Handler(BaseHTTPRequestHandler):

    def do_GET(self):

        # prepare a cursor object using cursor() method
        cursor = db.cursor()

        # execute SQL query using execute() method
        cursor.execute("SELECT VERSION()")

        # Fetch a single row using fetchone() method
        data = cursor.fetchone()

        self.send_response(200)
        self.send_header('Content-type','text/html')
        self.end_headers()

        self.wfile.write(data)
        return

try:

    db = MySQLdb.connect(host="localhost", user="root", passwd="yourpwd", db="pw_exam")

    server = HTTPServer(('', PORT_NUMBER), myHandler)
    print 'Started httpserver on port ' , PORT_NUMBER

    server.serve_forever()

except KeyboardInterrupt:
    print '^C received, shutting down the web server'
    server.socket.close()

Answer 2

You can't do this from the javascript in the browser for a simple reason - if your database is available to your javascript code, it is also available for any web site user. And someone will just delete all your data or will replace it with something funny.

That is why we usually also have the server-side application and it can basically be in any language - php, python or javascript.

And there are frameworks and libraries to simplify your job also for any language.

For example, for python you can use flask and sqlalchemy.

If you want javascript everywhere, then use nodejs on the server and expressjs as server-side framework is one of most popular options for node. And you can find node.js packages to make SQL queries to your database too.

The general flow of your application can look like this:

• Your client-side application is opened in the browser and it needs some data from the database
• It sends the AJAX request to the sever
• Server makes a database request and returns the data as json
• Client-side application displays this data

It works similar when data need to be modified. The difference here is that your database is "covered" by server side, so you can be sure that only allowed operations are performed. Like nobody is able to drop the database, or only a registered user can edit the profile data (and only own profile data editing is possible), etc.

Answer 3

JQuery is a brilliant way to create live Database feeds in Website development. You can simple import the JavaScript files from CDN or Google or download and use it here: https://jquery.com

Front end:

$(document).ready(function(){
    $("#idHere").click(function(){
        var testdata = document.getElementByID("#idHere").value;
        var anothertestdata = document.getElementByID("#idHere").value;
        $.ajax({
        url: "/inc/Database.php",
        data: {
        example: testdata,
       anotherexample: anothertestdata
    },
    success: function( data ) {
        $( "#idHere" ).html( "<strong>" + data + "</strong> " );
    }
  });
});
});

PHP file:

if(isset($_GET['testdata'])):
    $db = mysqli_connect($db_host, $db_user, $db_pass, $db_name);
    $query = "SELECT * FROM TABLE";
    $result = mysqli_query($db, $query);
    while($row = mysqli_fetch_array($result)):
        echo $row['CollumnName'];
    endwhile;
    $db->close();
endif;

Anything echo'd will show in the response.

Using JavaScript to open a Database is hard, long and has MANY security issues. I would not recommend in doing so.