SQLAlchemy: avoiding repetition in declarative style class definition

Question

I'm using SQLAlchemy, and many classes in my object model have the same two attributes: id and (integer & primary key), and name (a string). I'm trying to avoid declaring them in every class like so:

class C1(declarative_base()):
    id = Column(Integer, primary_key = True)
    name = Column(String)
    #...

class C2(declarative_base()):
    id = Column(Integer, primary_key = True)
    name = Column(String)
    #...

What's a good way to do that? I tried using metaclasses but it didn't work yet.

dhaffey · Accepted Answer · 2016-07-15T23:42:59.117

14

You could factor out your common attributes into a mixin class, and multiply inherit it alongside declarative_base():

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

class IdNameMixin(object):
    id = Column(Integer, primary_key=True)
    name = Column(String)

class C1(declarative_base(), IdNameMixin):
    __tablename__ = 'C1'

class C2(declarative_base(), IdNameMixin):
    __tablename__ = 'C2'

print C1.__dict__['id'] is C2.__dict__['id']
print C1.__dict__['name'] is C2.__dict__['name']

EDIT: You might think this would result in C1 and C2 sharing the same Column objects, but as noted in the SQLAlchemy docs, Column objects are copied when originating from a mixin class. I've updated the code sample to demonstrate this behavior.

edited Jul 15 '16 at 23:42

answered Sep 02 '10 at 18:56

dhaffey

1,354
9
12

Unfortunately, this is not going to work because the id attribute would then be shared among all the subclasses of IdNameMixin. In SQLAlchemy, each class must have its own id (a newly created object of class Column). – max Sep 03 '10 at 08:10
Ah very cool!! Thank you. Now if only I could do something about the `__tablename__`, which definitely does have to be unique :) But I like your approach with the mixin class better than my metaclass modification. – max Sep 03 '10 at 17:49

score 2 · Answer 2 · answered May 24 '11 at 20:42

2

Could you also use the Column's copy method? This way, fields can be defined independently of tables, and those fields that are reused are just field.copy()-ed.

id = Column(Integer, primary_key = True)
name = Column(String)

class C1(declarative_base()):
    id = id.copy()
    name = name.copy()
    #...

class C2(declarative_base()):
    id = id.copy()
    name = name.copy()
    #...

answered May 24 '11 at 20:42

jkmacc

6,125
3
30
27

1

Anyone know how to alter properties of those columns in different table classes, then? That is, if id isn't primary in C1, but is primary in C2, how does one make that distinction without the repetition we're avoiding? – jkmacc Nov 04 '11 at 16:23

score 1 · Answer 3 · answered Sep 03 '10 at 09:39

I think I got it to work.

I created a metaclass that derives from DeclarativeMeta, and made that the metaclass of C1 and C2. In that new metaclass, I simply said

def __new__(mcs, name, base, attr):
  attr['__tablename__'] = name.lower()
  attr['id'] = Column(Integer, primary_key = True)
  attr['name'] = Column(String)
  return super().__new__(mcs, name, base, attr)

And it seems to work fine.

SQLAlchemy: avoiding repetition in declarative style class definition

3 Answers3

Linked