import sqlalchemy as sa
from sqlalchemy import orm
import sqlalchemy.ext.declarative as decl

engine = sa.create_engine(
    'sqlite:///:memory:'

    # To see the same code working on a different engine, try this string:
    # 'postgresql://postgres:password@localhost/postgres'
    # with this docker container:
    # docker run -dp 5432:5432 -e POSTGRES_PASSWORD=password postgres:10-alpine
)

Session = orm.sessionmaker(bind=engine)

if engine.dialect.name == 'postgresql':
    with engine.connect() as c:
        c.execute('CREATE EXTENSION IF NOT EXISTS "uuid-ossp"')
        c.execute("""
            CREATE TEMP TABLE test_table(
                id VARCHAR PRIMARY KEY NOT NULL
                    DEFAULT uuid_generate_v4()::text
            );
        """)
elif engine.dialect.name == 'sqlite':
    engine.execute("""
        CREATE TABLE test_table(
            id VARCHAR PRIMARY KEY NOT NULL
                DEFAULT (lower(hex(randomblob(16))))
        );
    """)

Base = decl.declarative_base()

class TestTable(Base):
    __tablename__ = 'test_table'
    id = sa.Column(
              sa.VARCHAR,
              primary_key=True,
              server_default=sa.FetchedValue())


sess = Session()
t = TestTable()
sess.add(t)

# sqlalchemy.orm.exc.FlushError: Instance <TestTable at 0x7f66b41c0a10> has a
# NULL identity key.  If this is an auto-generated value, check that the
# database table allows generation of new primary key values, and that the
# mapped Column object is configured to expect these generated values.  Ensure
# also that this flush() is not occurring at an inappropriate time, such as
# within a load() event.
sess.commit()

# Only ever get this far on postgres
print(t.id)