Places App with MariaDB Connector/Python

This page is part of MariaDB's Documentation.

The parent of this page is: Sample Code

Topics on this page:

Overview
Prerequisites
Install Back-end Application Service
- Configuring the Back-end Application Service
- Start the Back-end Application Service
Install Front-end
Live Demonstration

Overview

Places is a sample web application to manage location information. The application is compatible with MariaDB Connector/Python and Single Node Transactions and Replicated Transactions topologies on MariaDB SkySQL.

Places demonstrates the power and versatility of MariaDB Connectors and Transactions services by managing arbitrary location data in JSON through a Google Maps interface.

Places has three components:

React.js and Google Maps front-end web application
Python-based back-end application service
Single Node Transactions back-end database service, running on MariaDB SkySQL

Prerequisites

Clone the Repository

Places is available from a MariaDB Corporation repository on GitHub. The repository contains the SQL code to initialize the database, the back-end application service code in Python and other languages, and the React.js front-end web application that connects to the back-end.

To download the Places code, clone the repository from GitHub:

$ git clone https://github.com/mariadb-corporation/dev-example-places.git

This creates a dev-example-places directory on your file system.

Provision Database Services

Places requires a back-end database service using the InnoDB storage engine. The back-end app is compatible with Single Node Transactions and Replicated Transactions topologies. For the purposes of demonstration, this guide uses the Single Node Transactions topology on MariaDB SkySQL.

For additional information, see "Launch".

Create the Database Schema

Places uses the Google Maps API to provide map information and a Single Node Transactions service to store data on locations of interest to the user. You need to create a database and table to store this data for the application.

To create the schema for the Places application:

Create a places database:
```
CREATE DATABASE places;
```

Create a Locations table:

CREATE TABLE places.locations (
   id INT PRIMARY KEY AUTO_INCREMENT,
   name VARCHAR(100) NOT NULL DEFAULT '',
   description VARCHAR(500) DEFAULT '',
   type CHAR(1) NOT NULL DEFAULT '',
   latitude DECIMAL(9,6) NOT NULL,
   longitude DECIMAL(9,6) NOT NULL,
   attr LONGTEXT
      CHARACTER SET utf8mb4
      COLLATE utf8mb4_general_ci
      DEFAULT NULL
      CHECK(JSON_VALID(attr)))
ENGINE = InnoDB
AUTO_INCREMENT = 19
DEFAULT CHARSET = utf8mb4;

Set Google Maps API Key

Places requires an active Google Maps API key. For information on obtaining this key, see Google Cloud.

Install Back-end Application Service

Places front-end web application connects to the database service using a back-end application service. The repository contains different versions of the back-end app, allowing you to test and experiment with different languages and different MariaDB Connectors. These instructions are for Python using MariaDB Connector/Python.

Navigate to the correct api directory:

$ cd /path/to/dev-example-places/api/python/

Configuring the Back-end Application Service

To connect the back-end app to your MariaDB SkySQL database service, configure the back-end app to use the appropriate credentials:

Create a .env file in the api/python directory, set the variables to the login credentials for your MariaDB SkySQL service:

DB_HOST = example.skysql.net
DB_USER = db_user
DB_PASS = db_user_passwd
DB_PORT = 5001
DB_NAME = places

Initialize virtual environment:
```
$ python3 -m venv .
```
Activate the virtual environment:
```
$ sh venv/bin/activate activate
```

Install dependencies in the virtual environment:

$ ./venv/pip3 install python-dotenv flask mariadb

Start the Back-end Application Service

Once you have the back-end API configured and installed, you can start the service:

$ python3 api.py

The back-end application service is now running. Install and start the React.js web application to use Places.

Install Front-end

Places uses a React.js front-end web application. The front-end connects to the back-end applications service and provides a web interface for the user.

To install and start the web app:

Navigate to the client directory:

$ cd /path/to/dev-example-places/client/

Edit the .env file to add your Google API Key:
```
REACT_APP_GOOGLE_API_KEY=your_api_key
```
Install the client application:
```
$ npm install
```
Start the client:
```
$ npm start
```

The React.js web application is now running. The app is accessible through your web browser at http://localhost:3000.

Live Demonstration

Places is a sample web application for MariaDB SkySQL that demonstrates a Single Node Transactions service working with JSON data to record locations in Google Maps.

The web interface opens onto a Google Map. It provides a Map view and a Satellite view. You can zoom in, zoom out, and place the figure to get a Street view. Clicking the "Add Location" button opens a form to add a location to the database.

Places allows you to create an Attraction, Restaurant, or Sports Venue. You can then specify a name, description, position in latitude and longitude, and category. When you click "Save" the application writes the location to the database:

+----+---------------------+-------------+------+------------+--------------+-------------------------+
| id | name                | description | type | latitude   | longitude    | attr                    |
+----+---------------------+-------------+------+------------+--------------+-------------------------+
| 19 | Washington Monument | NULL        | A    | 38.8895563 |  -77.0352546 | {"category":"Landmark"} |
+----+---------------------+-------------+------+------------+--------------+-------------------------+

Locations can contain arbitrary information in addition to the general fields in the table. These are stored on the attr column using JSON data.

Attraction:
```
{
   "category":"Landmark",
}
```

Restaurant:

{
   "details":{
      "foodType":"Pizza",
      "menu":"www.giodanos.com/menu"
   },
   "favorites":[
      {
         "description":"Classic Chicago",
         "price":24.99
      },
      {
         "description":"Salad",
         "price":9.99
      }
   ]
}

Sports Venue:

{
   "details":{
      "yearOpened":1994,
      "capacity":23500
   },
   "events":[
      {
         "date":"10/18/2019",
         "description":"Bulls vs Celtics"
      },
      {
         "date":"10/21/2019",
         "description":"Bulls vs Lakers"
      },
      {
         "date":"11/5/2019",
         "description":"Bulls vs Bucks"
      },
      {
         "date":"11/5/2019",
         "description":"Blackhawks vs Blues"
      }
   ]
}