Python sqlalchemy.orm.scoped_session() Examples

def init(url):
        DBManager.engine = create_engine(url, pool_pre_ping=True, pool_size=10, max_overflow=20)

        # https://docs.sqlalchemy.org/en/13/core/events.html#sqlalchemy.events.PoolEvents.connect
        @event.listens_for(DBManager.engine, "connect")
        def on_connect(dbapi_connection, connection_record):
            # http://initd.org/psycopg/docs/connection.html#connection.notices
            # > The notices attribute is writable: the user may replace it with any Python object
            # > exposing an append() method. If appending raises an exception the notice is silently dropped.
            # This replaces the list object with a logger that logs the incoming notices
            dbapi_connection.notices = ServerNoticeLogger()

        DBManager.Session = sessionmaker(bind=DBManager.engine, autoflush=False)
        DBManager.ScopedSession = scoped_session(sessionmaker(bind=DBManager.engine)) 

def make_session(engines, force_scope=False, info=None):
    if force_scope:
        scopefunc = scope_func
    else:
        scopefunc = None

    session = scoped_session(
        sessionmaker(
            class_=RoutingSession,
            expire_on_commit=False,
            engines=engines,
            info=info or {"name": uuid.uuid4().hex},
        ),
        scopefunc=scopefunc
    )
    return session 

def url(self, val):
        self._url = val
        self.engine = create_engine(val)
        if self.is_sqlite:
            import sqlite3
            sqlite3.register_adapter(str, lambda s: s.decode('utf8'))
        session_factory = sessionmaker(self.engine)
        self.session = scoped_session(session_factory) 

def DumpMLPResult(id, srvname, mlpjson, result):
    InitDB(srvname)
    engine = create_engine(DBPath(srvname))
    Base.metadata.bind = engine
    DBSession = sessionmaker(bind=engine)
    session = scoped_session(DBSession)
    try:
        md = MLPDump(id=id, mlpjson=json.dumps(mlpjson), result=result, createdon=datetime.utcnow())
        session.add(md)
        session.commit()
    except Exception as e:
        session.rollback()
        raise 

def GetPipelineDump(id, srvname):
    result = None
    try:
        engine = create_engine(DBPath(srvname))
        Base.metadata.bind = engine

        DBSession = sessionmaker(bind=engine)
        session = scoped_session(DBSession)
        result = session.query(PipelineDump).filter(PipelineDump.id == id).one()
    except NoResultFound as e:
        result = None

    return result 

def GetMLPDump(id, srvname):
    result = None
    try:
        engine = create_engine(DBPath(srvname))
        Base.metadata.bind = engine

        DBSession = sessionmaker(bind=engine)
        session = scoped_session(DBSession)
        result = session.query(MLPDump).filter(MLPDump.id == id).one()
    except NoResultFound as e:
        result = None

    return result 

def make_session(self, engine):
        """
        To use: session = self.make_session(engine)
        """
        self.engine = engine
        try:
            Session = scoped_session(sessionmaker())
            Session.configure(bind=self.engine)
        except SQLAlchemyError as e:
            logger.error(f'Error creating SQL session manager {e}')
        return Session() 

def initialize(self):
        initialize_sql(self.engine)
        self.session[threading.get_ident()] = scoped_session(grinbase.model.session_factory) 

def initializeSession(self):
        self.session[threading.get_ident()]  = scoped_session(grinbase.model.session_factory) 

def __init__(self):
        self.engine = create_engine('sqlite:///:memory:')
        self.session = scoped_session(sessionmaker(self.engine)) 

def get_session(connection=None):
    """Get a new db session."""
    session = scoped_session(sessionmaker(bind=engine))
    return session 

def DumpPipelineResult(id, srvname, pipeline, result):
    InitDB(srvname)
    engine = create_engine(DBPath(srvname))
    Base.metadata.bind = engine
    DBSession = sessionmaker(bind=engine)
    session = scoped_session(DBSession)
    try:
        pd = PipelineDump(id=id, pipeline=json.dumps(pipeline), result=result, createdon=datetime.utcnow())
        session.add(pd)
        session.commit()
    except Exception as e:
        session.rollback()
        raise 

def session(cls):
        """
        :rtype: scoped_session | Session
        """
        if cls._session is not None:
            return cls._session
        else:
            raise NoSessionError('Cant get session.'
                                 'Please, call SaActiveRecord.set_session()') 

def set_session(cls, session):
        """
        :type session: scoped_session | Session
        """
        cls._session = session 

def reset_session():
    session = scoped_session(sessionmaker(bind=engine))
    BaseModel.set_session(session)
    return session

#################### setup some data ###################### 

def create_session_factory(self):
        """Create the SQLA Session factory

        in function of the Core Session class ans the Core Qery class
        """
        if self.Session is None or self.must_recreate_session_factory():
            bind = self.bind
            if self.Session:
                if not self.withoutautomigration:
                    # this is the only case to use commit in the construction
                    # of the registry
                    self.commit()

                # remove all existing instance to create a new instance
                # because the instance are cached
                self.Session.remove()

            query_bases = [] + self.loaded_cores['Query']
            query_bases += [self.registry_base]
            Query = type('Query', tuple(query_bases), {})
            session_bases = [self.registry_base] + self.loaded_cores['Session']
            Session = type('Session', tuple(session_bases), {
                'registry_query': Query})

            extension = self.additional_setting.get('sa.session.extension')
            if extension:
                extension = extension()
            self.Session = scoped_session(
                sessionmaker(bind=bind, class_=Session, extension=extension),
                EnvironmentManager.scoped_function_for_session())

            self.nb_query_bases = len(self.loaded_cores['Query'])
            self.nb_session_bases = len(self.loaded_cores['Session'])
            self.apply_session_events()
        else:
            self.flush() 

def setup_class(cls):
        if not tests.is_datastore_supported():
            raise nose.SkipTest("Datastore not supported")
        p.load('datastore')
        ctd.CreateTestData.create()
        cls.sysadmin_user = model.User.get('testsysadmin')
        cls.normal_user = model.User.get('annafan')
        resource = model.Package.get('annakarenina').resources[0]
        cls.data = {
            'resource_id': resource.id,
            'aliases': u'b\xfck2',
            'fields': [{'id': 'book', 'type': 'text'},
                       {'id': 'author', 'type': 'text'},
                       {'id': 'rating with %', 'type': 'text'}],
            'records': [{'book': 'annakarenina', 'author': 'tolstoy',
                         'rating with %': '90%'},
                        {'book': 'warandpeace', 'author': 'tolstoy',
                         'rating with %': '42%'}]
        }

        engine = db._get_engine(
            {'connection_url': config['ckan.datastore.write_url']})
        cls.Session = orm.scoped_session(orm.sessionmaker(bind=engine))
        set_url_type(
            model.Package.get('annakarenina').resources, cls.sysadmin_user) 

def setup_class(cls):

        wsgiapp = middleware.make_app(config['global_conf'], **config)
        cls.app = paste.fixture.TestApp(wsgiapp)
        if not tests.is_datastore_supported():
            raise nose.SkipTest("Datastore not supported")
        p.load('datastore')
        ctd.CreateTestData.create()
        cls.sysadmin_user = model.User.get('testsysadmin')
        cls.normal_user = model.User.get('annafan')
        engine = db._get_engine(
            {'connection_url': config['ckan.datastore.write_url']})
        cls.Session = orm.scoped_session(orm.sessionmaker(bind=engine))
        set_url_type(
            model.Package.get('annakarenina').resources, cls.sysadmin_user) 

def _execute_sql(self, sql, *args):
        engine = db._get_engine(
            {'connection_url': config['ckan.datastore.write_url']})
        session = orm.scoped_session(orm.sessionmaker(bind=engine))
        return session.connection().execute(sql, *args) 

def setup_class(cls):
        if not tests.is_datastore_supported():
            raise nose.SkipTest("Datastore not supported")
        p.load('datastore')
        ctd.CreateTestData.create()
        cls.sysadmin_user = model.User.get('testsysadmin')
        cls.normal_user = model.User.get('annafan')
        set_url_type(
            model.Package.get('annakarenina').resources, cls.sysadmin_user)
        resource = model.Package.get('annakarenina').resources[0]
        hhguide = u"hitchhiker's guide to the galaxy"
        cls.data = {
            'resource_id': resource.id,
            'fields': [{'id': u'b\xfck', 'type': 'text'},
                       {'id': 'author', 'type': 'text'},
                       {'id': 'nested', 'type': 'json'},
                       {'id': 'characters', 'type': 'text[]'},
                       {'id': 'published'}],
            'primary_key': u'b\xfck',
            'records': [{u'b\xfck': 'annakarenina', 'author': 'tolstoy',
                        'published': '2005-03-01', 'nested': ['b', {'moo': 'moo'}]},
                        {u'b\xfck': 'warandpeace', 'author': 'tolstoy',
                        'nested': {'a':'b'}},
                        {'author': 'adams',
                        'characters': ['Arthur Dent', 'Marvin'],
                        'nested': {'foo': 'bar'},
                        u'b\xfck': hhguide}
                       ]
            }
        postparams = '%s=1' % json.dumps(cls.data)
        auth = {'Authorization': str(cls.sysadmin_user.apikey)}
        res = cls.app.post('/api/action/datastore_create', params=postparams,
                           extra_environ=auth)
        res_dict = json.loads(res.body)
        assert res_dict['success'] is True

        engine = db._get_engine(
            {'connection_url': config['ckan.datastore.write_url']})
        cls.Session = orm.scoped_session(orm.sessionmaker(bind=engine)) 

def setup_class(cls):
        if not tests.is_datastore_supported():
            raise nose.SkipTest("Datastore not supported")
        p.load('datastore')
        ctd.CreateTestData.create()
        cls.sysadmin_user = model.User.get('testsysadmin')
        cls.normal_user = model.User.get('annafan')
        set_url_type(
            model.Package.get('annakarenina').resources, cls.sysadmin_user)
        resource = model.Package.get('annakarenina').resources[0]
        cls.data = {
            'resource_id': resource.id,
            'fields': [{'id': u'b\xfck', 'type': 'text'},
                       {'id': 'author', 'type': 'text'},
                       {'id': 'nested', 'type': 'json'},
                       {'id': 'characters', 'type': 'text[]'},
                       {'id': 'published'}],
            'primary_key': u'b\xfck',
            'records': [{u'b\xfck': 'annakarenina', 'author': 'tolstoy',
                        'published': '2005-03-01', 'nested': ['b', {'moo': 'moo'}]},
                        {u'b\xfck': 'warandpeace', 'author': 'tolstoy',
                        'nested': {'a':'b'}}
                       ]
            }
        postparams = '%s=1' % json.dumps(cls.data)
        auth = {'Authorization': str(cls.sysadmin_user.apikey)}
        res = cls.app.post('/api/action/datastore_create', params=postparams,
                           extra_environ=auth)
        res_dict = json.loads(res.body)
        assert res_dict['success'] is True

        engine = db._get_engine(
            {'connection_url': config['ckan.datastore.write_url']})
        cls.Session = orm.scoped_session(orm.sessionmaker(bind=engine)) 

def setup_class(cls):
        if not tests.is_datastore_supported():
            raise nose.SkipTest("Datastore not supported")
        p.load('datastore')
        ctd.CreateTestData.create()
        cls.sysadmin_user = model.User.get('testsysadmin')
        cls.normal_user = model.User.get('annafan')
        set_url_type(
            model.Package.get('annakarenina').resources, cls.sysadmin_user)
        resource = model.Package.get('annakarenina').resources[0]
        cls.data = {
            'resource_id': resource.id,
            'fields': [{'id': u'b\xfck', 'type': 'text'},
                       {'id': 'author', 'type': 'text'},
                       {'id': 'nested', 'type': 'json'},
                       {'id': 'characters', 'type': 'text[]'},
                       {'id': 'published'}],
            'primary_key': u'b\xfck',
            'records': [{u'b\xfck': 'annakarenina', 'author': 'tolstoy',
                        'published': '2005-03-01', 'nested': ['b', {'moo': 'moo'}]},
                        {u'b\xfck': 'warandpeace', 'author': 'tolstoy',
                        'nested': {'a':'b'}}
                       ]
            }
        postparams = '%s=1' % json.dumps(cls.data)
        auth = {'Authorization': str(cls.sysadmin_user.apikey)}
        res = cls.app.post('/api/action/datastore_create', params=postparams,
                           extra_environ=auth)
        res_dict = json.loads(res.body)
        assert res_dict['success'] is True

        engine = db._get_engine(
            {'connection_url': config['ckan.datastore.write_url']})
        cls.Session = orm.scoped_session(orm.sessionmaker(bind=engine)) 

def setup_class(cls):
        wsgiapp = middleware.make_app(config['global_conf'], **config)
        cls.app = paste.fixture.TestApp(wsgiapp)
        if not tests.is_datastore_supported():
            raise nose.SkipTest("Datastore not supported")
        p.load('datastore')
        p.load('datapusher')
        ctd.CreateTestData.create()
        cls.sysadmin_user = model.User.get('testsysadmin')
        cls.normal_user = model.User.get('annafan')
        engine = db._get_engine(
            {'connection_url': config['ckan.datastore.write_url']})
        cls.Session = orm.scoped_session(orm.sessionmaker(bind=engine))
        set_url_type(
            model.Package.get('annakarenina').resources, cls.sysadmin_user) 

def setup_class(cls):
        wsgiapp = middleware.make_app(config['global_conf'], **config)
        cls.app = paste.fixture.TestApp(wsgiapp)
        if not tests.is_datastore_supported():
            raise nose.SkipTest("Datastore not supported")
        p.load('datastore')
        p.load('datapusher')
        p.load('test_datapusher_plugin')

        resource = factories.Resource(url_type='datastore')
        cls.dataset = factories.Dataset(resources=[resource])

        cls.sysadmin_user = factories.User(name='testsysadmin', sysadmin=True)
        cls.normal_user = factories.User(name='annafan')
        engine = db._get_engine(
            {'connection_url': config['ckan.datastore.write_url']})
        cls.Session = orm.scoped_session(orm.sessionmaker(bind=engine)) 

def refreshdb(dump_file_path):
    """
    Refresh/Load DB data before tests; also exec mysqldump to write a
    SQL dump file for faster refreshes during test runs.
    """
    if 'NO_REFRESH_DB' not in os.environ:
        # setup the connection
        conn = get_db_engine().connect()
        logger.info('Refreshing DB (session-scoped)')
        # clean the database
        biweeklybudget.models.base.Base.metadata.drop_all(get_db_engine())
        biweeklybudget.models.base.Base.metadata.create_all(get_db_engine())
        # load the sample data
        data_sess = scoped_session(
            sessionmaker(autocommit=False, autoflush=False, bind=conn)
        )
        SampleDataLoader(data_sess).load()
        data_sess.flush()
        data_sess.commit()
        data_sess.close()
        # close connection
        conn.close()
    else:
        logger.info('Skipping session-scoped DB refresh')
    # write the dump files
    do_mysqldump(dump_file_path, get_db_engine())
    do_mysqldump(dump_file_path, get_db_engine(), with_data=False)
    yield 

def _refreshdb(self):
        """
        Refresh/Load DB data before tests
        """
        if 'NO_REFRESH_DB' in os.environ:
            logger.info('Skipping session-scoped DB refresh')
            return
        # setup the connection
        conn = engine.connect()
        logger.info('Refreshing DB (session-scoped)')
        # clean the database
        biweeklybudget.models.base.Base.metadata.reflect(engine)
        biweeklybudget.models.base.Base.metadata.drop_all(engine)
        biweeklybudget.models.base.Base.metadata.create_all(engine)
        # load the sample data
        data_sess = scoped_session(
            sessionmaker(autocommit=False, autoflush=False, bind=conn)
        )
        SampleDataLoader(data_sess).load()
        data_sess.flush()
        data_sess.commit()
        data_sess.close()
        conn.close()
        logger.info('DB refreshed.') 

def _credit_payoff_postshot(self):
        logger.info('credit payoff postshot - DB update')
        conn = engine.connect()
        data_sess = scoped_session(
            sessionmaker(autocommit=False, autoflush=False, bind=conn)
        )
        txn = data_sess.query(OFXTransaction).get(
            (4, '%s-MANUAL-CCPAYOFF' % dtnow().strftime('%Y%m%d%H%M%S'))
        )
        data_sess.delete(txn)
        data_sess.commit()
        data_sess.close()
        conn.close()
        logger.info('credit payoff postshot done') 

def _credit_payoff_preshot(self):
        logger.info('credit payoff preshot - DB update')
        conn = engine.connect()
        data_sess = scoped_session(
            sessionmaker(autocommit=False, autoflush=False, bind=conn)
        )
        acct = data_sess.query(Account).get(4)
        stmt = data_sess.query(OFXStatement).get(7)
        txn = OFXTransaction(
            account=acct,
            statement=stmt,
            fitid='%s-MANUAL-CCPAYOFF' % dtnow().strftime('%Y%m%d%H%M%S'),
            trans_type='debit',
            date_posted=stmt.as_of,
            amount=Decimal('46.9061'),
            name='Interest Charged - MANUALLY ENTERED',
            is_interest_charge=True
        )
        data_sess.add(txn)
        data_sess.commit()
        data_sess.close()
        conn.close()
        logger.info('credit payoff preshot done')
        self.get('/accounts/credit-payoff') 

def _budgets_preshot(self):
        logger.info('budgets preshot - DB update')
        conn = engine.connect()
        data_sess = scoped_session(
            sessionmaker(autocommit=False, autoflush=False, bind=conn)
        )
        pp = BiweeklyPayPeriod.period_for_date(dtnow(), data_sess).previous
        data_sess.add(Budget(
            name='Budget3', is_periodic=True, starting_balance=Decimal('0')
        ))
        data_sess.add(Budget(
            name='Budget4', is_periodic=True, starting_balance=Decimal('0')
        ))
        data_sess.add(Budget(
            name='Budget5', is_periodic=True, starting_balance=Decimal('250')
        ))
        data_sess.flush()
        data_sess.commit()
        for i in range(0, 10):
            self._add_transactions(data_sess, pp)
            pp = pp.previous
        data_sess.close()
        conn.close()
        logger.info('budgets preshot - DB update done; click')
        self.get('/budgets')
        sleep(10)
        logger.info('budgets preshot - executing script')
        self.browser.execute_script(
            "$('#budget-per-period-chart').find('.morris-hover').show()"
        )
        sleep(2)
        logger.info('budgets preshot done') 

def _index_postshot(self):
        logger.info('Index postshot')
        conn = engine.connect()
        data_sess = scoped_session(
            sessionmaker(autocommit=False, autoflush=False, bind=conn)
        )
        for acct_id in [2, 5]:
            acct = data_sess.query(Account).get(acct_id)
            s = acct.ofx_statement
            s.as_of = dtnow()
            data_sess.add(s)
        data_sess.flush()
        data_sess.commit()
        data_sess.close()
        conn.close()
        logger.info('Done updating DB (index postshot)')