How do I tell sqlalchemy to ignore certain (say, null) columns on INSERT

Question

I have a legacy database that creates default values for several columns using a variety of stored procedures. It would be more or less prohibitive to try and track down the names and add queries to my code, not to mention a maintenance nightmare.

What I would like is to be able to tell sqlalchemy to ignore the columns that I don't really care about. Unfortunately, it doesn't. Instead it provides null values that violate the DB constraints.

Here's an example of what I mean:

import sqlalchemy as sa
import logging
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

l = logging.getLogger('sqlalchemy.engine')
l.setLevel(logging.INFO)
l.addHandler(logging.StreamHandler())




engine = sa.create_engine('postgresql+psycopg2://user@host:port/dbname')

Session = sessionmaker(bind=engine)
session = Session()
temp_metadata = sa.MetaData(schema='pg_temp')
TempBase = declarative_base(metadata=temp_metadata)

with session.begin(subtransactions=True):
    session.execute('''
        CREATE TABLE pg_temp.whatevs (
            id      serial
          , fnord   text not null default 'fnord'
          , value   text not null
        );

        INSERT INTO pg_temp.whatevs (value) VALUES ('something cool');
    ''')

    class Whatever(TempBase):
        __tablename__ = 'whatevs'

        id = sa.Column('id', sa.Integer, primary_key=True, autoincrement=True)
        fnord = sa.Column('fnord', sa.String)
        value = sa.Column('value', sa.String)

    w = Whatever(value='something cool')
    session.add(w)

This barfs, because:

INSERT INTO pg_temp.whatevs (fnord, value) VALUES (%(fnord)s, %(value)s) RETURNING pg_temp.whatevs.id
{'fnord': None, 'value': 'something cool'}
ROLLBACK
Traceback (most recent call last):
  File "/home/wayne/.virtualenvs/myenv/lib64/python3.5/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/home/wayne/.virtualenvs/myenv/lib64/python3.5/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
psycopg2.IntegrityError: null value in column "fnord" violates not-null constraint
DETAIL:  Failing row contains (2, null, something cool).

What I expected is that it would just skip out on the fnord column, since it didn't get set.

Even if I do:

w = Whatever()
w.value = 'this breaks too'

or add:

def __init__(self, value):
    self.value = value

to the Whatever class... still no dice.

How can I tell sqlalchemy that "look, these other columns are fine, I know I'm not providing a value - the database is going to take care of that for me. It's okay, just don't worry about these columns"?

The only way I'm aware of is to futz with the class definition and lie, saying those columns don't exist... but I do actually want them to come in on queries.

Answer 1

Add a server side default with server_default for fnord:

class Whatever(TempBase):
    __tablename__ = 'whatevs'

    id = sa.Column(sa.Integer, primary_key=True, autoincrement=True)
    fnord = sa.Column(sa.String, nullable=False, server_default='fnord')
    value = sa.Column(sa.String, nullable=False)

SQLAlchemy quite happily lets the default do its thing server side, if just told about it. If you have columns that do not have a default set in the DDL, but through triggers, stored procedures, or the like, have a look at FetchedValue.

A test with SQLite:

In [8]: engine.execute("""CREATE TABLE whatevs (
   ...:  id INTEGER NOT NULL,
   ...:  fnord VARCHAR DEFAULT 'fnord' NOT NULL,
   ...:  value VARCHAR NOT NULL,
   ...:  PRIMARY KEY (id)
   ...: )""")

In [12]: class Whatever(Base):
    ...:     __tablename__ = 'whatevs'
    ...:     id = Column(Integer, primary_key=True, autoincrement=True)
    ...:     fnord = Column(String, nullable=False, server_default="fnord")
    ...:     value = Column(String, nullable=False)
    ...:     

In [13]: session.add(Whatever(value='asdf'))

In [14]: session.commit()
2016-08-31 23:46:09,826 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
2016-08-31 23:46:09,827 INFO sqlalchemy.engine.base.Engine INSERT INTO whatevs (value) VALUES (?)
INFO:sqlalchemy.engine.base.Engine:INSERT INTO whatevs (value) VALUES (?)
2016-08-31 23:46:09,827 INFO sqlalchemy.engine.base.Engine ('asdf',)
INFO:sqlalchemy.engine.base.Engine:('asdf',)
2016-08-31 23:46:09,828 INFO sqlalchemy.engine.base.Engine COMMIT
INFO:sqlalchemy.engine.base.Engine:COMMIT