René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"Add support for printing SQL of SQLAlchemy query","articleBody":"`pgvector.sqlalchemy.Vector` doesn't provide a `process_literal_param` method, so any SQLALchemy query containing literal values for such column will fail to be rendered to final SQL including the values when `literal_binds` are enabled. Please implement the method.\r\n\r\nRepro steps:\r\n```\r\nimport sqlalchemy as sa\r\nimport sqlalchemy.dialects.postgresql\r\nimport pgvector.sqlalchemy as pgv\r\n\r\nengine = sa.create_engine(\"postgresql+psycopg2://postgres:password@localhost:5432/neighbors-2\")\r\nconn = engine.connect()\r\ntable = sa.Table(\"foo\", sa.MetaData(), sa.Column(\"col1\", pgv.Vector(4)))\r\nquery = sa.select(table.c.col1).where((table.c.col1.max_inner_product([0.5, 0.5, 0.5, 0.5]) * -1) \u003e= 0.1)\r\nkwargs = {\"literal_binds\": True}\r\nsql = str(query.compile(dialect=sqlalchemy.dialects.postgresql.dialect()), compile_kwargs=kwargs)\r\n```\r\nraises\r\n```\r\nsqlalchemy.exc.CompileError: No literal value renderer is available for literal value \"[0.5, 0.5, 0.5, 0.5]\" with datatype VECTOR(4)\r\n```\r\n(The query itself is OK which can be verified by using `kwargs = {}` -- the compilation returns `'SELECT foo.col1 \\nFROM foo \\nWHERE (foo.col1 \u003c#\u003e %(col1_1)s) * %(param_1)s \u003e= %(param_2)s'`)\r\n\r\nWorkaround:\r\n```\r\nimport pgvector.utils\r\n\r\nclass Vector(pgv.Vector, sa.TypeDecorator):\r\n    impl = pgv.Vector\r\n    cache_ok = True\r\n\r\n    def process_literal_param(self, value, dialect):\r\n        return repr(pgvector.utils.to_db(value, self.dim))\r\n```\r\nand use this wrapper class in the table definition instead of the library-provided `pgv.Vector`. The SQL rendering then works:\r\n```\r\nstr(query.compile(dialect=sqlalchemy.dialects.postgresql.dialect(), compile_kwargs={\"literal_binds\": True}))\r\n\"SELECT foo.col1 \\nFROM foo \\nWHERE (foo.col1 \u003c#\u003e '[0.5,0.5,0.5,0.5]') * -1 \u003e= 0.1\"\r\n```","author":{"url":"https://github.com/sarimak","@type":"Person","name":"sarimak"},"datePublished":"2023-08-07T08:40:02.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":4},"url":"https://github.com/23/pgvector-python/issues/23"}