date:20161108

Re: [sqlalchemy] Using Python 'sorted' doesn't change data?

2016-11-08 Thread mike bayer

it does not, and as an exercise I'd recommend trying to theorize how 
sorted() *could* make such an effect - and if it did, what assumptions 
would it be making?   Hopefully this would reveal that the assumptions 
the library would need to make in order to even make such a thing happen 
would not be appropriate (e.g., what column would it be changing?  how 
would it know to change *that* column and not another one?  what if your 
query.all() were against some other SQL statement?  etc).




On 11/08/2016 12:58 PM, TomS. wrote:

Hi,

I know this is silly question, but I just need confirmation - Python
'sorted' doesn't affect in any way data stored in DB?

Example:

class PPL(db.Model):

person_pk_id = db.Column(db.Integer, primary_key=True)
person_type = db.Column(db.Integer)
person_order_s = db.Column(db.Integer)
person_order_k = db.Column(db.Integer)

all_ppl = PPL.query.all()

technician = [person for person in all_ppl if person.person_type == 1]

technician_sorted = sorted(technician, key=lambda k: getattr(k,
u'person_order_' + extra_par))

Using 'sorted' won't make any changes in the rows which are in table
used by PPL?

Thank you very much!

Cheers



--
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.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] DeclaredAttr

2016-11-08 Thread mike bayer


Run this:

from sqlalchemy.orm import configure_mappers
configure_mappers()


that ensures RefClass's deferred mapping gets set up appropriately.



On 11/08/2016 01:39 PM, de...@devinfee.com wrote:

First, thanks for your very insightful response. I'm trying to reproduce
what you've provided, but I'm getting an `InvalidRequestError` when
querying on `RefClass`:
/InvalidRequestError: SQL expression, column, or mapped entity expected
- got ''
/

Indeed, RefClass has no `__mapper__` attribute. Querying on any other
object works as intended though. I imagine there's a small detail I'm
missing, so I've highlighted the changes from yesterday's post.


from sqlalchemy import (
Column,
ForeignKey,
Integer,
create_engine,
)
from sqlalchemy.ext.declarative import (
AbstractConcreteBase,
declared_attr,
declarative_base,
has_inherited_table,
)
from sqlalchemy.orm import Session

Base = declarative_base()


class AClass(Base):
__tablename__ = 'aclass'
id = Column(Integer, primary_key=True)


class BClass(Base):
__tablename__ = 'bclass'
id = Column(Integer, primary_key=True)


class RefClass(AbstractConcreteBase, Base):
@declared_attr
def __tablename__(cls):
if cls.__name__ == 'RefClass':
return None
return cls.__name__.lower()

@declared_attr.cascading
def id(cls):
if cls.__name__ == 'RefClass':
return Column(Integer)
column_name = '{}.id'.format(cls.ref.__tablename__)
return Column(ForeignKey(column_name), primary_key=True)

@declared_attr
def __mapper_args__(cls):
if cls.__name__ == 'RefClass':
return {}
return {
'concrete': True,
'polymorphic_identity': cls.ref.__name__
}


class ARefClass(RefClass):
ref = AClass


class BRefClass(RefClass):
ref = BClass


engine = create_engine('sqlite://', echo=True)
Base.metadata.bind = engine
Base.metadata.create_all()
db = Session(engine)


Thanks!

--
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.
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.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] SQLite VARBINARY is mapped to NUMERIC type, but sa.dialects.sqlite.dialect.type_descriptor reports VARBINARY

2016-11-08 Thread mike bayer




On 11/08/2016 01:03 PM, Vlad Frolov wrote:

I was trying to solve an issue of an incorrect Alembic migration
autogeneration for a custom field (SQLAlchemy-Uitls.PasswordType), which
fallbacks to VARBINARY type for SQLite and it turned out that SQLAlchemy
confuses me:


*from* sqlalchemy *import* VARBINARY
*from* sqlalchemy.dialects.sqlite *import* dialect as sqlite_dialect



sqlite_dialect._resolve_type_affinity(*str*(VARBINARY(128)))

NUMERIC(precision=128)


sqlite_dialect.type_descriptor(sqlalchemy.VARBINARY(128))

VARBINARY(length=128)

Is this an expected behaviour? I expected to see the same output in both
cases. Alembic gets confused as the DB reports the column type as
NUMERIC(precision=128) (which type affinity is Numeric), while
PasswordType inspection ends up with VARBINARY(length=128) (which type
affinity is _Binary).


I went to see what "resolve_type_affinity" does as this is an odd method 
in the first place.  SQLAlchemy has its own concept of "type affinity" 
but this particular method is doing something totally specific to SQLite 
itself.


The docstring for resolve_type_affinity is in-depth and should explain 
the behavior, if you also read the datatype3.html document linked 
(though note it appears to be section 3.1 now, no longer section 2.1). 
The short answer is, specific types don't matter on SQLite very much so 
you wouldn't want to use a very specific type like VARBINARY in the 
first place, and in this case VARBINARY on SQLite is NUMERIC due to 
SQLite's own naming rules (there is no actual VARBINARY type).


If sqlalchemy-utils is using VARBINARY in a database-agnostic way, they 
should change that.  VARBINARY is a very specific datatype that means 
different things on different platforms (which is what UPPERCASE_NAMES 
mean in the typing system).   There is no "short binary" datatype that 
is database agnostic, unfortunately, only LargeBinary which generally 
tries to act like BLOB (and which SQlite's affinity rules support).



def _resolve_type_affinity()

Return a data type from a reflected column, using affinity tules.

SQLite's goal for universal compatibility introduces some complexity 
during reflection, as a column's defined type might not actually be a 
type that SQLite understands - or indeed, my not be defined *at all*. 
Internally, SQLite handles this with a 'data type affinity' for each 
column definition, mapping to one of 'TEXT', 'NUMERIC', 'INTEGER', 
'REAL', or 'NONE' (raw bits). The algorithm that determines this is 
listed in http://www.sqlite.org/datatype3.html section 2.1.


This method allows SQLAlchemy to support that algorithm, while still 
providing access to smarter reflection utilities by regcognizing column 
definitions that SQLite only supports through affinity (like DATE and 
DOUBLE).






--
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.
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.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] DeclaredAttr

2016-11-08 Thread devin

First, thanks for your very insightful response. I'm trying to reproduce 
what you've provided, but I'm getting an `InvalidRequestError` when 
querying on `RefClass`:

*InvalidRequestError: SQL expression, column, or mapped entity expected - 
got ''*

Indeed, RefClass has no `__mapper__` attribute. Querying on any other 
object works as intended though. I imagine there's a small detail I'm 
missing, so I've highlighted the changes from yesterday's post.


from sqlalchemy import (
Column,
ForeignKey,
Integer,
create_engine,
)
from sqlalchemy.ext.declarative import (
AbstractConcreteBase,
declared_attr,
declarative_base,
has_inherited_table,
)
from sqlalchemy.orm import Session

Base = declarative_base()


class AClass(Base):
__tablename__ = 'aclass'
id = Column(Integer, primary_key=True)


class BClass(Base):
__tablename__ = 'bclass'
id = Column(Integer, primary_key=True)


class RefClass(AbstractConcreteBase, Base):
@declared_attr
def __tablename__(cls):
if cls.__name__ == 'RefClass':
return None
return cls.__name__.lower()

@declared_attr.cascading
def id(cls):
if cls.__name__ == 'RefClass':
return Column(Integer)
column_name = '{}.id'.format(cls.ref.__tablename__)
return Column(ForeignKey(column_name), primary_key=True)

@declared_attr
def __mapper_args__(cls):
if cls.__name__ == 'RefClass':
return {}
return {
'concrete': True,
'polymorphic_identity': cls.ref.__name__
}


class ARefClass(RefClass):
ref = AClass


class BRefClass(RefClass):
ref = BClass


engine = create_engine('sqlite://', echo=True)
Base.metadata.bind = engine
Base.metadata.create_all()
db = Session(engine)


Thanks!

-- 
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.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: SQLite VARBINARY is mapped to NUMERIC type, but sa.dialects.sqlite.dialect.type_descriptor reports VARBINARY

2016-11-08 Thread Vlad Frolov

Here are references to the relevant discussions:

   - https://github.com/kvesteri/sqlalchemy-utils/issues/106
   - https://github.com/kvesteri/sqlalchemy-utils/pull/233
   

On Tuesday, November 8, 2016 at 8:03:45 PM UTC+2, Vlad Frolov wrote:
>
> I was trying to solve an issue of an incorrect Alembic migration 
> autogeneration for a custom field (SQLAlchemy-Uitls.PasswordType), which 
> fallbacks to VARBINARY type for SQLite and it turned out that SQLAlchemy 
> confuses me:
>
> >>> *from* sqlalchemy *import* VARBINARY
> >>> *from* sqlalchemy.dialects.sqlite *import* dialect as sqlite_dialect
>
> >>> sqlite_dialect._resolve_type_affinity(*str*(VARBINARY(128)))
> NUMERIC(precision=128)
>
> >>> sqlite_dialect.type_descriptor(sqlalchemy.VARBINARY(128))
> VARBINARY(length=128)
>
> Is this an expected behaviour? I expected to see the same output in both 
> cases. Alembic gets confused as the DB reports the column type as 
> NUMERIC(precision=128) (which type affinity is Numeric), while PasswordType 
> inspection ends up with VARBINARY(length=128) (which type affinity is 
> _Binary).
>

-- 
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.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] SQLite VARBINARY is mapped to NUMERIC type, but sa.dialects.sqlite.dialect.type_descriptor reports VARBINARY

2016-11-08 Thread Vlad Frolov

I was trying to solve an issue of an incorrect Alembic migration 
autogeneration for a custom field (SQLAlchemy-Uitls.PasswordType), which 
fallbacks to VARBINARY type for SQLite and it turned out that SQLAlchemy 
confuses me:

>>> *from* sqlalchemy *import* VARBINARY
>>> *from* sqlalchemy.dialects.sqlite *import* dialect as sqlite_dialect

>>> sqlite_dialect._resolve_type_affinity(*str*(VARBINARY(128)))
NUMERIC(precision=128)

>>> sqlite_dialect.type_descriptor(sqlalchemy.VARBINARY(128))
VARBINARY(length=128)

Is this an expected behaviour? I expected to see the same output in both 
cases. Alembic gets confused as the DB reports the column type as 
NUMERIC(precision=128) (which type affinity is Numeric), while PasswordType 
inspection ends up with VARBINARY(length=128) (which type affinity is 
_Binary).

-- 
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.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Using Python 'sorted' doesn't change data?

2016-11-08 Thread TomS.


Hi,

I know this is silly question, but I just need confirmation - Python 
'sorted' doesn't affect in any way data stored in DB?


Example:

class PPL(db.Model):

person_pk_id = db.Column(db.Integer, primary_key=True)
person_type = db.Column(db.Integer)
person_order_s = db.Column(db.Integer)
person_order_k = db.Column(db.Integer)

all_ppl = PPL.query.all()

technician = [person for person in all_ppl if person.person_type == 1]

technician_sorted = sorted(technician, key=lambda k: getattr(k, 
u'person_order_' + extra_par))


Using 'sorted' won't make any changes in the rows which are in table 
used by PPL?


Thank you very much!

Cheers

--
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.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Cannot delete Child still linked to Parent (Dependency rule tried to blank-out primary key column)

2016-11-08 Thread irobin

Thanks a lot, that solved my problem ! 

On Tuesday, 8 November 2016 17:05:00 UTC+1, Mike Bayer wrote:
>
>
>
> On 11/08/2016 10:40 AM, iro...@kpler.com  wrote: 
> > Hello, 
> > 
> > I am struggling with this case and so far, I haven't seen on 
> > documentation or on the different posts, a way to solve my problem. 
> > 
> > Firstly, I cannot have a ForeignKey linking Child to Parent because 
> > Parent has a polymorphic identity, and PostgreSQL is not dealing with 
> > ForeignKey in such cases. Let's say ParentA is a Parent. 
> > 
> > So I have to define relationship between Child and Parent "manually" 
> > 
> > Thus, I have this : 
> > 
> > | 
> > | 
> > class Child(SaBaseClass): 
> > 
> > parent_id = Column(Integer, index=True, nullable=True) 
> > 
> > parent = relationship('Parent', 
> > primaryjoin='foreign(Child.parent_id) == Parent.id', lazy='joined', 
> > uselist=False, foreign_keys='Child.parent_id') 
> > 
> > class Parent(SaBaseClass): 
> > 
> > children = relationship('Child', primaryjoin='Child.parent_id == 
> > foreign(Parent.id), backref='children', cascade='all, delete-orphan', 
> > uselist=True, lazy='joined', single_parent=True) 
> > | 
> > 
> > | 
>
>
> the first problem is that you are mixing up your foreign() directives on 
> your two relationships. foreign() indicates the column(s) that would 
> behave as though they have FOREIGN KEY CONSTRAINT placed upon them, 
> which note is optional, foreign() is all you need.  In this case the 
> values for foreign() are contradictory and it seems that Child.parent_id 
> is your foreign() here. 
>
> Next, these relationships are bi-directional with each other.  They 
> would be linked together using back_populates.  The ORM makes great use 
> of this clue to know that Child.parent and Parent.children are related 
> to each other and it can help in a lot of situations.  There seems to be 
> some misunderstood use of the "backref" directive here too with 
> "Parent.children" having a backref to..."Child.children", which makes no 
> sense, we will take that out. 
>
> Next is that single_parent=True makes no sense here, it should not be 
> needed; people use it because they saw the error/warning indicating it 
> should be used, which in this case you probably saw this error due to 
> the mis-placed foreign() directive, so we will take that out. 
>
> We can make your example look exactly like that at 
>
> http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#one-to-many,
>  
>
> the only change is that instead of using ForeignKey(), we use foreign() 
> in the primary join.  Everything then works out great, see below. 
>
>
> from sqlalchemy import * 
> from sqlalchemy.orm import * 
> from sqlalchemy.ext.declarative import declarative_base 
>
> Base = declarative_base() 
>
>
> class Child(Base): 
>  __tablename__ = 'child' 
>  id = Column(Integer, primary_key=True) 
>
>  parent_id = Column(Integer, index=True, nullable=True) 
>
>  parent = relationship( 
>  'Parent', 
>  primaryjoin='foreign(Child.parent_id) == Parent.id', 
>  lazy='joined', back_populates="children") 
>
>
> class Parent(Base): 
>  __tablename__ = 'parent' 
>  id = Column(Integer, primary_key=True) 
>
>  children = relationship( 
>  'Child', primaryjoin="foreign(Child.parent_id) == Parent.id", 
>  cascade='all, delete-orphan', lazy='joined', 
> back_populates="parent") 
>
> e = create_engine("postgresql://scott:tiger@localhost/test", echo=True) 
> Base.metadata.create_all(e) 
> s = Session(e) 
>
> p1 = Parent() 
> c1 = Child() 
> p1.children.append(c1) 
> s.add_all([p1, c1]) 
> s.commit() 
>
> s.delete(c1) 
> s.commit() 
>
> assert p1.children == [] 
>
>
>
>
>
> > 
> > So a child is linked to only one parent, and a parent can be linked to 
> > several children, and when I delete a Child instance I would like to 
> > "unlink" it from its Parent. When trying to delete a child instance I 
> > get "Dependency rule tried to blank-out primary key column parent.id on 
> > instance 'ParentA', which makes me think it's trying to delete Parent 
> > instance... 
>
>
>
>
> > 
> > If someone has a clue of what's wrong with my definition of the 
> > relationship, thanks in advance for your help ! 
> > 
> > Isabelle 
> > 
> > -- 
> > 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+...@googlegroups.com  
> > . 
> > To post to this group, send email to sqlal...@googlegroups.com 
>  
> > . 
>

Re: [sqlalchemy] postgresql array column, fetch only_load() element by index

2016-11-08 Thread Jonathan Vanasco

FYI, if you query via the ORM and load_only, the query should be something 
like :

SELECT primary_key_column, array[1] FROM table;

The ORM adds the primary key behind-the-scenes so it can setup the objects.

as far as i know, If you need to only load array[1] and not the primary key 
column, you need to use the sqlalchemy core methods.

-- 
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.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Cannot delete Child still linked to Parent (Dependency rule tried to blank-out primary key column)

2016-11-08 Thread mike bayer




On 11/08/2016 10:40 AM, iro...@kpler.com wrote:

Hello,

I am struggling with this case and so far, I haven't seen on
documentation or on the different posts, a way to solve my problem.

Firstly, I cannot have a ForeignKey linking Child to Parent because
Parent has a polymorphic identity, and PostgreSQL is not dealing with
ForeignKey in such cases. Let's say ParentA is a Parent.

So I have to define relationship between Child and Parent "manually"

Thus, I have this :

|
|
class Child(SaBaseClass):

parent_id = Column(Integer, index=True, nullable=True)

parent = relationship('Parent',
primaryjoin='foreign(Child.parent_id) == Parent.id', lazy='joined',
uselist=False, foreign_keys='Child.parent_id')

class Parent(SaBaseClass):

children = relationship('Child', primaryjoin='Child.parent_id ==
foreign(Parent.id), backref='children', cascade='all, delete-orphan',
uselist=True, lazy='joined', single_parent=True)
|

|



the first problem is that you are mixing up your foreign() directives on 
your two relationships. foreign() indicates the column(s) that would 
behave as though they have FOREIGN KEY CONSTRAINT placed upon them, 
which note is optional, foreign() is all you need.  In this case the 
values for foreign() are contradictory and it seems that Child.parent_id 
is your foreign() here.


Next, these relationships are bi-directional with each other.  They 
would be linked together using back_populates.  The ORM makes great use 
of this clue to know that Child.parent and Parent.children are related 
to each other and it can help in a lot of situations.  There seems to be 
some misunderstood use of the "backref" directive here too with 
"Parent.children" having a backref to..."Child.children", which makes no 
sense, we will take that out.


Next is that single_parent=True makes no sense here, it should not be 
needed; people use it because they saw the error/warning indicating it 
should be used, which in this case you probably saw this error due to 
the mis-placed foreign() directive, so we will take that out.


We can make your example look exactly like that at 
http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#one-to-many, 
the only change is that instead of using ForeignKey(), we use foreign() 
in the primary join.  Everything then works out great, see below.



from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class Child(Base):
__tablename__ = 'child'
id = Column(Integer, primary_key=True)

parent_id = Column(Integer, index=True, nullable=True)

parent = relationship(
'Parent',
primaryjoin='foreign(Child.parent_id) == Parent.id',
lazy='joined', back_populates="children")


class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)

children = relationship(
'Child', primaryjoin="foreign(Child.parent_id) == Parent.id",
cascade='all, delete-orphan', lazy='joined', 
back_populates="parent")


e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.create_all(e)
s = Session(e)

p1 = Parent()
c1 = Child()
p1.children.append(c1)
s.add_all([p1, c1])
s.commit()

s.delete(c1)
s.commit()

assert p1.children == []







So a child is linked to only one parent, and a parent can be linked to
several children, and when I delete a Child instance I would like to
"unlink" it from its Parent. When trying to delete a child instance I
get "Dependency rule tried to blank-out primary key column parent.id on
instance 'ParentA', which makes me think it's trying to delete Parent
instance...







If someone has a clue of what's wrong with my definition of the
relationship, thanks in advance for your help !

Isabelle

--
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.
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] Cannot delete Child still linked to Parent (Dependency rule tried to blank-out primary key column)

2016-11-08 Thread irobin

Hello,

I am struggling with this case and so far, I haven't seen on documentation 
or on the different posts, a way to solve my problem.

Firstly, I cannot have a ForeignKey linking Child to Parent because Parent 
has a polymorphic identity, and PostgreSQL is not dealing with ForeignKey 
in such cases. Let's say ParentA is a Parent. 

So I have to define relationship between Child and Parent "manually"

Thus, I have this :

class Child(SaBaseClass): 
 
parent_id = Column(Integer, index=True, nullable=True)

parent = relationship('Parent', primaryjoin='foreign(Child.parent_id) 
== Parent.id', lazy='joined', uselist=False, foreign_keys='Child.parent_id')

class Parent(SaBaseClass):

children = relationship('Child', primaryjoin='Child.parent_id == 
foreign(Parent.id), backref='children', cascade='all, delete-orphan', 
uselist=True, lazy='joined', single_parent=True)


So a child is linked to only one parent, and a parent can be linked to 
several children, and when I delete a Child instance I would like to 
"unlink" it from its Parent. When trying to delete a child instance I get 
"Dependency rule tried to blank-out primary key column parent.id on 
instance 'ParentA', which makes me think it's trying to delete Parent 
instance...

If someone has a clue of what's wrong with my definition of the 
relationship, thanks in advance for your help !

Isabelle

-- 
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.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Malformed whereclause for relationship with custom primaryjoin

2016-11-08 Thread mike bayer




On 11/08/2016 08:00 AM, Sebastian Eckweiler wrote:

Hi there,

I'm having an issue when building queries against a relationship using a
custom primaryjoin.
The issue can be reproduced with a slightly modified Users/Address model
as taken from the docs:

|

fromsqlalchemyimportInteger,ForeignKey,String,Column
fromsqlalchemy.ext.declarativeimportdeclarative_base
fromsqlalchemy.ormimportrelationship

Base=declarative_base()

classUser(Base):
__tablename__ ='user'
id =Column(Integer,primary_key=True)
name =Column(String)

address_id =Column(Integer,ForeignKey('address.id'))
boston_address =relationship("BostonAddress",

primaryjoin="and_(User.address_id==Address.id, "
  "Address.city=='Boston')")

classAddress(Base):
__tablename__ ='address'
__mapper_args__ ={'polymorphic_on':'type',
   'polymorphic_identity':'any'}

id =Column(Integer,primary_key=True)
type =Column(String,primary_key=True)

street =Column(String)
city =Column(String)
state =Column(String)
zip =Column(String)

classBostonAddress(Address):

__mapper_args__ ={'polymorphic_on':'type',
   'polymorphic_identity':'Boston'}

|

The actual application uses more than one Address subclass obviously -
but the above suffices for the example.
When trying to query for "Users not having a boston_address" I'd use

|
User.boston_address ==None
|

as a where clause, which however turns out be become:

|
"user".address_id IS NULL AND 'Boston'IS NULL
|

which obvisouly won't yield any results - I'm not sure though, whether
this is a bug or simply due to the maybe unusual model.
I've now implemented a dedicated comparator for this type of
relationship which seems to work.
But still I'm wondering if there's an easier way to fix this, e.g. by
annotating parts of the join condition as "do not touch"...?



well, this is a many-to-one so the usual comparison tries to cut out the 
"Address" table totally for the "address == None" equation, which 
doesn't work here.   We can make it not consider Address.city as one of 
the columns to "null out" like this:


primaryjoin="and_(User.address_id==remote(Address.id), "
  "Address.city=='Boston')")

and then the expression comes out as:

"user".address_id IS NULL AND address.city = :city_1
{u'city_1': 'Boston'}


which also does not work, because then in a query you get:

SELECT "user".id AS user_id, "user".name AS user_name, "user".address_id 
AS user_address_id

FROM "user", address
WHERE "user".address_id IS NULL AND address.city = :city_1

If we are to use this expression as written to produce a user with no 
boston_address, the only way to do this "magically" is to use has():



print Session().query(User).filter(~User.boston_address.has())

SELECT "user".id AS user_id, "user".name AS user_name, "user".address_id 
AS user_address_id

FROM "user"
WHERE NOT (EXISTS (SELECT 1
FROM address
WHERE "user".address_id = address.id AND address.city = :city_1 AND 
address.type IN (:type_1)))


Where you'll notice that has() is also smart enough to get the 
polymorphic discriminator in there.


The relational quirk this model is tripping over is that a BostonAddress 
would have a primary key that includes "Boston" in the type but you are 
not including this in the schema for your User table, that is, 
User.address_id == 5 is ambiguous, it could point to address (5, 
'Denver') or (5, 'Boston').   In fact a strict database like Postgresql 
will disallow such a structure from being created, because this is not a 
unique key:


 (psycopg2.ProgrammingError) there is no unique constraint matching 
given keys for referenced table "address"
 [SQL: '\nCREATE TABLE "user" (\n\tid SERIAL NOT NULL, \n\tname 
VARCHAR, \n\taddress_id INTEGER, \n\tPRIMARY KEY (id), \n\tFOREIGN 
KEY(address_id) REFERENCES address (id)\n)\n\n']


If we used a better relational form like this:

class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
name = Column(String(50))

address_id = Column(Integer)
address_type = Column(String)
boston_address = relationship("BostonAddress")
__table_args__ = (
ForeignKeyConstraint(
['address_id', 'address_type'],
['address.id', 'address.type']),
)

now we see the original User.boston_address == None works smoothly (note 
no custom primaryjoin needed):


 "user".address_id IS NULL AND "user".address_type IS NULL


The way you're doing it is fine if you are stuck with that model, but 
the need to create a custom comparator is warranted since you're moving 
away from a generic foreign key constraint model.












Cheers
Sebastian



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

[sqlalchemy] Malformed whereclause for relationship with custom primaryjoin

2016-11-08 Thread Sebastian Eckweiler

Hi there,

I'm having an issue when building queries against a relationship using a 
custom primaryjoin.
The issue can be reproduced with a slightly modified Users/Address model as 
taken from the docs:

from sqlalchemy import Integer, ForeignKey, String, Column
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()

class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
name = Column(String)

address_id = Column(Integer, ForeignKey('address.id'))
boston_address = relationship("BostonAddress",
  
primaryjoin="and_(User.address_id==Address.id, "
  "Address.city=='Boston')")

class Address(Base):
__tablename__ = 'address'
__mapper_args__ = {'polymorphic_on': 'type',
   'polymorphic_identity': 'any'}

id = Column(Integer, primary_key=True)
type = Column(String, primary_key=True)

street = Column(String)
city = Column(String)
state = Column(String)
zip = Column(String)

class BostonAddress(Address):

__mapper_args__ = {'polymorphic_on': 'type',
   'polymorphic_identity': 'Boston'}


The actual application uses more than one Address subclass obviously - but 
the above suffices for the example.
When trying to query for "Users not having a boston_address" I'd use

User.boston_address == None

as a where clause, which however turns out be become:

"user".address_id IS NULL AND 'Boston' IS NULL

which obvisouly won't yield any results - I'm not sure though, whether this 
is a bug or simply due to the maybe unusual model.
I've now implemented a dedicated comparator for this type of relationship 
which seems to work.
But still I'm wondering if there's an easier way to fix this, e.g. by 
annotating parts of the join condition as "do not touch"...?

Cheers
Sebastian



-- 
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.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] postgresql array column, fetch only_load() element by index

2016-11-08 Thread Simon King

If you have an SQLAlchemy session, you would write this:

session.query(Model.array[1]).all()

Assuming your "Model.query" is a shorthand for "session.query(Model)",
you might be able to use:

Model.query.with_entities(Model.array[1]).all()

Simon

On Tue, Nov 8, 2016 at 10:12 AM, Dorian Hoxha  wrote:
> Hi Simon,
>
> It all works. All I need is how to do:
>
> Model.query.options(load_only(Model.array[1])).all()   ?
>
>
> Thanks
>
>
> On Tue, Nov 8, 2016 at 10:56 AM, Simon King  wrote:
>>
>> On Tue, Nov 8, 2016 at 9:31 AM, Dorian Hoxha 
>> wrote:
>> > So,
>> >
>> > I want to do "SELECT array[1] FROM table;". Meaning to select only 1
>> > element. Is this possible (didn't find by searching
>> > docs,mailing-list,google).
>> > Though I can do it by normal query.
>> >
>>
>> I haven't used arrays with postgresql, so I've no idea if this works,
>> but for documentation you probably want to start here:
>>
>>
>> http://docs.sqlalchemy.org/en/latest/core/type_basics.html#sqlalchemy.types.ARRAY
>>
>> According to those docs, if you've declared a table with a column
>> using the ARRAY type, you should be able to query elements of the
>> array using python indexing operators. For example:
>>
>> table = sa.Table('mytable', metadata, sa.Column('array',
>> sa.ARRAY(sa.Integer)))
>> sa.select([table.c.array[1]])
>>
>> Hope that helps,
>>
>> Simon
>>
>> --
>> 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 a topic in the
>> Google Groups "sqlalchemy" group.
>> To unsubscribe from this topic, visit
>> https://groups.google.com/d/topic/sqlalchemy/dkOnyUkgRX4/unsubscribe.
>> To unsubscribe from this group and all its topics, 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.
>> 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.
> 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.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] postgresql array column, fetch only_load() element by index

2016-11-08 Thread Dorian Hoxha

Hi Simon,

It all works. All I need is how to do:

Model.query.options(load_only(Model.array[1])).all()   ?


Thanks


On Tue, Nov 8, 2016 at 10:56 AM, Simon King  wrote:

> On Tue, Nov 8, 2016 at 9:31 AM, Dorian Hoxha 
> wrote:
> > So,
> >
> > I want to do "SELECT array[1] FROM table;". Meaning to select only 1
> > element. Is this possible (didn't find by searching
> > docs,mailing-list,google).
> > Though I can do it by normal query.
> >
>
> I haven't used arrays with postgresql, so I've no idea if this works,
> but for documentation you probably want to start here:
>
> http://docs.sqlalchemy.org/en/latest/core/type_basics.html#
> sqlalchemy.types.ARRAY
>
> According to those docs, if you've declared a table with a column
> using the ARRAY type, you should be able to query elements of the
> array using python indexing operators. For example:
>
> table = sa.Table('mytable', metadata, sa.Column('array',
> sa.ARRAY(sa.Integer)))
> sa.select([table.c.array[1]])
>
> Hope that helps,
>
> Simon
>
> --
> 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 a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/
> topic/sqlalchemy/dkOnyUkgRX4/unsubscribe.
> To unsubscribe from this group and all its topics, 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.
> 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.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] postgresql array column, fetch only_load() element by index

2016-11-08 Thread Dorian Hoxha

So,

I want to do "SELECT array[1] FROM table;". Meaning to select only 1 
element. Is this possible (didn't find by searching 
docs,mailing-list,google).
Though I can do it by normal query.

Thank You

-- 
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.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Using Python 'sorted' doesn't change data?

Re: [sqlalchemy] DeclaredAttr

Re: [sqlalchemy] SQLite VARBINARY is mapped to NUMERIC type, but sa.dialects.sqlite.dialect.type_descriptor reports VARBINARY

Re: [sqlalchemy] DeclaredAttr

[sqlalchemy] Re: SQLite VARBINARY is mapped to NUMERIC type, but sa.dialects.sqlite.dialect.type_descriptor reports VARBINARY

[sqlalchemy] SQLite VARBINARY is mapped to NUMERIC type, but sa.dialects.sqlite.dialect.type_descriptor reports VARBINARY

[sqlalchemy] Using Python 'sorted' doesn't change data?

Re: [sqlalchemy] Cannot delete Child still linked to Parent (Dependency rule tried to blank-out primary key column)

Re: [sqlalchemy] postgresql array column, fetch only_load() element by index

Re: [sqlalchemy] Cannot delete Child still linked to Parent (Dependency rule tried to blank-out primary key column)

[sqlalchemy] Cannot delete Child still linked to Parent (Dependency rule tried to blank-out primary key column)

Re: [sqlalchemy] Malformed whereclause for relationship with custom primaryjoin

[sqlalchemy] Malformed whereclause for relationship with custom primaryjoin

Re: [sqlalchemy] postgresql array column, fetch only_load() element by index

Re: [sqlalchemy] postgresql array column, fetch only_load() element by index

[sqlalchemy] postgresql array column, fetch only_load() element by index

16 matches

Site Navigation

Mail list logo

Footer information