Python & MySQL: RESTful

Page 1

Correlation Between MySQL and RESTful Methods

Introduction HTTP & MySQL Features HTTP and MySQL Correlation Table Server Responses Summary

Introduction

Learn how to prepare HTTP RESTful services with MySQL, Python and a relational database. MySQL and HTTP methods don't correspond perfectly with each other. See the HTTP & MySQL Correlation Table, below, for a basic relationship between MySQL and RESTful HTTP methods. This Python & MySQL: RESTful series, connects Python and RESTful Web services with similar relationships.

This page provides an overview for a set of Python and MySQL interactive examples. This Python & MySQL: RESTful series includes explanation, examples and source code to create, read, update and delete (CRUD) MySQL data, then apply those operations with Python to implement RESTful Web services.

The next page discusses the basics of a relational database management system (RDBMS) with MySQL and Python. For more detail regarding MySQL itself, please see MySQL Statements: RDBMS.

HTTP & MySQL Features

Basic MySQL functionality includes Create, Read, Update and Delete, usually abbreviated as CRUD. HTTP request methods include Connect, Delete, Get, Head, Options, Patch, Post, Put and Trace. MySQL and HTTP methods relate to each other. They're similar.

HTTP & MySQL Correlation Table

The following table includes alternating rows with HTTP methods and similar MySQL operations.

The second to last column indicates whether or not the examples are idempotent. In computer science (not mathematics) idempotent operations may change the underlying struction once or never. Multiple idempotent operations, of the same type, may modify a SQL database the first time, while accomplishing nothing when executed many times.

The last column, In URI, indicates whether or not values for the HTTP method pass to a Web service, appended to the Web address. When In URI equals No, then it's more difficult to change arguments and values, because they are invisible to most users. They're probably more secure.

When In URI equals Yes, then you might see values added to the end of a Web address. For example, 7, might pass to the Web service as http://example.com/order-app/order/7. In that case it's easy to change the order information to 1, 10, 9, or any other number. Therefore In URI is easier to modify.

Operation	Method	Functionality	idempotent	In URI
HTTP	POST	Create Resource	No	No
MySQL	Create	Create Resource	No
HTTP	GET	Read Resource	Yes¹	All:No,Some:Yes
MySQL	Read	Read Resource	Yes
HTTP	PUT	Create or Replace Resource	Yes²	Yes
MySQL	Update	Create or Replace Resource	Yes
HTTP	DELETE	Delete Resource	No³	Yes
MySQL	Delete	Delete Resource	No³

HTTP & MySQL Correlation Table: Superscripts

GET:Read:idempotent¹: Repeated execution of the same GET request returns the same result unless values in the database have changed.
PUT:Update:idempotent²: Repeated execution of the same PUT request returns the same result.
DELETE:Delete:idempotent³: Repeated execution of the same DELETE request returns the same result.
GET & PUT: Both share the same resource with the same ID in the URI. However PUT modifies the resource with content in the body of the message.

Server Responses

The Web service should return a response number, in the header. This enables the Web client to display information appropriately. The following list includes some common responses.

English Response	Numbered Response
OK	200
Created	201
Accepted	202¹
No Content	204
Not Modified	304²
Not Found	404
Bad Request	400

Server Response: Superscripts

Accepted:201:Numbered Response¹: May take time to process.
Not Modified:304:Numbered Response²: The client already has this data.

Simple Response

The following code demonstrates sending a simple response from a server. The response tells the client its request was handled OK. The response includes the date, server and content type followed by new line characters. The header follows the response.

The server responds with a status code before the HTML header, as follows, with the number 200. Substitute response numbers, depending on how the server handles a request.

HTTP/1.1 200 OK
Date: Fri, 2 Apr 2021 24:43:02 GMT
Server: Python/3.2.3
Content-Type: text/html\n\n
<header>
 ...
</header>

Threads

Python threads help with long running operations such as input/output and execution of MySQL statements. Threads ideally allow the user interface to remain responsive, during operations which require long periods of time. Some pages in this series include threads, or asynchronous operations, with Python.

Summary

Learn how to prepare HTTP RESTful services with MySQL, Python and a relational database. MySQL and HTTP methods don't correspond perfectly with each other. See the HTTP & MySQL Correlation Table, above, for a basic relationship between MySQL and RESTful HTTP methods. This Python & MySQL: RESTful series, connects Python and RESTful Web services with similar relationships.

This page provided an overview for a set of Python and MySQL interactive examples. This Python & MySQL: RESTful series includes explanation, examples and source code to create, read, update and delete (CRUD) MySQL data, then apply those operations with Python to implement RESTful Web services.

The next page discusses the basics of a relational database management system (RDBMS) with MySQL and Python. For more detail regarding MySQL itself, please see MySQL Statements: RDBMS.

RESTful Methods with MySQL

Select a Page:

Python with MySQL

Modifications

HostGator switched to Python MySQLdb connectors for shared hosting, after I completed this series. The primary coding differences follow.

import MySQLdb instead of import mysql.connector.
Connect with cnx = MySQLdb.connect(host,user,password,database) instead of cnx = mysql.connector.connect(user, password,host,database)
Access columns with columns = mycursor.description instead of mycursor.column_names.
Columns return as a tuple of tuples, instead of a list of lists.
Tuples required some extra formatting as well.

Combine Python, MySQL and RESTful methods for Web development.

Unfortunately the requests module isn't available in my particular hosting environment, at this time. The recommended requests module simplifies RESTful Python development.