Cannot adapt type 'ndarray' with placeholder %s about pgvector-python

[ankane]

I'd confirm you're calling register_vector on the connection you're using for semantic_search. This script produces the same error:

import numpy as np
import psycopg

conn = psycopg.connect(dbname='test')
conn.execute('SELECT %s', (np.array([1.0, 2.0, 3.0]),))

psycopg.ProgrammingError: cannot adapt type 'ndarray' using placeholder '%s' (format: AUTO)

[ankane]

Hi @AbhiPawar5, please create a minimal reproducible example if you're looking for help (re: pgvector/pgvector#327).

[AbhiPawar5]

Hi @ankane, here's an boilerplate code that I have. I hope this helps you understand the issue:

from pgvector.psycopg import register_vector
import psycopg
from sentence_transformers import SentenceTransformer
from tqdm import tqdm

conn = psycopg.connect(dbname='test', autocommit=True)
conn.execute('CREATE EXTENSION IF NOT EXISTS vector')
register_vector(conn)

conn.execute(''' CREATE TABLE assets 
            (id bigserial PRIMARY KEY, 
            assetId varchar(40),
            assetTypeId varchar(40),
            Class varchar(40),
            AssetTypeName varchar(40),
            Name text,
            Value text,
            FormattedValue text,
            embedding vector(384)) ''')

model = SentenceTransformer('all-MiniLM-L6-v2')
embeddings = model.encode(text_to_encode)

column_names = ", ".join(combined_df.columns)
column_names = column_names + ",embedding"

for data in combined_df.values.tolist():
    conn.execute('INSERT INTO assets (AssetId, AssetTypeId, Class, AssetTypeName, Name, Value, FormattedValue) VALUES (%s, %s, %s, %s, %s, %s, %s)', (data))

for i, embeds in enumerate(embeddings):
    conn.execute('UPDATE assets SET embedding = (%s) WHERE id = (%s)', (embeds, i))

# We see the results of this query with embeddings
query = conn.execute("SELECT * FROM assets WHERE id != 1 ORDER BY embedding <-> (SELECT embedding FROM assets WHERE id = 1) LIMIT 5;")

for res in query:
    print(res)

Now, image I do semantic search as following:

def semantic_search(query):
    global model, conn

    embed = model.encode(query)

    results = conn.execute('SELECT id, Class, AssetTypeName, Name, Value, FormattedValue FROM assets ORDER BY embedding <=> %s LIMIT 5', (embed,))
    return results

When I call the above function semantic_search, it returns:

---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
Cell In[49], line 1
----> 1 res = semantic_search("Claim or Claim Feature is closed")
      3 for x in res:
      4     print(x)

Cell In[48], line 6, in semantic_search(query)
      2 global model, conn
      4 embed = model.encode(query)
----> 6 results = conn.execute('SELECT id, Class, AssetTypeName, Name, Value, FormattedValue FROM assets ORDER BY embedding <=> %s LIMIT 5', (embed,))
      7 return results

File [~/miniconda3/envs/pgvect/lib/python3.9/site-packages/psycopg/connection.py:892](https://file+.vscode-resource.vscode-cdn.net/Users/abhishek.pawar/Downloads/PG%20Vector/~/miniconda3/envs/pgvect/lib/python3.9/site-packages/psycopg/connection.py:892), in Connection.execute(self, query, params, prepare, binary)
    889     return cur.execute(query, params, prepare=prepare)
    891 except e._NO_TRACEBACK as ex:
--> 892     raise ex.with_traceback(None)

ProgrammingError: cannot adapt type 'ndarray' using placeholder '%s' (format: AUTO)

[AbhiPawar5]

Yup. That is correct

…

On Mon, 30 Oct, 2023, 2:25 pm Andrew Kane, ***@***.***> wrote: I'd confirm you're calling register_vector on the connection you're using for semantic_search. This script produces the same error: import numpy as npimport psycopg conn = psycopg.connect(dbname='test')conn.execute('SELECT %s', (np.array([1.0, 2.0, 3.0]),)) psycopg.ProgrammingError: cannot adapt type 'ndarray' using placeholder '%s' (format: AUTO) — Reply to this email directly, view it on GitHub <#40 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AK3AUDHBMDP4IB4DL2AVDNTYB5TPFAVCNFSM6AAAAAA6VRFYYWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTOOBUG42DIOBVGM> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[AbhiPawar5]

Hi @ankane, converting embeddings to np array solved my issue. It was numpy.ndarray before :)