Serialization issues with UUID on PostgreSQL

I’m trying to build a small web rest api using postgresql as a backend. I allready have a database set up and am trying to use Sanic and Databases to make the data avaiable. However, I’m running into some issues with my UUID database columns when using Databases.

If I define my Table like this:

activities = Table(
    Column("id", UUID(as_uuid=False), primary_key=True),
    Column("points", Float),

And serialize the resulting query like this:

query =
rows = await database.fetch_all(query=query)
content = [
    {"id": row[], "points": row[activities.c.points]} for row in rows

The resulting json in content looks like this:

[{'id': UUID('348beab0-34dc-11e7-8762-1b7a0b79ec0c'), 'points': 6.667}]

Note the UUID() wrapping of the UUID string, which should not be there. As far as googling goes, every answer I could find was that setting “as_uuid=True” should solve it. However, that results in the following error:

AttributeError: 'UUID' object has no attribute 'replace'

This seems odd to me, since every google result on that error message gives “as_uuid=True” as a solution to that error message, rather than the cause.

Could anyone please point me in the right direction, I find my self stuck. Here is a gist with a minimal sample that results in the error above

Hey @magnus.ahlberg,

I might try to have a deeper look, but in meantime you should be aware that the Postgres driver in databases is asyncpg, that is different to psycopg2 that’s generally used with sqlalchemy, hence there might be some underlying subtle syntax differences, mostly around how the query is rendered for the driver. You can try to inspect what query is rendered and what’s returned by the raw driver (that you can access as connection.raw_driver).

By default asyncpg provides types conversion back to python type unless it’s explicitly overridden in the query (select id:text from ...) or by the codec.

If you’re pressed by time you might try to workaround it with one of the following way:

  • setting the type explicitly in the query (not sure it’s manageable from sqlalchemy)
  • setting the codecs to the driver
  • using serialisation/deserialisation “solution” by one of your schema validation library if you use it (typesystem, pydantic, marshmallow, …) that also works well with conversion from python types to json compatible way.
1 Like

Thank you so much! That has given me several new directions do check out. I’ll try to dig a bit deeper and see if I understand. It seems so close since the UUID-type is able to represent itself as a string already, just not the way I would like. :slight_smile: Thanks for the info about the driver being asyncpg, I’ll check that driver’s UUID-type and see if I can find some magic method to get to that sweet sweet uuid juice. :wink:

Hah, can’t believe I didn’t try this. Looking at the asyncpg documentation I found that asyncpg uses the builting Python uuid-type by default. And just wrapping the result in str() solved the problem.

Guess I thought that the string conversion used in json serialization was the same as when using str(). Now I learned that. :slight_smile:

1 Like

Awesome! I’m glad it helped