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)

aW1wb3J0IHNxbGFsY2hlbXkgYXMgc2EKZnJvbSBzcWxhbGNoZW15IGltcG9ydCBvcm0KaW1wb3J0IHNxbGFsY2hlbXkuZXh0LmRlY2xhcmF0aXZlIGFzIGRlY2wKCmVuZ2luZSA9IHNhLmNyZWF0ZV9lbmdpbmUoCiAgICAnc3FsaXRlOi8vLzptZW1vcnk6JwoKICAgICMgVG8gc2VlIHRoZSBzYW1lIGNvZGUgd29ya2luZyBvbiBhIGRpZmZlcmVudCBlbmdpbmUsIHRyeSB0aGlzIHN0cmluZzoKICAgICMgJ3Bvc3RncmVzcWw6Ly9wb3N0Z3JlczpwYXNzd29yZEBsb2NhbGhvc3QvcG9zdGdyZXMnCiAgICAjIHdpdGggdGhpcyBkb2NrZXIgY29udGFpbmVyOgogICAgIyBkb2NrZXIgcnVuIC1kcCA1NDMyOjU0MzIgLWUgUE9TVEdSRVNfUEFTU1dPUkQ9cGFzc3dvcmQgcG9zdGdyZXM6MTAtYWxwaW5lCikKClNlc3Npb24gPSBvcm0uc2Vzc2lvbm1ha2VyKGJpbmQ9ZW5naW5lKQoKaWYgZW5naW5lLmRpYWxlY3QubmFtZSA9PSAncG9zdGdyZXNxbCc6CiAgICB3aXRoIGVuZ2luZS5jb25uZWN0KCkgYXMgYzoKICAgICAgICBjLmV4ZWN1dGUoJ0NSRUFURSBFWFRFTlNJT04gSUYgTk9UIEVYSVNUUyAidXVpZC1vc3NwIicpCiAgICAgICAgYy5leGVjdXRlKCIiIgogICAgICAgICAgICBDUkVBVEUgVEVNUCBUQUJMRSB0ZXN0X3RhYmxlKAogICAgICAgICAgICAgICAgaWQgVkFSQ0hBUiBQUklNQVJZIEtFWSBOT1QgTlVMTAogICAgICAgICAgICAgICAgICAgIERFRkFVTFQgdXVpZF9nZW5lcmF0ZV92NCgpOjp0ZXh0CiAgICAgICAgICAgICk7CiAgICAgICAgIiIiKQplbGlmIGVuZ2luZS5kaWFsZWN0Lm5hbWUgPT0gJ3NxbGl0ZSc6CiAgICBlbmdpbmUuZXhlY3V0ZSgiIiIKICAgICAgICBDUkVBVEUgVEFCTEUgdGVzdF90YWJsZSgKICAgICAgICAgICAgaWQgVkFSQ0hBUiBQUklNQVJZIEtFWSBOT1QgTlVMTAogICAgICAgICAgICAgICAgREVGQVVMVCAobG93ZXIoaGV4KHJhbmRvbWJsb2IoMTYpKSkpCiAgICAgICAgKTsKICAgICIiIikKCkJhc2UgPSBkZWNsLmRlY2xhcmF0aXZlX2Jhc2UoKQoKY2xhc3MgVGVzdFRhYmxlKEJhc2UpOgogICAgX190YWJsZW5hbWVfXyA9ICd0ZXN0X3RhYmxlJwogICAgaWQgPSBzYS5Db2x1bW4oCiAgICAgICAgICAgICAgc2EuVkFSQ0hBUiwKICAgICAgICAgICAgICBwcmltYXJ5X2tleT1UcnVlLAogICAgICAgICAgICAgIHNlcnZlcl9kZWZhdWx0PXNhLkZldGNoZWRWYWx1ZSgpKQoKCnNlc3MgPSBTZXNzaW9uKCkKdCA9IFRlc3RUYWJsZSgpCnNlc3MuYWRkKHQpCgojIHNxbGFsY2hlbXkub3JtLmV4Yy5GbHVzaEVycm9yOiBJbnN0YW5jZSA8VGVzdFRhYmxlIGF0IDB4N2Y2NmI0MWMwYTEwPiBoYXMgYQojIE5VTEwgaWRlbnRpdHkga2V5LiAgSWYgdGhpcyBpcyBhbiBhdXRvLWdlbmVyYXRlZCB2YWx1ZSwgY2hlY2sgdGhhdCB0aGUKIyBkYXRhYmFzZSB0YWJsZSBhbGxvd3MgZ2VuZXJhdGlvbiBvZiBuZXcgcHJpbWFyeSBrZXkgdmFsdWVzLCBhbmQgdGhhdCB0aGUKIyBtYXBwZWQgQ29sdW1uIG9iamVjdCBpcyBjb25maWd1cmVkIHRvIGV4cGVjdCB0aGVzZSBnZW5lcmF0ZWQgdmFsdWVzLiAgRW5zdXJlCiMgYWxzbyB0aGF0IHRoaXMgZmx1c2goKSBpcyBub3Qgb2NjdXJyaW5nIGF0IGFuIGluYXBwcm9wcmlhdGUgdGltZSwgc3VjaCBhcwojIHdpdGhpbiBhIGxvYWQoKSBldmVudC4Kc2Vzcy5jb21taXQoKQoKIyBPbmx5IGV2ZXIgZ2V0IHRoaXMgZmFyIG9uIHBvc3RncmVzCnByaW50KHQuaWQp