https://supabase.com/ logo
Run SQL editor with API?
m

MarkeD

05/25/2023, 6:19 PM
Hello 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 PM
You 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 PM
I'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 PM
https://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 AM
Thanks, 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
`