Re: [sqlalchemy] joined load inheritance with extra joins.. possible?

[Julien Cigar]

On Tue, Mar 20, 2018 at 08:32:14PM -0400, Mike Bayer wrote:
> I think it would be a lot easier to have the corresponding translation
> linked off using relationship().   can you work with that?

I could .. :) I'll make some "helper functions" or maybe a custom Query
object, with maybe some proxy-like properties too

Thanks

> 
> On Tue, Mar 20, 2018 at 9:19 AM, Julien Cigar  wrote:
> > Hello,
> >
> > I have an existing CMS-like application which uses joined table
> > inheritance at its core. Basically I have a base class Content from
> > which all other classes (Folder, Document, Event, File, ...) inherit.=20
> > It works wonderfully well. This is my (Postgre)SQL schema if you are=20
> > interested:=20
> > https://gist.githubusercontent.com/silenius/2e0f69fadfde9877e32c5b778efd39a=
> > f/raw/f08e2af2f7cd239c865777d1e54342ee53608520/sa.sql
> >
> > Now we'd like to support multiple languages. We plan to add an=20
> > additional table for each inherited class (additional translation=20
> > table approach), so basically we'll have something like:
> >
> > content_type
> >|
> >|
> > content --- content_translation
> >|
> >|
> > document --- document_translation
> >
> > Currently I have a polymorphic_on=3Dtables.content.c.content_type_id on=20
> > the base mapper (Content) and polymorphic_identity=3Dget_type_id(config,
> > 'some_content_type_name') for each inherited mapper (the get_type_id
> > function simply return the content_type ID:
> > https://gist.github.com/silenius/25b8f46192b5b9b23477fe0c541bd9f3 )
> >
> > As the application is quite large I'd like to avoid having to refactor a
> > lot of code.
> >
> > I wondered what would be the best SQLAlchemy approach to do this?
> >
> > For example let's say I have something like this in my application:
> > session.query(Document).all() which SQLAlchemy translates as:
> >
> > SELECT ..  FROM content=20
> > JOIN document ON content.id =3D document.content_id
> >
> > Is there some mapper configuration that I could change so that it
> > translates now as:
> >
> > SELECT .. FROM content=20
> > JOIN content_translation=20
> > ON content.id =3D content_translation.content_id=20
> > AND content_translation.lang =3D 'some_language_code'=20
> > JOIN document ON document.content_id =3D content.id=20
> > JOIN document_translation=20
> > ON document_translation.document_id =3D document.content_id=20
> > AND document_translation.lang =3D 'some_language_code'
> >
> > If not, what would be the best approach?
> >
> > Thanks !
> >
> >
> > --
> > Julien Cigar
> > Belgian Biodiversity Platform (http://www.biodiversity.be)
> > PGP fingerprint: EEF9 F697 4B68 D275 7B11  6A25 B2BB 3710 A204 23C0
> > No trees were killed in the creation of this message.
> > However, many electrons were terribly inconvenienced.
> >
> > --
> > 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.

-- 
Julien Cigar
Belgian Biodiversity Platform (http://www.biodiversity.be)
PGP fingerprint: EEF9 F697 4B68 D275 7B11  6A25 B2BB 3710 A204 23C0
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.

-- 
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] Duplicate primary key on table migration

[Colton Allen]

I'm moving data from one table to another.  During this move I'm preserving 
the ID of the old table before dropping it.  However, by doing so the 
sequence gets out of whack and the database will no longer allow inserts to 
the trigger table.  What can I do to fix the broken sequence?  The id 
column is a UUID.

# Migration
select = sa.select([outcome.c.id, outcome.c.a, outcome.c.b])
statement = sa.insert(trigger).from_select(['id', 'a', 'b'], select)
connection.execute(statement)

# Insert some time later
statement = sa.insert(trigger).values(a=1, b=2)
connection.execute(statement)  # duplicate key value violates unique 
constraint "trigger_pkey"

-- 
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] Aliased FROM raises self_group() error

[Mike Bayer]

On Fri, Mar 23, 2018 at 6:58 PM, Colton Allen  wrote:
> I'm trying to reset my primary key sequence and its raising a weird error.
> My primary key is a UUID.
>
> op.execute(
> sa.select([
> sa.func.setval(
> 'trigger_id_seq',
> sa.select([sa.func.max(trigger.c.id)]).alias('max_trigger_id')
> )
> ])
> )
>
> # raises: self_group() got an unexpected keyword argument 'against'

this is likely https://bitbucket.org/zzzeek/sqlalchemy/issues/3939
which is fixed in 1.2.   however, just don't use alias() in that
context, use .as_scalar().label('max_trigger_id') instead since you
want this to be a scalar column expression.


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

[sqlalchemy] Two application versions, different back ends

[Rich Shepard]

  I'm starting to write an application which will have two versions. One is
single-user with sqlite3 as the backend, the other is multi-user with
postgres as the backend. The single-user version will be written first.

  Both will be placed on github as F/OSS applications.

  Please point me to the docs where I can learn what differences I need to
make to accommodate both versions.

Rich

--
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: Foreign key not set when appending related object to collection using a variable?

[Derek Lambert]

Thank you, excellent explanation! Setting autoflush off fixed the issue.

In the end I created the Filter objects directly and set directory and 
category on them, which generated a single INSERT. It was generating a ton 
of SELECT / INSERT the way I was initially doing it. 

On Saturday, March 17, 2018 at 2:02:00 PM UTC-5, Derek Lambert wrote:
>
> I'm probably overlooking something simple, looking for feedback before 
> opening an issue.
>
> I have some objects with relationships defined between. When I create a 
> new related object and pass it in the append() method of the collection 
> everything works as expected, the foreign key is set. When I assign the new 
> related object to a variable and pass that to the append() method, the 
> foreign key isn't set and I get a 'null value in column "directory_name" 
> violates not-null constraint'. This is with SQLAlchemy 1.2.5 and python 3.6.
>
> import sqlalchemy as sa
> import sqlalchemy.orm as orm
> from sqlalchemy.ext.declarative import declarative_base
>
>
> Base = declarative_base()
>
>
> class Directory(Base):
> name = sa.Column(sa.String, primary_key=True)
>
> __tablename__ = 'directory'
>
>
> class Category(Base):
> name = sa.Column(sa.String, primary_key=True)
>
> __tablename__ = 'category'
>
>
> class Filter(Base):
> directory_name = sa.Column(sa.String, sa.ForeignKey('directory.name'), 
> primary_key=True)
> category_name  = sa.Column(sa.String, sa.ForeignKey('category.name'), 
> primary_key=True)
> filter = sa.Column(sa.String, primary_key=True)
>
> directory = orm.relationship('Directory', 
> backref=orm.backref('filters', lazy='joined'), lazy='joined')
> category  = orm.relationship('Category', 
> backref=orm.backref('filters', lazy='joined'), lazy='joined')
>
> __tablename__ = 'filter'
>
>
> engine = 
> sa.create_engine('postgresql+psycopg2://postgres@localhost/bug_test')
> Base.metadata.create_all(engine)
> session = orm.sessionmaker(bind=engine)()
>
> directory = Directory(name='test')
> category_a = Category(name='category a')
> category_b = Category(name='category b')
>
> session.add(directory)
> session.add(category_a)
> session.add(category_b)
> session.commit()
>
> assert len(session.new) == 0
>
> # Instantiate object in call to append - works
> directory.filters.append(Filter(filter='test filter', category=category_a))
> session.commit()
>
> assert len(session.new) == 0
>
> # Instantiate object before call to append - fails
> new_filter = Filter(filter='new filter', category=category_b)
> directory.filters.append(new_filter)
>
> session.commit()
>
> assert len(session.new) == 0
>
>
> Thanks,
> Derek
>

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