PostgreSQL

SQL

The PostgreSQL connector is a SQL connector that queries a PostgreSQL database and maps result rows directly to claims via column mapping.

How it works

Uses fetch() which yields claim dicts — the base class run() handles batching
Connects via psycopg2.extras.RealDictCursor for named column access
Each row returned by the SQL query becomes one claim via the column mapping
All values are automatically converted to strings
Provenance is tracked with the DSN as the source identifier

Column mapping

The mapping dict maps claim fields to column names in your query result. Three fields are required: subject, predicate, and object. Optional fields include subject_type, predicate_type, object_type, and confidence.

mapping = {
    "subject": "column_name",      # Required
    "predicate": "column_name",    # Required
    "object": "column_name",       # Required
    "subject_type": "entity",      # Optional (default: "entity")
    "predicate_type": "relates_to",# Optional
    "object_type": "entity",       # Optional
    "confidence": "column_name",   # Optional
}

1 Ensure PostgreSQL connectivity

Make sure your machine has network access to the PostgreSQL server. The connector uses a standard DSN (Data Source Name) connection string.

2 DSN format

postgresql://user:pass@host:port/dbname

3 Install psycopg2-binary

$ pip install psycopg2-binary

4 Connect

conn = db.connect("postgres",
    dsn="postgresql://user:pass@localhost/mydb",
    query="SELECT gene, relation, target FROM interactions",
    mapping={"subject": "gene", "predicate": "relation", "object": "target"},
)
result = conn.run(db)

Parameter	Required	Default	Description
`dsn`	Yes	—	PostgreSQL connection string (`postgresql://user:pass@host:port/dbname`)
`query`	Yes	—	SQL query to execute
`mapping`	Yes	—	Column-to-claim field mapping dict
`source_type`	No	`database_import`	Provenance source type
`save`	No	`False`	Encrypt and persist DSN

Mapping structure

The mapping dict maps claim fields to column names. Three fields are required (subject, predicate, object). Optional fields use literal string values as defaults when the key is not a column name.

Mapping Key	Required	Default	Description
`subject`	Yes	—	Column name for claim subject
`predicate`	Yes	—	Column name for claim predicate
`object`	Yes	—	Column name for claim object
`subject_type`	No	`entity`	Subject entity type (literal or column name)
`predicate_type`	No	`relates_to`	Predicate type (literal or column name)
`object_type`	No	`entity`	Object entity type (literal or column name)
`confidence`	No	—	Column name for confidence score

Basic usage

conn = db.connect("postgres",
    dsn="postgresql://user:pass@localhost/mydb",
    query="SELECT gene, relation, target FROM interactions",
    mapping={"subject": "gene", "predicate": "relation", "object": "target"},
)
result = conn.run(db)
print(f"Ingested {result.claims_ingested} claims")

With confidence column

conn = db.connect("postgres",
    dsn="postgresql://user:pass@host/db",
    query="SELECT src, rel, dst, score FROM edges",
    mapping={
        "subject": "src",
        "predicate": "rel",
        "object": "dst",
        "confidence": "score",
    },
)
result = conn.run(db)

With save (persist encrypted DSN)

conn = db.connect("postgres",
    dsn=os.environ["PG_DSN"],
    query="SELECT * FROM claims_view",
    mapping={"subject": "subj", "predicate": "pred", "object": "obj"},
    save=True,
)
result = conn.run(db)