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.
