[sqlalchemy] correct syntax to pass an sqlalchemy table class into postgresql functions?
Hi, I've built a postgresql function that takes as input a row from a table, and returns the sum of a particular column (of type array) between two specified indices. Here is how I've defined my function CREATE OR REPLACE FUNCTION specres_sum_segment(cube datadb.cube, index1 integer, index2 integer) RETURNS numeric LANGUAGE plpgsql STABLE AS $$ DECLARE result numeric; BEGIN select sum(f) from unnest(cube.specres[index1:index2]) as f into result; return result; END; $$; and here is how it works in psql. select c.pk from datadb.cube as c where(specres_sum_segment(c,2000,2005) 12000); This works and returns the cube entries where this condition is true. Now I'm trying to call this function with an SQLalchemy query. I've mapped a DeclarativeMeta class called Cube to my datadb.cube table, but when I try to run my session query I'm getting an error. My sqlalchemy session query is session.query(datadb.Cube).filter(func.specres_sum_segment(datadb.Cube,2000,2005)== 12000).all() but I get the error ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'DeclarativeMeta' What is the right syntax to use when passing a mapped SQLalchemy class into a function so postgresql will understand it? I'm using SQLalchemy 1.0.0 and PostgreSQL 9.3. Any help would be appreciated. Thanks. Cheers, Brian -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] correct syntax to pass an sqlalchemy table class into postgresql functions?
On 5/20/15 12:09 PM, Brian Cherinka wrote: Hi, I've built a postgresql function that takes as input a row from a table, and returns the sum of a particular column (of type array) between two specified indices. Here is how I've defined my function CREATE OR REPLACE FUNCTION specres_sum_segment(cube datadb.cube, index1 integer, index2 integer) RETURNS numeric LANGUAGE plpgsql STABLE AS $$ DECLARE result numeric; BEGIN select sum(f) from unnest(cube.specres[index1:index2]) as f into result; return result; END; $$; and here is how it works in psql. select c.pk from datadb.cube as c where(specres_sum_segment(c,2000,2005) 12000); This works and returns the cube entries where this condition is true. Now I'm trying to call this function with an SQLalchemy query. I've mapped a DeclarativeMeta class called Cube to my datadb.cube table, but when I try to run my session query I'm getting an error. My sqlalchemy session query is session.query(datadb.Cube).filter(func.specres_sum_segment(datadb.Cube,2000,2005)== 12000).all() but I get the error ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'DeclarativeMeta' sounds like your use of declarative is incorrect, please share the means by which the Cube class is declared as well as its base. What is the right syntax to use when passing a mapped SQLalchemy class into a function so postgresql will understand it? I'm using SQLalchemy 1.0.0 and PostgreSQL 9.3. Any help would be appreciated. Thanks. Cheers, Brian -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] correct syntax to pass an sqlalchemy table class into postgresql functions?
On 5/20/15 12:09 PM, Brian Cherinka wrote: Hi, I've built a postgresql function that takes as input a row from a table, and returns the sum of a particular column (of type array) between two specified indices. Here is how I've defined my function CREATE OR REPLACE FUNCTION specres_sum_segment(cube datadb.cube, index1 integer, index2 integer) RETURNS numeric LANGUAGE plpgsql STABLE AS $$ DECLARE result numeric; BEGIN select sum(f) from unnest(cube.specres[index1:index2]) as f into result; return result; END; $$; and here is how it works in psql. select c.pk from datadb.cube as c where(specres_sum_segment(c,2000,2005) 12000); This works and returns the cube entries where this condition is true. Now I'm trying to call this function with an SQLalchemy query. I've mapped a DeclarativeMeta class called Cube to my datadb.cube table, but when I try to run my session query I'm getting an error. My sqlalchemy session query is session.query(datadb.Cube).filter(func.specres_sum_segment(datadb.Cube,2000,2005)== 12000).all() this is the problem, you can't pass a declarative class or Table object as an argument to a function.It is extremely unusual that Postgresql allows the name of a table or alias to be an argument to a function, so to support this you need to hardwire the table or alias name in use to make this happen as a SQL expression, as in: print( session.query(Cube).filter( func.specres_sum_segment(literal_column(cube), 2000, 2005) == 12000 ).all() ) but I get the error ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'DeclarativeMeta' What is the right syntax to use when passing a mapped SQLalchemy class into a function so postgresql will understand it? I'm using SQLalchemy 1.0.0 and PostgreSQL 9.3. Any help would be appreciated. Thanks. Cheers, Brian -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] correct syntax to pass an sqlalchemy table class into postgresql functions?
Hi Michael,
Here is the beginning of my Cube class in SQLalchemy. It also has a bunch
of properties and methods I'm not printing here, to keep it short
class Cube(Base,ArrayOps):
__tablename__ = 'cube'
__table_args__ = {'autoload' : True, 'schema' : 'mangadatadb',
'extend_existing':True}
specres = deferred(Column(ARRAY(Float)))
def __repr__(self):
return 'Cube (pk={0}, plate={1}, ifudesign={2}, tag={3})'.format(self.pk,
self.plate, self.ifu.name,self.pipelineInfo.version.version)
Here is the chain (going backwards) that produces my Base
Base = db.Base
db = DatabaseConnection()
and here is my DatabaseConnection class
class DatabaseConnection(object):
_singletons = dict()
def __new__(cls, database_connection_string=None, expire_on_commit=True):
This overrides the object's usual creation mechanism.
if not cls in cls._singletons:
assert database_connection_string is not None, A database connection
string must be specified!
cls._singletons[cls] = object.__new__(cls)
#
# This is the custom initialization
#
me = cls._singletons[cls] # just for convenience (think self)
me.database_connection_string = database_connection_string
# change 'echo' to print each SQL query (for debugging/optimizing/the
curious)
me.engine = create_engine(me.database_connection_string, echo=False)
me.metadata = MetaData()
me.metadata.bind = me.engine
me.Base = declarative_base(bind=me.engine)
me.Session = scoped_session(sessionmaker(bind=me.engine, autocommit=True,
expire_on_commit=expire_on_commit))
Cheers, Brian
On Wednesday, May 20, 2015 at 12:51:36 PM UTC-4, Michael Bayer wrote:
On 5/20/15 12:09 PM, Brian Cherinka wrote:
Hi,
I've built a postgresql function that takes as input a row from a table,
and returns the sum of a particular column (of type array) between two
specified indices. Here is how I've defined my function
CREATE OR REPLACE FUNCTION specres_sum_segment(cube datadb.cube, index1
integer, index2 integer) RETURNS numeric
LANGUAGE plpgsql STABLE
AS $$
DECLARE result numeric;
BEGIN
select sum(f) from unnest(cube.specres[index1:index2]) as f into result;
return result;
END; $$;
and here is how it works in psql.
select c.pk from datadb.cube as c where(specres_sum_segment(c,2000,2005)
12000);
This works and returns the cube entries where this condition is true.
Now I'm trying to call this function with an SQLalchemy query. I've
mapped a DeclarativeMeta class called Cube to my datadb.cube table, but
when I try to run my session query I'm getting an error.
My sqlalchemy session query is
session.query(datadb.Cube).filter(func.specres_sum_segment(datadb.Cube,2000,2005)==
12000).all()
but I get the error
ProgrammingError: (psycopg2.ProgrammingError) can't adapt type
'DeclarativeMeta'
sounds like your use of declarative is incorrect, please share the means
by which the Cube class is declared as well as its base.
What is the right syntax to use when passing a mapped SQLalchemy class
into a function so postgresql will understand it? I'm using SQLalchemy
1.0.0 and PostgreSQL 9.3. Any help would be appreciated. Thanks.
Cheers, Brian
--
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 javascript:.
To post to this group, send email to sqlal...@googlegroups.com
javascript:.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
