Integrate Python And MySQL With Ajax

By dedtech.info

This blog post will explain how to integrate Python and MySQL with AJAX. The code displayed below will create two text boxes and a button. After the button is pressed, the AJAX callback function will process the values in the text boxes and add them to a MySQL database.

<!DOCTYPE html>
<html>
<body>

<script>
function show(str1,str2) {
  const xhttp = new XMLHttpRequest();
  xhttp.onload = function() {
    document.getElementById("query").innerHTML =
    this.responseText;
  }
  xhttp.open("GET", "db.py?p=" + str1 + "&" + "q=" + str2);
  xhttp.send();   
}
</script>

<p>Name <input type="text" id="txt1"></p>
<p>Job <input type="text" id="txt2"></p>
<button onclick="show(txt1.value,txt2.value)">click</button>
<br />
<span id="query"></span>

</body>
</html>

The required header has to attached to the top of the server side Python script.

#!C:\Users\bigdaddys-pawn\AppData\Local\Programs\Thonny\python.exe
print("Content-Type: text/html\n")

The required libraries have to be imported.

import cgi
import mysql.connector

The variable vars will initialize the cgi class. The variables p and q will hold the values that were passed with the URL in the AJAX callback function.

vars = cgi.FieldStorage()
p = vars["p"].value
q = vars["q"].value

The database connection has to be setup.

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="somedatabase"
)

A cursor has to be setup to traverse the rows and columns of a database table.

cursor = conn.cursor()

The variable sql will hold a query that will be sent to the database server.

sql = "INSERT INTO employees (name,job) VALUES (%s,%s)"
values = (p,q)

The cursor.execute and conn.commit functions will execute the MySQL query.

cursor.execute(sql, values)
conn.commit()

To see if the MySQL query was successful, see if a SELECT statement can retrieve the newly added row from the employees table.

cursor.execute("SELECT * FROM employees")
rows = cursor.fetchall()

This print statement will print out all the rows stored in the employees table.

for row in rows:
    print(row)

These statements will close the database connection.

cursor.close()
conn.close()

This is what the whole Python script looks like.

#!C:\Users\bigdaddys-pawn\AppData\Local\Programs\Thonny\python.exe
print("Content-Type: text/html\n")

import cgi
import mysql.connector

vars = cgi.FieldStorage()
p = vars["p"].value
q = vars["q"].value

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="somedatabase"
)

cursor = conn.cursor()

sql = "INSERT INTO employees (name,job) VALUES (%s,%s)"
values = (p,q)

cursor.execute(sql, values)
conn.commit()

cursor.execute("SELECT * FROM employees")
rows = cursor.fetchall()

for row in rows:
    print(row)

cursor.close()
conn.close()

Integrate Python And MySQL With Ajax

Leave a Reply Cancel reply