Issue with transforming data on insert?

Question

So I was wondering if anyone could help me.

I currently have some class

class Sha1(ColumnClause):
    pass

@compiles(Sha1)
def compile_sha1(element, compiler, **kw):
    return 'PASSWORD("{}")'.format(element.name)

And when I use SQLAlchemy orm to insert a column I can pass the attribute as Sha1(value) and let it do its thing. However for testing purposes we load up our models into a database something like

insert = model.__table__.insert(values)
session.execute(insert)

Is there a way, in sqlalchemy, to override the way a specific model's sql is generated such that it will change the generated output from

insert into model (uuid, password) values (:uuid, :password)

to use my Sha1 class like

insert into model (uuid, password) values (:uuid, PASSWORD(:password))

Any ideas?

score 0 · Answer 1 · answered Jun 21 '13 at 19:32

There's a newer feature which allows this called bind_expression(). If i understand correctly, you probably want to change your Sha1 to accept an arbitrary element, you'd no longer need to call Sha1 explicitly, and in this case the argument would always be a BindParam:

from sqlalchemy import *
from sqlalchemy.sql.expression import ColumnElement
from sqlalchemy.ext.compiler import compiles

class Sha1(ColumnElement):
    def __init__(self, arg):
        self.arg = arg

@compiles(Sha1)
def compile_sha1(element, compiler, **kw):
    return 'PASSWORD(%s)' % compiler.process(element.arg, **kw)


class Sha1Type(TypeDecorator):
    impl = String

    def bind_expression(self, bindvalue):
        return Sha1(bindvalue)

m = MetaData()
t = Table('mytable', m,

        Column('id', Integer, primary_key=True),
        Column('password', Sha1Type)
    )


print t.insert()
print t.insert().values(password='some password')

Issue with transforming data on insert?

1 Answers1