Django - Function with template returns `TypeError: not enough arguments for format string`-CodePudding

I am trying to use PostgreSQL's FORMAT function in Django to format phone number strings.

I can accomplish this with the following SQL query:

SELECT
       phone_number, FORMAT('(%s) %s-%s', SUBSTRING(phone_number,3,3), SUBSTRING(phone_number,6,3), SUBSTRING(phone_number,9,4))
FROM core_user
WHERE phone_number iS NOT NULL

which returns a result like:

Trying to implement this into Django to be used for an ORM query, I did the following:

class FormatPhoneNumber(Func):
    function = "FORMAT"
    template = "%(function)s('(%s) %s-%s', SUBSTRING(%(expressions)s,3,3), SUBSTRING(%(expressions)s,6,3), SUBSTRING(%(expressions)s,9,4))"

ORM query:

User.objects.annotate(phone_number2=FormatPhoneNumber(f"phone_number"))

Returns the following error:

File /venv/lib/python3.10/site-packages/django/db/models/expressions.py:802, in Func.as_sql(self, compiler, connection, function, template, arg_joiner, **extra_context)
    800 arg_joiner = arg_joiner or data.get("arg_joiner", self.arg_joiner)
    801 data["expressions"] = data["field"] = arg_joiner.join(sql_parts)
--> 802 return template % data, params

TypeError: not enough arguments for format string

I believe it is due to this line '(%s) %s-%s' that is supplied to the FORMAT function.

Does anyone have any ideas on how I can make this work?

CodePudding user response：

Yes, you use two consecutive percentages to produce a percentage after formatting, so:

class FormatPhoneNumber(Func):
    function = "FORMAT"
    template = "%(function)s('(%%s) %%s-%%s', SUBSTRING(%(expressions)s,3,3), SUBSTRING(%(expressions)s,6,3), SUBSTRING(%(expressions)s,9,4))"

But normally formatting is not done by the database, normally you do this in the model, in a model field, or in the view or template.

CodePudding user response：

I was not able to get the solution using the FORMAT function to work, but I did get this to work with REGEXP_REPLACE which gives the same output:

class FormatPhoneNumber2(Func):
    function = "REGEXP_REPLACE"
    template = "%(function)s(SUBSTRING(%(expressions)s,3,10), '(\d{3})(\d{3})(\d{4})', '(\\1) \\2-\\3')"


In [27]: result = (
    ...:     User.objects.filter(phone_number__isnull=False)
    ...:     .annotate(phone_number2=FormatPhoneNumber2("phone_number"))
    ...:     .values_list("phone_number", "phone_number2")
    ...: )

In [28]: for user in result:
    ...:     print(user)
(' 16502553199', '(650) 255-3199')
(' 12147047099', '(214) 704-7099')
(' 12147547099', '(214) 754-7099')