Django raw() query with SIMILAR TO errors 'tuple' object has no attribute 'id'

Question

I am making a few queries and then removing duplicates based on rank for a cemetery burial app. I am trying to use PostgreSQL's SIMILAR TO in my last search rank, but any time I use it I get the 'tuple' object has no attribute 'id'.

Here are two queries:

query1 = """
            SELECT "burials_burial"."id", "burials_burial"."name_id",
                "burials_burial"."grave_id",
                "burials_burial"."interment_date",
                "burials_burial"."user_id",
                "cemeteries_grave"."search1_id" AS "cemetery_id",
                '2' AS "rank"
            FROM "burials_burial"
            INNER JOIN "names_name"
                ON ( "burials_burial"."name_id" = "names_name"."id" )
            INNER JOIN "cemeteries_grave"
                ON ( "burials_burial"."grave_id" =
                    "cemeteries_grave"."id")
            WHERE (
                (LOWER("names_name"."first_name") IN {0!s})
                OR (LOWER("names_name"."middle_name") IN {0!s})
                OR (LOWER("names_name"."last_name") IN {0!s})
            )
        """

query2 = """
            SELECT "burials_burial"."id", "burials_burial"."name_id",
                "burials_burial"."grave_id",
                "burials_burial"."interment_date",
                "burials_burial"."user_id",
                "cemeteries_grave"."search1_id" AS "cemetery_id",
                '1' AS "rank"
            FROM "burials_burial"
            INNER JOIN "names_name"
                ON ( "burials_burial"."name_id" = "names_name"."id" )
            INNER JOIN "cemeteries_grave"
                ON ( "burials_burial"."grave_id" =
                    "cemeteries_grave"."id")
            WHERE (
                (LOWER("names_name"."first_name") SIMILAR TO {0!s})
                OR (LOWER("names_name"."middle_name") SIMILAR TO {0!s})
                OR (LOWER("names_name"."last_name") SIMILAR TO {0!s})
            )
        """

When I call the queries I supply in the appropriate search parameters here:

names1 = Burial.objects.raw(query1.format(terms))
names2 = Burial.objects.raw(query2.format(terms.replace('\'', '').replace(', ', '|').replace('(', '\'%(').replace(')', ')%\'')))

Lastly, I just try to go through the names and do something with them:

for names in (names1, names2):
    for n in names:
        <do something here>

Now for where things are weird. For names1 I get my results and I can loop through them. For names2 I get the error noted above. Why?

I have looked at raw query output to make sure that what I supply to PostgreSQL is valid. Running the queries as output by the python shell gives me results as I expect:

IN: print names1.raw_query

OUT: SELECT "burials_burial"."id", "burials_burial"."name_id",
                        "burials_burial"."grave_id",
                        "burials_burial"."interment_date",
                        "burials_burial"."user_id",
                        "cemeteries_grave"."search1_id" AS "cemetery_id",
                        '25' AS "rank"
                    FROM "burials_burial"
                    INNER JOIN "names_name"
                        ON ( "burials_burial"."name_id" = "names_name"."id" )
                    INNER JOIN "cemeteries_grave"
                        ON ( "burials_burial"."grave_id" =
                            "cemeteries_grave"."id")
                    WHERE (
                        LOWER("names_name"."first_name") IN ('william', 'david', 'smith')
                        AND LOWER("names_name"."middle_name") IN ('william', 'david', 'smith')
                    )
IN: print names2.raw_query

OUT: SELECT "burials_burial"."id", "burials_burial"."name_id",
                    "burials_burial"."grave_id",
                    "burials_burial"."interment_date",
                    "burials_burial"."user_id",
                    "cemeteries_grave"."search1_id" AS "cemetery_id",
                    '1' AS "rank"
                FROM "burials_burial"
                INNER JOIN "names_name"
                    ON ( "burials_burial"."name_id" = "names_name"."id" )
                INNER JOIN "cemeteries_grave"
                    ON ( "burials_burial"."grave_id" =
                        "cemeteries_grave"."id")
                WHERE (
                    (LOWER("names_name"."first_name") SIMILAR TO '%(william|david|smith)%')
                    OR (LOWER("names_name"."middle_name") SIMILAR TO '%(william|david|smith)%')
                    OR (LOWER("names_name"."last_name") SIMILAR TO '%(william|david|smith)%')
                )

I have also used the cursor directly with the query and get results set back, although not as objects so this is not ideal. But it does show that python/django can handle the query:

IN: from django.db import connections
IN: cursor = connection.cursor()
IN: cursor.execute(names2.raw_query)
IN: results = cursor.fetchall()
IN: len(results)

OUT: 1

So, again, my question. Why does a seemingly valid query give me the 'tuple' object has no attribute 'id' error?

Answer 1

It seems that the problem is that I have a % sign in the SQL. PostgreSQL's Similar To is a cross of LIKE and POSIX regex. It uses the % as a wildcard, and will require a wildcard just as the LIKE statement will. I find that when I remove the %, I don't get the error described, but I also don't get the actual results I am looking for.

Python uses % for string formatting as outlined here: https://docs.python.org/2/library/stdtypes.html#string-formatting. According to another stack overflow post the way to overcome this is to use %%.

There are therefore two work arounds. First is to use PostgreSQL's ~ operator, which does POSIX regex only. This lets me take out the % wildcard. The resulting query is this:

query6 = """
                    SELECT "burials_burial"."id", "burials_burial"."name_id",
                        "burials_burial"."grave_id",
                        "burials_burial"."interment_date",
                        "burials_burial"."user_id",
                        "cemeteries_grave"."search1_id" AS "cemetery_id",
                        '1' AS "rank"
                    FROM "burials_burial"
                    INNER JOIN "names_name"
                        ON ( "burials_burial"."name_id" = "names_name"."id" )
                    INNER JOIN "cemeteries_grave"
                        ON ( "burials_burial"."grave_id" =
                            "cemeteries_grave"."id")
                    WHERE (
                        (LOWER("names_name"."first_name") ~ {0!s})
                        OR (LOWER("names_name"."middle_name") ~ {0!s})
                        OR (LOWER("names_name"."last_name") ~ {0!s})
                    )
                """

names6 = Burial.objects.raw(query6.format(terms.replace('\'', '')\
                                    .replace(', ', '|')\
                                    .replace('(', '\'(')\
                                    .replace(')', ')\'')))

The other is to have the replace input with two % characters, bypassing the string replacement like so:

names2 = Burial.objects.raw(query2.format(terms.replace('\'', '').replace(', ', '|').replace('(', '\'%%(').replace(')', ')%%\'')))

Thanks so much for the comments and time put in on this.