use python function in annotate

Using Django
1

hey,
I have a product model with some properties.
I use a python function inside the model to generate the catalog number of the product (customer_catalog_gen).
it looks like this:

def catalog_part(self, *args, **kwargs):
        return ''.join(utils.catalog_representation[self.category_index])
    def customer_catalog_gen(self, *args, **kwargs):
        ret = self.category.catalog_rep + 'z' + self.catalog_part() + '-' + str(self.id)
        return ret
    customer_catalog_gen.short_description = _("customer catalog number")

as you can see customer_catalog_gen calls to catalog_part calls to utils.catalog_representation[int]. catalog_representation is an array of pairs to all the possile (a-z and A-Z)

catalog_representation = [('A', 'a'), ('A', 'b'), ('A', 'c'), ('A', 'd'), ('A', 'e'), ...]

it all works for now, but I would like to generate the catalog_part with annotate in SQL for performance. how would I do that? I don’t think I can call python function in annotate. So what would you recommend? create a new model for catalog_representation and save there all the pairs?

2

You’re right, you can’t. (Call a python function in your Django code from the database.)

You’ve got a couple different options:

  • Define this function as a User-Defined function in PostgreSQL. (You don’t mention what database you are using, I’m assuming PostgreSQL.)
  • You can define your annotation using a Func expression to create your value.
3

Thank you for your response!
currently, I am using SQLite
I have a small question about migrating to PostgreSQL, I don’t have a problem with scaling, this is a website with not a lot of hits to spikes. I’m running the website on AWS EC2, so my question is could I run the PostgreSQL and the Django on the same EC2 instance? how would I get into the PostgreSQL admin if my website is running?

4

Sure - running your entire environment in an instance is no different than running everything on a single server. Anything you would do when connecting to your server would work the same way when connecting to your EC2 instance.