def test_reflect_function(engine, session): session.execute(CREATE_FUNCTION) session.commit() functions = reflect_functions(engine, schema="public", type_map=pg_base.ischema_names) to_upper = [x for x in functions if x.name == "to_upper"] assert len(to_upper) == 1
def test_call_function(engine, session): session.execute(CREATE_FUNCTION) session.commit() functions = reflect_functions(engine, schema="public", type_map=pg_base.ischema_names) to_upper = [x for x in functions if x.name == "to_upper"][0] query = select([to_upper.to_executable(["abc"]).label("result")]) result = session.execute(query).fetchone()["result"] assert result == "ABC"
def reflect_sqla_models( engine: Engine, schema: str = "public" ) -> Tuple[List[TableProtocol], List[SQLFunction]]: """Reflect SQLAlchemy Declarative Models from a database connection""" meta = MetaData() declarative_base = automap_base(metadata=meta) # Register event listeners to apply GQL attr keys to columns # Retrive a copy of the full type map basic_type_map = pg_base.ischema_names.copy() # Reflect composite types (not supported by sqla) composites = reflect_composites(engine, schema, basic_type_map) # Register composite types with SQLA to make them available during reflection pg_base.ischema_names.update({ type_name: type_ for (type_schema, type_name), type_ in composites.items() }) # Retrive a copy of the full type map # NOTE: types are not schema namespaced so colisions can occur reflecting tables type_map = pg_base.ischema_names.copy() type_map["bool"] = types.Boolean # type: ignore # Reflect views as SQLA ORM table views = reflect_views(engine=engine, schema=schema, declarative_base=declarative_base) declarative_base.prepare( engine, reflect=True, schema=schema, classname_for_table=rename_table, name_for_scalar_relationship=rename_to_one_collection, name_for_collection_relationship=rename_to_many_collection, ) # Register tables as types so functions can return a row of a table tables = list(declarative_base.classes) + views for table in tables + views: type_map[table.__table__.name] = table # Reflect functions, allowing composite types functions = reflect_functions(engine=engine, schema=schema, type_map=type_map) # SQLA Tables return (tables, functions)
def test_reflect_function_returning_row(engine, session): session.execute(CREATE_FUNCTION) session.commit() functions = reflect_functions(engine, schema="public", type_map=pg_base.ischema_names) get_account = functions[0] res = session.execute(get_account.to_executable([1])).first() print(res) # psycopg2 does not know how to deserialize row results assert res == ("(1,oli)", )