I have a PostgreSQL table which is defined as follow
"""
CREATE TYPE similarity AS (
user_id integer,
similarity real,
rank integer
)
""",
"""
CREATE TABLE user_similarities (
user_id int REFERENCES books_user_id (user_id) ON UPDATE CASCADE ON DELETE CASCADE,
similarities similarity[]
)
"""
to insert values I use the following function which is totally inspired from http://www.postgresqltutorial.com/postgresql-python/insert/
def insert_similarities(connection, user_id,sim_user,sim,rank):
""" insert a similarities into user_similarities"""
sql = """INSERT INTO user_similarities(user_id,similarities)
VALUES(%s,%s::similarity[]);"""
#conn = None
#vendor_id = None
test = [(sim_user,sim_,rank)]
try:
# read database configuration
# connect to the PostgreSQL database
conn = connection
# create a new cursor
cur = conn.cursor()
# execute the INSERT statement
cur.execute(sql, (user_id,test))
# commit the changes to the database
conn.commit()
# close communication with the database
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
What I want to achieve, is to append a new similarity type value to the array of similarity of a given user. In https://www.postgresql.org/docs/9.1/functions-array.html there are already existing functions that do the job but I am not using them correctly and I don't know how to do it... The function that I want to use is:
I tried a lot of sql queries, here is one an example, obviously not successful
sql = """UPDATE user_similarities SET similarities = array_append(similarities, %s::similarity[]) WHERE user_id = %s;"""
to test the output I do the following:
conn = psycopg2.connect("dbname=recommendation_t user=Etienne")
cur = conn.cursor()
cur.execute(sql,([40,0.2,2],2))
And I get:
CannotCoerce: cannot cast type integer to similarity
LINE 1: ...T similarities = array_append(similarities, ARRAY[40,0.2,2]:...
^
array_append(similarities, %s::similarity)instead ofarray_append(similarities, %s::similarity[]). Second argument is an element, not array. I didn't try it : )