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 sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@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 > > <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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@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/b75565f8-f91c-4145-846d-8ed8e887c021%40www.fastmail.com. For more options, visit https://groups.google.com/d/optout.
