# Other tools

## The DB-API

`agensgraph.dbapi` is the driver's [PEP 249](https://peps.python.org/pep-0249/) module, for
libraries that drive any database through that interface:

```pycon
>>> import agensgraph.dbapi
>>> agensgraph.dbapi.apilevel, agensgraph.dbapi.threadsafety, agensgraph.dbapi.paramstyle
('2.0', 2, 'pyformat')
>>> other = agensgraph.dbapi.connect("host=localhost dbname=docs user=agens")
>>> type(other).__name__
'Connection'
>>> other.close()
```

`connect()` returns the same `Connection` as `agensgraph.connect()`, with the graph types and the
server check. The module also has the type constructors (`Date`, `Timestamp`, `Binary`, ...) and
the exception hierarchy, as the very classes psycopg raises, so a library that matches on
`module.IntegrityError` matches the exceptions it really receives. `conn.closed` and
`conn.broken` let such a library tell a closed connection from a failed one without reading
messages.

`agensgraph.dbapi.__version__` is psycopg's version, which is what a library asking that module for
its version wants; the driver's own is `agensgraph.__version__`.

## SQLAlchemy

SQLAlchemy's psycopg dialect works with the driver's DB-API module in place of psycopg's. Pass it
as `module`:

```python
import agensgraph.dbapi
from sqlalchemy import create_engine, text

engine = create_engine("postgresql+psycopg://agens@localhost/docs", module=agensgraph.dbapi)

with engine.connect() as session:
    session.execute(text("SET graph_path = moviekb"))
    rows = session.execute(
        text(r"MATCH (m\:movie {title: :title}) RETURN m"), {"title": "Inception"}
    ).all()
```

`rows` holds `Vertex` values, and the string parameter is sent as `text`, as everywhere in this
driver. Three things to know:

- **Escape a colon that starts a label.** SQLAlchemy reads `:name` as a bound parameter when nothing
  word-like comes before the colon, so `(:movie)` and `-[:acted_in]->` must be written `(\:movie)`
  and `-[\:acted_in]->`, in a raw string. `(m:movie)` is left alone.
- **Select the graph on each connection,** with `SET graph_path` as above, an `ALTER ROLE ... SET
  graph_path` default, or a pool event listener. SQLAlchemy rolls back when it returns a connection
  to its pool, and that undoes a `SET` made inside the transaction, so select it again after every
  rollback, or give the role a default.
- **Use `text()` for Cypher.** SQLAlchemy's expression language builds SQL; a Cypher statement goes
  through `text()` with bound parameters.

To use a URL of its own, such as `postgresql+agensgraph://`, subclass the dialect and register it:

```{code-block} python
:class: no-run

from sqlalchemy.dialects import registry
from sqlalchemy.dialects.postgresql.psycopg import PGDialect_psycopg

class AgensGraphDialect(PGDialect_psycopg):
    driver = "agensgraph"
    supports_statement_cache = True

    @classmethod
    def import_dbapi(cls):
        import agensgraph.dbapi
        return agensgraph.dbapi

registry.register("postgresql.agensgraph", "myapp.db", "AgensGraphDialect")
```

The server's version string matches the PostgreSQL dialect's own pattern, so SQLAlchemy detects
the PostgreSQL version as usual.

## A psycopg connection the driver did not make

The driver registers its graph types only on the connections it makes, never on psycopg's global
adapters, so a plain `psycopg.connect()` in the same process is untouched. On such a connection a
vertex is the text the server printed:

```pycon
>>> import psycopg
>>> plain = psycopg.connect("host=localhost dbname=docs user=agens", autocommit=True)
>>> plain.execute("SET graph_path = moviekb")
<psycopg.Cursor ...>
>>> plain.execute("MATCH (m:movie {title: 'Inception'}) RETURN m").fetchone()[0][:21]
'movie[3.5]{"plot": "A'
```

To read graph values on a connection you made yourself, register the driver's adapters on it:

```pycon
>>> from agensgraph.adapters import register_text
>>> register_text(plain)
>>> plain.execute("MATCH (m:movie {title: 'Inception'}) RETURN m").fetchone()
(Vertex(movie[3.5]),)
>>> plain.close()
```

`register_text` reads vertices, edges, paths and identities in the text rendering. It also installs
the driver's way of **sending** values on that connection: a `str` goes as `text`, a `dict` as
`jsonb`, and a NaN is refused, as described in [Parameters](parameters.md). Everything else in this
manual, such as `execute_query()`, the label table and the bulk loaders, needs an
`agensgraph.Connection`.