SQLAlchemy with dataclass default not populating postgres database

Question

I am using dataclasses combined with the SQLAlchemy classical mapping paradigm. When I define a dataclass combined with default values for the int and str fields SQLAlchemy does not populate the int and strs, but it does populate the List and datetime fields. For example the following code:

from dataclasses import dataclass, field
from typing import List
from datetime import datetime
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, ARRAY, TIMESTAMP
from sqlalchemy.orm import sessionmaker, mapper

metadata = MetaData()
person_table = \
    Table('people', metadata,
          Column('id', Integer, primary_key=True, autoincrement=True),
          Column('name', String(255)),
          Column('age', Integer),
          Column('hobbies', ARRAY(String)),
          Column('birthday', TIMESTAMP)
          )

@dataclass
class Person:
    id: int = None
    name: str = ''
    age: int = 0
    hobbies: List[str] = field(default_factory=list)
    birthday: datetime = field(default_factory=datetime)

mapper(Person, person_table)

engine = create_engine('postgresql://postgres@localhost:32771/test', echo=True)
metadata.create_all(engine)

session = sessionmaker(bind=engine)()
person = Person(id=None, name='Robby', age=33, hobbies=['golf', 'hiking'], birthday=datetime(1985, 7, 25))
session.add(person)
session.commit()

This correctly populates the person object in memory, but the commit operation produces the following data in postgres (the name and age columns are null):

 id | name | age |    hobbies    |      birthday       
----+------+-----+---------------+---------------------
  1 |      |     | {golf,hiking} | 1985-07-25 00:00:00

If I change the Person class to remove the default values from name and age then the data is populated correctly in postgres:

@dataclass
class Person:
    id: int = None
    name: str
    age: int
    hobbies: List[str] = field(default_factory=list)
    birthday: datetime = field(default_factory=datetime)

Note, I have verified that when the person object is created in the "no-default" version of the class that the name and age fields are populated correctly in memory.

How do I use SQLAlchemy classical mappings in conjunction with dataclasses with default values?

(Python 3.6, SQLAlchemy 1.2.16, PostgreSQL 11.2)

Isn't this due to the difference between default and server_default? — EvertW
– EvertW, Commented Jan 15, 2021 at 8:52
i have a similar issue, can anyone suggest please? stackoverflow.com/questions/67185813/… — greg
– greg, Commented Apr 22, 2021 at 23:05

Tryph · Accepted Answer · 2019-04-09 10:23:58Z

5

Since '' and 0 are respectively default returned values of str() and int() functions, you could use the following code to insert thoses defaults:

@dataclass
class Person:
    id: int = None
    name: str = field(default_factory=str)
    age: int = field(default_factory=int)
    hobbies: List[str] = field(default_factory=list)
    birthday: datetime = field(default_factory=datetime)

Unfortunately, for some reason, using default parameter of field() function does not work as we could expect (could be a bug of the dataclasses backport or a misunterstanding...). But you still can use the default_factory to specify values differents from '' and 0 using lambda:

@dataclass
class Person:
    id: int = None
    name: str = field(default_factory=lambda: 'john doe')
    age: int = field(default_factory=lambda: 77)
    hobbies: List[str] = field(default_factory=list)
    birthday: datetime = field(default_factory=datetime)

edited Apr 9, 2019 at 10:23

answered Apr 8, 2019 at 17:25

Tryph

6,27935 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

robbymurphy Over a year ago

this works if I declare name: str = field(default_factory=str) but not when name: str = field(default=20). I would be satisfied if the more advanced uses of fields were not handled, but the default value is pretty essential.

Tryph Over a year ago

@robbymurphy: I updated the the answer with a tested solution which works for default values other than '' and 0 using default_factory and lambda

robbymurphy Over a year ago

It doesn't explain why default='john doe' doesn't work, but to be fair it answers my original question. I'll give this a try and mark as answer if it works.

Ilja Everilä Over a year ago

At least one reason why default= does not work is that dataclasses sets the default as a class attribute, and SQLA classical mapping does not setup instrumentation over existing class attributes.

Marti Nito Over a year ago

The sad thing is: One uses classical mapping to avoid invasive changes to current codebase (not inheriting from Base). But now, you need to change your codebase to make it work with defaults. (I know that it's still invasive, as the instrumentation attaches the table and other meta information to the class).

lzukowski · Accepted Answer · 2020-12-01 13:55:25Z

Mapping changes the class that you map. So dataclass attributes are overwritten by Columns from the table and properties describes in mapping. You will see more issues when you declare

@dataclass(frozen=True)

You should use default values in Column declaration and maybe the declared model fits better for you, it's similar to dataclass.

If you want to separate infrastructure/DB from your domain code then you need another approach. For this, I suggest do treat your dataclass as an interface for DB Model. So DB Model will implement the behavior described by your DTO. But to make it work you will need a repository that can handle this. I describe a similar case in this blogpost: 4 ways of mapping Value Object in SQLAlchemy

Collectives™ on Stack Overflow

SQLAlchemy with dataclass default not populating postgres database

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related