Connecting Unity with a database using Python Flask REST WebService

Introduction

In some cases, we have games where we need to store various data into a database for future retrieval. Unity has its own PlayerPref library that can store player data. However to make things more flexible and have control on how the data would be stored and how it would be maintained online, we are going to make a system, that will connect Unity game to a database using a Python webservice. Right now for this tutorial, we will create a localhost database and a webservice that will also run on localhost. Here is the checklist of things that we are going to do:

• Create a Postgres database on the LocalHost for use.
• Create a Flask application where we can apply REST method to retrieve and store data to and from the database. The data will be displayed in the form of JSON.
• Create a Unity app that send data to store to the database and retrieve data from the database.

Creating Postgres database

I am using a Windows system while I am writing this tutorial. Assuming you have Postgres installed with postgres as the root user let’s connect to the postgres server and create a sample database. To make things simpler, we are going to use pgAdmin. Lets connect to an existing postgres database on Localhost.

Next we select the existing postgres database and click on Edit > New Object > New Database and finally name the database gamerdb. Navigate to the Schemas and finally Tables and right click on it. Click on New Table.

To make things quickly, just enter the following on the SQL section and click OK.

CREATE TABLE public.gamerlist
(
   user_id integer NOT NULL, 
   user_name text NOT NULL,
   score integer,
   PRIMARY KEY (user_id)
) 
WITH (
  OIDS = FALSE
);

Lets add three rows to the table. Keeping the gamerlist table selected, click on View the data in Selected Object and add the three rows of data.

Alternatively you can click on Execute arbitrary SQL Query button and execute the following SQL queries:

INSERT INTO gamerlist(user_id, user_name, score)
VALUES
 (1, 'koshai', 20000),
 (2, 'fragger', 40000),
 (3, 'destructor', 50000);

We have a sample database ready. Our next step is to create a Python Webservice to retrieve and store data.

Creating the Flask REST application

I chose Flask since it is pretty fast to set up, lightweight and easy to deploy. Assuming you have Python installed in your machine (3.5 or above). Lets create a folder anywhere at your choice and name it FlaskREST using the Terminal or Git Bash on Windows.

$ mkdir FlaskREST

Head onto the FlaskREST folder and before we create a Flask application, lets create a virtual environment (to make sure the components we use to run the Flask application remains on that folder instead of the whole computer system). Install virtualenv using pip.

$ pip install virtualenv

Once virtualenv is installed we create a new virtualenv called rest.

$ virtualenv rest

Just to note that I am using Windows here and Git Bash is my terminal so if you need to work using mac and Ubuntu, the command lines are little different. To activate the rest virtualenv, head to rest folder by typing cd rest and type the following:

$ source ./Scripts/activate

The rest text in parenthesis will show, indicating that the virtualenv is running. Return back to FlaskREST folder by typing cd .. and create a new folder.

$ mkdir FlaskApp

Head to the folder and create two files called setup.py and requirements.txt. The contents of each files are given below:

requirements.txt

Flask==1.0.2
Flask-SQLAlchemy==2.3.2
gunicorn==19.8.1
psycopg2-binary==2.7.5
SQLAlchemy==1.2.9

setup.py

#!/usr/bin/env python

import os

from setuptools import setup, find_packages

def get_requirements():
    basedir = os.path.dirname(__file__)
    with open(os.path.join(basedir, 'requirements.txt')) as f:
        return [l.strip() for l in f]

def get_version():
    basedir = os.path.dirname(__file__)
    try:
        with open(os.path.join(basedir, 'version.txt')) as f:
            return f.read().strip()
    except:
        return "1.0.0"

setup(  
    name         = 'unityrest',
    version      = get_version(),
    
    packages = find_packages(),

    include_package_data = True,    
    
    tests_require = ['nose>=0.11', "coverage>=3.4"],

    test_suite = "nose.collector",

    zip_safe = False,

    install_requires = get_requirements(),
)

When we execute the setup.py file, it will take in all the packages mentioned in the requirements.txt file and install those in your app folder. Lets run setup.py.

$ python setup.py install

Time to create the Flask app. Create a new folder call api.

$ mkdir api

Create a python file called app.py and enter the following inside the app.py file.

app.py

from flask import Flask, jsonify
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://postgres@localhost:5432/gamerdb'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
db = SQLAlchemy(app)

@app.route('/')
def hello_world():
   return 'Hello, World!'

@app.route('/list')
def fetch_data():
    fetch_result = gamerlist.query.all()
    print(fetch_result)
    return jsonify([str(result) for result in fetch_result])
    
class gamerlist(db.Model):
    user_id = db.Column(db.Integer, primary_key=True, nullable=False)
    user_name = db.Column(db.String(80), nullable=False)
    score = db.Column(db.Integer, unique=False, nullable=True)
       
    def __repr__(self):
        return 'Gamer {0}: {1}: {2}:'.format(self.user_id, self.user_name, self.score)
        
if __name__ == "__main__":
    app.run(host='localhost', debug=True)

As a walkthrough, we are setting the database link by using the app.config[‘SQLALCHEMY_DATABASE_URI’]. We initially creating an object class where the data for each user from the gamerdb database will be stored in variables of that class which will queried out when called. Make sure the name of the class and the variables matches with the table name and column names from the database. Also the datatype of the variable should match with the column ones in the table. As a means to check whether the app itself works, we will be displaying Hello World in the root (‘/’) route. We will be displaying all the data using /list route. In the fetch_data method, we are querying all the gamerlist object classes and display them in JSON format. In the terminal, we go to api folder and over there we run:

$ flask run

Now head to http://127.0.0.1:5000/ on your browser and if you see Hello World, then your app is working. Also while you are in the browser, if you go to http://127.0.0.1:5000/list and see the following:

[
"Gamer 1: koshai: 20000:",
"Gamer 2: fragger: 40000:",
"Gamer 3: destructor: 50000:"
]

then your app is properly connected to the database and working fine.

Creating Unity App

We will create the Unity app at first to retrieve the data. In this section we will just write a simple script that will allow to retrieve the data and display in Unity console using a click of a button. So lets create a Unity Project and add a button into the scene. Re-position the button according to your needs (having Screen Space – Overlay as RenderMode). Rename the button as Get Data. Finally on the button add a new Script called GetMe.

Click on the GetMe script to open Visual Studio (or any other editor) and type the following into the script:

using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using UnityEngine.Networking;

public class GetMe : MonoBehaviour {

    public void GetData()
    {
        StartCoroutine(GetRequest("http://127.0.0.1:5000/list"));
    }

    IEnumerator GetRequest(string uri)
    {
        UnityWebRequest uwr = UnityWebRequest.Get(uri);
        yield return uwr.SendWebRequest();

        if (uwr.isNetworkError)
        {
            Debug.Log("Error While Sending: " + uwr.error);
        }
        else
        {
            Debug.Log("Received: " + uwr.downloadHandler.text);
        }
    }
}

Make sure the address is correct, which is exactly the same as the one we used to view through the browser. Finally attach the script into the button event as shown below:

Compile and run the program. If you see the following in the console output, then your Unity app is working:

Received: ["Gamer 1: koshai: 20000:","Gamer 2: fragger: 40000:","Gamer 3: destructor: 50000:"]

Adding new data in the form of JSON and insert into the database table.

Our main goal is to send a JSON file to the webservice which will process to add the data into the gamerdb database. We will now start from Unity. Lets create a button to post data and three input field to that will take ID, user_name and score. It will look like the following:

Now lets create a script for the post data button. Create a script called PostMe and type the following:

using System;
using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using UnityEngine.Networking;
using UnityEngine.UI;

public class PostMe : MonoBehaviour {

    public InputField userID;
    public InputField userName;
    public InputField score;

    [Serializable]
    public class Gamer
    {
        public int userId;
        public string userName;
        public int score;
    }

    public void PostData()
    {
        int userIDFromInput = Int32.Parse(userID.text);
        string userNameFromInput = userName.text;
        int scoreFromInput = Int32.Parse(score.text);

        Gamer gamer = new Gamer();
        gamer.userId = userIDFromInput;
        gamer.userName = userNameFromInput;
        gamer.score = scoreFromInput;

        string json = JsonUtility.ToJson(gamer);
        StartCoroutine(PostRequest("http://127.0.0.1:5000/list_add", json));
    }

    IEnumerator PostRequest(string url, string json)
    {
        var uwr = new UnityWebRequest(url, "POST");
        byte[] jsonToSend = new System.Text.UTF8Encoding().GetBytes(json);
        uwr.uploadHandler = (UploadHandler)new UploadHandlerRaw(jsonToSend);
        uwr.downloadHandler = (DownloadHandler)new DownloadHandlerBuffer();
        uwr.SetRequestHeader("Content-Type", "application/json");

        //Send the request then wait here until it returns
        yield return uwr.SendWebRequest();

        if (uwr.isNetworkError)
        {
            Debug.Log("Error While Sending: " + uwr.error);
        }
        else
        {
            Debug.Log("Received: " + uwr.downloadHandler.text);
        }
    }
}

Next compile the script. Add the Post Me component to the Button click event. Also add the InputFields into the following blank fields in the Post Me components as shown below:

Lets head to the app.py and over there add another route and modify the gamerlist class. The overall script is going to be like this:

from flask import Flask, jsonify, request
from flask_sqlalchemy import SQLAlchemy
import sys

app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://postgres@localhost:5432/gamerdb'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
db = SQLAlchemy(app)

@app.route('/')
def hello_world():
   return 'Hello, World!'

@app.route('/list')
def fetch_data():
    fetch_result = gamerlist.query.all()
    print(fetch_result)
    return jsonify([str(result) for result in fetch_result])

@app.route('/list_add', methods=['POST'])
def insert_user():
    print("Before JSON")
    data = request.get_json()
    print(data)
    print("This is where i am")
    sys.stdout.flush()
    new_user_id = data['userId']
    new_user_name = data['userName']
    new_score = data['score']
    user = gamerlist(new_user_id, new_user_name, new_score)
    db.session.add(user)
    db.session.commit()
    return "OK"

class gamerlist(db.Model):
    user_id = db.Column(db.Integer, primary_key=True, nullable=False)
    user_name = db.Column(db.String(80), nullable=False)
    score = db.Column(db.Integer, unique=False, nullable=True)

    def __init__(self, new_user_id, new_user_name, new_score):
        self.user_id = new_user_id
        self.user_name = new_user_name,
        self.score = new_score
       
    def __repr__(self):
        return 'Gamer {0}: {1}: {2}:'.format(self.user_id, self.user_name, self.score)
        
if __name__ == "__main__":
    app.run(host='localhost', debug=True)

Finally run the app.py by typing in the Git bash:

$ flask run

Run the Unity app and add your data in the three fields and click on Post Data. Later if you click Get Data and find a new entry like this:

Received: ["Gamer 1: koshai: 20000:","Gamer 2: fragger: 40000:","Gamer 3: destructor: 50000:","Gamer 4: Kaboom: 60000:"]

Then congratulations, you have created a Unity app that is connected to a webservice that connects to a database. Give a pat on your back for job well done.