Python sqlalchemy.orm.Session() Examples

def do_user_import(user_lookup: Dict[str, str]) -> Dict[str, User]:
    print("Importing users ... ", flush=True)
    with progressbar.ProgressBar(max_value=len(user_lookup)) as bar:
        for idx, (email, name) in enumerate(user_lookup.items()):
            session: Session = DbSession.factory()
            session.expire_on_commit = False

            user = User()
            user.email = email
            user.name = name
            session.add(user)

            session.commit()
            bar.update(idx)

    print()
    sys.stderr.flush()
    sys.stdout.flush()

    session: Session = DbSession.factory()
    return {u.email: u for u in session.query(User)} 

def upgrade():
    conn = op.get_bind()
    session = Session(bind=conn)

    op.alter_column('user', 'password_hash',
           existing_type=sa.VARCHAR(length=128),
           type_=sa.VARCHAR(length=200),
           existing_nullable=False)
    op.alter_column('user', 'pin_hash',
           existing_type=sa.VARCHAR(),
           type_=sa.VARCHAR(length=200),
           existing_nullable=False)
    session.commit()

    f = Fernet(current_app.config['PASSWORD_PEPPER'])
    for user in session.query(User).execution_options(show_all=True).all():
        if user.password_hash:
            user.password_hash = f.encrypt(user.password_hash.encode()).decode()
        if user.pin_hash:
            user.pin_hash = f.encrypt(user.pin_hash.encode()).decode()
    session.commit() 

def downgrade():
    conn = op.get_bind()
    session = Session(bind=conn)

    f = Fernet(current_app.config['PASSWORD_PEPPER'])

    for user in session.query(User).execution_options(show_all=True).all():
        if user.password_hash:
            user.password_hash = f.decrypt(user.password_hash.encode()).decode()
        if user.pin_hash:
            user.pin_hash = f.decrypt(user.pin_hash.encode()).decode()
    session.commit()

    op.alter_column('user', 'password_hash',
           existing_type=sa.VARCHAR(length=200),
           type_=sa.VARCHAR(length=128),
           existing_nullable=False)
    op.alter_column('user', 'pin_hash',
           existing_type=sa.VARCHAR(length=200),
           type_=sa.VARCHAR(),
           existing_nullable=False)
    session.commit() 

def upgrade():
    conn = op.get_bind()
    session = Session(bind=conn)

    op.add_column('organisation', sa.Column('external_auth_username', sa.String(), nullable=True))
    op.add_column('organisation', sa.Column('_external_auth_password', sa.String(), nullable=True))

    tcr = sa.sql.table('organisation',
                       sa.Column('id', sa.Integer, primary_key=True),
                       sa.Column('external_auth_username', sa.String(), nullable=True),
                       sa.Column('_external_auth_password', sa.String(), nullable=True),
                       sa.Column('name', sa.String(), nullable=True))

    for org in session.query(tcr).execution_options(show_all=True).all():
        org.external_auth_username = 'admin_'+(org.name or '').lower().replace(' ', '_')
        org._external_auth_password = encrypt_string(secrets.token_hex(16))
    session.commit() 

def do_import_licenses(file_data: List[dict]):
    imported = set()
    print("Importing licenses ... ", flush=True)
    with progressbar.ProgressBar(max_value=len(file_data)) as bar:
        for idx, p in enumerate(file_data):
            info = p.get('info')
            license_text = detect_license(info.get('license'))

            if license_text and license_text not in imported:
                imported.add(license_text)
                session: Session = DbSession.factory()

                package_license = License()
                package_license.id = license_text
                package_license.description = info.get('license')

                session.add(package_license)
                session.commit()

            bar.update(idx)

    sys.stderr.flush()
    sys.stdout.flush() 

def do_import_licenses(file_data: List[dict]):
    imported = set()
    print("Importing licenses ... ", flush=True)
    with progressbar.ProgressBar(max_value=len(file_data)) as bar:
        for idx, p in enumerate(file_data):
            info = p.get('info')
            license_text = detect_license(info.get('license'))

            if license_text and license_text not in imported:
                imported.add(license_text)
                session: Session = DbSession.factory()

                package_license = License()
                package_license.id = license_text
                package_license.description = info.get('license')

                session.add(package_license)
                session.commit()

            bar.update(idx)

    sys.stderr.flush()
    sys.stdout.flush() 

def do_user_import(user_lookup: Dict[str, str]) -> Dict[str, User]:
    print("Importing users ... ", flush=True)
    with progressbar.ProgressBar(max_value=len(user_lookup)) as bar:
        for idx, (email, name) in enumerate(user_lookup.items()):
            session: Session = DbSession.factory()
            session.expire_on_commit = False

            user = User()
            user.email = email
            user.name = name
            session.add(user)

            session.commit()
            bar.update(idx)

    print()
    sys.stderr.flush()
    sys.stdout.flush()

    session: Session = DbSession.factory()
    return {u.email: u for u in session.query(User)} 

def do_import_licenses(file_data: List[dict]):
    imported = set()
    print("Importing licenses ... ", flush=True)
    with progressbar.ProgressBar(max_value=len(file_data)) as bar:
        for idx, p in enumerate(file_data):
            info = p.get('info')
            license_text = detect_license(info.get('license'))

            if license_text and license_text not in imported:
                imported.add(license_text)
                session: Session = DbSession.factory()

                package_license = License()
                package_license.id = license_text
                package_license.description = info.get('license')

                session.add(package_license)
                session.commit()

            bar.update(idx)

    sys.stderr.flush()
    sys.stdout.flush() 

def do_import_licenses(file_data: List[dict]):
    imported = set()
    print("Importing licenses ... ", flush=True)
    with progressbar.ProgressBar(max_value=len(file_data)) as bar:
        for idx, p in enumerate(file_data):
            info = p.get('info')
            license_text = detect_license(info.get('license'))

            if license_text and license_text not in imported:
                imported.add(license_text)
                session: Session = DbSession.factory()

                package_license = License()
                package_license.id = license_text
                package_license.description = info.get('license')

                session.add(package_license)
                session.commit()

            bar.update(idx)

    sys.stderr.flush()
    sys.stdout.flush() 

def get_session():
    """
        Return SQLAlchemy session
    """

    global sessions

    if global_scope['db_file'] is None:
        raise RuntimeError('`db_file` is not defined in the global scope')

    # Create a unique key for the db session
    db_file = global_scope['db_file']

    # Add a session to the current list
    if not sessions.get(db_file):
        sessions[db_file] = Session(bind=get_engine())

    return sessions[db_file] 

def do_import_licenses(file_data: List[dict]):
    imported = set()
    print("Importing licenses ... ", flush=True)
    with progressbar.ProgressBar(max_value=len(file_data)) as bar:
        for idx, p in enumerate(file_data):
            info = p.get('info')
            license_text = detect_license(info.get('license'))

            if license_text and license_text not in imported:
                imported.add(license_text)
                session: Session = DbSession.factory()

                package_license = License()
                package_license.id = license_text
                package_license.description = info.get('license')

                session.add(package_license)
                session.commit()

            bar.update(idx)

    sys.stderr.flush()
    sys.stdout.flush() 

def do_user_import(user_lookup: Dict[str, str]) -> Dict[str, User]:
    print("Importing users ... ", flush=True)
    with progressbar.ProgressBar(max_value=len(user_lookup)) as bar:
        for idx, (email, name) in enumerate(user_lookup.items()):
            session: Session = DbSession.factory()
            session.expire_on_commit = False

            user = User()
            user.email = email
            user.name = name
            session.add(user)

            session.commit()
            bar.update(idx)

    print()
    sys.stderr.flush()
    sys.stdout.flush()

    session: Session = DbSession.factory()
    return {u.email: u for u in session.query(User)} 

def do_import_licenses(file_data: List[dict]):
    imported = set()
    print("Importing licenses ... ", flush=True)
    with progressbar.ProgressBar(max_value=len(file_data)) as bar:
        for idx, p in enumerate(file_data):
            info = p.get('info')
            license_text = detect_license(info.get('license'))

            if license_text and license_text not in imported:
                imported.add(license_text)
                session: Session = DbSession.factory()

                package_license = License()
                package_license.id = license_text
                package_license.description = info.get('license')

                session.add(package_license)
                session.commit()

            bar.update(idx)

    sys.stderr.flush()
    sys.stdout.flush() 

def upgrade():
    try:
        op.add_column('policy', sa.Column('adminuser', sa.Unicode(length=256), nullable=True))
    except Exception as exx:
        print('Adding of column "adminuser" in table policy failed: {!r}'.format(exx))
        print('This is expected behavior if this column already exists.')

    # Now that we added the column in the table, we can move the "user" from admin-policies to
    # the "adminuser" column

    try:
        bind = op.get_bind()
        session = orm.Session(bind=bind)
        pol_name = None
        for policy in session.query(Policy).filter(Policy.user != "", Policy.scope == "admin"):
            pol_name = policy.name
            # move the "user" to the "adminuser"
            policy.adminuser = policy.user
            policy.user = u""
        session.commit()
    except Exception as exx:
        session.rollback()
        print("Failed to migrate column adminuser in policies due to error in policy '{0!s}'.".format(pol_name))
        print(exx) 

def do_user_import(user_lookup: Dict[str, str]) -> Dict[str, User]:
    print("Importing users ... ", flush=True)
    with progressbar.ProgressBar(max_value=len(user_lookup)) as bar:
        for idx, (email, name) in enumerate(user_lookup.items()):
            session: Session = DbSession.factory()
            session.expire_on_commit = False

            user = User()
            user.email = email
            user.name = name
            session.add(user)

            session.commit()
            bar.update(idx)

    print()
    sys.stderr.flush()
    sys.stdout.flush()

    session: Session = DbSession.factory()
    return {u.email: u for u in session.query(User)} 

def mongoquery(cls, query_or_session: Union[Query, Session] = None) -> MongoQuery:
        """ Build a MongoQuery

        Note that when `None` is given, the resulting Query is not bound to any session!
        You'll have to bind it manually, after calling .end()

        :param query_or_session: Query to start with, or a session object to initiate the query with
        :type query_or_session: sqlalchemy.orm.Query | sqlalchemy.orm.Session | None
        :rtype: mongosql.MongoQuery
        """
        if query_or_session is None:
            query = Query([cls])
        elif isinstance(query_or_session, Session):
            query = query_or_session.query(cls)
        elif isinstance(query_or_session, Query):
            query = query_or_session
        else:
            raise ValueError('Argument must be Query or Session')

        return cls._get_mongoquery().from_query(query) 

def do_import_licenses(file_data: List[dict]):
    imported = set()
    print("Importing licenses ... ", flush=True)
    with progressbar.ProgressBar(max_value=len(file_data)) as bar:
        for idx, p in enumerate(file_data):
            info = p.get('info')
            license_text = detect_license(info.get('license'))

            if license_text and license_text not in imported:
                imported.add(license_text)
                session: Session = DbSession.factory()

                package_license = License()
                package_license.id = license_text
                package_license.description = info.get('license')

                session.add(package_license)
                session.commit()

            bar.update(idx)

    sys.stderr.flush()
    sys.stdout.flush() 

def do_user_import(user_lookup: Dict[str, str]) -> Dict[str, User]:
    print("Importing users ... ", flush=True)
    with progressbar.ProgressBar(max_value=len(user_lookup)) as bar:
        for idx, (email, name) in enumerate(user_lookup.items()):
            session: Session = DbSession.factory()
            session.expire_on_commit = False

            user = User()
            user.email = email
            user.name = name
            session.add(user)

            session.commit()
            bar.update(idx)

    print()
    sys.stderr.flush()
    sys.stdout.flush()

    session: Session = DbSession.factory()
    return {u.email: u for u in session.query(User)} 

def do_import_licenses(file_data: List[dict]):
    imported = set()
    print("Importing licenses ... ", flush=True)
    with progressbar.ProgressBar(max_value=len(file_data)) as bar:
        for idx, p in enumerate(file_data):
            info = p.get('info')
            license_text = detect_license(info.get('license'))

            if license_text and license_text not in imported:
                imported.add(license_text)
                session: Session = DbSession.factory()

                package_license = License()
                package_license.id = license_text
                package_license.description = info.get('license')

                session.add(package_license)
                session.commit()

            bar.update(idx)

    sys.stderr.flush()
    sys.stdout.flush() 

def do_user_import(user_lookup: Dict[str, str]) -> Dict[str, User]:
    print("Importing users ... ", flush=True)
    with progressbar.ProgressBar(max_value=len(user_lookup)) as bar:
        for idx, (email, name) in enumerate(user_lookup.items()):
            session: Session = DbSession.factory()
            session.expire_on_commit = False

            user = User()
            user.email = email
            user.name = name
            session.add(user)

            session.commit()
            bar.update(idx)

    print()
    sys.stderr.flush()
    sys.stdout.flush()

    session: Session = DbSession.factory()
    return {u.email: u for u in session.query(User)} 

def do_import_licenses(file_data: List[dict]):
    imported = set()
    print("Importing licenses ... ", flush=True)
    with progressbar.ProgressBar(max_value=len(file_data)) as bar:
        for idx, p in enumerate(file_data):
            info = p.get('info')
            license_text = detect_license(info.get('license'))

            if license_text and license_text not in imported:
                imported.add(license_text)
                session: Session = DbSession.factory()

                package_license = License()
                package_license.id = license_text
                package_license.description = info.get('license')

                session.add(package_license)
                session.commit()

            bar.update(idx)

    sys.stderr.flush()
    sys.stdout.flush() 

def do_import_licenses(file_data: List[dict]):
    imported = set()
    print("Importing licenses ... ", flush=True)
    with progressbar.ProgressBar(max_value=len(file_data)) as bar:
        for idx, p in enumerate(file_data):
            info = p.get('info')
            license_text = detect_license(info.get('license'))

            if license_text and license_text not in imported:
                imported.add(license_text)
                session: Session = DbSession.factory()

                package_license = License()
                package_license.id = license_text
                package_license.description = info.get('license')

                session.add(package_license)
                session.commit()

            bar.update(idx)

    sys.stderr.flush()
    sys.stdout.flush() 

def do_import_licenses(file_data: List[dict]):
    imported = set()
    print("Importing licenses ... ", flush=True)
    with progressbar.ProgressBar(max_value=len(file_data)) as bar:
        for idx, p in enumerate(file_data):
            info = p.get('info')
            license_text = detect_license(info.get('license'))

            if license_text and license_text not in imported:
                imported.add(license_text)
                session: Session = DbSession.factory()

                package_license = License()
                package_license.id = license_text
                package_license.description = info.get('license')

                session.add(package_license)
                session.commit()

            bar.update(idx)

    sys.stderr.flush()
    sys.stdout.flush() 

def do_user_import(user_lookup: Dict[str, str]) -> Dict[str, User]:
    print("Importing users ... ", flush=True)
    with progressbar.ProgressBar(max_value=len(user_lookup)) as bar:
        for idx, (email, name) in enumerate(user_lookup.items()):
            session: Session = DbSession.factory()
            session.expire_on_commit = False

            user = User()
            user.email = email
            user.name = name
            session.add(user)

            session.commit()
            bar.update(idx)

    print()
    sys.stderr.flush()
    sys.stdout.flush()

    session: Session = DbSession.factory()
    return {u.email: u for u in session.query(User)} 

def do_import_licenses(file_data: List[dict]):
    imported = set()
    print("Importing licenses ... ", flush=True)
    with progressbar.ProgressBar(max_value=len(file_data)) as bar:
        for idx, p in enumerate(file_data):
            info = p.get('info')
            license_text = detect_license(info.get('license'))

            if license_text and license_text not in imported:
                imported.add(license_text)
                session: Session = DbSession.factory()

                package_license = License()
                package_license.id = license_text
                package_license.description = info.get('license')

                session.add(package_license)
                session.commit()

            bar.update(idx)

    sys.stderr.flush()
    sys.stdout.flush() 

def do_import_licenses(file_data: List[dict]):
    imported = set()
    print("Importing licenses ... ", flush=True)
    with progressbar.ProgressBar(max_value=len(file_data)) as bar:
        for idx, p in enumerate(file_data):
            info = p.get('info')
            license_text = detect_license(info.get('license'))

            if license_text and license_text not in imported:
                imported.add(license_text)
                session: Session = DbSession.factory()

                package_license = License()
                package_license.id = license_text
                package_license.description = info.get('license')

                session.add(package_license)
                session.commit()

            bar.update(idx)

    sys.stderr.flush()
    sys.stdout.flush() 

def do_import_licenses(file_data: List[dict]):
    imported = set()
    print("Importing licenses ... ", flush=True)
    with progressbar.ProgressBar(max_value=len(file_data)) as bar:
        for idx, p in enumerate(file_data):
            info = p.get('info')
            license_text = detect_license(info.get('license'))

            if license_text and license_text not in imported:
                imported.add(license_text)
                session: Session = DbSession.factory()

                package_license = License()
                package_license.id = license_text
                package_license.description = info.get('license')

                session.add(package_license)
                session.commit()

            bar.update(idx)

    sys.stderr.flush()
    sys.stdout.flush() 

def upgrade():
    try:
        with op.batch_alter_table("smsgatewayoption") as batch_op:
            batch_op.drop_constraint('sgix_1', type_='unique')
            batch_op.create_unique_constraint('sgix_1', ['gateway_id', 'Key', 'Type'])
    except Exception as exx:
        print("Cannot change constraint 'sgix_1' in table smsgatewayoption.")
        print(exx)

    try:
        bind = op.get_bind()
        session = orm.Session(bind=bind)
        # add default type 'option' for all rows
        for row in session.query(SMSGatewayOption):
            if not row.Type:
                row.Type = "option"

    except Exception as exx:
        session.rollback()
        print("Failed to add option type for all existing entries in table smsgatewayoption!")
        print(exx)

    session.commit() 

def upgrade():
    bind = op.get_bind()
    session = orm.Session(bind=bind)
    try:
        # Step 1: Create sequence on Postgres
        seq = sa.Sequence('eventcounter_seq')
        try:
            create_seq(seq)
        except Exception as _e:
            pass
        # Step 2: Create new eventcounter_new table
        op.create_table('eventcounter_new',
                        sa.Column("id", sa.Integer, sa.Sequence("eventcounter_seq"), primary_key=True),
                        sa.Column("counter_name", sa.Unicode(80), nullable=False),
                        sa.Column("counter_value", sa.Integer, default=0),
                        sa.Column("node", sa.Unicode(255), nullable=False),
                        sa.UniqueConstraint('counter_name', 'node', name='evctr_1'),
                        mysql_row_format='DYNAMIC'
                        )
        # Step 3: Migrate data from eventcounter to eventcounter_new
        node = get_privacyidea_node()
        for old_ctr in session.query(OldEventCounter).all():
            new_ctr = NewEventCounter(counter_name=old_ctr.counter_name,
                                      counter_value=old_ctr.counter_value,
                                      node=node)
            session.add(new_ctr)
            print("Migrating counter {!r}={} on node={!r} ...".format(new_ctr.counter_name,
                                                                      new_ctr.counter_value,
                                                                      node))
        session.commit()
        # Step 4: Remove eventcounter
        op.drop_table("eventcounter")
        op.rename_table("eventcounter_new", "eventcounter")
    except Exception as exx:
        session.rollback()
        print("Could not migrate table 'eventcounter'")
        print (exx) 

def downgrade():
    # Delete the policy, if it still exists
    bind = op.get_bind()
    session = orm.Session(bind=bind)
    session.query(Policy).filter(Policy.name == u"{0!s}".format(POLICYNAME)).delete()
    session.commit()