How to call a Postgres stored function with an matrix as an argument from Python
あまりサンプルがなかったので，簡単なものを作成してみた。その２。
ここを参考にした。
- Postgres 16
- Python 12
二次元配列で数字のリストを渡して，ストアドファンクションで合計を計算し，返す
Postgresql
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | drop function if exists matfunc; create function matfunc(mat integer[][]) returns integer as $$ declare res integer := 0; i integer; j integer; begin for i in array_lower(mat,1)..array_upper(mat,1) loop for j in array_lower(mat,2)..array_upper(mat,2) loop res := res + mat[i][j]; end loop; end loop; return res; end; $$ language plpgsql; select matfunc(ARRAY[ARRAY[1,2,3], ARRAY[4,5,6]]); |
Python
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | import psycopg2 try: ps_connection = psycopg2.connect(user="username", password="password", host="127.0.0.1", port="5432", database="testdatabase") cursor = ps_connection.cursor() lst =[ [1,2,3],[4,5,6]] # call stored procedure cursor.execute('select matfunc(%s)', (lst,)) result = cursor.fetchall() for row in result: print("sum = ", row[0], ) except (Exception, psycopg2.DatabaseError) as error: print("Error while connecting to PostgreSQL", error) finally: # closing database connection. if ps_connection: cursor.close() ps_connection.close() print("PostgreSQL connection is closed") |