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 On Mon, 15 Jul 2019 at 16:42, Mike Bayer <[email protected]> wrote: > > > 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() > <https://docs.python.org/2/library/sqlite3.html#connection-objects> 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 [email protected]. > To post to this group, send email to [email protected]. > 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 > <https://groups.google.com/d/msgid/sqlalchemy/b6c0b14a-d722-4b92-97ef-c463c68b6faf%40googlegroups.com?utm_medium=email&utm_source=footer> > . > For more options, visit https://groups.google.com/d/optout. > > > -- > 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 [email protected]. > To post to this group, send email to [email protected]. > 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/b75565f8-f91c-4145-846d-8ed8e887c021%40www.fastmail.com > <https://groups.google.com/d/msgid/sqlalchemy/b75565f8-f91c-4145-846d-8ed8e887c021%40www.fastmail.com?utm_medium=email&utm_source=footer> > . > For more options, visit https://groups.google.com/d/optout. > -- 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 [email protected]. To post to this group, send email to [email protected]. 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/CAJuLiSUAhOc%3DQt0VaY8w%2BcBs3M3aJvByyEGah6zU_v-4TBGQfQ%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
