How do I tell sqlalchemy to ignore certain (say, null) columns on INSERT
<p>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.</p>

<p>What I would <em>like</em> 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 <code>null</code> values that violate the DB constraints.</p>

<p>Here's an example of what I mean:</p>

<pre><code>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)
</code></pre>

<p>This barfs, because:</p>

<pre><code>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).
</code></pre>

<p>What I <em>expected</em> is that it would just skip out on the <code>fnord</code> column, since it didn't get set.</p>

<p>Even if I do:</p>

<pre><code>w = Whatever()
w.value = 'this breaks too'
</code></pre>

<p>or add:</p>

<pre><code>def __init__(self, value):
    self.value = value
</code></pre>

<p>to the <code>Whatever</code> class... still no dice.</p>

<p>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"? </p>

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

<p>Add a <a href="http://docs.sqlalchemy.org/en/latest/core/defaults.html#server-defaults" rel="nofollow">server side default</a> with <a href="http://docs.sqlalchemy.org/en/latest/core/metadata.html#sqlalchemy.schema.Column.params.server_default" rel="nofollow"><code>server_default</code></a> for <code>fnord</code>:</p>

<pre><code>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)
</code></pre>

<p>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 <a href="http://docs.sqlalchemy.org/en/latest/core/defaults.html#triggered-columns" rel="nofollow">through triggers</a>, stored procedures, or the like, have a look at <a href="http://docs.sqlalchemy.org/en/latest/core/defaults.html#sqlalchemy.schema.FetchedValue" rel="nofollow"><code>FetchedValue</code></a>.</p>

<p>A test with SQLite:</p>

<pre><code>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
</code></pre>

python postgresql sqlalchemy