Create ad hoc custom function

[Ľuboš Katrinec]

I am looking for creating custom Python callable function that would be use in raw SQL queries, very same to Connection.create_function() which is perfectly working as I intend. I would like to be able to do it in SQLAlchemy in any Database engine.

I understood that sqlalchemy.sql.functions.Function class should come to hand in this regard. I do not understand how to use it though. I miss some example on that.

[Mike Bayer]

On Mon, Jul 15, 2019, at 10:14 AM, Ľuboš Katrinec wrote:

I am looking for creating custom Python callable function that would be use in raw SQL queries, very same to Connection.create_function() which is perfectly working as I intend. I would like to be able to do it in SQLAlchemy in any Database engine.

I understood that sqlalchemy.sql.functions.Function class should come to hand in this regard. I do not understand how to use it though. I miss some example on that.

connection.create_function() is doing something very special that only applies to the pysqlite database, which has to do with the fact that the sqlite database engine is embedded in the Python interpreter, and that sqlite's embeddable library allows the caller to embed C functions into the SQL language.      All other databases use a client/server model over TCP which does not allow for such a feature, meaning, a Python function in a particular python process can be invoked directly over a database connection, by the server.   This might not be what you actually need, though.

Within the traditional client/server architecture, the Postgresql database also supports Python in order to write stored procedures, meaning, you can write a Python function and have it be invoked by the server with Postgresql, however, you would need to transmit the source code of the function over to the database, and not a Python code object which is what your in-process Python function actually is.   There also may be many caveats to this system and I dont know anyone who has actually used it.   It's documented at https://www.postgresql.org/docs/current/plpython.html .  

In the more general sense, that you want to create custom functions on the database, most databases support stored procedures and you normally write them using the stored procedure language provided by the database.

The SQLAlchemy Function construct comes into play when invoking these procedures, at least, when they are written so that they may be executed as inline-SQL functions (which is not always the case).   If you construct a database-side procedure or function that can be invoked inline within a SQL statement, e.g. does not need something like SQL Server's "EXEC" in order to function, the "func." namespace can be used to render the name of the function as well as supply parameters to it, as in the examples at https://docs.sqlalchemy.org/en/13/core/tutorial.html#functions .

basically pysqlite's create_function() is nifty but it does something that does not directly apply to client/server databases that are not embedded in the Python interpreter.

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To post to this group, send email to sqlal...@googlegroups.com.

Visit this group at https://groups.google.com/group/sqlalchemy.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/b6c0b14a-d722-4b92-97ef-c463c68b6faf%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Ľuboš Katrinec]

Thanks Mike for your explanation. It totally makes sense. I have not realize that at all.

Just for the record I am trying to follow my ETL process idea to allow maximum flexibility to user so he can construct and shape data by using SQL commands from JSON/CSV data produced (EXTRACT) by any kind of data source (be it a shell script if needed). Actually I think I am mixing things together too much. Probably I will  change the design to use sqlite:///:memory: for processing/shaping (TRANSFORM) data and then move/copy (LOAD) final table view to real database.

Regards,

Lubos

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/b75565f8-f91c-4145-846d-8ed8e887c021%40www.fastmail.com.

[Mike Bayer]

On Mon, Jul 15, 2019, at 11:16 AM, Ľuboš Katrinec wrote:

Thanks Mike for your explanation. It totally makes sense. I have not realize that at all.

Just for the record I am trying to follow my ETL process idea to allow maximum flexibility to user so he can construct and shape data by using SQL commands from JSON/CSV data produced (EXTRACT) by any kind of data source (be it a shell script if needed). Actually I think I am mixing things together too much. Probably I will  change the design to use sqlite:///:memory: for processing/shaping (TRANSFORM) data and then move/copy (LOAD) final table view to real database.

yeah that is a great idea.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAJuLiSUAhOc%3DQt0VaY8w%2BcBs3M3aJvByyEGah6zU_v-4TBGQfQ%40mail.gmail.com.