Create a Real Time Data Appending API with Flask and MySQL

Python's Flask library is, in my opinion, the best of the best in the Python world. Paired together with Celery and RHEL7 or Ubuntu Server 18.04, and you have a rock solid web server and automation solution that can be run on VPS servers for a few dollars a month.

I put together a small database of 1.2 million records of names and addresses, home and mobile numbers, email addresses, IP addresses + geolocation data, and some other ancillary consumer data.

For more details, the full source code for this project can be located in GitHub

The data model is fairly straight-forward.

User and IPData. Simple enough. To speed up the searches in such large datasets, we need to create a few indexes.

mysql> create index idx_ip on ipdata(ip);  

Next, I created indexes on each of these fields in MySQL.

  • cell_phone
  • home_phone
  • ip (example from above)
  • postal_code
  • first_name
  • last_name
  • lat
  • lng
  • city

Now, I am going to deploy the following Flask app to accept our customer's data requests and start appending data to their requests in real time.

Here is only a small excerpt from the Flask app relating to the SMS Lookup. For the full source, see the GitHub repo.

To see the full source code, visit this link to GitHub

Let's fire up the app to test our API.

(.env) marketing-data-intelligence:~$ python

OK, let's begin testing. If you have not read any of my previous posts on this site, then you should know, I always use Postman to test my API's.

I'm testing locally right now, so I am going to use an ngrok process to create a secure HTTP tunnel to my local machine. This gives me tunnel introspection which helps debugging while building apps. What does that mean exactly?

Tunnel introspection allows developer's to visualize the HTTP requests as the data flows through the tunnel from the client to the server and then back to the client. What do you get to see? Everything! Request headers, variables, response data and much, much more. Super helpful.

Launch ngrok in a separate terminal...

(.env) marketing-data-intelligence:~$ ngrok http 8088


Now to begin testing...

I want to make a GET request at this resource for a consumer cell phone number.


I have a test phone number: 513-919-6079


Postman response:


In the screen shot above, you can see the API has returned a response object with the full dataset for the record found just by looking up a cell phone number.

If a record is not found, the API will return a data not found response like so...

    "Number Not Found": "+13214054266",
    "GeoData": {
        "geocode": "Florida",
        "carrier": "Verizon",
        "timezone": [

The ngrok tunnel report shows us tons and tons of information about each request we made.


I can also append real time data to an IP address by using the following route.



Or, if no matching data found, a more generic response.

    "Response": "No data found for IP:"

Considering there are 4+ billion IP addresses in the IPv4 address space, this is the response you would likely see the most often.

Wrapping Up

It's no secret that I absolutely love developing applications, websites and APIs with Python. Flask makes it really simple to define a data model and create views to interact with the data model. Easily validate data in both directions and ensure proper API authentication and responses from defined endpoints.

This Data Broker API is now in the final stages of development before deploying into production on AWS Lightsail.

Craig Derington

Secular Humanist, Libertarian, FOSS Evangelist building Cloud Apps developed on Red Hat Enterprise Linux and Ubuntu Server. My toolset includes Python, Celery, Flask, Django, MySQL, MongoDB and Git.

comments powered by Disqus