Skip to content
This repository was archived by the owner on Aug 19, 2025. It is now read-only.
This repository was archived by the owner on Aug 19, 2025. It is now read-only.

Error: could not determine data type of parameter $2 #300

@Lepiloff

Description

@Lepiloff

databases==0.4.1
python==3.7
postgresql == PostgreSQL 13.2 (Ubuntu 13.2-1.pgdg18.04+1)

Hi there!
I'm having trouble with using queries in sqlalchemy expression language with func . The code is

    async def get(self, pk: int):
        query = (
            select(
                [
                    func.json_build_object(
                        "id",
                        events.c.id,
                        "title",
                        events.c.title,
                        "location",
                        func.json_agg(
                            func.json_build_object(
                                "city",
                                locations.c.city,
                                "street",
                                locations.c.street,
                                "building",
                                locations.c.building,
                            )
                        ),
                        "location_all_columns_example",
                        func.json_agg(func.json_build_object(
                            *itertools.chain(*[(_.name, _) for _ in locations.c])
                        )),
                        "activity",
                        func.json_agg(
                            func.json_build_object(
                                "name",
                                activities.c.name,
                            )
                        ),
                    )
                ]
            )
                .select_from(events.join(locations).join(activities))
                .where(
                and_(
                    events.c.id == pk,
                    locations.c.id == events.c.location_id,
                    activities.c.id == events.c.activities_id,
                )
            )
                .order_by(desc(events.c.created_at))
                .group_by(events.c.id)  # !!! <- IMPORTANT
        )
        ev = dict(await database.fetch_one(query))
        if ev is None:
            raise HTTPException(status_code=404, detail="Event not found")
        return ev

This expression generate next sql query

SELECT json_build_object(:json_build_object_2, events.id, :json_build_object_3, events.title, :json_build_object_4, json_agg(json_build_object(:json_build_object_5, locations.city, :json_build_object_6, locations.street, :json_build_object_7, locations.building)), :json_build_object_8, json_agg(json_build_object(:json_build_object_9, locations.id, :json_build_object_10, locations.lat, :json_build_object_11, locations.long, :json_build_object_12, locations.city, :json_build_object_13, locations.street, :json_build_object_14, locations.building)), :json_build_object_15, json_agg(json_build_object(:json_build_object_16, activities.name))) AS json_build_object_1 
FROM events JOIN locations ON locations.id = events.location_id JOIN activities ON activities.id = events.activities_id 
WHERE events.id = :id_1 AND locations.id = events.location_id AND activities.id = events.activities_id GROUP BY events.id ORDER BY events.created_at DESC

This causes an error

  File "/home/e-lepilov/Env/wplay/lib/python3.7/site-packages/databases/core.py", line 146, in fetch_one
    return await connection.fetch_one(query, values)
  File "/home/e-lepilov/Env/wplay/lib/python3.7/site-packages/databases/core.py", line 246, in fetch_one
    return await self._connection.fetch_one(built_query)
  File "/home/e-lepilov/Env/wplay/lib/python3.7/site-packages/databases/backends/postgres.py", line 168, in fetch_one
    row = await self._connection.fetchrow(query, *args)
  File "/home/e-lepilov/Env/wplay/lib/python3.7/site-packages/asyncpg/connection.py", line 646, in fetchrow
    record_class=record_class,
  File "/home/e-lepilov/Env/wplay/lib/python3.7/site-packages/asyncpg/connection.py", line 1632, in _execute
    ignore_custom_codec=ignore_custom_codec,
  File "/home/e-lepilov/Env/wplay/lib/python3.7/site-packages/asyncpg/connection.py", line 1655, in __execute
    ignore_custom_codec=ignore_custom_codec,
  File "/home/e-lepilov/Env/wplay/lib/python3.7/site-packages/asyncpg/connection.py", line 1681, in _do_execute
    ignore_custom_codec=ignore_custom_codec,
  File "/home/e-lepilov/Env/wplay/lib/python3.7/site-packages/asyncpg/connection.py", line 380, in _get_statement
    ignore_custom_codec=ignore_custom_codec,
  File "asyncpg/protocol/protocol.pyx", line 166, in prepare
asyncpg.exceptions.IndeterminateDatatypeError: could not determine data type of parameter $2

This code absolutely working with sync query to DB like this
ev = session.execute(query).fetchone()

May be any idea about how to solve this?

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions