FastAPI with PyDapper and Sqlite
Google operates a free listing service for Hotels and Vacation Rentals to integrate with their maps and search engine. In theory the idea is that you supply Google with information about your listing and it will be verified and placed into their system at no cost to you. It is similar to how requesting Google to index your website results in you being given a slot in their search engine (not that you are able to set the positioning and relevance for terms).
How does Google achieve this? They use XML messages based on an extended standard that was ratified by travel groups decades ago Online Travel Agency formats (OTA messages). These messages classified as Availability, Rates, and Inventory (ARI) messages. These messages are supposed to give a snapshot of your listing price which Google will then verify against your public website to judge if your listing is accurate. If it is deemed to be accurate then you rise in the rankings for map and search engine results. If it is deemed inaccurate then your listing is ranked lower until it is delisted.
Complications
The messages are complicate and there are numerous specifications. Here is one for example, note that Google does not use this as is, they have made changes and extensions
The link to their XML schema messages is here
This is quite a lot to take in as you must construct a series of messages that indicate the listings availability, rates, promotions, inventory, and extras. To top this off there is no verification mechanism, public Application Programming Interface where these messages can be sent to calculate a listing availability feed price.
In summary you must submit a set of messages of the ARI format to public ingestion point (lots of authorization, schemas, certificate work there to get to that point) that is a black box and you have no idea if the calculation is correct and will match. This is a struggle to understand and verify the calculations used by Google to produce a public vacation rental price.
For example take the following message set that are submitted at a time
- Transaction Property Data
- OTA Hotel Rate Amount Notification Request
- OTA Hotel Availability Notification Request
- OTA Hotel Inventory Count Notification Request
- Tax and Fee Info
- Promotions
- Rate Modifications
- Extra Guest Charge (no public schema available to validate against)
These messages compromise an entire set of data which should be able to generate a calculated price for a listing at a time as combining the rate + promotion + taxes for example against availability and inventory will give you a price.
Solution: Build my own Vacation Rentals Calculator
As Google does and will not provide a public API to validate rates for a given set of messages and there is a pressing need to figure out how these messages go together for checking. I decided to create a program that will take these messages in and calculate a feed price.
Requirements
- Reads the ARI XML messages
- Translates the messages into some consolidated data source to review relationships and validate
- Apply formulas to calculate a price based on Arrival, Departure, and Booking Date
Looking these over I decided to use python and particularly FastAPI, PyDapper, and Sqlite to achieve this, breaking it down as such
- Translate XML messages into Sqlite database tables and relationships
First pass of table structures
Final table structure, worked on the keys, foreign keys, and relationships to interlink the messages properly.
The choice of python is to learn in more detail FastAPI and allow for sharing with open source as well as compatible as Google engineers support python programming language.
PyDapper is because other Object Relational Mapping (ORM) libraries are not close enough to the Structured Query Language (SQL) and would cause serious impedance if this project is to be translated to another language such as C#, sticking to basic compatible SQL is a deliberate choice as it gives a widely known compatibility level, the standard Sqlite database and the queries.
Completed code is here googlevr_calculator