How to wrap some SQL in Python to execute it as Server Script?

Your highnesses!

We are running the hosted version, so I can’t execute SQL queries straight from the cli.

How would one wrap some SQL in Python code in order to execute it once as a server script?


Hi there,

If you just need to run a query one-off, you can use the System Console to run arbitrary SQL instructions:

There’s also the frappe.db.sql method, though in the safe_exec environment available to you in server scripts I believe you can only make SELECT calls.

the system console! I didn’t even know that existed :slight_smile: Even with a handy python/sql chooser. Thanks Peter!

There is a gui console too … try “system console” on search bar. You can launch sql queries from there.

Thanks again for pointing me to the lovely System Console!

Having said that, there does not seem to be the python/sql chooser that I saw in my local installation.

frappe.db.sql('UPDATE [...]')
gives me
raise frappe.PermissionError("Only SELECT SQL allowed in scripting")
so Peter is right about that.

I understand that must be as fool proof as possible and I admit using that happened out of pure laziness. Guess it is time to install on a server now…

Well, I’ve tried on frappecloud and i can run sql queries … To execute UPDATE, DELETE, etc… “Commit” must be checked …

BTW you can access your database also using the credentials that frappe cloud provides for remote connection. For that you need to be on $50/month instance.

Are you on an older version in Frappe Cloud? I believe the sql picker is new to v14

Version 13 on $25/month plan. Maybe 14 will come along soon

It’s available now, at any price point.

not in the hosted version it seems (at least I don’t see any option for it yet)

It’s there for me:

THERE it is… thanks!