Run SQL editor with API?
m
MarkeD
05/25/2023, 6:19 PMHello Supabasers 🙂 I wondered if it was possible to run the SQL I use in the webUI via SQL Editor > New Query via the API? Python preferably. I don't want to insert data, but programmatically setup a table and search function. Is it possible?
g
garyaustin
05/25/2023, 6:35 PMYou have full database port access if you want to use SQL commands. You just use the appropriate postgresql driver for your environment and it better be server side.
But I may be missing what you are asking.
m
MarkeD
05/25/2023, 8:30 PMI'd like to use Python to execute this SQL for example
`
-- Enable the pgvector extension to work with embedding vectors
--create extension vector;
-- Create a table to store your documents
create table fnd (
id bigserial primary key,
content text, -- corresponds to Document.pageContent
metadata jsonb, -- corresponds to Document.metadata
embedding vector(1536) -- 1536 works for OpenAI embeddings, change if needed
);But sounds like a python Postgres library would do it?
g
garyaustin
05/25/2023, 8:33 PMhttps://www.psycopg.org/psycopg3/docs/basic/usage.html
I don't know anything about which libraries for python, but I believe anything like this library (my first hit in google) works.
You just connect thru the database port as user postgres.
That example shows cursors, I don't know much about them, or if you need them. Could just be the example I clicked on.
m
MarkeD
05/26/2023, 10:57 AMThanks, yes it all worked. This was my script in case anyone else asks:
`
import psycopg2
from psycopg2.errors import DuplicateObject
import logging
import os
def execute_supabase_from_file(filepath, params, return_rows=False):
# Get the directory of this Python script
dir_path = os.path.dirname(os.path.realpath(__file__))
# Build the full filepath by joining the directory with the filename
filepath = os.path.join(dir_path, filepath)
rows = []
# read the SQL file
with open(filepath, 'r') as file:
sql = file.read()
# substitute placeholders in the SQL
sql = sql.format(**params)
connection_string = os.getenv('DB_CONNECTION_STRING', None)
if connection_string is None:
raise ValueError("No connection string")
try:
connection = psycopg2.connect(connection_string)
cursor = connection.cursor()
# execute the SQL - raise the error if already found
cursor.execute(sql)
# commit the transaction to save changes to the database
connection.commit()
if return_rows:
rows = cursor.fetchall()
logging.info(f"Successfully executed SQL script from {filepath}")
except (psycopg2.errors.DuplicateObject,
psycopg2.errors.DuplicateTable,
psycopg2.errors.DuplicateFunction) as e:
logging.info(str(e))
print(str(e))
except (Exception, psycopg2.Error) as error:
logging.error("Error while connecting to PostgreSQL", exc_info=True)
finally:
if (connection):
cursor.close()
connection.close()
logging.info("PostgreSQL connection is closed")
if rows:
return rows
return True
`