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. > > 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 > > <https://groups.google.com/d/msgid/sqlalchemy/CAJuLiSUAhOc%3DQt0VaY8w%2BcBs3M3aJvByyEGah6zU_v-4TBGQfQ%40mail.gmail.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/89ec086d-2c7c-47e0-9433-a269b0668f79%40www.fastmail.com. For more options, visit https://groups.google.com/d/optout.
