date:20150915

Re: [sqlalchemy] Expressions generated by select(...) changes when submitted in session.query

2015-09-15 Thread Mike Bayer

On 9/15/15 4:52 PM, Mourad Ben Cheikh wrote:

Hi,
I am using sqlalchemy in flask, essentially through flask-sqlalchemy, 
and my goal is to generate and execute queries on the fly. I am pretty 
new to sqlalchemy ( a great challenge and a great work) I am using a 
model generated by automap.

The entities are automap classes

I managed to produce a select expression like this one:

>>> expr

>>> print expr

SELECT devis."N_Devis" AS "N\xb0Devis", devis."Date" AS "Date", 
devis."Code Client" AS "Code Client",
 (SELECT client."Nom" FROM client WHERE client."Code" = 
devis."Code Client") AS "Nom Client",

  devis."Nom Contact" AS "Nom Contact"
FROM devis

Note the embbed query (I formatted manually the print result here for 
easy reading).
This expression works perfectly when submitted directly to the RDBMS 
(mysql)

the probleme come when i generate the correspondant query

Query=db.session.query(expr)
you wouldn't want to do this.   If you have a select() object and just 
want the rows back, you'd call:

result = db.session.execute(expr)

If OTOH you have some objects to get back, you'd want to say:

q = db.session.query(MyClass).from_statement(my_select)

>>> Query

>>> print Query
SELECT"N\xb0Devis" AS "N\xb0Devis", "Date" AS "Date", "Code Client" AS 
"Code Client", "Nom Client" AS "Nom Client", "Nom Contact" AS "Nom 
Contact"
FROM (SELECT devis."N_Devis" AS "N\xb0Devis", devis."Date" AS "Date", 
devis."Code Client" AS "Code Client", (SELECT client."Nom"

FROM client
WHERE client."Code" = devis."Code Client") AS "Nom Client", devis."Nom 
Contact" AS "Nom Contact"

FROM devis)

The entire inital query was embedded as a subquery in the FROM clause
then

>>> Query.count()
Traceback (most recent call last):

..
  File 
"/home/mb/envjam/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", 
line 442, in do_execute

cursor.execute(statement, parameters)
  File 
"/home/mb/envjam/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 
205, in execute

self.errorhandler(self, exc, value)
  File 
"/home/mb/envjam/local/lib/python2.7/site-packages/MySQLdb/connections.py", 
line 36, in defaulterrorhandler

raise errorclass, errorvalue
OperationalError: (_mysql_exceptions.OperationalError) (1248, 'Every 
derived table must have its own alias')

So, maybe i'm missing something , or maybe it's the flask-sqlalchemy 
environment, my db object being an SQLAlchemy class of flask-sqlalchemy:

>>> db
engine='mysql://root:@localhost/biogal?charset=utf8'>

I know I coud add an alias to avoid the error, but doing so I end in 
other issues with my soft.

My question is: how to tell the query object to keep the initial 
expression inchanged ?

Thank you for your help

Regards
--
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.

--
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.

[sqlalchemy] Expressions generated by select(...) changes when submitted in session.query

2015-09-15 Thread Mourad Ben Cheikh

Hi,
I am using sqlalchemy in flask, essentially through flask-sqlalchemy, and 
my goal is to generate and execute queries on the fly. I am pretty new to 
sqlalchemy ( a great challenge and a great work) I am using a model 
generated by automap.
The entities are automap classes

I managed to produce a select expression like this one:

>>> expr


>>> print expr

SELECT devis."N_Devis" AS "N\xb0Devis", devis."Date" AS "Date", devis."Code 
Client" AS "Code Client", 
 (SELECT client."Nom" FROM client WHERE client."Code" = 
devis."Code Client") AS "Nom Client",
  devis."Nom Contact" AS "Nom Contact"
FROM devis

Note the embbed query (I formatted manually the print result here for easy 
reading).
This expression works perfectly when submitted directly to the RDBMS (mysql)

the probleme come when i generate the correspondant query

Query=db.session.query(expr)

>>> Query


>>> print Query
SELECT"N\xb0Devis" AS "N\xb0Devis", "Date" AS "Date", "Code Client" AS 
"Code Client", "Nom Client" AS "Nom Client", "Nom Contact" AS "Nom Contact"
FROM (SELECT devis."N_Devis" AS "N\xb0Devis", devis."Date" AS "Date", 
devis."Code Client" AS "Code Client", (SELECT client."Nom" 
FROM client 
WHERE client."Code" = devis."Code Client") AS "Nom Client", devis."Nom 
Contact" AS "Nom Contact" 
FROM devis)

The entire inital query was embedded as a subquery in the FROM clause
then

>>> Query.count()
Traceback (most recent call last):

..
  File 
"/home/mb/envjam/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py",
 
line 442, in do_execute
cursor.execute(statement, parameters)
  File 
"/home/mb/envjam/local/lib/python2.7/site-packages/MySQLdb/cursors.py", 
line 205, in execute
self.errorhandler(self, exc, value)
  File 
"/home/mb/envjam/local/lib/python2.7/site-packages/MySQLdb/connections.py", 
line 36, in defaulterrorhandler
raise errorclass, errorvalue
OperationalError: (_mysql_exceptions.OperationalError) (1248, 'Every 
derived table must have its own alias')

So, maybe i'm missing something , or maybe it's the flask-sqlalchemy 
environment, my db object being an SQLAlchemy class of flask-sqlalchemy:

>>> db


I know I coud add an alias to avoid the error, but doing so I end in other 
issues with my soft.


My question is: how to tell the query object to keep the initial expression 
inchanged ?

Thank you for your help

Regards

-- 
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] Cast model id column from String to Integer

2015-09-15 Thread Mike Bayer




On 9/15/15 4:10 PM, Mike Bayer wrote:



On 9/15/15 2:47 PM, Katie Wurman wrote:



class Person(Base):
__tablename__ = 'person'
id = Column('id_string', CastToIntegerType, primary_key=True)

pets = relationship('Pets', 
primaryjoin='foreign(Pets.person_id)==Person.id')


class Pets(Base):
__tablename__ = 'pets'
 id = Column('id', Integer, primary_key=True)
 person_id = Column('person_id', 
Integer, ForeignKey('Person.id'), primary_key=True)



In this case, when I query for person.pets the join condition between 
Person and Pets becomes: public.pets.person_id = CAST(%(param_1)s AS 
VARCHAR)
pets.person_id is persisted as an Integer, so I would think a 
comparison of pets.person_id to an integer value should be using 
Integer as the type?   Let's check...oh well, it does.   It's because 
that particular comparison is generated on a column-by-column basis.  
That's now bug 
https://bitbucket.org/zzzeek/sqlalchemy/issues/3530/bind-param-replacement-in-join_condition. 



OK this one is throwing me off, but actually if you really intend for 
this mapping to work in every way right now, you have to use CAST, else 
the relationship will not work for eager loading.   An eager load 
renders both columns directly so you'd need CAST in the join condition.


That is, to run both of these queries:


p1 = s.query(Person).first()


p1 = s.query(Person).options(joinedload(Person.pets)).first()

the mapping has to look like this:

pets = relationship('Pets',
primaryjoin="foreign(Pets.person_id) == cast(Person.id, Integer)")



the eager join generates:

FROM person LEFT OUTER JOIN pets AS pets_1 ON pets_1.person_id = 
CAST(person.id_string AS INTEGER)


the lazyload generates:

WHERE pets.person_id = CAST(CAST(%(param_1)s AS VARCHAR) AS INTEGER)


some kind of smartness could perhaps figure out that this is a double 
CAST.But really, in other cases it might not be clear that "id" and 
"id_string" here both deal with the same type on the Python side.


I'd probably stick with this approach for now.  I have doubts about both 
issues I've created.










So I'd stick a type_coerce in there and be done with it, which won't 
generate the CAST either, if that was the problemexceptthat 
doesn't seem to be working either because of the way type_coerce 
doesn't create a permanent construct, so that's bug 
https://bitbucket.org/zzzeek/sqlalchemy/issues/3531/bind-param-replacement-in-join_condition.


Workaround now is at the bottom of 3531 which looks like:

from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql import ColumnElement
from sqlalchemy.sql.elements import _clone


class LateTypeCoerce(ColumnElement):
def __init__(self, element, type_):
self.element = element
self.type = type_
self._proxies = [element]

def self_group(self, against=None):
return 
LateTypeCoerce(self.element.self_group(against=against), self.type)


def get_children(self, **kwargs):
return self.element,

def _copy_internals(self, clone=_clone, **kw):
self.element = clone(self.element, **kw)

@property
def _from_objects(self):
return self.element._from_objects

def _make_proxy(self, selectable, **kw):
return self.element._make_proxy(selectable, **kw)


then in mapping:

class Person(Base):
__tablename__ = 'person'
id = Column('id_string', CastToIntegerType, primary_key=True)

pets = relationship('Pets',
primaryjoin=lambda: foreign(Pets.person_id) == 
LateTypeCoerce(Person.id, Integer))




















I can amend this by explicitly casting Person.id to Integer in the 
primaryjoin expression, but that's a little redundant. Is there a 
cleaner way to do this?


Thanks!

On Tuesday, September 15, 2015 at 10:49:51 AM UTC-7, Michael Bayer 
wrote:




On 9/15/15 1:19 PM, Katie Wurman wrote:

Hi,

I'm having trouble implementing a model whose 'id' column needs
to be cast to Integer type. Below is the implementation I've got
so far:

class CastToIntegerType(types.TypeDecorator):
'''
Converts stored String values to Integer via CAST operation
'''
impl = types.Numeric
def column_expression(self, col):
return func.cast(col, Integer)

class Person(Base):
 __tablename__ = "person"
id = Column('id_string', CastToIntegerType, primary_key=True)


Then when I run the query

>> person = Person.query.get(12345)

I see the following error:

sqlalchemy.exc.ProgrammingError: (ProgrammingError) operator
does not exist: text = integer
LINE 3: WHERE public.person.id_string = 12345
 ^
HINT:  No operator matches the given name and argument type(s).
You might need to add explicit type casts.

Any ideas what I might be doing wrong?

OK, this is close, column_expression applies to how the column
list in the SELECT renders, e.g

Re: [sqlalchemy] Cast model id column from String to Integer

2015-09-15 Thread Mike Bayer




On 9/15/15 2:47 PM, Katie Wurman wrote:
Ok thanks! Adding a bind_expression to my CastToInteger type ensures 
that when Person.id is included in a WHERE clause, the param is cast 
to varchar.


This is ok, except now I have the following situation:

class CastToIntegerType(types.TypeDecorator):
impl = types.Numeric
def column_expression(self, col):
return func.cast(col, Integer)
def bind_expression(self,col):
return func.cast(col, String)


I think the "impl" here probably needs to be String, since "impl" is to 
represent the actual underlying datatype in the DB.





class Person(Base):
__tablename__ = 'person'
id = Column('id_string', CastToIntegerType, primary_key=True)

pets = relationship('Pets', 
primaryjoin='foreign(Pets.person_id)==Person.id')


class Pets(Base):
__tablename__ = 'pets'
 id = Column('id', Integer, primary_key=True)
 person_id = Column('person_id', Integer, ForeignKey('Person.id'), 
primary_key=True)



In this case, when I query for person.pets the join condition between 
Person and Pets becomes: public.pets.person_id = CAST(%(param_1)s AS 
VARCHAR)
pets.person_id is persisted as an Integer, so I would think a comparison 
of pets.person_id to an integer value should be using Integer as the 
type?   Let's check...oh well, it does.   It's because that particular 
comparison is generated on a column-by-column basis.  That's now bug 
https://bitbucket.org/zzzeek/sqlalchemy/issues/3530/bind-param-replacement-in-join_condition. 



So I'd stick a type_coerce in there and be done with it, which won't 
generate the CAST either, if that was the problemexceptthat 
doesn't seem to be working either because of the way type_coerce doesn't 
create a permanent construct, so that's bug 
https://bitbucket.org/zzzeek/sqlalchemy/issues/3531/bind-param-replacement-in-join_condition.


Workaround now is at the bottom of 3531 which looks like:

from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql import ColumnElement
from sqlalchemy.sql.elements import _clone


class LateTypeCoerce(ColumnElement):
def __init__(self, element, type_):
self.element = element
self.type = type_
self._proxies = [element]

def self_group(self, against=None):
return LateTypeCoerce(self.element.self_group(against=against), 
self.type)


def get_children(self, **kwargs):
return self.element,

def _copy_internals(self, clone=_clone, **kw):
self.element = clone(self.element, **kw)

@property
def _from_objects(self):
return self.element._from_objects

def _make_proxy(self, selectable, **kw):
return self.element._make_proxy(selectable, **kw)


then in mapping:

class Person(Base):
__tablename__ = 'person'
id = Column('id_string', CastToIntegerType, primary_key=True)

pets = relationship('Pets',
primaryjoin=lambda: foreign(Pets.person_id) == 
LateTypeCoerce(Person.id, Integer))




















I can amend this by explicitly casting Person.id to Integer in the 
primaryjoin expression, but that's a little redundant. Is there a 
cleaner way to do this?


Thanks!

On Tuesday, September 15, 2015 at 10:49:51 AM UTC-7, Michael Bayer wrote:



On 9/15/15 1:19 PM, Katie Wurman wrote:

Hi,

I'm having trouble implementing a model whose 'id' column needs
to be cast to Integer type. Below is the implementation I've got
so far:

class CastToIntegerType(types.TypeDecorator):
'''
Converts stored String values to Integer via CAST operation
'''
impl = types.Numeric
def column_expression(self, col):
return func.cast(col, Integer)

class Person(Base):
 __tablename__ = "person"
id = Column('id_string', CastToIntegerType, primary_key=True)


Then when I run the query

>> person = Person.query.get(12345)

I see the following error:

sqlalchemy.exc.ProgrammingError: (ProgrammingError) operator does
not exist: text = integer
LINE 3: WHERE public.person.id_string = 12345
 ^
HINT:  No operator matches the given name and argument type(s).
You might need to add explicit type casts.

Any ideas what I might be doing wrong?

OK, this is close, column_expression applies to how the column
list in the SELECT renders, e.g. "SELECT CAST(mycolumn, INT) as
mycolumn", for a SQL expression that wraps around a value you're
sending into the WHERE in a comparison expression, you'd use
bind_expression for that:

http://docs.sqlalchemy.org/en/rel_1_0/core/type_api.html#sqlalchemy.types.TypeEngine.bind_expression

,
and in this case you'd be CASTing to String so that the SQL
renders as "WHERE id_string = CAST(12345, VARCHAR)".








Thanks!
-- 
You received this message b

Re: [sqlalchemy] AttributeError: 'NoneType' object has no attribute 'accepts_scalar_loader'

2015-09-15 Thread dewey

I'll see if I can reproduce this in a simple example.it's currently in 
a job being run every night from a Celery-Beat scheduler..

Interestingly, just changing the data in the DB made this problem go away 
temporarily...we were seeing this error thrown every night on staging 
for 5 days straight,
But after we copied the Prod data into staging, it went away for about a 
week.

This morning, it threw on both staging and prod..first time we've seen 
it in Prod

Thanks for your thoughts...
Dewey

-- 
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] Cast model id column from String to Integer

2015-09-15 Thread Katie Wurman

Ok thanks! Adding a bind_expression to my CastToInteger type ensures that 
when Person.id is included in a WHERE clause, the param is cast to varchar. 

This is ok, except now I have the following situation: 

class CastToIntegerType(types.TypeDecorator):
impl = types.Numeric
def column_expression(self, col):
return func.cast(col, Integer)
  
def bind_expression(self,col):
return func.cast(col, String)

class Person(Base):
__tablename__ = 'person'
id = Column('id_string', CastToIntegerType, primary_key=True)

pets = relationship('Pets', 
primaryjoin='foreign(Pets.person_id)==Person.id')

class Pets(Base):
__tablename__ = 'pets'
 id = Column('id', Integer, primary_key=True)
 person_id = Column('person_id', Integer, ForeignKey('Person.id'), 
primary_key=True)


In this case, when I query for person.pets the join condition between 
Person and Pets becomes: public.pets.person_id = CAST(%(param_1)s AS 
VARCHAR)

I can amend this by explicitly casting Person.id to Integer in the 
primaryjoin expression, but that's a little redundant. Is there a cleaner 
way to do this? 

Thanks!

On Tuesday, September 15, 2015 at 10:49:51 AM UTC-7, Michael Bayer wrote:
>
>
>
> On 9/15/15 1:19 PM, Katie Wurman wrote:
>
> Hi,  
>
> I'm having trouble implementing a model whose 'id' column needs to be cast 
> to Integer type. Below is the implementation I've got so far: 
>
> class CastToIntegerType(types.TypeDecorator):
> '''
> Converts stored String values to Integer via CAST operation
> '''
> impl = types.Numeric
> def column_expression(self, col):
> return func.cast(col, Integer)
>
> class Person(Base):
>  __tablename__ = "person"
> id = Column('id_string', CastToIntegerType, primary_key=True)
>
>
> Then when I run the query 
>
> >> person = Person.query.get(12345)
>
> I see the following error:
>
> sqlalchemy.exc.ProgrammingError: (ProgrammingError) operator does not 
> exist: text = integer
> LINE 3: WHERE public.person.id_string = 12345
>  ^
> HINT:  No operator matches the given name and argument type(s). You might 
> need to add explicit type casts.
>
> Any ideas what I might be doing wrong? 
>
> OK, this is close, column_expression applies to how the column list in the 
> SELECT renders, e.g. "SELECT CAST(mycolumn, INT) as mycolumn", for a SQL 
> expression that wraps around a value you're sending into the WHERE in a 
> comparison expression, you'd use bind_expression for that: 
> http://docs.sqlalchemy.org/en/rel_1_0/core/type_api.html#sqlalchemy.types.TypeEngine.bind_expression,
>  
> and in this case you'd be CASTing to String so that the SQL renders as 
> "WHERE id_string = CAST(12345, VARCHAR)".
>
>
>
>
>
>
>
> Thanks!
> -- 
> 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 .
> To post to this group, send email to sqlal...@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] AttributeError: 'NoneType' object has no attribute 'accepts_scalar_loader'

2015-09-15 Thread Mike Bayer




On 9/15/15 2:05 PM, dewey wrote:


The contents of the full traceback was:


Traceback (most recent call last):

 File 
"/opt/paysys/python/lib/python2.7/site-packages/celery/app/trace.py", 
line 240, in trace_task


   R = retval = fun(*args, **kwargs)

 File 
"/opt/paysys/python/lib/python2.7/site-packages/celery/app/trace.py", 
line 438, in __protected_call__


   return self.run(*args, **kwargs)

 File "/opt/paysys/builds/20150907103013/source/jobs/spawn.py", line 
238, in loaderDaily


   startLoaderViaQueue(jobParams)

 File 
"/opt/paysys/builds/20150907103013/source/jobs/datain/loader.py", line 
459, in startViaQueue


   start(**job_params)

 File 
"/opt/paysys/builds/20150907103013/source/jobs/datain/loader.py", line 
443, in start


   startLoader(sess, jobArgs)  # , useFileNameForProcessDay=True

 File 
"/opt/paysys/builds/20150907103013/source/jobs/datain/loader.py", line 
404, in startLoader


   result = endFileProcessingFunc(sess, jobArgs, log, None)   # do any 
final work on the DB


 File 
"/opt/paysys/builds/20150907103013/source/jobs/datain/storage/fixed.py", 
line 327, in examinerStoreCtrAtts


   cot = sess.query(ContribTitle).get(int(cot_gem_id))

 File 
"/opt/paysys/python/lib/python2.7/site-packages/sqlalchemy/orm/query.py", 
line 840, in get


   return loading.load_on_ident(self, key)

 File 
"/opt/paysys/python/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", 
line 231, in load_on_ident


   return q.one()

 File 
"/opt/paysys/python/lib/python2.7/site-packages/sqlalchemy/orm/query.py", 
line 2395, in one


   ret = list(self)

 File 
"/opt/paysys/python/lib/python2.7/site-packages/sqlalchemy/orm/query.py", 
line 2437, in __iter__


   self.session._autoflush()

 File 
"/opt/paysys/python/lib/python2.7/site-packages/sqlalchemy/orm/session.py", 
line 1198, in _autoflush


   self.flush()

 File 
"/opt/paysys/python/lib/python2.7/site-packages/sqlalchemy/orm/session.py", 
line 1919, in flush


   self._flush(objects)

 File 
"/opt/paysys/python/lib/python2.7/site-packages/sqlalchemy/orm/session.py", 
line 2037, in _flush


   transaction.rollback(_capture_exception=True)

 File 
"/opt/paysys/python/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", 
line 63, in __exit__


   compat.reraise(type_, value, traceback)

 File 
"/opt/paysys/python/lib/python2.7/site-packages/sqlalchemy/orm/session.py", 
line 2037, in _flush


   transaction.rollback(_capture_exception=True)

 File 
"/opt/paysys/python/lib/python2.7/site-packages/sqlalchemy/orm/session.py", 
line 403, in rollback


   transaction._rollback_impl()

 File 
"/opt/paysys/python/lib/python2.7/site-packages/sqlalchemy/orm/session.py", 
line 434, in _rollback_impl


   self._restore_snapshot(dirty_only=self.nested)

 File 
"/opt/paysys/python/lib/python2.7/site-packages/sqlalchemy/orm/session.py", 
line 288, in _restore_snapshot


   s._expire(s.dict, self.session.identity_map._modified)

 File 
"/opt/paysys/python/lib/python2.7/site-packages/sqlalchemy/orm/state.py", 
line 385, in _expire


   if impl.accepts_scalar_loader and \

AttributeError: 'NoneType' object has no attribute 'accepts_scalar_loader'



that element cannot be None, unless something is very broken with the 
mappings and/or the way the program is being run (e.g. weird things with 
threads perhaps).  Would need to see a reproducing test case (See 
http://stackoverflow.com/help/mcve).




--
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.


--
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] AttributeError: 'NoneType' object has no attribute 'accepts_scalar_loader'

2015-09-15 Thread dewey

 

The contents of the full traceback was:


Traceback (most recent call last):

 File "/opt/paysys/python/lib/python2.7/site-packages/celery/app/trace.py", 
line 240, in trace_task

   R = retval = fun(*args, **kwargs)

 File "/opt/paysys/python/lib/python2.7/site-packages/celery/app/trace.py", 
line 438, in __protected_call__

   return self.run(*args, **kwargs)

 File "/opt/paysys/builds/20150907103013/source/jobs/spawn.py", line 238, 
in loaderDaily

   startLoaderViaQueue(jobParams)

 File "/opt/paysys/builds/20150907103013/source/jobs/datain/loader.py", 
line 459, in startViaQueue

   start(**job_params)

 File "/opt/paysys/builds/20150907103013/source/jobs/datain/loader.py", 
line 443, in start

   startLoader(sess, jobArgs)  # , useFileNameForProcessDay=True

 File "/opt/paysys/builds/20150907103013/source/jobs/datain/loader.py", 
line 404, in startLoader

   result = endFileProcessingFunc(sess, jobArgs, log, None)   # do any 
final work on the DB

 File 
"/opt/paysys/builds/20150907103013/source/jobs/datain/storage/fixed.py", 
line 327, in examinerStoreCtrAtts

   cot = sess.query(ContribTitle).get(int(cot_gem_id))

 File 
"/opt/paysys/python/lib/python2.7/site-packages/sqlalchemy/orm/query.py", 
line 840, in get

   return loading.load_on_ident(self, key)

 File 
"/opt/paysys/python/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", 
line 231, in load_on_ident

   return q.one()

 File 
"/opt/paysys/python/lib/python2.7/site-packages/sqlalchemy/orm/query.py", 
line 2395, in one

   ret = list(self)

 File 
"/opt/paysys/python/lib/python2.7/site-packages/sqlalchemy/orm/query.py", 
line 2437, in __iter__

   self.session._autoflush()

 File 
"/opt/paysys/python/lib/python2.7/site-packages/sqlalchemy/orm/session.py", 
line 1198, in _autoflush

   self.flush()

 File 
"/opt/paysys/python/lib/python2.7/site-packages/sqlalchemy/orm/session.py", 
line 1919, in flush

   self._flush(objects)

 File 
"/opt/paysys/python/lib/python2.7/site-packages/sqlalchemy/orm/session.py", 
line 2037, in _flush

   transaction.rollback(_capture_exception=True)

 File 
"/opt/paysys/python/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py",
 
line 63, in __exit__

   compat.reraise(type_, value, traceback)

 File 
"/opt/paysys/python/lib/python2.7/site-packages/sqlalchemy/orm/session.py", 
line 2037, in _flush

   transaction.rollback(_capture_exception=True)

 File 
"/opt/paysys/python/lib/python2.7/site-packages/sqlalchemy/orm/session.py", 
line 403, in rollback

   transaction._rollback_impl()

 File 
"/opt/paysys/python/lib/python2.7/site-packages/sqlalchemy/orm/session.py", 
line 434, in _rollback_impl

   self._restore_snapshot(dirty_only=self.nested)

 File 
"/opt/paysys/python/lib/python2.7/site-packages/sqlalchemy/orm/session.py", 
line 288, in _restore_snapshot

   s._expire(s.dict, self.session.identity_map._modified)

 File 
"/opt/paysys/python/lib/python2.7/site-packages/sqlalchemy/orm/state.py", 
line 385, in _expire

   if impl.accepts_scalar_loader and \

AttributeError: 'NoneType' object has no attribute 'accepts_scalar_loader'

-- 
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] Cast model id column from String to Integer

2015-09-15 Thread Mike Bayer




On 9/15/15 1:19 PM, Katie Wurman wrote:

Hi,

I'm having trouble implementing a model whose 'id' column needs to be 
cast to Integer type. Below is the implementation I've got so far:


class CastToIntegerType(types.TypeDecorator):
'''
Converts stored String values to Integer via CAST operation
'''
impl = types.Numeric
def column_expression(self, col):
return func.cast(col, Integer)

class Person(Base):
 __tablename__ = "person"
id = Column('id_string', CastToIntegerType, primary_key=True)


Then when I run the query

>> person = Person.query.get(12345)

I see the following error:

sqlalchemy.exc.ProgrammingError: (ProgrammingError) operator does not 
exist: text = integer

LINE 3: WHERE public.person.id_string = 12345
 ^
HINT:  No operator matches the given name and argument type(s). You 
might need to add explicit type casts.


Any ideas what I might be doing wrong?
OK, this is close, column_expression applies to how the column list in 
the SELECT renders, e.g. "SELECT CAST(mycolumn, INT) as mycolumn", for a 
SQL expression that wraps around a value you're sending into the WHERE 
in a comparison expression, you'd use bind_expression for that: 
http://docs.sqlalchemy.org/en/rel_1_0/core/type_api.html#sqlalchemy.types.TypeEngine.bind_expression, 
and in this case you'd be CASTing to String so that the SQL renders as 
"WHERE id_string = CAST(12345, VARCHAR)".









Thanks!
--
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.


--
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] AttributeError: 'NoneType' object has no attribute 'accepts_scalar_loader'

2015-09-15 Thread Mike Bayer




On 9/15/15 1:02 PM, dewey wrote:

I've got a SA session, and i'm running the following:

cot = sess.query(MyTable).get( int(someIntAsString) )

I'm seeing the error:

AttributeError: 'NoneType' object has no attribute 'accepts_scalar_loader'

I'm guessing that means the record was not found..any suggestions 
what else I should look for??

full stack trace might shed some light on what's being misinterpreted





Thanks,
Dewey
--
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.


--
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.

[sqlalchemy] Cast model id column from String to Integer

2015-09-15 Thread Katie Wurman

Hi, 

I'm having trouble implementing a model whose 'id' column needs to be cast 
to Integer type. Below is the implementation I've got so far: 

class CastToIntegerType(types.TypeDecorator):
'''
Converts stored String values to Integer via CAST operation
'''
impl = types.Numeric
def column_expression(self, col):
return func.cast(col, Integer)

class Person(Base):
 __tablename__ = "person"
id = Column('id_string', CastToIntegerType, primary_key=True)


Then when I run the query 

>> person = Person.query.get(12345)

I see the following error:

sqlalchemy.exc.ProgrammingError: (ProgrammingError) operator does not 
exist: text = integer
LINE 3: WHERE public.person.id_string = 12345
 ^
HINT:  No operator matches the given name and argument type(s). You might 
need to add explicit type casts.

Any ideas what I might be doing wrong? 

Thanks!

-- 
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.

[sqlalchemy] AttributeError: 'NoneType' object has no attribute 'accepts_scalar_loader'

2015-09-15 Thread dewey

I've got a SA session, and i'm running the following:

cot = sess.query(MyTable).get( int(someIntAsString) )

I'm seeing the error:

AttributeError: 'NoneType' object has no attribute 'accepts_scalar_loader'

I'm guessing that means the record was not found..any suggestions what 
else I should look for??

Thanks,
Dewey

-- 
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] Re: polymorphic identity after_delete event

2015-09-15 Thread Mike Bayer




On 9/15/15 10:09 AM, Jakub Bąk wrote:
I used before_delete and after_commit and it works like a charm. 
Thanks again!


I have another problem related to this setup. I have a property 
defined on the Image model:


|
@property
deffilename(self):
return'{basename}.{extension}'.format(basename=self.basename,extension=self.extension)
|

When I make this query:

|
image =db.session.query(Image).get(100)
image.filename
|

I get the filename property istantly but when I do it like this:

|
node =db.session.query(Node).get(100)
node.filename
|

Another query has to be issued which is not what I want. Do you know 
any possible solution to that problem Michael?


that is again the way the joined-table inheritance works; by default it 
only queries the base table.   Per query you'd do it like this:


session.query(Node).with_polymorphic(Image).get(100)







W dniu wtorek, 25 sierpnia 2015 17:16:16 UTC+2 użytkownik Michael 
Bayer napisał:




On 8/25/15 10:05 AM, Jakub Bąk wrote:

I just came across a solution to this problem.
Adding 'with_polymorphic': '*' to __mapper_args__ on the Node
model was enough.

|
classNode(db.Model):
   id =db.Column(db.Integer,primary_key=True)
   type =db.Column(db.String(20))
   name =db.Column(db.String(30),nullable=False)
   date_added =db.Column(db.DateTime,default=datetime.now())
   parent_id =db.Column(db.Integer,db.ForeignKey('node.id
'),nullable=True)
   path =db.Column(db.String(200),default='')
 __mapper_args__ ={'polymorphic_on':type,
'with_polymorphic':'*'}
|



OK, yeah that's a funny problem you have.   The with_polymorphic
in all cases is going to lead to heavier queries, because there's
the join but also if you already have joins between Node classes,
you'll start to see some very long queries with lots of joins /
sub-joins.

A more portable solution would be to also implement
before_delete(), where you either ensure that the Image columns
are loaded, or you add the files to be deleted to some kind of
collection; then you'd actually delete the files using a safer
event like after_commit(), so that way if your transaction fails,
the files are preserved.





-- 
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 .
To post to this group, send email to sqlal...@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.


--
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] Re: polymorphic identity after_delete event

2015-09-15 Thread Jakub Bąk

I used before_delete and after_commit and it works like a charm. Thanks 
again!

I have another problem related to this setup. I have a property defined on 
the Image model:

@property
def filename(self):
return '{basename}.{extension}'.format(basename=self.basename, extension
=self.extension)

When I make this query:

image = db.session.query(Image).get(100)
image.filename

I get the filename property istantly but when I do it like this:

node = db.session.query(Node).get(100)
node.filename

Another query has to be issued which is not what I want. Do you know any 
possible solution to that problem Michael? 



W dniu wtorek, 25 sierpnia 2015 17:16:16 UTC+2 użytkownik Michael Bayer 
napisał:
>
>
>
> On 8/25/15 10:05 AM, Jakub Bąk wrote:
>
> I just came across a solution to this problem. Adding 'with_polymorphic': 
> '*' to __mapper_args__ on the Node model was enough. 
>
> class Node(db.Model):
>id = db.Column(db.Integer, primary_key=True)
>type = db.Column(db.String(20))
>name = db.Column(db.String(30), nullable=False)
>date_added = db.Column(db.DateTime, default=datetime.now())
>parent_id = db.Column(db.Integer, db.ForeignKey('node.id'), nullable=
> True)
>path = db.Column(db.String(200), default='')
>__mapper_args__ = {'polymorphic_on': type,
>   'with_polymorphic': '*' }
>
>
> OK, yeah that's a funny problem you have.   The with_polymorphic in all 
> cases is going to lead to heavier queries, because there's the join but 
> also if you already have joins between Node classes, you'll start to see 
> some very long queries with lots of joins / sub-joins.
>
> A more portable solution would be to also implement before_delete(), where 
> you either ensure that the Image columns are loaded, or you add the files 
> to be deleted to some kind of collection; then you'd actually delete the 
> files using a safer event like after_commit(), so that way if your 
> transaction fails, the files are preserved.
>
>
>
>
>
> -- 
> 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 .
> To post to this group, send email to sqlal...@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] ORM: walk() related objects

2015-09-15 Thread Pavel S

Final version:

def walk_related(obj, enableDuplicates=False, level=-1, memo=None):
memo = memo or set()

insp = inspect(obj)

for relobj in insp.mapper.cascade_iterator('delete', insp):
if enableDuplicates or relobj[0] not in memo:
memo.add(relobj[0])

yield level, relobj[0]

relatedObjects = walk_related(relobj[0],
enableDuplicates=enableDuplicates, level=level + 1, memo=
memo)

for walked_relobj in relatedObjects:
yield walked_relobj



Dne pondělí 14. září 2015 11:57:05 UTC+2 Pavel S napsal(a):
>
> Hi,
>
> I just realized that I need your first solution, since I need to get only 
> those objects that would cascade in case of deletion. 
>
> But thanks anyhow...
>
> P
>
> Dne čtvrtek 10. září 2015 15:35:39 UTC+2 Michael Bayer napsal(a):
>>
>>
>>
>> On 9/10/15 8:48 AM, Pavel S wrote:
>>
>> Let's say, I have declarative classes A, B, C, D.
>>
>> A is the parent
>> B has FK&relationship to A
>> C has FK&relationship to B,
>> D has FK&relationship to C etc...
>>
>> I'd like to implement *generic method* walk(obj) which will recursively 
>> yield dependent/related objects of obj (which is instance of A).
>>
>> I know that there is introspection interface inspect(), however I'm 
>> don't really understand how to use it properly in my use case.
>>
>> Shall I do inspect(obj) or rather inspect(obj.__class__) and then 
>> somehow apply inspection to obj?
>>
>> Are there an examples and best practices?
>>
>> right now you can kind of get this effect using cascade_iterator: 
>> http://docs.sqlalchemy.org/en/rel_1_0/orm/mapping_api.html?highlight=cascade_iterator#sqlalchemy.orm.mapper.Mapper.cascade_iterator
>>
>> the limitation is that right now its based on relationship cascade 
>> settings, as that's what it was intended for, so you'd probably want to use 
>> "save-update":
>>
>> insp = inspect(my_object)
>> for obj in insp.mapper.cascade_iterator("save-update", insp):
>># ...
>>
>> to implement your own system, the graph of objects is strictly based on 
>> relationship.   so walk() is pretty simple:
>>
>> def walk(obj):
>> yield obj
>> insp = inspect(obj)
>> for relationship in insp.mapper.relationships:
>> related = getattr(obj, relationship.key)
>> if relationship.uselist:
>> for collection_member in related:
>> for walk_related in walk(collection_member):
>> yield walk_related
>> elif related is not None:
>> for walk_related in walk(related):
>> yield walk_related
>>
>>
>>
>>
>>
>>
>> -- 
>> 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.
>> To post to this group, send email to sqlal...@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.

[sqlalchemy] Re: SA support for SQLite ATTACH?

2015-09-15 Thread Ashish Srivastava

Thanks it is nice post.
I also refer very useful and helpful article about SQLite - ATTACH DATABASE

Please visit this helpful article.
http://www.mindstick.com/blog/10947/SQLite%20ATTACH%20DATABASE#.VffLzhFViko
http://www.tutorialspoint.com/sqlite/sqlite_attach_database.htm

-- 
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] Expressions generated by select(...) changes when submitted in session.query

[sqlalchemy] Expressions generated by select(...) changes when submitted in session.query

Re: [sqlalchemy] Cast model id column from String to Integer

Re: [sqlalchemy] Cast model id column from String to Integer

Re: [sqlalchemy] AttributeError: 'NoneType' object has no attribute 'accepts_scalar_loader'

Re: [sqlalchemy] Cast model id column from String to Integer

Re: [sqlalchemy] AttributeError: 'NoneType' object has no attribute 'accepts_scalar_loader'

Re: [sqlalchemy] AttributeError: 'NoneType' object has no attribute 'accepts_scalar_loader'

Re: [sqlalchemy] Cast model id column from String to Integer

Re: [sqlalchemy] AttributeError: 'NoneType' object has no attribute 'accepts_scalar_loader'

[sqlalchemy] Cast model id column from String to Integer

[sqlalchemy] AttributeError: 'NoneType' object has no attribute 'accepts_scalar_loader'

Re: [sqlalchemy] Re: polymorphic identity after_delete event

Re: [sqlalchemy] Re: polymorphic identity after_delete event

Re: [sqlalchemy] ORM: walk() related objects

[sqlalchemy] Re: SA support for SQLite ATTACH?

16 matches

Site Navigation

Mail list logo

Footer information