In my previous article, (https://medium.com/p/93c82361d0e9) we looked at common mistakes in predicting financial data, as well as developed our own pipeline with all sorts of nice custom stuff. After running some backtesting, I realized: I have too many models! And I don’t remember which models did well, or what parameters they have. Disaster!
So, let’s organize our models using a SQL database. This will serve two purposes:
- Keep track of our models and their parameters in one place, in case we want to re-create our models, train similar ones, continue to train existing ones with different hyper-parameters, or just admire our model empire.
- Have easy access to model parameters in one place, in case we want to create a RestAPI (which we will down the road) or control our local application variables for any other purpose.
For this purpose, we will use SQLite3 with Python.
Our first step is to figure out our database schema. I decided on 2 tables: Model and Adam_Optimizer.
The model table will store parameters such as model name, type of the model, input shape, and other variables like what time-interval was the model trained on, and what ticker data it was trained with.
Adam_Optimizer table will store the parameters of our Adam optimizer. In my project I exclusively use Adam — so this makes our life pretty simple. If we were to make a table of various optimizers, we would need to figure out how to deal with a variable number of parameters — most likely by storing the whole parameter dictionary as a BLOB object. Either way, that’s a project for the future, for now, our task is fairly simple.
So, let’s begin!
To use SQLite3 we first have to establish a connection to our database:
This will connect us to our database. Does no database exist? No problem, one will be created.
Before we continue, we are using the same structure as our project from my previous article. (If you are interested in crypto, markets, and deep learning, go read it! https://medium.com/p/93c82361d0e9)
Our models are using root/models/interval/ticker/type folder sub-structure, where the interval is the time-interval and ticker is the crypto-asset with which the model was trained and type is the type of model (such as LSTM).
I also have ensembly_average type, which is a simple ensemble we used previously to average out several models.
With all of that clarified, here are our tables:
We have a one-to-many relationship between the optimizer and model table so that we don’t have to keep duplicates of optimizers (since one setting can be used for several models). To signify that, we will use a foreign key attribute on “optimizer_id” from the optimizer table in our model table. I’ll explain the rest of the columns as we go along.
To create those new tables, we have to use a cursor object, which is a method of connecting to our database:
cursor_obj = connection.cursor()
cursor_obj.execute(create_model_table)Next, we will need to create prepared statements for the insertion of values in our database. Fortunately, this is very easy with SQLite3!
Notice that we use INSERT OR IGNORE for our adam_opt table so that we don’t store any duplicate optimizers. Running those 2 functions while providing them with a dictionary of parameters will insert a singular entry into our database.
Now for the fun part: obtaining our model parameters to pass them on.
I will do that by scanning all existing models in our project and writing their parameters into a dictionary. This way we can populate the database with the models that already exist.
First, we initialize an empty dictionary that will store our parameters. Then we will set up a path to our models — in my case, I’m running this script from /sql/ folder, so I will grab the current folder, and use pathlib (https://docs.python.org/3/library/pathlib.html — It’s really good!) library by calling a .parent method to go one folder above. Then we will join said path with our models folder and voila!
Next, we will use os.walk function to loop over all the files in our models folder. We are only interested in final files, not any intermediate folders, so we do an additional loop. We need to define our valid model types — since we might have ensemble folders in there, and we don’t want to use those for now.
Then using the .split python method we can get the name of the final folder — this will be the type of our model.
Next, we will use an if statement to check that our files have a .h5 extension (which Tensorflow uses by default) and that the folder is in valid_model_types (ensemble folders will have duplicates of models which we don’t want).
Our first parameter, model_name is super easy to get — simply read off the name variable of our for loop.
The ticker is simply the folder above the “type” folder, and the interval is the folder above that. We get both by using .split method.
Our model’s type depends on our final folder —we already got it in the previous snippet.
Finally, we can use pathlib Path method to define the full path to our model so that we can start loading them next.
Now all that remains is to load our model using Keras load_model function (don’t forget to specify any custom objects you might be using).
The rest of the parameters lie in the model config — which we can get using the .get_config() method.
We also can get the optimizer of the loaded model by using .optimizer.get_config() and read its parameters.
Final remaining thing is to check if our ticker folder has any ensemble types, and if so, allocate “ensemble = True” parameter to the models that are included in them. We simply check if the model exists in the ensemble folder and set the parameters respectively. If the folder itself doesn’t exist, we can simply set the ensemble parameter to False. This way we can easily check if our model is part of an ensemble, and if it is, which ensemble.
That’s it, we fully extracted all required features from our models!
The final step is to insert the parameters into our params dictionary, where model_name acts as a key and parameter dictionary are its value.
Full code:
The code to extract parameters from a singular model is mostly the same, so I won’t be including it here.
Now, all we have to do is to insert all of our models and their parameters into the database:
We will loop over our dictionary keys in line 3 (remember, our keys are model names and our values are dictionaries themselves). Notice that we first insert values into the adam optimizer table — this is because in our model table we have a foreign key, optimizer_id, which we need to know before being able to fully insert data.
In lines 9 to 11, we will grab said optimizer id, using the .fetchone method of our cursor object. After that, all we have to do is to provide the parameters matching our prepared statement in the right order, and that’s it. All of our models are now neatly in our database with the corresponding optimizers to match.
One last thing remains — we need to come up with a way to manage new models. We don’t want to re-index our database every time we train a new model that we like.
So first, we will create a function to save a singular model to our database. We will provide the path to the model, ticker, and interval (although the latter two could be extracted from the model path as we did before). Then all we have to do is to parse our model and insert it into the database.
But what if we want to delete our model? We can’t just delete it just from the hard drive or from the database manually, we have to do both.
For this function, we need the path to the model we want to delete. First, in lines 5–9 we will check if a said model exists, and if it does, we will delete it from our storage. Then, on lines 11–25 we will check our SQL database to see if our model is part of an ensemble folder, and if it is, delete it from there as well. Finally, we can delete our model from our SQL database. Nice and clean!
Finally, we need to create a function to move our new model to an ensemble folder, if we want to do that. This is important, as we are keeping track of whether a model is part of an ensemble, so we can’t just move the model physically, we need to also register it in our database.
Lucky for us, this is fairly simple: we will use Pythons shutil.copyfile method to copy our model file to the ensemble folder that we provide, and then set the ensemble type and ensemble status to the correct ones in our database.
And we are done! There are many ways to go from here; we can create a table that will store our backtesting/testing results, so we can see how each model performs, we can hook this up to Keras so that new models that are trained are automatically registered (maybe based on some sort of test first, to make sure they are decent models). Maybe we can even set up some sort of cross-training for an investment universe based on models trained on individual tickers. Possibilities are indeed endless!
Next time we will use this database to create a very basic RestAPI and see the predictions of the models right in our browser.
