def test_mapper_args_deferred(self): """test that __mapper_args__ is not called until *after* table reflection""" class User(DeferredReflection, fixtures.ComparableEntity, Base): __tablename__ = "users" @declared_attr def __mapper_args__(cls): return {"primary_key": cls.__table__.c.id} DeferredReflection.prepare(testing.db) with fixture_session() as sess: sess.add_all([ User(name="G"), User(name="Q"), User(name="A"), User(name="C"), ]) sess.commit() eq_( sess.query(User).order_by(User.name).all(), [ User(name="A"), User(name="C"), User(name="G"), User(name="Q"), ], )
def _initialize_engine(self) -> Optional[Engine]: if self.is_disabled(): logger.info( f"Skipping {self} connection due to db_connection_attempts=={master_config.db_connection_attempts}" ) return None engine = create_engine( "mssql+pyodbc://", creator=self._initialize_connection, echo=False, # Disable verbose logging of all SQL queries fast_executemany=True, # Improve insert performance, # see also https://github.com/mkleehammer/pyodbc/wiki/Features-beyond-the-DB-API#fast_executemany pool_pre_ping= True, # Always check status of database connections before using them, # see also https://docs.sqlalchemy.org/en/13/core/pooling.html#disconnect-handling-pessimistic ) try: DeferredReflection.prepare(engine) # type: ignore except NoSuchTableError as error: message = ( f"Could not find expected table in {self}: {error}. " f"Did you run the 'setup-database {self._database_type.name}' command to setup {self}?" ) if self._ignore_missing_tables: logger.debug(message) else: raise DatabaseConnectionFailure(message) from error self.schema_base_class.metadata.bind = engine return engine
def __init__(self, *args, **kwargs): self.engine = engine_from_config(kwargs, prefix="sqlalchemy.") # Tries to call the next object's __init__ in the inheritance # If it throws an TypeError than we can assume the parent object is # object or another object that doesn't accept *args and **kwargs. This # is a bit of a draw back but works fine in this case. try: super(SQLAlchemyBase, self).__init__(*args, **kwargs) except TypeError: pass # prepare expects the tables to exist in the database already # this is kind of a hack. I'll need to think of a better way # later on try: DeferredReflection.prepare(self.engine) except NoSuchTableError: self.init_db() DeferredReflection.prepare(self.engine) self._session = scoped_session(sessionmaker(autocommit=False, autoflush=True, expire_on_commit=False, bind=self.engine)) Base.query = self._session.query_property()
def try_create_connection( connection_string, wait_for_db, logger, echo: bool = False, reflections: bool = True) -> Tuple[Connection, Engine]: engine = create_engine(connection_string, echo=echo) if reflections: DeferredReflection.prepare(engine) if wait_for_db == 0: max_wait = float('inf') else: max_wait = time.clock_gettime(time.CLOCK_MONOTONIC) + wait_for_db for timeout in chain([1, 2, 5, 10, 30], repeat(60)): try: conn = engine.connect() return conn, engine except OperationalError: # Important: Use %r to print the URL, passwords are hidden by the # __repr__ of sqlalchemy.engine.URL logger.warn("Could not connect to database %r", engine.url) timeout = min(timeout, max_wait - time.clock_gettime(time.CLOCK_MONOTONIC)) if timeout > 0: logger.info("Waiting for %d seconds", timeout) time.sleep(timeout) else: raise
def __init__(self): try: sqlalchemy.create_engine(cfg.pg_dbstring) except sqlalchemy.exc.ArgumentError: pass self.engine = sqlalchemy.create_engine(cfg.pg_dbstring) DeferredReflection.prepare(self.engine) session_maker = sessionmaker(bind=self.engine) self.session = session_maker()
def test_pk_fk(self): class B(DeferredReflection, fixtures.ComparableEntity, Base): __tablename__ = "b" a = relationship("A") class A(DeferredReflection, fixtures.ComparableEntity, Base): __tablename__ = "a" DeferredReflection.prepare(testing.db)
def test_basic_deferred(self): class User(DeferredReflection, fixtures.ComparableEntity, Base): __tablename__ = "users" addresses = relationship("Address", backref="user") class Address(DeferredReflection, fixtures.ComparableEntity, Base): __tablename__ = "addresses" DeferredReflection.prepare(testing.db) self._roundtrip()
def lazy_reflection(): if not getattr(lazy_reflection, 'reflected', False): try: DeferredReflection.prepare(engine) setattr(lazy_reflection, 'reflected', True) except OperationalError as ex: FlaskApp().app.logger.error( 'Cannot connect with database {}'.format(ex.args)) return False return True
def storage_setup(self): sql_cfg = settings.DATABASE['default'] dsn = '%(DIALECT)s+%(DRIVER)s://%(USER)s:%(PASS)s@%(HOST)s:%(PORT)s/%(NAME)s' % sql_cfg sql_engine = create_engine(dsn) ## stop running syncdb at startup after development. storage.sql.Base.metadata.create_all(sql_engine) DeferredReflection.prepare(sql_engine) Session = sessionmaker(bind=sql_engine) self._sql = Session()
def test_string_resolution(self): class User(DeferredReflection, fixtures.ComparableEntity, Base): __tablename__ = "users" items = relationship("Item", secondary="user_items") class Item(DeferredReflection, fixtures.ComparableEntity, Base): __tablename__ = "items" DeferredReflection.prepare(testing.db) self._roundtrip()
def test_redefine_fk_double(self): class User(DeferredReflection, fixtures.ComparableEntity, Base): __tablename__ = "users" addresses = relationship("Address", backref="user") class Address(DeferredReflection, fixtures.ComparableEntity, Base): __tablename__ = "addresses" user_id = Column(Integer, ForeignKey("users.id")) DeferredReflection.prepare(testing.db) self._roundtrip()
def init_helper(fp, base, session, deferred=False, tables=None): """Creates the database based on the given path""" if fp == ':memory': fp = '' if fp: fp = '/' + os.path.realpath(fp).replace(os.sep, os.sep * 2) engine = create_engine('sqlite://{}'.format(fp)) if deferred: DeferredReflection.prepare(engine) base.metadata.bind = engine base.metadata.create_all(tables=tables) session.configure(bind=engine)
def test_cls_not_strong_ref(self): class User(DeferredReflection, fixtures.ComparableEntity, Base): __tablename__ = "users" class Address(DeferredReflection, fixtures.ComparableEntity, Base): __tablename__ = "addresses" eq_(len(_DeferredMapperConfig._configs), 2) del Address gc_collect() eq_(len(_DeferredMapperConfig._configs), 1) DeferredReflection.prepare(testing.db) assert not _DeferredMapperConfig._configs
def test_basic(self): class Foo(DeferredReflection, fixtures.ComparableEntity, Base): __tablename__ = "foo" __mapper_args__ = { "polymorphic_on": "type", "polymorphic_identity": "foo", } class Bar(Foo): __mapper_args__ = {"polymorphic_identity": "bar"} DeferredReflection.prepare(testing.db) self._roundtrip()
def test_add_subclass_column(self): class Foo(DeferredReflection, fixtures.ComparableEntity, Base): __tablename__ = "foo" __mapper_args__ = { "polymorphic_on": "type", "polymorphic_identity": "foo", } class Bar(Foo): __mapper_args__ = {"polymorphic_identity": "bar"} bar_data = Column(String(30)) DeferredReflection.prepare(testing.db) self._roundtrip()
def main(global_config, **settings): """ This function returns a Pyramid WSGI application. """ engine = engine_from_config(settings, 'sqlalchemy.') DBSession.configure(bind=engine) Base.metadata.bind = engine DeferredReflection.prepare(engine) config = Configurator(settings=settings) config.include('pyramid_chameleon') config.include('cornice') config.add_static_view('static', 'static', cache_max_age=3600) config.add_route('home', '/') config.scan() return config.make_wsgi_app()
def test_add_pk_column(self): class Foo(DeferredReflection, fixtures.ComparableEntity, Base): __tablename__ = "foo" __mapper_args__ = { "polymorphic_on": "type", "polymorphic_identity": "foo", } id = Column(Integer, primary_key=True) class Bar(Foo): __mapper_args__ = {"polymorphic_identity": "bar"} DeferredReflection.prepare(testing.db) self._roundtrip()
def _sql(self): if not self.__sql: sql_cfg = settings.DATABASE['default'] dsn = '%(DIALECT)s+%(DRIVER)s://%(USER)s:%(PASS)s@%(HOST)s:%(PORT)s/%(NAME)s' % sql_cfg if sql_cfg['DIALECT'] == 'mysql': dsn += '?charset=utf8' sql_engine = create_engine(dsn) elif sql_cfg['DIALECT'] == 'postgresql': sql_engine = create_engine(dsn, client_encoding='utf8') storage.sql.Base.metadata.create_all(sql_engine) DeferredReflection.prepare(sql_engine) Session = sessionmaker( bind=sql_engine) #, autoflush=False) #, autocommit=True) self.__sql = Session() return self.__sql
def _sql(self): if not self.__sql: sql_cfg = settings.DATABASE['default'] dsn = '%(DIALECT)s+%(DRIVER)s://%(USER)s:%(PASS)s@%(HOST)s:%(PORT)s/%(NAME)s' % sql_cfg if sql_cfg['DIALECT'] == 'mysql': dsn += '?charset=utf8' sql_engine = create_engine(dsn) elif sql_cfg['DIALECT'] == 'postgresql': sql_engine = create_engine(dsn, client_encoding='utf8') storage.sql.Base.metadata.create_all(sql_engine) DeferredReflection.prepare(sql_engine) Session = sessionmaker(bind=sql_engine) #, autoflush=False) #, autocommit=True) self.__sql = Session() return self.__sql
def init_db(conn_str): """ Initialize a connection to the database :param conn_str: SQLAlchemy database URL :return: """ engine = create_engine(conn_str) _DBSession.bind = engine Base.metadata.bind = engine # we can reflect it ourselves from a database, using options # such as 'only' to limit what tables we look at... metadata = MetaData() metadata.reflect(engine, only=['domain', 'mailbox', 'alias']) # calling prepare() just sets up mapped classes and relationships. DeferredReflection.prepare(engine) Base.prepare(engine)
def setup(): global engine, connection, _setup_stack if _setup_stack > 0: return try: uri = os.environ['PYCROFT_DB_URI'] except KeyError: raise RuntimeError("Environment variable PYCROFT_DB_URI must be " "set to an SQLalchemy connection string.") engine = create_engine(uri, poolclass=SingletonThreadPool) connection = engine.connect() drop_db_model(connection) create_db_model(connection) DeferredReflection.prepare(engine) _setup_stack += 1
def main(): DeferredReflection.prepare(engine) # Autoload the tables from DB schema try: db_session.execute( Device.__table__.update().values({'query_status': 'queue'}) ) db_session.commit() except ProgrammingError: db_session.rollback() with Pool() as pool: pool.map( query_device, db_session.query( Device.id ).filter_by( is_active=True ), chunksize=1, )
app.config.from_object('config.Config') # prepare controller for pack in filter(lambda item: re.search("\.py$", item), os.listdir(controller.__path__[0])): data = __import__(inspect.getmodulename("controller." + pack), fromlist=['controller']) for view in filter(lambda item: (re.search("^(?!Flask).*View$", item[0])), inspect.getmembers(data, inspect.isclass)): view[1].register(app) # prepare database and model engine = create_engine(app.config['DATABASE']) Session = scoped_session(sessionmaker(bind=engine)) AppModel._set_session(Session) DeferredReflection.prepare(engine) mongo = MongoClient() # for global action @app.before_request def before_request(): if (request.path == '/users/' and request.method == "POST"): return user = User.find_by(token=request.headers.get('Authorized-Token')) if not user: abort(401) setattr(g, 'user', user) setattr(g, 'mongo', MongoClient())
def _create_scheduling_row(self, parset_property): """ """ # Link to receivers receivers_on = parset_property.get("hd_receivers", []) receivers_on += parset_property.get("nri_receivers", []) if parset_property.get("xst_userfile", False): # Add the xst option, which is not a proper receiver receivers_on.append("xst") if not np.all(np.isin(receivers_on, RECEIVERS)): log.warning( f"One of the receiver listed ({receivers_on}) does not belong to the predefined list ({RECEIVERS})." ) receivers = self.session.query(ReceiverTable).filter( ReceiverTable.name.in_(receivers_on)).all() # scheduling_row = SchedulingTable( # name=parset_property['name'], # contact_name=parset_property['contactName'], # contact_email=parset_property['contactEmail'], # key_project_code=parset_property['topic'].split(' ', 1)[0], # key_project_name=parset_property['topic'].split(' ', 1)[1], # start_time=parset_property['startTime'].datetime, # stop_time=parset_property['stopTime'].datetime, # receivers=[ReceiverAssociation(receiver=receiver) for receiver in receivers], # parset_file=self.parset # ) scheduling_row = self.session.query(SchedulingTable).filter_by( fileName=basename(self.parset)).first() if scheduling_row is None: username = "******" if parset_property[ "contactName"] == "" else parset_property["contactName"] # Check if 'username' exists if inspect(self.engine).has_table("nenufar_users"): class NenufarUserTable(DeferredReflection, Base): """ Fake class for NenuFAR User Table """ __tablename__ = 'nenufar_users' DeferredReflection.prepare(self.engine) username_entry = self.session.query( NenufarUserTable).filter_by(username=username).first() if username_entry is None: log.warning( f"Username '{username}' not found in 'nenufar_users' table, skipping it." ) raise UserNameNotFound(f"'{username}'") # Sort out the topic topic = parset_property.get("topic", "ES00 DEBUG") if topic.lower().strip() == 'maintenance': topic = "MAINTENANCE" else: # We have something like "ES00 DEBUG" topic = topic.split(" ", 1)[1] # Create the new row scheduling_row = SchedulingTable( name=parset_property['name'], fileName=basename(self.parset), path=dirname(self.parset), startTime=parset_property["startTime"].datetime, endTime=parset_property["stopTime"].datetime, state="default_value", topic=topic, username=username, receivers=[ ReceiverAssociation(receiver=receiver) for receiver in receivers ]) is_new = True log.debug( f"Row of table 'scheduling' created for '{scheduling_row.name}'." ) else: # Only add the receiver association, the row already exists in scheduling table [ ReceiverAssociation(receiver=receiver, scheduling=scheduling_row) for receiver in receivers ] is_new = False log.debug( f"Row of table 'scheduling' updated for '{scheduling_row.name}'." ) if "nickel" in receivers_on: log.debug("Adding the association to NICKEL subbands.") nickel_subbands = self.session.query(SubBandTable).filter( SubBandTable.index.in_( parset_property.get("nri_subbandList", []))).all() [ SubBandNickelAssociation(subband=sb, scheduling=scheduling_row) for sb in nickel_subbands ] return scheduling_row, is_new