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-15 23:42:59Z

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, 2016 at 23:42

answered Sep 2, 2010 at 18:56

dhaffey

1,3749 silver badges12 bronze badges

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

2 Comments

max Over a year ago

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 Over a year ago

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.

jkmacc · Accepted Answer · 2011-05-24 20:42:26Z

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, 2011 at 20:42

jkmacc

6,5973 gold badges33 silver badges30 bronze badges

1 Comment

jkmacc Over a year ago

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?

max · Accepted Answer · 2010-09-03 09:39:00Z

1

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.

answered Sep 3, 2010 at 9:39

max

52.7k60 gold badges224 silver badges379 bronze badges

Collectives™ on Stack Overflow

SQLAlchemy: avoiding repetition in declarative style class definition

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related