CODEWITHSUNDEEP
pythonsqlalchemy
Kiran Jonnalagadda
Kiran Jonnalagadda

Reputation: 2826

How do I make compound columns with SQLAlchemy declarative?

I frequently make models with Text columns that hold Markdown formatted richtext. My models look like this:

class Document(Base):
    id = Column(Integer, primary_key=True)
    title = Column(Unicode(250))
    description = Column(Text)
    description_html = Column(Text)

My edit forms (a) read from and write to description and then (b) write the Markdown formatted version to description_html. My (Jinja2) view templates (c) load the HTML version with {{ doc.description_html|safe }}.

I'd like to cut down these three recurring operations into one Column definition, like this:

class Document(Base):
    id = Column(Integer, primary_key=True)
    title = Column(Unicode(250))
    description = Column(MarkdownText)

Where MarkdownText is a new column type that:

  1. Makes two columns in the database table (description and description_html),
  2. Upon writes to the column, also writes a Markdown formatted version to the html column, and
  3. Provides a __html__() method that returns the contents of the html column. This will allow it to be used from a Jinja2 template as {{ doc.description }} without the safe filter.

Question: Is #1 possible? Can I define a column that makes two columns?

Upvotes: 4

Views: 1380

Answers (2)

plaes
plaes

Reputation: 32746

Here we go - now with composite columns:

from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, Text
from sqlalchemy.orm import composite, sessionmaker
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('sqlite:///')
session = sessionmaker(bind=engine)()
Base = declarative_base()

class MarkdownText(object):

    def __init__(self, text):
        self._text = text
        self._html = "<html>%s</html>" % text

    @classmethod
    def _from_db(cls, text, html):
        mt = MarkdownText(text)
        mt._html = html
        return mt

    def __composite_values__(self):
        return (self._text, self._html)

    def __str__(self):
        return self._text

    @property
    def __html__(self):
        return self._html

class Foo(Base):
    __tablename__ = 'foo'

    id = Column(Integer, primary_key=True)
    a = composite(MarkdownText._from_db,
                     Column('_text', Text),
                     Column('_html', Text))

    def __init__(self, a):
        self.a = MarkdownText(a)

    def __repr__(self):
        return '(%s)' % (self.a)

Base.metadata.create_all(engine)

session.add_all([Foo('test'), Foo('nips')])
session.commit()
x = session.query(Foo).all()
print x
print x[0].a.__html__
print x[0].a

And this gives us nicely:

[(test), (nips)]
<html>test</html>
test

Upvotes: 4

plaes
plaes

Reputation: 32746

Instead of answering to your bullet points, I better ask you this: "Do you really want to store both plain text and html text in database?". Here's how I would do it:

def text2html(text):
    # TODO: Implement me!
    pass

class Document(Base):
    id = Column(Integer, primary_key=True)
    title = Column(Unicode(250))
    description = Column(Text)

    @property
    def description_html(self):
        return text2html(self.description)

And in view the html description can be accessed just as document.description_html...

Upvotes: 2

Related Questions