PostreSQL

Installation

You need to install the psycopg2-binary library to use this in blendsql.

Bases: SQLAlchemyDatabase

A PostgreSQL database connection. Can be initialized via the SQLAlchemy input string. https://docs.sqlalchemy.org/en/20/core/engines.html#postgresql

Examples:

from blendsql.db import PostgreSQL
db = PostgreSQL("user:password@localhost/mydatabase")

Source code in blendsql/db/_postgres.py

class PostgreSQL(SQLAlchemyDatabase):
    """A PostgreSQL database connection.
    Can be initialized via the SQLAlchemy input string.
    https://docs.sqlalchemy.org/en/20/core/engines.html#postgresql

    Examples:
        ```python
        from blendsql.db import PostgreSQL
        db = PostgreSQL("user:password@localhost/mydatabase")
        ```
    """

    def __init__(self, db_path: str):
        if not _has_psycopg2:
            raise ImportError(
                "Please install psycopg2 with `pip install psycopg2-binary`!"
            ) from None
        db_url: URL = make_url(f"postgresql+psycopg2://{db_path}")
        if db_url.username is None:
            logging.warning(
                Fore.RED
                + "Connecting to postgreSQL database without specifying user!\nIt is strongly encouraged to create a `blendsql` user with read-only permissions and temp table creation privileges."
            )
        super().__init__(db_url=db_url)

    def has_temp_table(self, tablename: str) -> bool:
        return tablename in self.execute_to_list(
            "SELECT table_name FROM information_schema.tables WHERE table_schema LIKE 'pg_temp_%'"
        )

    @cached_property
    def sqlglot_schema(self) -> dict:
        # TODO
        return None

Creating a blendsql User

When executing a BlendSQL query, there are internal checks to ensure prior to execution that a given query does not contain any 'modify' actions.

However, it is still best practice when using PostgreSQL to create a dedicated 'blendsql' user with only the permissions needed.

You can create a user with the required permissions with the script below (after invoking postgres via psql)

CREATE USER blendsql;
GRANT pg_read_all_data TO blendsql;
GRANT TEMP ON DATABASE mydb TO blendsql;

Now, we can initialize a PostgreSQL database with our new user.

from blendsql.db import PostgreSQL
db = PostgreSQL("blendsql@localhost:5432/mydb")