Using Databases with CherryPy application
Python: CherryPy
USING DATABASES WITH CHERRYPY APPLICATION
Storing the session data in cherrypy.session, which by default is stored in the process memory, is not the right way of keeping the data in the long run. Sessions are there to identify the users and carry as little amount of data as necessary for the operation carried by the users.
To store, persist and query data we need a proper database server. We will use the sqlite database which is directly supported by Python.
We will write a simple application which will find the square of a number client-side. The application will replace the storage of the calculated square from the session to a SQLite database. This application is an extension of the application we made in the previous article, for more details about it, see the previous article titled, Ajax application using CherryPy, the link of which is given at the end.
First save the below CSS code in a file 'style.css' which is present in the directory 'public/css'.
body {
color: white;
background-color: rgb(31, 44, 44);
}
display: none;
}
For the main page of the application we will use the jQuery framework out of simplicity but feel free to replace it with your favourite tool. The page is composed of simple HTML elements to get user input and display the generated string. It also contains client-side code to talk to the backend API that actually performs the hard work.
Save the below code in file 'index.html'.
<!DOCTYPE html>
<html>
<head>
<link href="/static/css/style.css" rel="stylesheet" />
<script src="http://code.jquery.com/jquery-2.0.3.min.js"></script>
<script type="text/javascript">
$(document).ready(function () {
$("#calc-square").click(function (e) {
$.post("/square", {
number: $("input[name='number']").val(),
}).done(function (square_val) {
$("#square-val").show();
$("#square-val input").val(square_val);
});
e.preventDefault();
});
$("#replace-val").click(function (e) {
$.ajax({
type: "PUT",
url: "/square",
data: { number: $("#square-val input").val() },
}).done(function () {
alert("Replaced!");
});
e.preventDefault();
});
$("#delete-val").click(function (e) {
$.ajax({
type: "DELETE",
url: "/square",
}).done(function () {
$("#square-val").hide();
});
e.preventDefault();
});
});
</script>
</head>
<body>
<input type="text" value="2" name="number" />
<button id="calc-square">Calculate Square</button>
<div id="square-val">
<input type="text" />
<button id="replace-val">Replace result</button>
<button id="delete-val">Delete result</button>
</div>
</body>
</html>
Finally we will write the application's code that serves the HTML page above and responds to requests to calculate the square of a number and stores them in the database 'my.db'.
Save the below code in a file named 'sql_app.py'.
import cherrypy
import os
import sqlite3
import time
DB_STRING = "my.db"
class application:
def index(self):
return open('index.html')
class applicationWebService:
def GET(self):
with sqlite3.connect(DB_STRING) as c:
cherrypy.session['ts'] = time.time()
r = c.execute("SELECT value FROM user WHERE session_id=?",
[cherrypy.session.id])
return r.fetchone()
def POST(self, number=2):
answer = str(int(number)**2)
with sqlite3.connect(DB_STRING) as c:
cherrypy.session['ts'] = time.time()
c.execute("INSERT INTO user VALUES (?, ?)",
[cherrypy.session.id, answer])
return answer
def PUT(self, number):
with sqlite3.connect(DB_STRING) as c:
cherrypy.session['ts'] = time.time()
c.execute("UPDATE user SET value=? WHERE session_id=?",
[number, cherrypy.session.id])
def DELETE(self):
cherrypy.session.pop('ts', None)
with sqlite3.connect(DB_STRING) as c:
c.execute("DELETE FROM user WHERE session_id=?",
[cherrypy.session.id])
def setup_database():
"""
Create the `user` table in the database
on server startup
"""
with sqlite3.connect(DB_STRING) as con:
con.execute("CREATE TABLE user (session_id, value)")
def cleanup_database():
"""
Destroy the `user` table from the database
on server shutdown.
"""
with sqlite3.connect(DB_STRING) as con:
con.execute("DROP TABLE user")
if __name__ == '__main__':
conf = {
'/': {
'tools.sessions.on': True,
'tools.staticdir.root': os.path.abspath(os.getcwd())
},
'/square': {
'request.dispatch': cherrypy.dispatch.MethodDispatcher(),
'tools.response_headers.on': True,
'tools.response_headers.headers': [('Content-Type', 'text/plain')],
},
'/static': {
'tools.staticdir.on': True,
'tools.staticdir.dir': './public'
}
}
cherrypy.engine.subscribe('start', setup_database)
cherrypy.engine.subscribe('stop', cleanup_database)
webapp = application()
webapp.square = applicationWebService()
cherrypy.quickstart(webapp, '/', conf)
Run the code through the Terminal/Command Line as follows:
python3 sql_app.py
Now, by going to the URL http://localhost:8080/ and calculating the square of some numbers, we can see that the values are being stored in the database 'my.db'.
Link to the article 'Ajax application using CherryPy':
https://cppsecrets.com/users/5617971101051071011161151049711410997484852494964103109971051084699111109/Ajax-application-using-CherryPy.php
Comments