postgresql - How do I prevent sql alchemy from inserting the None value to field? -
the alembic migration script :
def upgrade(): uuid_gen = saexp.text("uuid generate v1mc()") op.create_table( 'foo', sa.column('uuid', uuid, primary_key=true, server_default=uuid_gen), sa.column( 'inserted', sa.datetime(timezone=true), server_default=sa.text("not null now()")) sa.column('data', sa.text) ) this base class sql alchemy:
class foo(base): __tablename__ = 'foo' inserted = column(timestamp) uuid = column(uuid, primary_key=true) data = column(text) it has static mehtod insert :
@staticmethod def insert(session, jsondata): foo = foo() foo.data = jsondata['data'] if 'inserted' in jsondata: foo.inserted = jsondata['inserted'] if 'uuid' in jsondata: foo.uuid = jsondata['uuid'] session.add(foo) return foo the purpose of 2 if's simplify testing. way can "inject" uuid , inserted date, predictible data tests
when trying insert data
foo = foo() foo.insert(session, {"data": "foo bar baz"}) session.commit() i integrityerror :
[sql: 'insert foo (inserted, data) values (%(inserted)s, %(data)s) returning foo.uuid'] [parameters: {'data': 'foo bar baz', 'inserted': none}] wich seem normal me because insert violates "not-null" constraint in postgres database.
how prevent sql alchemy inserting none value inserted field ?
while playing , testing around, found if "inserted" column defined primary key , sql alchemy not include field in insert statement.
def upgrade(): uuid_gen = saexp.text("uuid generate v1mc()") op.create_table( 'foo', sa.column('uuid', uuid, primary_key=true, server_default=uuid_gen), sa.column( 'inserted', primary_key=true, sa.datetime(timezone=true), server_default=sa.text("not null now()")) sa.column('data', sa.text) ) but not want.
the primary problem server_default missing in inserted member in class foo. it's present in alembic script. note alembic definitions only used when running migrations. not affect application. reason, it's idea copy exact same definitions alembic script application (or vice-versa).
because no value defined in model definition, sqlalchemy seems set none when class instantiated. sent db complain. fix this, either set default or server_default on model definition (the class inheriting base).
some additional notes/questions:
- where
uuid generate v1mc()come from? official docs different. replacedfunc.uuid_generate_v1mc(). - the
server_defaultvalue in case containsnot nullincorrect. should setnullable=falseon column attribute (see below).
alembic script
# revision identifiers, used alembic. revision = this_is_different_on_each_instance! # '1b7e145f2138' down_revision = none branch_labels = none depends_on = none alembic import op import sqlalchemy sa sqlalchemy.dialects.postgresql import uuid def upgrade(): op.create_table( 'foo', sa.column('uuid', uuid, primary_key=true, server_default=sa.func.uuid_generate_v1mc()), sa.column( 'inserted', sa.datetime(timezone=true), nullable=false, server_default=sa.text("now()")), sa.column('data', sa.text) ) def downgrade(): op.drop_table('foo') tester.py
from sqlalchemy.ext.declarative import declarative_base sqlalchemy import column, create_engine, func sqlalchemy.orm import scoped_session, sessionmaker sqlalchemy.dialects.postgresql import ( text, timestamp, uuid, ) engine = create_engine('postgresql://michel@/michel') session = scoped_session(sessionmaker(autocommit=false, autoflush=false, bind=engine)) base = declarative_base() class foo(base): __tablename__ = 'foo' inserted = column(timestamp, nullable=false, server_default=func.now()) uuid = column(uuid, primary_key=true, server_default=func.uuid_generate_v1mc()), data = column(text) @staticmethod def insert(session, jsondata): foo = foo() foo.data = jsondata['data'] if 'inserted' in jsondata: foo.inserted = jsondata['inserted'] if 'uuid' in jsondata: foo.uuid = jsondata['uuid'] session.add(foo) return foo if __name__ == '__main__': session = session() foo.insert(session, {"data": "foo bar baz"}) session.commit() session.close() output after execution
[9:43:54] michel@bbs-nexus [1 background job(s)] /home/users/michel/tmp› psql -c "select * foo" uuid | inserted | data --------------------------------------+-------------------------------+------------- 71f5fd32-0602-11e6-aebb-27be4bbac26e | 2016-04-19 09:43:45.297191+02 | foo bar baz (1 row) 
Comments
Post a Comment