Python sqlalchemy.orm.sessionmaker() Examples

def __init__(self, user, password, database, host, port):
        driver = 'mysql+pymysql'

        self.url = URL(driver, user, password, host, port, database)

        # Hack to establish SSL connection (see #231)
        try:
            self._engine = create_engine(self.url, echo=True,
                                         connect_args={'ssl': {'activate': True}})
            self._engine.connect().close()
        except InternalError:
            self._engine = create_engine(self.url, echo=True)

        self._Session = sessionmaker(bind=self._engine)

        # Create the schema on the database.
        # It won't replace any existing schema
        ModelBase.metadata.create_all(self._engine) 

def get_db_session_factory(provider: str,
                           db_name: str = None,
                           data_schema: object = None):
    """
    get db session factory of the (provider,db_name) or (provider,data_schema)

    :param provider:
    :type provider:
    :param db_name:
    :type db_name:
    :param data_schema:
    :type data_schema:
    :return:
    :rtype:
    """
    if data_schema:
        db_name = get_db_name(data_schema=data_schema)

    session_key = '{}_{}'.format(provider, db_name)
    session = zvt_context.db_session_map.get(session_key)
    if not session:
        session = sessionmaker()
        zvt_context.db_session_map[session_key] = session
    return session 

def validate_session():
    """ Validate ORM Session """
    worker_precheck = conf.getboolean('core', 'worker_precheck', fallback=False)
    if not worker_precheck:
        return True
    else:
        check_session = sessionmaker(bind=engine)
        session = check_session()
        try:
            session.execute("select 1")  # pylint: disable=no-member
            conn_status = True
        except exc.DBAPIError as err:
            log.error(err)
            conn_status = False
        session.close()  # pylint: disable=no-member
        return conn_status 

def open(self,database):
    """
    params:
        database: str. Database to be opered. The path should be included
    """
    assert(database[-4:]=='.mdo')
    if not os.path.exists(database):
        self._create(database)
    operate_db=database[:-4]+'.op'
    shutil.copy(database,operate_db)
#        engine=create_engine('sqlite:///:memory:')
    engine=create_engine('sqlite:///'+operate_db) #should be run in memory in the future
    Session=o.sessionmaker(bind=engine)
    self.session=Session()
    self.__operate_db=operate_db
    self.__storage_db=database 

def main(args, env):
    global Session

    if args.verbose >= 1:
        app.config['DEBUG'] = True
    sys.stderr.write("connecting to DB server {:s}\n".format(args.db))
    connection_succeeded = False
    while not connection_succeeded:
        try:
            engine = create_engine(args.db)
            Session = sessionmaker(bind = engine)
            Base.metadata.create_all(engine)
            sys.stderr.write("connection succeeded!\n")
            connection_succeeded = True
            app.run(debug = args.verbose >= 1, host = "0.0.0.0", port = 80)
        except OperationalError as err:
            if "Connection refused" in str(err):
                connection_succeeded = False
                time.sleep(10)
            else:
                raise 

def setUp(self):
        self.engine = create_engine("sqlite:///:memory:")
        Session = mptt_sessionmaker(sessionmaker(bind=self.engine))
        self.session = Session()
        self.base.metadata.create_all(self.engine)
        self.fixture = Fixtures(self.session)
        self.fixture.add(
            self.model, os.path.join("fixtures", getattr(self, "fixtures", "tree.json"))
        )

        self.result = self.session.query(
            self.model.get_pk_column(),
            self.model.left,
            self.model.right,
            self.model.level,
            self.model.parent_id,
            self.model.tree_id,
        ) 

def write(self):
        Session = sessionmaker(bind=droidsql.engine)
        session = Session()
        sample = droidsql.Sample(sha256=self.sha256, \
                                 sanitized_basename=self.sanitized_basename, \
                                 file_nb_classes=self.file_nb_classes, \
                                 file_nb_dir=self.file_nb_dir,\
                                 file_size=self.file_size,\
                                 file_small=self.file_small,\
                                 filetype=self.filetype,\
                                 file_innerzips=self.file_innerzips,\
                                 manifest_properties=json.dumps(self.manifest), \
                                 smali_properties=json.dumps(self.smali),\
                                 wide_properties=json.dumps(self.wide),\
                                 arm_properties=json.dumps(self.arm),\
                                 dex_properties=json.dumps(self.dex),\
                                 kits=json.dumps(self.kits))
        session.add(sample)
        try:
            session.commit()
        except sqlalchemy.exc.IntegrityError:
            # occurs when the sample with the same sha256 is already in
            if self.verbose:
                print("Sample is already in the database") 

def setUpClass(cls):
        # Set db location
        file_ = tempfile.NamedTemporaryFile(delete=False)
        global_scope['db_file'] = file_.name

        # Create a user key
        cls.secret_key = str(uuid.uuid4())
        cls.enc = global_scope['enc'] = Encryption(cls.secret_key.encode())

        # Load config
        cls.conf_path = tempfile.TemporaryDirectory()
        cls.config = Config(cls.conf_path.name + '/config')
        global_scope['conf'] = cls.config

        # Create engine
        engine = get_engine()

        # Create tables and set database session
        Base.metadata.create_all(engine)
        Session = sessionmaker(bind=engine)
        cls.session = Session()

        # Populate db
        cls.populate_base() 

def __init__(self, results_queue, thread_pool, use_file=True, use_database=True, filename="proxy-ip-list.csv"):
        self.use_file = use_file
        self.use_database = use_database
        self.filename = filename
        self.results_queue = results_queue
        self.thread_pool = thread_pool

        if use_database:
            try:
                cf = ConfigParser.ConfigParser()
                cf.read("config.ini")
                db_name = cf.get("Pansidong", "database")
                username = cf.get(db_name, "username")
                password = cf.get(db_name, "password")
                host = cf.get(db_name, "host")
                database = cf.get(db_name, "database")
            except AttributeError, e:
                logger.fatal(e.message)
                sys.exit(1)
            self.engine = create_engine("mysql://" + username + ":" + password + "@" +
                                        host + "/" + database + "?charset=utf8")
            self.db_session = sessionmaker(bind=self.engine)
            self.session = self.db_session() 

def hourly_pv():
    db_str_target = local_history_database["db_str"]
    engine_target = create_engine(db_str_target, echo=False)
    Session_target = sessionmaker(bind=engine_target)
    session_target = Session_target()

    session_source = Session_target()

    for i in range(8760):

        row_source = session_source.query(half_hourly_history_data.PV_PG).filter(and_(half_hourly_history_data.TIME_STAMP>=i*2,half_hourly_history_data.TIME_STAMP<(i+1)*2)).all()

        row = session_target.query(hourly_history_data).filter(hourly_history_data.TIME_STAMP == i).first()
        temp = 0
        for j in range(2):
            temp += row_source[j][0]

        row.PV_PG = temp/2

        session_target.commit()
        print(i) 

def half_hour_pv():
    db_str_target = local_history_database["db_str"]
    engine_target = create_engine(db_str_target, echo=False)
    Session_target = sessionmaker(bind=engine_target)
    session_target = Session_target()

    session_source = Session_target()

    for i in range(8760*2):

        row_source = session_source.query(five_minutes_history_data.PV_PG).filter(and_(five_minutes_history_data.TIME_STAMP>=i*6,five_minutes_history_data.TIME_STAMP<(i+1)*6)).all()

        row = session_target.query(half_hourly_history_data).filter(half_hourly_history_data.TIME_STAMP == i).first()
        temp = 0
        for j in range(6):
            temp += row_source[j][0]

        row.PV_PG = temp/6

        session_target.commit()
        print(i) 

def five_minute_pv():
    db_str_target = local_history_database["db_str"]
    engine_target = create_engine(db_str_target, echo=False)
    Session_target = sessionmaker(bind=engine_target)
    session_target = Session_target()

    session_source = Session_target()

    for i in range(8760*12):

        row_source = session_source.query(one_minute_history_data.PV_PG).filter(and_(one_minute_history_data.TIME_STAMP>=i*5,one_minute_history_data.TIME_STAMP<(i+1)*5)).all()

        row = session_target.query(five_minutes_history_data).filter(five_minutes_history_data.TIME_STAMP == i).first()
        temp = 0
        for j in range(5):
            temp += row_source[j][0]

        row.PV_PG = temp/5

        session_target.commit()
        print(i) 

def _accept_with(cls, target):
        if isinstance(target, scoped_session):

            target = target.session_factory
            if not isinstance(target, sessionmaker) and \
                (
                    not isinstance(target, type) or
                    not issubclass(target, Session)
            ):
                raise exc.ArgumentError(
                    "Session event listen on a scoped_session "
                    "requires that its creation callable "
                    "is associated with the Session class.")

        if isinstance(target, sessionmaker):
            return target.class_
        elif isinstance(target, type):
            if issubclass(target, scoped_session):
                return Session
            elif issubclass(target, Session):
                return target
        elif isinstance(target, Session):
            return target
        else:
            return None 

def init_database(self):
        self.raw_db = create_engine(self.config['url'], echo=False)
        self.raw_conn = self.raw_db.connect()
        self.operations = Operations(self.raw_conn, self.config)

        try:
            self.raw_conn.connection.set_isolation_level(0)
        except AttributeError:
            logger.info('Could not set isolation level to 0')

        self.db = create_engine(self.config['stellar_url'], echo=False)
        self.db.session = sessionmaker(bind=self.db)()
        self.raw_db.session = sessionmaker(bind=self.raw_db)()
        tables_missing = self.create_stellar_database()

        self.create_stellar_tables()

        # logger.getLogger('sqlalchemy.engine').setLevel(logger.WARN) 

def session_using_test_dataset(engine_using_test_dataset):
    Session = sessionmaker(bind=engine_using_test_dataset)
    session = Session()
    return session 

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

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

def start_session(db_path):
    """basic script for starting a session"""
    engine = create_engine('sqlite:///' + db_path)
    Session = sessionmaker(bind=engine)
    return Session()


###
# Dictionary mapping tables to their respective transcript/alignment modes
### 

def reflect_hints_db(db_path):
    """
    Reflect the database schema of the hints database, automapping the existing tables

    The NullPool is used to avoid concurrency issues with luigi. Using this activates pooling, but since sqlite doesn't
    really support pooling, what effectively happens is just that it locks the database and the other connections wait.

    :param db_path: path to hints sqlite database
    :return: sqlalchemy.MetaData object, sqlalchemy.orm.Session object
    """
    engine = sqlalchemy.create_engine('sqlite:///{}'.format(db_path), poolclass=NullPool)
    metadata = sqlalchemy.MetaData()
    metadata.reflect(bind=engine)
    Base = automap_base(metadata=metadata)
    Base.prepare()
    speciesnames = Base.classes.speciesnames
    seqnames = Base.classes.seqnames
    hints = Base.classes.hints
    featuretypes = Base.classes.featuretypes
    Session = sessionmaker(bind=engine)
    session = Session()
    return speciesnames, seqnames, hints, featuretypes, session 

def __init__(self, loggingdb_path):
		url = f"sqlite:///{loggingdb_path}"
		self.database_url = DatabaseURL(url)
		engine = sqlalchemy.create_engine(url)
		Base.metadata.create_all(engine)
		Session = sessionmaker(bind=engine)
		self.session = Session() 

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 __init__(self, config=None):
        self.name = "sqlstats"
        self.config = config or {}
        self.engine = get_engine(self.name, self._create_engine)
        # create a configured "Session" class. ``scoped_session`` is not
        # necessary because we do not share session objects among threads.
        # We use it anyway as a safety measure.
        Session = scoped_session(sessionmaker(bind=self.engine))
        self.session = Session()
        # Ensure that the connection gets returned to the pool when the request has
        # been handled. This may close an already-closed session, but this is not a problem.
        register_finalizer(self.session.close)
        self.session._model_changes = {} 

def __init__(self, config=None):
        super(Audit, self).__init__(config)
        self.name = "sqlaudit"
        self.sign_data = not self.config.get("PI_AUDIT_NO_SIGN")
        self.sign_object = None
        self.verify_old_sig = self.config.get('PI_CHECK_OLD_SIGNATURES')
        if self.sign_data:
            self.read_keys(self.config.get("PI_AUDIT_KEY_PUBLIC"),
                           self.config.get("PI_AUDIT_KEY_PRIVATE"))
            self.sign_object = Sign(self.private, self.public)

        # We can use "sqlaudit" as the key because the SQLAudit connection
        # string is fixed for a running privacyIDEA instance.
        # In other words, we will not run into any problems with changing connect strings.
        self.engine = get_engine(self.name, self._create_engine)
        # create a configured "Session" class. ``scoped_session`` is not
        # necessary because we do not share session objects among threads.
        # We use it anyway as a safety measure.
        Session = scoped_session(sessionmaker(bind=self.engine))
        self.session = Session()
        # Ensure that the connection gets returned to the pool when the request has
        # been handled. This may close an already-closed session, but this is not a problem.
        register_finalizer(self.session.close)
        self.session._model_changes = {} 

def setUp(self):
        super(UnitTestBase, self).setUp()

        try:
            session.create_tables(self.connection)
        except Exception as e:
            # tearDown doesn't run if setUp throws an exception!
            self.tearDownDb()
            raise e

        Session = sessionmaker(bind=self.connection)
        self.open_session = Session 

def __init__(self, database_uri):
        self.Session = sessionmaker()
        self.engine = create_engine(
            database_uri, json_serializer=self.dumps, json_deserializer=json.loads
        )
        if not database_exists(self.engine.url):
            create_database(self.engine.url)
        self._create_db_tables()
        self.Session.configure(bind=self.engine) 

def get_connection_session(url):
    try:
        engine = create_engine(url, echo=True)
        session = sessionmaker()
        session.configure(bind=engine)
        Base.metadata.create_all(engine)
        s = session()
        return s
    except Exception as e:
        logger.debug("Exception is %s" % e)
        return None


# get connection using url 

def get_user_session(user_id):
    try:
        engine = create_engine(user_orm_url, echo=True)
        session = sessionmaker()
        session.configure(bind=engine)
        Base.metadata.create_all(engine)
        s = session()
        ret = s.query(monitor_user.User).filter_by(username=user_id).first()
        return ret
    except Exception as e:
        logger.debug("Exception is %s" % e)
        return None


# get connection session 

def db_session_scope(self):
        DBSession = sessionmaker(bind=self.db_engine)
        self.db_session = DBSession()

        try:
            yield self.db_session
            # logger.debug("New data {}".format(self.db_session.new))
            # logger.debug("Updated data {}".format(self.db_session.dirty))
            self.db_session.commit()
        except Exception as e:
            self.db_session.rollback()
            raise e
        finally:
            self.db_session.close() 

def init_database(autoflush=True):
    """ Init DB
    :rtype: (sqlalchemy.engine.Engine, sqlalchemy.orm.Session)
    """
    engine = create_engine('postgresql://postgres:postgres@localhost/test_mongosql', convert_unicode=True, echo=False)
    Session = sessionmaker(autocommit=autoflush, autoflush=autoflush, bind=engine)
    return engine, Session 

def __init__(self):
        """Initializes the engine and session for database operations

        Retrieves the database uri for connection from ConfigurationManager.
        """
        uri = ConfigurationManager().get_value("core",
                                               "sqlalchemy_database_uri")
        # set echo=True to log SQL
        self.engine = create_engine(uri)
        # statements
        self.session = scoped_session(sessionmaker(bind=self.engine)) 

def __init__(self, lostconfig, debug=False):
        '''Init database connection

        Args:
            lostconfig (object): :class:`lost.pyapi.parse.LOSTConfig`
            debug (bool): If True, echo database communication.
        '''
        orm_connection_str = convert_connection_str(lostconfig)
        self.engine = sqlalchemy.create_engine(orm_connection_str, echo=debug,
                                               poolclass=NullPool)
        self.__Session = sessionmaker(bind=self.engine)
        self.session = self.__Session()
        self.lostconfig = lostconfig