Re: [sqlalchemy] PostgreSQL table inheritance with "postgresql_inherits" does not take "add_is_dependent_on" into account

[Adrian Schreyer]

I actually I just found the problem; the tables are in fact created in the
right order - the problem is that the DDL contains INHERITS ( "parent" ).
It gives the same error if I try to run the code in a GUI with the
inherited table name quoted, without (the quoting) though it works.

On Mon, Nov 23, 2015 at 6:55 PM Mike Bayer  wrote:

>
>
> On 11/23/2015 12:43 PM, Adrian wrote:
> > Hello,
> >
> > I have the following problem - I recently upgraded to the 1.0+ branch
> > from 0.9 and now the PostgreSQL table inheritance does not work properly
> > any longer because the tables that inherit from the master table are
> > sometimes created before (random) the actual table they inherit from,
> > throwing (psycopg2.ProgrammingError) relation "" does not exist
> > errors. With the 0.9+ branch a simple add_is_dependent_on was working to
> > solve this but it does not seem to be taken into account anymore.
>
> this is not the case, that API is still taken into account.  I can
> remove the code that does so and the test which exercises this feature
> then fails, so it is also tested.
>
> Can you please provide a full reproducing test case?It needs to be
> succinct, single file, and runnable by me, thanks.
>
>
>
>
> Is
> > there something that changed from 0.9 to 1.0 that needs to be done to
> > get it to work? metadata.sorted_tables returns the proper table order
> > (master table first, dependencies later) though but tables are not
> > created in that order by metadata.create_all().
> >
> > Thanks,
> >
> > Adrian
> >
> > --
> > 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 a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy/k4Lhj7i5sBM/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 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] Creating PostgreSQL Index with ASC/DESC and NULLS FIRST/LAST

[Florian Rüchel]

Hey,

I want to execute the following statement in the most SQLAlchemy way 
possible:

CREATE INDEX ix_user_points ON "user" (points DESC NULLS LAST);

So I want to add a "DESC NULLS LAST" or equivalent as per documentation 
(http://www.postgresql.org/docs/current/static/indexes-ordering.html)

Can SQLAlchemy do this in any way and understand it? Alternatively: Where 
would be the correct spot to execute the explicit SQL? I was thinking about 
Schema Events here.

Regards,
Florian

-- 
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] Creating PostgreSQL Index with ASC/DESC and NULLS FIRST/LAST

[Mike Bayer]

On 11/23/2015 03:01 PM, Florian Rüchel wrote:
> Hey,
> 
> I want to execute the following statement in the most SQLAlchemy way
> possible:
> 
> CREATE INDEX ix_user_points ON "user" (points DESC NULLS LAST);
> 
> So I want to add a "DESC NULLS LAST" or equivalent as per documentation
> (http://www.postgresql.org/docs/current/static/indexes-ordering.html)


this is supported directly

Index("ix_user_points", user.c.points.desc().nullslast())



> 
> Can SQLAlchemy do this in any way and understand it? Alternatively:
> Where would be the correct spot to execute the explicit SQL? I was
> thinking about Schema Events here.
> 
> Regards,
> Florian
> 
> -- 
> 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] Creating PostgreSQL Index with ASC/DESC and NULLS FIRST/LAST

[Florian Rüchel]

Thanks!

On 11/23/2015 09:06 PM, Mike Bayer wrote:
>
> On 11/23/2015 03:01 PM, Florian Rüchel wrote:
>> Hey,
>>
>> I want to execute the following statement in the most SQLAlchemy way
>> possible:
>>
>> CREATE INDEX ix_user_points ON "user" (points DESC NULLS LAST);
>>
>> So I want to add a "DESC NULLS LAST" or equivalent as per documentation
>> (http://www.postgresql.org/docs/current/static/indexes-ordering.html)
>
> this is supported directly
>
> Index("ix_user_points", user.c.points.desc().nullslast())
>
>
>
>> Can SQLAlchemy do this in any way and understand it? Alternatively:
>> Where would be the correct spot to execute the explicit SQL? I was
>> thinking about Schema Events here.
>>
>> Regards,
>> Florian
>>
>> -- 
>> 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] PostgreSQL table inheritance with "postgresql_inherits" does not take "add_is_dependent_on" into account

[Adrian]

I attached a script that reproduces the problem. It actually only happens 
if the metadata contains a schema, then the tablename in the INHERITS() 
clause get quoted, which causes the problem.

On Monday, November 23, 2015 at 7:13:27 PM UTC+1, Adrian wrote:
>
> I actually I just found the problem; the tables are in fact created in the 
> right order - the problem is that the DDL contains INHERITS ( "parent" ). 
> It gives the same error if I try to run the code in a GUI with the 
> inherited table name quoted, without (the quoting) though it works. 
>
> On Mon, Nov 23, 2015 at 6:55 PM Mike Bayer  
> wrote:
>
>>
>>
>> On 11/23/2015 12:43 PM, Adrian wrote:
>> > Hello,
>> >
>> > I have the following problem - I recently upgraded to the 1.0+ branch
>> > from 0.9 and now the PostgreSQL table inheritance does not work properly
>> > any longer because the tables that inherit from the master table are
>> > sometimes created before (random) the actual table they inherit from,
>> > throwing (psycopg2.ProgrammingError) relation "" does not exist
>> > errors. With the 0.9+ branch a simple add_is_dependent_on was working to
>> > solve this but it does not seem to be taken into account anymore.
>>
>> this is not the case, that API is still taken into account.  I can
>> remove the code that does so and the test which exercises this feature
>> then fails, so it is also tested.
>>
>> Can you please provide a full reproducing test case?It needs to be
>> succinct, single file, and runnable by me, thanks.
>>
>>
>>
>>
>> Is
>> > there something that changed from 0.9 to 1.0 that needs to be done to
>> > get it to work? metadata.sorted_tables returns the proper table order
>> > (master table first, dependencies later) though but tables are not
>> > created in that order by metadata.create_all().
>> >
>> > Thanks,
>> >
>> > Adrian
>> >
>> > --
>> > 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 a topic in the 
>> Google Groups "sqlalchemy" group.
>> To unsubscribe from this topic, visit 
>> https://groups.google.com/d/topic/sqlalchemy/k4Lhj7i5sBM/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 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.
from sqlalchemy import create_engine
from sqlalchemy.types import String
from sqlalchemy.engine.url import URL
from sqlalchemy import MetaData, Table, Column

url = URL(drivername="postgresql+psycopg2", username="",
  password="", host="localhost", port=5432, database="")

engine = create_engine(url, echo=True)

metadata = MetaData(bind=engine, schema="myschema")

parent = Table(
"parent", metadata,
Column("foo", String(4), nullable=False),
prefixes=["UNLOGGED"])

child = Table(
"child", metadata,
Column("bar", String(4), nullable=False),
postgresql_inherits=parent.fullname,
prefixes=["UNLOGGED"])

child.add_is_dependent_on(parent)

metadata.drop_all(checkfirst=True)
metadata.create_all(checkfirst=True)

Re: [sqlalchemy] PostgreSQL table inheritance with "postgresql_inherits" does not take "add_is_dependent_on" into account

[Mike Bayer]

On 11/23/2015 03:15 PM, Adrian wrote:
> I attached a script that reproduces the problem. It actually only
> happens if the metadata contains a schema, then the tablename in the
> INHERITS() clause get quoted, which causes the problem.
> 

postgresql_inherits was only added in 1.0.How can this have "worked"
in 0.9?






> On Monday, November 23, 2015 at 7:13:27 PM UTC+1, Adrian wrote:
> 
> I actually I just found the problem; the tables are in fact created
> in the right order - the problem is that the DDL contains INHERITS (
> "parent" ). It gives the same error if I try to run the code in a
> GUI with the inherited table name quoted, without (the quoting)
> though it works. 
> 
> On Mon, Nov 23, 2015 at 6:55 PM Mike Bayer  > wrote:
> 
> 
> 
> On 11/23/2015 12:43 PM, Adrian wrote:
> > Hello,
> >
> > I have the following problem - I recently upgraded to the 1.0+
> branch
> > from 0.9 and now the PostgreSQL table inheritance does not
> work properly
> > any longer because the tables that inherit from the master
> table are
> > sometimes created before (random) the actual table they
> inherit from,
> > throwing (psycopg2.ProgrammingError) relation "" does
> not exist
> > errors. With the 0.9+ branch a simple add_is_dependent_on was
> working to
> > solve this but it does not seem to be taken into account anymore.
> 
> this is not the case, that API is still taken into account.  I can
> remove the code that does so and the test which exercises this
> feature
> then fails, so it is also tested.
> 
> Can you please provide a full reproducing test case?It needs
> to be
> succinct, single file, and runnable by me, thanks.
> 
> 
> 
> 
> Is
> > there something that changed from 0.9 to 1.0 that needs to be
> done to
> > get it to work? metadata.sorted_tables returns the proper
> table order
> > (master table first, dependencies later) though but tables are not
> > created in that order by metadata.create_all().
> >
> > Thanks,
> >
> > Adrian
> >
> > --
> > 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 a topic
> in the Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy/k4Lhj7i5sBM/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 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 

Re: [sqlalchemy] PostgreSQL table inheritance with "postgresql_inherits" does not take "add_is_dependent_on" into account

[Adrian Schreyer]

That's true now that you are saying it, I actually implemented it myself
before using a simple @compiles with CreateTable.

On Mon, Nov 23, 2015 at 9:33 PM Mike Bayer  wrote:

>
>
> On 11/23/2015 03:15 PM, Adrian wrote:
> > I attached a script that reproduces the problem. It actually only
> > happens if the metadata contains a schema, then the tablename in the
> > INHERITS() clause get quoted, which causes the problem.
> >
>
> postgresql_inherits was only added in 1.0.How can this have "worked"
> in 0.9?
>
>
>
>
>
>
> > On Monday, November 23, 2015 at 7:13:27 PM UTC+1, Adrian wrote:
> >
> > I actually I just found the problem; the tables are in fact created
> > in the right order - the problem is that the DDL contains INHERITS (
> > "parent" ). It gives the same error if I try to run the code in a
> > GUI with the inherited table name quoted, without (the quoting)
> > though it works.
> >
> > On Mon, Nov 23, 2015 at 6:55 PM Mike Bayer  > > wrote:
> >
> >
> >
> > On 11/23/2015 12:43 PM, Adrian wrote:
> > > Hello,
> > >
> > > I have the following problem - I recently upgraded to the 1.0+
> > branch
> > > from 0.9 and now the PostgreSQL table inheritance does not
> > work properly
> > > any longer because the tables that inherit from the master
> > table are
> > > sometimes created before (random) the actual table they
> > inherit from,
> > > throwing (psycopg2.ProgrammingError) relation "" does
> > not exist
> > > errors. With the 0.9+ branch a simple add_is_dependent_on was
> > working to
> > > solve this but it does not seem to be taken into account
> anymore.
> >
> > this is not the case, that API is still taken into account.  I
> can
> > remove the code that does so and the test which exercises this
> > feature
> > then fails, so it is also tested.
> >
> > Can you please provide a full reproducing test case?It needs
> > to be
> > succinct, single file, and runnable by me, thanks.
> >
> >
> >
> >
> > Is
> > > there something that changed from 0.9 to 1.0 that needs to be
> > done to
> > > get it to work? metadata.sorted_tables returns the proper
> > table order
> > > (master table first, dependencies later) though but tables are
> not
> > > created in that order by metadata.create_all().
> > >
> > > Thanks,
> > >
> > > Adrian
> > >
> > > --
> > > 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 a topic
> > in the Google Groups "sqlalchemy" group.
> > To unsubscribe from this topic, visit
> >
> https://groups.google.com/d/topic/sqlalchemy/k4Lhj7i5sBM/unsubscribe
> > <
> https://groups.google.com/d/topic/sqlalchemy/k4Lhj7i5sBM/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 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
> > .
> 

Re: [sqlalchemy] PostgreSQL table inheritance with "postgresql_inherits" does not take "add_is_dependent_on" into account

[Mike Bayer]

On 11/23/2015 03:15 PM, Adrian wrote:
> I attached a script that reproduces the problem. It actually only
> happens if the metadata contains a schema, then the tablename in the
> INHERITS() clause get quoted, which causes the problem.

anyway, there's no direct "postgresql_inherits_schema" feature as of
yet, so yo can work around that you have to artificially place the
schema name inside of the table name using direct quote control:

from sqlalchemy.sql.elements import quoted_name

child = Table(
"child", metadata,
Column("bar", String(4), nullable=False),
postgresql_inherits=quoted_name(parent.fullname, quote=False),
prefixes=["UNLOGGED"])



> 
> On Monday, November 23, 2015 at 7:13:27 PM UTC+1, Adrian wrote:
> 
> I actually I just found the problem; the tables are in fact created
> in the right order - the problem is that the DDL contains INHERITS (
> "parent" ). It gives the same error if I try to run the code in a
> GUI with the inherited table name quoted, without (the quoting)
> though it works. 
> 
> On Mon, Nov 23, 2015 at 6:55 PM Mike Bayer  > wrote:
> 
> 
> 
> On 11/23/2015 12:43 PM, Adrian wrote:
> > Hello,
> >
> > I have the following problem - I recently upgraded to the 1.0+
> branch
> > from 0.9 and now the PostgreSQL table inheritance does not
> work properly
> > any longer because the tables that inherit from the master
> table are
> > sometimes created before (random) the actual table they
> inherit from,
> > throwing (psycopg2.ProgrammingError) relation "" does
> not exist
> > errors. With the 0.9+ branch a simple add_is_dependent_on was
> working to
> > solve this but it does not seem to be taken into account anymore.
> 
> this is not the case, that API is still taken into account.  I can
> remove the code that does so and the test which exercises this
> feature
> then fails, so it is also tested.
> 
> Can you please provide a full reproducing test case?It needs
> to be
> succinct, single file, and runnable by me, thanks.
> 
> 
> 
> 
> Is
> > there something that changed from 0.9 to 1.0 that needs to be
> done to
> > get it to work? metadata.sorted_tables returns the proper
> table order
> > (master table first, dependencies later) though but tables are not
> > created in that order by metadata.create_all().
> >
> > Thanks,
> >
> > Adrian
> >
> > --
> > 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 a topic
> in the Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy/k4Lhj7i5sBM/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 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 

Re: [sqlalchemy] (OperationalError) no such column when child class uses different name for primary key in polymorphic relationship and a subselect issued

[Martin Pengelly-Phillips]

Thanks Michael.

On Saturday, 21 November 2015 22:35:14 UTC, Michael Bayer wrote:
>
>
>
> On 11/20/2015 12:20 PM, Martin Pengelly-Phillips wrote: 
> > Hi there, 
> > 
> > Using SQLAlchemy 1.0.9 
> > 
> > I am dealing with some legacy code and came across the following issue. 
> > It appears that in a polymorphic relationship SQLAlchemy is not able to 
> > correctly determine what to load for a relationship when using a 
> > subselect (due to limit on query) and child class has a different field 
> > name for the primary key. 
> > 
> > Here is a reproducible test case to show the issue better: 
> > 
> > | 
> > fromuuid importuuid1 asuuid 
> > 
> > importsqlalchemy 
> > importsqlalchemy.orm 
> > fromsqlalchemy import( 
> > Column,Unicode,ForeignKey,CHAR,Boolean,UniqueConstraint 
> > ) 
> > fromsqlalchemy.orm importrelationship,backref,synonym 
> > fromsqlalchemy.ext.declarative importdeclarative_base,declared_attr 
> > 
> > 
> > Base=declarative_base() 
> > 
> > 
> > classContext(Base): 
> > '''Represent a context.''' 
> > 
> > __tablename__ ='context' 
> > 
> > context_type =Column(Unicode(32),nullable=False) 
> > 
> > __mapper_args__ ={ 
> > 'polymorphic_on':context_type, 
> > 'polymorphic_identity':'context' 
> > } 
> > 
> > id =Column(CHAR(36),primary_key=True,default=lambda:str(uuid())) 
> > 
> > 
> > classTask(Context): 
> > '''Represent a task.''' 
> > 
> > __tablename__ ='task' 
> > 
> > __mapper_args__ ={ 
> > 'polymorphic_identity':'task' 
> > } 
> > 
> > # Change this and references to it to 'id' to fix issue. 
> > task_id =Column( 
> > CHAR(36), 
> > ForeignKey('context.id'), 
> > primary_key=True 
> > ) 
> > 
> > scopes =relationship('Scope',secondary='task_scope') 
> > 
> > 
> > classScope(Base): 
> > '''Represent a Scope.''' 
> > 
> > __tablename__ ='scope' 
> > 
> > id =Column(CHAR(36),primary_key=True,default=lambda:str(uuid())) 
> > 
> > 
> > classTaskScope(Base): 
> > '''Represent a relation between a scope and a task.''' 
> > 
> > __tablename__ ='task_scope' 
> > 
> > id =Column(CHAR(36),primary_key=True,default=lambda:str(uuid())) 
> > 
> > scope_id =Column(CHAR(36),ForeignKey(Scope.id),nullable=False) 
> > 
> > task_id =Column(CHAR(36),ForeignKey(Task.task_id),nullable=False) 
> > 
> > 
> > defmain(): 
> > '''Execute test.''' 
> > engine =sqlalchemy.create_engine('sqlite://') 
> > Base.metadata.create_all(engine) 
> > Session=sqlalchemy.orm.sessionmaker(bind=engine) 
> > 
> > session =Session() 
> > 
> > task =Task() 
> > scope =Scope() 
> > 
> > session.add(task) 
> > session.add(scope) 
> > session.commit() 
> > 
> > link =TaskScope(scope_id=scope.id,task_id=task.task_id) 
> > session.add(link) 
> > session.commit() 
> > 
> > query =session.query(Task).options( 
> > sqlalchemy.orm.load_only('context_type'), 
> > sqlalchemy.orm.joinedload('scopes').load_only() 
> > ).limit(10) 
> > printquery 
> > 
> > results =query.all() 
> > printresults 
> > 
> > 
> > if__name__ =='__main__': 
> > main() 
> > | 
> > 
> > Running the above gives: 
> > 
> > | 
> > sqlalchemy.exc.OperationalError:(sqlite3.OperationalError)nosuch 
> > column:task.task_id [SQL:u'SELECT anon_1.context_context_type AS 
> > anon_1_context_context_type, anon_1.context_id AS anon_1_context_id, 
> > anon_2.scope_1_id AS scope_1_id \nFROM (SELECT context.context_type AS 
> > context_context_type, context.id AS context_id \nFROM context JOIN task 
> > ON context.id = task.task_id\n LIMIT ? OFFSET ?) AS anon_1 LEFT OUTER 
> > JOIN (SELECT task_scope_1.id AS task_scope_1_id, task_scope_1.scope_id 
> > AS task_scope_1_scope_id, task_scope_1.task_id AS task_scope_1_task_id, 
> > scope_1.id AS scope_1_id \nFROM task_scope AS task_scope_1 JOIN scope 
> AS 
> > scope_1 ON scope_1.id = task_scope_1.scope_id) AS anon_2 ON 
> task.task_id 
> > = anon_2.task_scope_1_task_id'][parameters:(10,0)] 
> > | 
> > 
> > 
> > If you change 'task_id' to 'id' (and references to it) then the query 
> > will now work correctly. 
>
> this is a bug and requires five separate conditions to all be in place 
> at the same time in order to occur.  This is issue 
>
> https://bitbucket.org/zzzeek/sqlalchemy/issues/3592/logic-to-ensure-parent-cols-present-in
>  
> fixed in 60c36ca8418cec18073 and 7998f15b1687a0bd0b3 for rel_1_0.   For 
> workaround, don't exclude the attribute that's needed for the 
> joinedload(), eg: 
>
> query = session.query(Task).options( 
> sqlalchemy.orm.load_only('context_type', 'task_id'), 
> sqlalchemy.orm.joinedload('scopes').load_only() 
> ).limit(10) 
>
>
>
>
>
> > 
> > As I mentioned this is legacy code so there are many references to 
> > task_id throughout the code base unfortunately. I have found a 
> > workaround by using a synonym, but wanted to report the issue to see if 

[sqlalchemy] Creating a derived class object without inserting into base class table

[amit geron]

Hi,

I am using the flask-sqlalchemy extension with MySQL (default 
configuration), and I have a structure similar to the following example:

class User(db.Model):
id = db.Column(db.Integer, primary_key=True)

def __init__(self):
return


class A(User):
id = db.Column(db.Integer, db.ForeignKey('user.id'), primary_key=True)

def __init__(self):
super(A, self).__init__()


class B(User):
id = db.Column(db.Integer, db.ForeignKey('user.id'), primary_key=True)

def __init__(self):

super(B, self).__init__()



Consider the following flow-

   - a = A()
   - db.session.add(a)
   - db.session.commit()

Now I have an entry in the 'user' table with some id, and an entry in the 
'a' table with the same id. So far so good.

But now what I'm trying to accomplish, is the creation of an object (and a 
table entry) of class B *with the same id* as my class A object instance.

Whatever I tried so far with SQLAlchemy, it always ended up with a failure 
due to an insert into the 'user' table resulting in a duplicate error.

One 1 way to do so (and the only one I found so far), is via MySQL query 
(INSERT INTO b (id) VALUES ()), but I would really like to figure out 
if I there's a way to do the same using SQLAlchemy functions/configuration.

Thanks,

Amit

-- 
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] PostgreSQL table inheritance with "postgresql_inherits" does not take "add_is_dependent_on" into account

[Adrian]

Hello,

I have the following problem - I recently upgraded to the 1.0+ branch from 
0.9 and now the PostgreSQL table inheritance does not work properly any 
longer because the tables that inherit from the master table are sometimes 
created before (random) the actual table they inherit from, throwing 
(psycopg2.ProgrammingError) 
relation "" does not exist errors. With the 0.9+ branch a simple 
add_is_dependent_on was working to solve this but it does not seem to be 
taken into account anymore. Is there something that changed from 0.9 to 1.0 
that needs to be done to get it to work? metadata.sorted_tables returns the 
proper table order (master table first, dependencies later) though but 
tables are not created in that order by metadata.create_all().

Thanks,

Adrian

-- 
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] PostgreSQL table inheritance with "postgresql_inherits" does not take "add_is_dependent_on" into account

[Mike Bayer]

On 11/23/2015 12:43 PM, Adrian wrote:
> Hello,
> 
> I have the following problem - I recently upgraded to the 1.0+ branch
> from 0.9 and now the PostgreSQL table inheritance does not work properly
> any longer because the tables that inherit from the master table are
> sometimes created before (random) the actual table they inherit from,
> throwing (psycopg2.ProgrammingError) relation "" does not exist
> errors. With the 0.9+ branch a simple add_is_dependent_on was working to
> solve this but it does not seem to be taken into account anymore. 

this is not the case, that API is still taken into account.  I can
remove the code that does so and the test which exercises this feature
then fails, so it is also tested.

Can you please provide a full reproducing test case?It needs to be
succinct, single file, and runnable by me, thanks.




Is
> there something that changed from 0.9 to 1.0 that needs to be done to
> get it to work? metadata.sorted_tables returns the proper table order
> (master table first, dependencies later) though but tables are not
> created in that order by metadata.create_all().
> 
> Thanks,
> 
> Adrian
> 
> -- 
> 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] new object using a dictionary as the parameter

[mzagrabe]

Greetings,

I'm new to SQLAlchemy - sorry if the answer to this question is obvious.

I have a table with many fields.

I've created the ORM mapping:

Base = declarative_base()
class CallRecord(Base):
[stuff]

I'd like to create the CallRecord object by passing a dictionary to the 
constructor. Is that possible?

call_record = CallRecord(my_dict)

Thanks for any help!

-m

-- 
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] PostgreSQL table inheritance with "postgresql_inherits" does not take "add_is_dependent_on" into account

[Adrian Schreyer]

That works and solves it, thanks!

On Mon, Nov 23, 2015 at 9:37 PM Mike Bayer  wrote:

>
>
> On 11/23/2015 03:15 PM, Adrian wrote:
> > I attached a script that reproduces the problem. It actually only
> > happens if the metadata contains a schema, then the tablename in the
> > INHERITS() clause get quoted, which causes the problem.
>
> anyway, there's no direct "postgresql_inherits_schema" feature as of
> yet, so yo can work around that you have to artificially place the
> schema name inside of the table name using direct quote control:
>
> from sqlalchemy.sql.elements import quoted_name
>
> child = Table(
> "child", metadata,
> Column("bar", String(4), nullable=False),
> postgresql_inherits=quoted_name(parent.fullname, quote=False),
> prefixes=["UNLOGGED"])
>
>
>
> >
> > On Monday, November 23, 2015 at 7:13:27 PM UTC+1, Adrian wrote:
> >
> > I actually I just found the problem; the tables are in fact created
> > in the right order - the problem is that the DDL contains INHERITS (
> > "parent" ). It gives the same error if I try to run the code in a
> > GUI with the inherited table name quoted, without (the quoting)
> > though it works.
> >
> > On Mon, Nov 23, 2015 at 6:55 PM Mike Bayer  > > wrote:
> >
> >
> >
> > On 11/23/2015 12:43 PM, Adrian wrote:
> > > Hello,
> > >
> > > I have the following problem - I recently upgraded to the 1.0+
> > branch
> > > from 0.9 and now the PostgreSQL table inheritance does not
> > work properly
> > > any longer because the tables that inherit from the master
> > table are
> > > sometimes created before (random) the actual table they
> > inherit from,
> > > throwing (psycopg2.ProgrammingError) relation "" does
> > not exist
> > > errors. With the 0.9+ branch a simple add_is_dependent_on was
> > working to
> > > solve this but it does not seem to be taken into account
> anymore.
> >
> > this is not the case, that API is still taken into account.  I
> can
> > remove the code that does so and the test which exercises this
> > feature
> > then fails, so it is also tested.
> >
> > Can you please provide a full reproducing test case?It needs
> > to be
> > succinct, single file, and runnable by me, thanks.
> >
> >
> >
> >
> > Is
> > > there something that changed from 0.9 to 1.0 that needs to be
> > done to
> > > get it to work? metadata.sorted_tables returns the proper
> > table order
> > > (master table first, dependencies later) though but tables are
> not
> > > created in that order by metadata.create_all().
> > >
> > > Thanks,
> > >
> > > Adrian
> > >
> > > --
> > > 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 a topic
> > in the Google Groups "sqlalchemy" group.
> > To unsubscribe from this topic, visit
> >
> https://groups.google.com/d/topic/sqlalchemy/k4Lhj7i5sBM/unsubscribe
> > <
> https://groups.google.com/d/topic/sqlalchemy/k4Lhj7i5sBM/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 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 

Re: [sqlalchemy] new object using a dictionary as the parameter

[Simon King]

> On 23 Nov 2015, at 17:58, mzagr...@d.umn.edu wrote:
> 
> Greetings,
> 
> I'm new to SQLAlchemy - sorry if the answer to this question is obvious.
> 
> I have a table with many fields.
> 
> I've created the ORM mapping:
> 
> Base = declarative_base()
> class CallRecord(Base):
> [stuff]
> 
> I'd like to create the CallRecord object by passing a dictionary to the 
> constructor. Is that possible?
> 
> call_record = CallRecord(my_dict)
> 
> Thanks for any help!
> 

The declarative_base sets up an __init__ function that accepts initial values 
as keyword arguments, so you can write something like:

  call_record = CallRecord(a=123, b=456)

In Python, you can call a function with keyword arguments from a dictionary 
using the “**” syntax:

  args = {‘a’: 123, ‘b’: 456}
  call_record = CallRecord(**args)

Hope that helps,

Simon

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