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)", )
