In this blog, I'll cover details on how to write and modify data on a HANA Database from a Python application on XSA. I am going to use the same pyapp project from part 1 of this blog. You can find the code for it at this link. In part 1, I also covered details on how to develop a local database module in an XSA project and query data from it using HTTP GET requests.
Reading data using GET requests, in general, is quite straightforward as shown in Part 1. However, things get a bit more interesting when it comes to changing items in the database. You have to send more complicated HTTP requests (POST, PUT, or DELETE) with proper authorization credentials and valid Cross Site Request Forgery (CSRF) tokens attached. This is to make sure that it is indeed you who is making the changes to the database and that you actually intend to make those changes. If you are not familiar with CSRF, follow this link to learn more.
In this section, I am going to demonstrate how you can send and handle POST requests in your XSA application to add a new product to your database. In general, you need to create an HTML form (in the application router module) which can get product details from the user and transmit them in a POST request to your Python application along with the CSRF token and authorization information. The Python application can then call a stored procedure to insert the product into the database using the received information as input parameters.
You can also send POST requests from within your Python application(s) or through external tools such as Postman or curl (used especially for testing APIs), but for that to work properly, you need to do a bit more work for fetching authorization information and handling CSRF tokens. I will cover this in detail in a separate blog. For now, let's keep things simple and use the application router to get the CSRF token and attach it to our POST request.
Note that CSRF protection can be turned off during development if needed. You can specify "csrfProtection" to be false for the respective application in the xs-app.json file located in the app-router directory. I am going to keep the protection on within this blog, but if you prefer, you can choose to turn it off!
First, let's set up the HTML page in the application router that is going to retrieve product information from the user. Navigate to pyapp > app-router > resources and create a new directory called addproduct. In this directory, create a new file called addproduct.html and paste the following code into it. This code simply creates an HTML form to get some information about the product and calls a function with the name callAddFunc() when the submit button is clicked.
Now, let's program the callAddFunc() which prepares and sends a POST request to your Python application for it to add the provided product to the database. Create a new file in the current directory called addproduct.js. and paste the code for the callAddFunc() shown below into it. This code reads user input, writes it into a JSON object and forwards it within the POST request.
So far you have created a form to get input from the user and send it to your Python application. This alone is not enough. As I mentioned earlier, you need to attach a CSRF token and authorization information with your POST request for it to be accepted. The application router already takes care of the authorization part, but you have to program the fetching and attaching of the CSRF token yourself. The better way to do this is to program this into a $.ajaxSetup() function so that every POST request (sent with AJAX) automatically calls the function to get a CSRF token and incorporates it in the request headers.
Let's go ahead and do that! Create a new directory called common within the resources directory. Create a new file in this directory called csrf.js. In this file, copy the following code which checks if a CSRF token is needed in the outgoing request and if so, retrieves the token and adds it to the request headers.
Now you just need to connect both the csrf.js and the addproduct.js files to the addproduct.html file so that they can both be accessed by the HTML form. Open the addproduct.html file and insert the following lines inside the body tag.
That's all you have to do from the application router's end, which means you are already more than halfway through setting up this POST API! Woot woot! Let's power through and finish the back-end Python component as well.
You just need to modify your Python application to be able to handle a POST request for adding a new product. In the server.py file, add the following code which reads input data from the incoming POST request, parses it to extract the required input parameters, and calls a stored procedure in the database to add the product to the Product.Products table.
That's it! Congratulations, you have finally set up your application to receive input from the user about a product and add it to your database! Build and run your pyapp project using xs push from the command prompt. Open the URL for the web application and replace the "/index.html" with "addproduct/addproduct.html". The output should be as follows.
Fill out the form with values of your choice and click Submit. The output should change to "Product <product_id> inserted successfully" given the values you enter are valid. You can even confirm if the product has actually been added to the database or not by opening the Database explorer and checking the data in the Product.Products table.
I have personally run into too many HTTP errors while preparing the content for this blog, starting from 401 (unauthorized) to 403 (forbidden) and 405 (method not allowed). Not to mention the countless 500 (internal server errors). Anyway, if you follow all the steps I have outlined above, you should be able to avoid all of these! If you do run into any trouble, just know that if you are getting 401 error, it has probably something to do with the application router or the authorization information not being in the HTTP request. For 403, it is most likely something to do with the CSRF token and for 405, you probably just need to make sure your program is enabled to receive/send POST requests. If you get 500 error, you probably just made a typo in the code somewhere.
The procedure for modifying and deleting data from your database is almost entirely the same as that for posting data. Instead of using a POST request, however, you use a PUT or DELETE request. The headers still remain the same as authorization information and CSRF token are required for these operations as well. There aren't any new steps I am going to introduce in this section so I am going to go over everything in a bit less detail than before.
For a demonstration of modifying data in the database, let's create an API for changing the price for a product. Create a directory in the resources directory of the application router named changeprice and create two new files in the directory named changeprice.html and changeprice.js. Copy and paste the following code snippets into the files respectively. The code is very similar to what we had for adding a product. Basically, we are getting information from the user for changing the product price and sending it in a PUT request this time to our Python application.
In the server.py file, create a new route handler to receive the PUT request for changing product price and update the price in the database as shown below.
Build and run the application again using xs push from the command prompt. Open the URL for the web application on your browser. Change the URL from "/index.html" to "changeprice/changeprice.html" and the output should be as follows.
Fill in the information using a valid product id and click Submit. This should send a PUT request to your Python application which should respond back with "Price for product <product_id> has been changed to <new_price>".
Finally, for deleting a product, following the same steps as before, create a new directory under resources directory called deleteproduct containing two files named deleteproduct.html and deleteproduct.js. Copy and paste the following code snippets into the files.
In the server.py file, create a new route handler to receive the DELETE request for deleting the product as shown.
Build and run the application again using xs push from the command prompt. Open the URL for the web application on your browser. Change the URL from “/index.html” to “deleteproduct/deleteproduct.html”. The browser should return an HTML form where you can fill out the product id for the product you want to delete. Once you click submit, the specified product should be deleted which, again, you can verify using the Database Explorer.
To conclude, I hope this blog (parts 1 and 2) helps you further advance in your journey of using Python with SAP HANA XSA. If you actually did read through everything and followed along, kudos to you! Hopefully, you’ll be able to design and use REST APIs within your XSA applications without much trouble after reading this blog! To access the code for the complete application from this blog, follow this link.
Posting Data
Reading data using GET requests, in general, is quite straightforward as shown in Part 1. However, things get a bit more interesting when it comes to changing items in the database. You have to send more complicated HTTP requests (POST, PUT, or DELETE) with proper authorization credentials and valid Cross Site Request Forgery (CSRF) tokens attached. This is to make sure that it is indeed you who is making the changes to the database and that you actually intend to make those changes. If you are not familiar with CSRF, follow this link to learn more.
In this section, I am going to demonstrate how you can send and handle POST requests in your XSA application to add a new product to your database. In general, you need to create an HTML form (in the application router module) which can get product details from the user and transmit them in a POST request to your Python application along with the CSRF token and authorization information. The Python application can then call a stored procedure to insert the product into the database using the received information as input parameters.
You can also send POST requests from within your Python application(s) or through external tools such as Postman or curl (used especially for testing APIs), but for that to work properly, you need to do a bit more work for fetching authorization information and handling CSRF tokens. I will cover this in detail in a separate blog. For now, let's keep things simple and use the application router to get the CSRF token and attach it to our POST request.
Note that CSRF protection can be turned off during development if needed. You can specify "csrfProtection" to be false for the respective application in the xs-app.json file located in the app-router directory. I am going to keep the protection on within this blog, but if you prefer, you can choose to turn it off!
First, let's set up the HTML page in the application router that is going to retrieve product information from the user. Navigate to pyapp > app-router > resources and create a new directory called addproduct. In this directory, create a new file called addproduct.html and paste the following code into it. This code simply creates an HTML form to get some information about the product and calls a function with the name callAddFunc() when the submit button is clicked.
>>> addproduct.html
<body>
<div id="form">
<h1>Add New Product</h1>
<form action="javascript:callAddFunc()">
Product ID:<br>
<input type="text" name="productID" required />
<br>
Category:<br>
<input type="text" name="category" />
<br>
Price:<br>
<input type="text" name="price" />
<br><br>
<input type="submit" value="Submit" />
</form>
</div>
</body>Now, let's program the callAddFunc() which prepares and sends a POST request to your Python application for it to add the provided product to the database. Create a new file in the current directory called addproduct.js. and paste the code for the callAddFunc() shown below into it. This code reads user input, writes it into a JSON object and forwards it within the POST request.
>>> addproduct.js
function callAddFunc(){
// read user input and write it to a json object
var id = document.getElementsByName('productID')[0]['value'];
var category = document.getElementsByName('category')[0]['value'];
var price = document.getElementsByName('price')[0]['value'];
var params = {
"productID": id,
"category": category,
"price": price
};
//send post request to python application
$.ajax({
url: "/core-py/addProduct",
type: "POST",
contentType: "application/json",
data: JSON.stringify(params),
complete: function(xhr, status){
document.getElementById("form").innerHTML = xhr.responseText;
console.log(xhr.responseText);
}
});
}So far you have created a form to get input from the user and send it to your Python application. This alone is not enough. As I mentioned earlier, you need to attach a CSRF token and authorization information with your POST request for it to be accepted. The application router already takes care of the authorization part, but you have to program the fetching and attaching of the CSRF token yourself. The better way to do this is to program this into a $.ajaxSetup() function so that every POST request (sent with AJAX) automatically calls the function to get a CSRF token and incorporates it in the request headers.
Let's go ahead and do that! Create a new directory called common within the resources directory. Create a new file in this directory called csrf.js. In this file, copy the following code which checks if a CSRF token is needed in the outgoing request and if so, retrieves the token and adds it to the request headers.
>>> csrf.js
/*eslint no-console: 0, no-unused-vars: 0, no-use-before-define: 0, no-redeclare: 0*/
$.ajaxSetup({
beforeSend: function(xhr,settings) {
//retrieve csrf token if needed
if (settings && settings.hasOwnProperty("type")
&& settings.type !== "GET"){
var token = getCSRFToken();
xhr.setRequestHeader("X-CSRF-Token", token); //add it to the headers
}
}
});
//actual request to get the csrf token
function getCSRFToken() {
var token = null;
$.ajax({
url: "/",
type: "GET",
async: false,
beforeSend: function(xhr) {
xhr.setRequestHeader("X-CSRF-Token", "Fetch");
},
complete: function(xhr) {
token = xhr.getResponseHeader("X-CSRF-Token");
}
});
return token;
}Now you just need to connect both the csrf.js and the addproduct.js files to the addproduct.html file so that they can both be accessed by the HTML form. Open the addproduct.html file and insert the following lines inside the body tag.
<!-- connect to jQuery, addproduct.js, and csrf.js files -->
<script id="sap-ui-bootstrap" src="https://sapui5.hana.ondemand.com/resources/sap-ui-core.js"></script>
<script src="../common/csrf.js"></script>
<script src="addproduct.js"></script>That's all you have to do from the application router's end, which means you are already more than halfway through setting up this POST API! Woot woot! Let's power through and finish the back-end Python component as well.
You just need to modify your Python application to be able to handle a POST request for adding a new product. In the server.py file, add the following code which reads input data from the incoming POST request, parses it to extract the required input parameters, and calls a stored procedure in the database to add the product to the Product.Products table.
#used to read incoming POST, PUT, DELETE request args
def getRequestParams(data):
params = {}
req = data.decode('utf-8')[1:-1].split(',')
for param in req:
temp = param.split(':')
params[temp[0].strip()[1:-1]] = temp[1].strip()[1:-1]
return params
#adds product to database if a valid post request is received
@app.route('/addProduct', methods=['POST'])
def addProduct():
#authorize user
logger.info('Authorization successful') if checkAuth(request.headers) else abort(403)
#establish db connection
conn = connectDB('hdi-db')
logger.info('Database connection successful: ' + str(conn.isconnected()))
cursor = conn.cursor()
#get parameters from post request
params = getRequestParams(request.data)
#call stored procedure to add product
in_params = (params['productID'], params['category'], float(params['price']), None)
output = cursor.callproc('"insert_product_data"', in_params)
cursor.close()
return str(output)That's it! Congratulations, you have finally set up your application to receive input from the user about a product and add it to your database! Build and run your pyapp project using xs push from the command prompt. Open the URL for the web application and replace the "/index.html" with "addproduct/addproduct.html". The output should be as follows.
Fill out the form with values of your choice and click Submit. The output should change to "Product <product_id> inserted successfully" given the values you enter are valid. You can even confirm if the product has actually been added to the database or not by opening the Database explorer and checking the data in the Product.Products table.
I have personally run into too many HTTP errors while preparing the content for this blog, starting from 401 (unauthorized) to 403 (forbidden) and 405 (method not allowed). Not to mention the countless 500 (internal server errors). Anyway, if you follow all the steps I have outlined above, you should be able to avoid all of these! If you do run into any trouble, just know that if you are getting 401 error, it has probably something to do with the application router or the authorization information not being in the HTTP request. For 403, it is most likely something to do with the CSRF token and for 405, you probably just need to make sure your program is enabled to receive/send POST requests. If you get 500 error, you probably just made a typo in the code somewhere.
Modifying and Deleting Data
The procedure for modifying and deleting data from your database is almost entirely the same as that for posting data. Instead of using a POST request, however, you use a PUT or DELETE request. The headers still remain the same as authorization information and CSRF token are required for these operations as well. There aren't any new steps I am going to introduce in this section so I am going to go over everything in a bit less detail than before.
For a demonstration of modifying data in the database, let's create an API for changing the price for a product. Create a directory in the resources directory of the application router named changeprice and create two new files in the directory named changeprice.html and changeprice.js. Copy and paste the following code snippets into the files respectively. The code is very similar to what we had for adding a product. Basically, we are getting information from the user for changing the product price and sending it in a PUT request this time to our Python application.
>>> changeprice.html
<body>
<script id="sap-ui-bootstrap" src="https://sapui5.hana.ondemand.com/resources/sap-ui-core.js"></script>
<script src="../common/csrf.js"></script>
<script src="changeprice.js"></script>
<div id="form">
<h1>Change Price</h1>
<form action="javascript:callChangeFunc()">
Product ID:<br>
<input type="text" name="productID" />
<br>
New Price:<br>
<input type="text" name="newPrice" />
<br><br>
<input type="submit" value="Submit" />
</form>
</div>
</body>>>> changeprice.js
function callChangeFunc(){
var id = document.getElementsByName('productID')[0]['value'];
var newPrice = document.getElementsByName('newPrice')[0]['value'];
var params = {
"productID": id,
"newPrice": newPrice
};
$.ajax({
url: "/core-py/changeProduct",
type: "PUT",
contentType: "application/json",
data: JSON.stringify(params),
complete: function(xhr, status){
document.getElementById("form").innerHTML = xhr.responseText;
console.log(xhr.responseText);
}
});
}In the server.py file, create a new route handler to receive the PUT request for changing product price and update the price in the database as shown below.
@app.route('/changePrice', methods=['PUT'])
def changePrice():
#authorize user
logger.info('Authorization successful') if checkAuth(request.headers) else abort(403)
#establish db connection
conn = connectDB('hdi-db')
logger.info('Database connection successful: ' + str(conn.isconnected()))
cursor = conn.cursor()
params = getRequestParams(request.data)
in_params = (params['productID'], float(params['newPrice']), None)
output = cursor.callproc('"update_product_prices"', in_params)
cursor.close()
return str(output)Build and run the application again using xs push from the command prompt. Open the URL for the web application on your browser. Change the URL from "/index.html" to "changeprice/changeprice.html" and the output should be as follows.
Fill in the information using a valid product id and click Submit. This should send a PUT request to your Python application which should respond back with "Price for product <product_id> has been changed to <new_price>".
Finally, for deleting a product, following the same steps as before, create a new directory under resources directory called deleteproduct containing two files named deleteproduct.html and deleteproduct.js. Copy and paste the following code snippets into the files.
>>> deleteproduct.html
<body>
<script id="sap-ui-bootstrap" src="https://sapui5.hana.ondemand.com/resources/sap-ui-core.js"></script>
<script src="../common/csrf.js"></script>
<script src="deleteproduct.js"></script>
<div id="form">
<h1>Delete Product</h1>
<form action="javascript:callDelFunc()">
Product ID:<br>
<input type="text" name="productID" />
<br>
<input type="submit" value="Submit" />
</form>
</div>
</body>>>> deleteproduct.js
function callDelFunc(){
var id = document.getElementsByName('productID')[0]['value'];
var params = {
"productID": id
};
$.ajax({
url: "/core-py/deleteProduct",
type: "DELETE",
contentType: "application/json",
data: JSON.stringify(params),
complete: function(xhr, status){
document.getElementById("form").innerHTML = xhr.responseText;
console.log(xhr.responseText);
}
});
}In the server.py file, create a new route handler to receive the DELETE request for deleting the product as shown.
@app.route('/deleteProduct', methods=['DELETE'])
def deleteProduct():
#authorize user
logger.info('Authorization successful') if checkAuth(request.headers) else abort(403)
#establish db connection
conn = connectDB('hdi-db')
logger.info('Database connection successful: ' + str(conn.isconnected()))
cursor = conn.cursor()
params = getRequestParams(request.data)
sql_query = 'DELETE FROM "Product.Products" WHERE PRODUCTID = ' + params['productID'] + ';'
logger.info(sql_query)
cursor.execute(sql_query)
return 'Product ' + params['productID'] + ' has been deleted.'Build and run the application again using xs push from the command prompt. Open the URL for the web application on your browser. Change the URL from “/index.html” to “deleteproduct/deleteproduct.html”. The browser should return an HTML form where you can fill out the product id for the product you want to delete. Once you click submit, the specified product should be deleted which, again, you can verify using the Database Explorer.
To conclude, I hope this blog (parts 1 and 2) helps you further advance in your journey of using Python with SAP HANA XSA. If you actually did read through everything and followed along, kudos to you! Hopefully, you’ll be able to design and use REST APIs within your XSA applications without much trouble after reading this blog! To access the code for the complete application from this blog, follow this link.
- SAP Managed Tags:
