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?

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.

I'd like to use Python to execute this SQL for example

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.

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
`