CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
PaszaVonPomiot
PaszaVonPomiot

Reputation: 189

SQLAlchemy ORM to convert input data on insert

I would like to load records to Postgres using SQLAlchemy ORM but I get this error: psycopg2.ProgrammingError: can't adapt type 'lxml.objectify.StringElement'

I have the model:

class B2bProduct(Base):
    __tablename__ = 'b2b_product'
    code = Column(String, primary_key=True)

And when I'm attempting to insert product list (list of dicts where values are of type'lxml.objectify.StringElement'):

with session_scope() as s:
    s.bulk_insert_mappings(B2bProduct, prod_list)

I get this error:

psycopg2.ProgrammingError: can't adapt type 'lxml.objectify.StringElement'

As a workaround I can cast all values to Python String before bulk insert. However I would much more prefer casting to take place in B2bProduct class definition. Is it possible to use declarative ORM so that it will automatically convert any value I give it to Python String (before actually inserting)?

Something like this:

code = Column(String, primary_key=True, convert_to_string=True)

I don't know but I might be asking about TypeEngine that will do the conversion. Can you advise?

Upvotes: 1

Views: 1349

Answers (1)

PaszaVonPomiot
PaszaVonPomiot

Reputation: 189

So I found the answer is TypeDecorator as hinted by Raito. Thanks.

class Float2(types.TypeDecorator):
    ''' Float Type that replaces commas with  dots on input '''
    impl = types.Float
    def process_bind_param(self, value, dialect):  # insert
        return value.replace(',','.')
    def process_result_value(self, value, dialect):  # select
        return value

Upvotes: 2

Related Questions