Re: [sqlalchemy] orm.column_property()
On Fri, Jun 23, 2023 at 09:29:27AM -0400, Mike Bayer wrote: > > Hi Julien - Hi Mike, > > OK, if we're going to do one of your big queries, can we please start fresh, > make a new discussion at https://github.com/sqlalchemy/sqlalchemy/discussions > , give me **really really succinct** models + the query you want, etc. > > the google groups thing here is not exactly going away but it's an antiquated > interface at this point. > I think you missed one message, I added nesting=True to the cte() expression and it does what I want :) Next time I'll use https://github.com/sqlalchemy/sqlalchemy/discussions Thanks! Julien > > > On Fri, Jun 23, 2023, at 4:48 AM, Julien Cigar wrote: > > On Wed, Jun 21, 2023 at 08:35:36AM -0400, Mike Bayer wrote: > >> > >> > >> > >> On Wed, Jun 21, 2023, at 5:12 AM, Julien Cigar wrote: > >> > Hello, > >> > > >> > I'm trying to add a column_property to recursively load and merge some > >> > json column. > >> > > >> > I have the following: > >> > https://gist.github.com/silenius/1af1072abae5829f54584f1ea554e074 but I > >> > cannot figure how can I replace the '5011' at line 11 (which is the > >> > object primary key) with the "current" involved objet..? > >> > > >> > For example when I'm doing dbsession.get(MyClass, 1234) I cannot figure > >> > how reference the id in the column_property query > >> > >> well you wouldnt hardcode the id like that, you'd need to make your query > >> correlated to the parent. > >> > >> so it would select() from "ac" where "ac" is "aliased(class_)" , then > >> correlate to the class > >> > >> > >> .where( > >> class_.id == ac.id > >> ). > > > > Thank you Mike, it _almost_ work. > > With (1) SQLAlchemy now generates (2). I'm still getting an error: > > > > sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedTable) > > invalid reference to FROM-clause entry for table "content" > > LINE 4: WHERE content_1.id = content.id UNION ALL SELECT content.id ... > > ^ > > HINT: Perhaps you meant to reference the table alias "content_1". > > > > This is because the WITH statement appears at the "top" rather than a > > subselect (and cannot reference the outer table) > > > > Any idea how could I transform (1) to generate (3) rather than (2) ? > > > > (1) https://gist.github.com/silenius/43717848057d87ef7945c4d621fce5e8 > > (2) https://gist.github.com/silenius/34d3a319a52f8fe4fcd2b6418ca9e927 > > (3) https://gist.github.com/silenius/6d048f2b7819d39b70cc4d77237f9031 > > > > Thanks a lot! > > > > Julien > > > >> > >> > >> as far as how that integrates into the CTE and all that you'd have to > >> arrange the geometry of the SELECT to work out correctly, I would start > >> with something very simple and build outwards. > >> > >> correlated subquery column_property at > >> https://docs.sqlalchemy.org/en/20/orm/mapped_sql_expr.html#using-column-property > >> > >> -- > >> 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 view this discussion on the web visit > >> https://groups.google.com/d/msgid/sqlalchemy/400b6b72-f601-47c8-aa30-827240097eb8%40app.fastmail.com. > > > > -- > > 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
Re: [sqlalchemy] orm.column_property()
On Fri, Jun 23, 2023 at 10:48:27AM +0200, Julien Cigar wrote: > > On Wed, Jun 21, 2023 at 08:35:36AM -0400, Mike Bayer wrote: > > > > > > > > On Wed, Jun 21, 2023, at 5:12 AM, Julien Cigar wrote: > > > Hello, > > > > > > I'm trying to add a column_property to recursively load and merge some > > > json column. > > > > > > I have the following: > > > https://gist.github.com/silenius/1af1072abae5829f54584f1ea554e074 but I > > > cannot figure how can I replace the '5011' at line 11 (which is the > > > object primary key) with the "current" involved objet..? > > > > > > For example when I'm doing dbsession.get(MyClass, 1234) I cannot figure > > > how reference the id in the column_property query > > > > well you wouldnt hardcode the id like that, you'd need to make your query > > correlated to the parent. > > > > so it would select() from "ac" where "ac" is "aliased(class_)" , then > > correlate to the class > > > > > > .where( > > class_.id == ac.id > > ). > > Thank you Mike, it _almost_ work. > With (1) SQLAlchemy now generates (2). I'm still getting an error: > > sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedTable) invalid > reference to FROM-clause entry for table "content" > LINE 4: WHERE content_1.id = content.id UNION ALL SELECT content.id ... > ^ > HINT: Perhaps you meant to reference the table alias "content_1". > > This is because the WITH statement appears at the "top" rather than a > subselect (and cannot reference the outer table) > > Any idea how could I transform (1) to generate (3) rather than (2) ? OK I found it! I added nesting=True to the cte() expression > > (1) https://gist.github.com/silenius/43717848057d87ef7945c4d621fce5e8 > (2) https://gist.github.com/silenius/34d3a319a52f8fe4fcd2b6418ca9e927 > (3) https://gist.github.com/silenius/6d048f2b7819d39b70cc4d77237f9031 > > Thanks a lot! > > Julien > > > > > > > as far as how that integrates into the CTE and all that you'd have to > > arrange the geometry of the SELECT to work out correctly, I would start > > with something very simple and build outwards. > > > > correlated subquery column_property at > > https://docs.sqlalchemy.org/en/20/orm/mapped_sql_expr.html#using-column-property > > > > -- > > 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 view this discussion on the web visit > > https://groups.google.com/d/msgid/sqlalchemy/400b6b72-f601-47c8-aa30-827240097eb8%40app.fastmail.com. > > -- > 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 view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/fdxvwszr6d6k624egxak3ctr5wcu43daz4z6o7okhikglq57to%40puqqqclybeig. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/b5pqlperijpzgy32rpklyyoou4jctjwc44kmjcdu7yrtplzffa%40fcmeqs3xo3dw. signature.asc Description: PGP signature
Re: [sqlalchemy] orm.column_property()
On Wed, Jun 21, 2023 at 08:35:36AM -0400, Mike Bayer wrote: > > > > On Wed, Jun 21, 2023, at 5:12 AM, Julien Cigar wrote: > > Hello, > > > > I'm trying to add a column_property to recursively load and merge some > > json column. > > > > I have the following: > > https://gist.github.com/silenius/1af1072abae5829f54584f1ea554e074 but I > > cannot figure how can I replace the '5011' at line 11 (which is the > > object primary key) with the "current" involved objet..? > > > > For example when I'm doing dbsession.get(MyClass, 1234) I cannot figure > > how reference the id in the column_property query > > well you wouldnt hardcode the id like that, you'd need to make your query > correlated to the parent. > > so it would select() from "ac" where "ac" is "aliased(class_)" , then > correlate to the class > > > .where( > class_.id == ac.id > ). Thank you Mike, it _almost_ work. With (1) SQLAlchemy now generates (2). I'm still getting an error: sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedTable) invalid reference to FROM-clause entry for table "content" LINE 4: WHERE content_1.id = content.id UNION ALL SELECT content.id ... ^ HINT: Perhaps you meant to reference the table alias "content_1". This is because the WITH statement appears at the "top" rather than a subselect (and cannot reference the outer table) Any idea how could I transform (1) to generate (3) rather than (2) ? (1) https://gist.github.com/silenius/43717848057d87ef7945c4d621fce5e8 (2) https://gist.github.com/silenius/34d3a319a52f8fe4fcd2b6418ca9e927 (3) https://gist.github.com/silenius/6d048f2b7819d39b70cc4d77237f9031 Thanks a lot! Julien > > > as far as how that integrates into the CTE and all that you'd have to arrange > the geometry of the SELECT to work out correctly, I would start with > something very simple and build outwards. > > correlated subquery column_property at > https://docs.sqlalchemy.org/en/20/orm/mapped_sql_expr.html#using-column-property > > -- > 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 view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/400b6b72-f601-47c8-aa30-827240097eb8%40app.fastmail.com. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/fdxvwszr6d6k624egxak3ctr5wcu43daz4z6o7okhikglq57to%40puqqqclybeig. signature.asc Description: PGP signature
[sqlalchemy] orm.column_property()
Hello, I'm trying to add a column_property to recursively load and merge some json column. I have the following: https://gist.github.com/silenius/1af1072abae5829f54584f1ea554e074 but I cannot figure how can I replace the '5011' at line 11 (which is the object primary key) with the "current" involved objet..? For example when I'm doing dbsession.get(MyClass, 1234) I cannot figure how reference the id in the column_property query Any idea ? 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/p4htucd3cmfazp5wjwtakmfpxkc75qxfgjl4n4h35p7rzrfvsf%40scdc3ayc36nm. signature.asc Description: PGP signature
Re: [sqlalchemy] migrating to 1.4 and sql.and_()
On Mon, Jun 21, 2021 at 12:04:48PM -0400, Mike Bayer wrote: > hi - > Hello, > can you please create a self-contained MCVE and post to > https://github.com/sqlalchemy/sqlalchemy/issues - thanks. I've created a ticket with a MCVE https://github.com/sqlalchemy/sqlalchemy/issues/6661 Have a nice day, Julien > > - mike > > > > On Mon, Jun 21, 2021, at 5:25 AM, Julien Cigar wrote: > > ok .. :) > > > > Another regression from 1.3: I have two deferred column_property on my > > mapped class (1) and with (2) I'm getting a > > sqlalchemy.exc.ProgrammingError: sqlalchemy.exc.ProgrammingError: > > (psycopg2.errors.InvalidColumnReference) WITH query "parents" has 21 > > columns available but 23 columns specified. > > > > The generated query is (3), which is wrong: it includes deferred columns > > in the WITH RECUSRIVE statement but don't load them in the SELECT > > clause. If I'm set deferred=False in (1) then is works as expected > > (generated query is (4)) > > > > (1) > > https://github.com/silenius/amnesia/blob/dev/amnesia/modules/content/mapper.py#L117-L130 > > (2) https://gist.github.com/silenius/34ce40ed288c6a681058ab064147bd70 > > (3) https://gist.github.com/silenius/02a8c2481f77ca1a1ec05bfad830821d > > (4) https://gist.github.com/silenius/5de95ceddd14e62bfc3e52366643bd32 > > > > Julien > > > > On Fri, Jun 18, 2021 at 10:11:10AM -0400, Mike Bayer wrote: > > > > > > that's a really obscure API you found there. Coverage for that wasn't > > > carried along to 1.4 unfortunately so you'd need to assemble that list > > > outside of the and_() first for now. > > > > > > > > > On Fri, Jun 18, 2021, at 4:08 AM, Julien Cigar wrote: > > > > Hello, > > > > > > > > While updating SQLAlchemy dependency to 1.4 (from 1.3) I noticed that I > > > > couldn't use .append() on an existing sql.and_() clause .. is it > > > > expected..? > > > > > > > > The problematic code is > > > > https://gist.github.com/silenius/7d3043d64fddaa8474dcd062e23ced44 (line > > > > 18-21) > > > > > > > > Another example that worked with 1.3: > > > > https://gist.github.com/silenius/2054e7dc690946c0122c72e6b05f1433 > > > > > > > > Thanks, > > > > Julien > > > > > > > > -- > > > > 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 > > > > <mailto:sqlalchemy%2Bunsubscribe%40googlegroups.com> > > > > <mailto:sqlalchemy%2Bunsubscribe%40googlegroups.com>. > > > > To view this discussion on the web visit > > > > https://groups.google.com/d/msgid/sqlalchemy/20210618080804.b47v72c3whdaluyh%40x1. > > > > > > > > > > -- > > > 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 > > > <mailto:sqlalchemy%2Bunsubscribe%40googlegroups.com>. > > > To view this discussion on the web visit > > > https:/
Re: [sqlalchemy] migrating to 1.4 and sql.and_()
ok .. :) Another regression from 1.3: I have two deferred column_property on my mapped class (1) and with (2) I'm getting a sqlalchemy.exc.ProgrammingError: sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InvalidColumnReference) WITH query "parents" has 21 columns available but 23 columns specified. The generated query is (3), which is wrong: it includes deferred columns in the WITH RECUSRIVE statement but don't load them in the SELECT clause. If I'm set deferred=False in (1) then is works as expected (generated query is (4)) (1) https://github.com/silenius/amnesia/blob/dev/amnesia/modules/content/mapper.py#L117-L130 (2) https://gist.github.com/silenius/34ce40ed288c6a681058ab064147bd70 (3) https://gist.github.com/silenius/02a8c2481f77ca1a1ec05bfad830821d (4) https://gist.github.com/silenius/5de95ceddd14e62bfc3e52366643bd32 Julien On Fri, Jun 18, 2021 at 10:11:10AM -0400, Mike Bayer wrote: > > that's a really obscure API you found there. Coverage for that wasn't > carried along to 1.4 unfortunately so you'd need to assemble that list > outside of the and_() first for now. > > > On Fri, Jun 18, 2021, at 4:08 AM, Julien Cigar wrote: > > Hello, > > > > While updating SQLAlchemy dependency to 1.4 (from 1.3) I noticed that I > > couldn't use .append() on an existing sql.and_() clause .. is it > > expected..? > > > > The problematic code is > > https://gist.github.com/silenius/7d3043d64fddaa8474dcd062e23ced44 (line > > 18-21) > > > > Another example that worked with 1.3: > > https://gist.github.com/silenius/2054e7dc690946c0122c72e6b05f1433 > > > > Thanks, > > Julien > > > > -- > > 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 > > <mailto:sqlalchemy%2Bunsubscribe%40googlegroups.com>. > > To view this discussion on the web visit > > https://groups.google.com/d/msgid/sqlalchemy/20210618080804.b47v72c3whdaluyh%40x1. > > > > -- > 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 view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/31f1f804-21f2-4faa-9a71-ef356149248a%40www.fastmail.com. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/20210621092524.pzzpcnn5nkuxsr6y%40x1.
Re: [sqlalchemy] migrating to 1.4 and sql.and_()
On Fri, Jun 18, 2021 at 10:08:04AM +0200, Julien Cigar wrote: > Hello, > > While updating SQLAlchemy dependency to 1.4 (from 1.3) I noticed that I > couldn't use .append() on an existing sql.and_() clause .. is it > expected..? I forgot to add that the error I get is: Traceback (most recent call last): File "/usr/home/julien/code/venvs/riparias/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 826, in __getattr__ return getattr(self.comparator, key) AttributeError: 'Comparator' object has no attribute '_text_converter_role' The above exception was the direct cause of the following exception: Traceback (most recent call last): File "", line 1, in File "/usr/home/julien/code/venvs/riparias/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 2302, in append coercions.expect(self._text_converter_role, clause).self_group( File "/usr/home/julien/code/venvs/riparias/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 837, in __getattr__ replace_context=err, File "/usr/home/julien/code/venvs/riparias/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 207, in raise_ raise exception AttributeError: Neither 'BooleanClauseList' object nor 'Comparator' object has an attribute '_text_converter_role' > > The problematic code is > https://gist.github.com/silenius/7d3043d64fddaa8474dcd062e23ced44 (line > 18-21) > > Another example that worked with 1.3: > https://gist.github.com/silenius/2054e7dc690946c0122c72e6b05f1433 > > Thanks, > Julien > > -- > 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 view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/20210618080804.b47v72c3whdaluyh%40x1. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/20210618092135.7f7iwlpy7oxpzgjb%40x1.
[sqlalchemy] migrating to 1.4 and sql.and_()
Hello, While updating SQLAlchemy dependency to 1.4 (from 1.3) I noticed that I couldn't use .append() on an existing sql.and_() clause .. is it expected..? The problematic code is https://gist.github.com/silenius/7d3043d64fddaa8474dcd062e23ced44 (line 18-21) Another example that worked with 1.3: https://gist.github.com/silenius/2054e7dc690946c0122c72e6b05f1433 Thanks, Julien -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/20210618080804.b47v72c3whdaluyh%40x1.
Re: [sqlalchemy] issue with hybrid properties
On Mon, Jun 14, 2021 at 10:04:10AM -0400, Mike Bayer wrote:
> Buried deep in this example you are using the "relationship to aliased class"
> pattern, which is fine, not sure if that was in the previous example, but in
> this case this is the source of the issue.
>
> The approach to solving all hybrid related querying issues, as well as assoc
> proxy issues, is to fully write out the query not using either approach.
> I see the docs at
> https://docs.sqlalchemy.org/en/14/orm/join_conditions.html#relationship-to-aliased-class
> don't clarify that when one makes a relationship to an aliased class, that's
> your entity now, you can not query this relationship in terms of the original
> class.
>
> so to illustrate test six not using hybrid:
>
> def test_six_not_using_hybrids(self):
> s = Session(e)
>
> # the "relationship to aliased class" pattern is in use, and this
> # aliased object was private to the setup_relationships() function,
> # so pull it out here so we can use it in a query
> partition_alias = Document.document_current_translation.entity.entity
>
> q = (
> s.query(Document)
> .join(Document.document_current_translation)
> .options(orm.lazyload("*"))
> .filter(partition_alias.title.like("doc3%"))
> .order_by(partition_alias.title)
> )
one more question: I'm wondering why does it work without a
.contains_eager(Document.document_current_translation) ?
I thought that it was somewhat mandatory when filtering/ordering on a
relationship which was explicitely joined in the Query (as mentionned in
the "Routing Explicit Joins/Statements into Eagerly Loaded Collections"
documentation)
>
>
> Then to get this to work with hybrids, the hybrid also must be set up to use
> this alias object in queries, not the actual class, as that's not what the
> relationship is mapped towards:
>
> def setup_hybrids(
> cls, name, translation_cls, current_locale=get_current_locale,
> default=None
> ):
>
> # the "relationship to aliased class" pattern is in use, and this
> # aliased object was private to the setup_relationships() function,
> # so pull it out here so we can use it in a query
> partition_alias = cls.current_translation.entity.entity
>
> def _fget(self):
> return getattr(self.current_translation, name, default)
>
> def _fset(self, value):
> locale_name = current_locale()
>
> trans = self.translations.setdefault(
> locale_name, translation_cls(language_id=locale_name)
> )
>
> setattr(trans, name, value)
>
> def _expr(_cls):
> return getattr(partition_alias, name)
> # foo = orm.aliased(cls.document_current_translation)
> # q = sql.select([getattr(foo, name)]).as_scalar()
> # return q
>
> log.info("Adding hybrid attribute: %s.%s", cls, name)
>
> prop = hybrid_property(fget=_fget, fset=_fset, expr=_expr)
>
> setattr(cls, name, prop)
>
>
> then your original test_six works as given.
>
>
>
>
> On Mon, Jun 14, 2021, at 6:20 AM, Julien Cigar wrote:
> > On Tue, Jun 01, 2021 at 08:36:38AM -0400, Mike Bayer wrote:
> > > I can't make out your "ideal" SQL due to bad formatting however, if you
> > > want the query to have an additional JOIN tacked onto it whenever you
> > > refer to this hybrid property in the COLUMNS clause, you need to use
> > > query.join() explicitly, either up front or by using something like a
> > > query builder function or the do_orm_execute event to automatically alter
> > > the query.
> >
> > Thank you Mike, I'm still wondering why with when I'm joining the
> > row-limited relationship (through a window function) called
> > "current_translation" in my case the generated FROM is wrong. It looks
> > like there is an issue when the aliased class is joined through
> > inheritance.
> >
> > Attached my updated POC, the issue is in test_six and test_seven
> >
> > (pytest -s poc.py)
> >
> > you can clearly see that the FROM clause is wrong
> > (https://gist.github.com/silenius/3c090ee9470a4889e9812f7d8da64f56#file-gistfile1-txt-L28-L31),
> > although the .current_translation relationship is properly loaded
> > (https://gist.github.com/silenius/3c090ee9470a4889e9812f7d8da64f56#file-gistfile1-txt-L28-L31)
> >
> > (and I'm sorry, but I'm still don't see how could I subselect from this
> > row-limited current_translation relationsh
Re: [sqlalchemy] issue with hybrid properties
On Tue, Jun 01, 2021 at 08:36:38AM -0400, Mike Bayer wrote:
> I can't make out your "ideal" SQL due to bad formatting however, if you want
> the query to have an additional JOIN tacked onto it whenever you refer to
> this hybrid property in the COLUMNS clause, you need to use query.join()
> explicitly, either up front or by using something like a query builder
> function or the do_orm_execute event to automatically alter the query.
Thank you Mike, I'm still wondering why with when I'm joining the
row-limited relationship (through a window function) called
"current_translation" in my case the generated FROM is wrong. It looks
like there is an issue when the aliased class is joined through
inheritance.
Attached my updated POC, the issue is in test_six and test_seven
(pytest -s poc.py)
you can clearly see that the FROM clause is wrong
(https://gist.github.com/silenius/3c090ee9470a4889e9812f7d8da64f56#file-gistfile1-txt-L28-L31),
although the .current_translation relationship is properly loaded
(https://gist.github.com/silenius/3c090ee9470a4889e9812f7d8da64f56#file-gistfile1-txt-L28-L31)
(and I'm sorry, but I'm still don't see how could I subselect from this
row-limited current_translation relationship in my hybrid property
expression ..)
Have a good day,
Julien
>
> On Tue, Jun 1, 2021, at 6:58 AM, Julien Cigar wrote:
> > On Fri, May 28, 2021 at 10:53:26AM -0400, Mike Bayer wrote:
> > > the hybrid here is pulling in additional tables which are causing a
> > > cartesian product since there is nothing joining the Document entity to
> > > this other entity which seems to be DocumentTranslation.Run this in
> > > 1.4 and you will see the warnings generated. an ORDER BY etc. has to
> > > either produce a self-contained scalar expression, like a correlated
> > > subquery, or you otherwise need to make sure the query has JOIN/WHERE to
> > > link them together, such as:
> > >
> > > q = (
> > > s.query(Document)
> > > .join(DocumentTranslation)
> > > .filter(Document.title.like("doc3%"))
> > > .order_by(Document.title)
> > > )
> >
> > Thank you for your quick reply and help.
> >
> >
> >
> >
> >
> >
> >
> > It works with: .join(DocumentTranslation) or
> >
> >
> >
> > .join(Document.translations). However it doesn't work with
> >
> >
> >
> > .join(Document.current_translation), the FROM clause is wrong (as you
> >
> >
> >
> > can see in my test_six()). This is because my Translation-like classes
> >
> >
> >
> > are polymorphic too I think.
> >
> > >
> > >
> > > seems like you'd be better off making sure Document.title returns a
> > > correlated scalar subquery rather than what it does now which seems to be
> > > :
> > >
> >
> > yes, this would be really the best, but I don't see how could I
> >
> >
> >
Re: [sqlalchemy] issue with hybrid properties
On Fri, May 28, 2021 at 10:53:26AM -0400, Mike Bayer wrote:
> the hybrid here is pulling in additional tables which are causing a cartesian
> product since there is nothing joining the Document entity to this other
> entity which seems to be DocumentTranslation.Run this in 1.4 and you will
> see the warnings generated. an ORDER BY etc. has to either produce a
> self-contained scalar expression, like a correlated subquery, or you
> otherwise need to make sure the query has JOIN/WHERE to link them together,
> such as:
>
> q = (
> s.query(Document)
> .join(DocumentTranslation)
> .filter(Document.title.like("doc3%"))
> .order_by(Document.title)
> )
Thank you for your quick reply and help.
It works with: .join(DocumentTranslation) or
.join(Document.translations). However it doesn't work with
.join(Document.current_translation), the FROM clause is wrong (as you
can see in my test_six()). This is because my Translation-like classes
are polymorphic too I think.
>
>
> seems like you'd be better off making sure Document.title returns a
> correlated scalar subquery rather than what it does now which seems to be :
yes, this would be really the best, but I don't see how could I
subselect from the .current_translation relationship (which is a
relationship on an orm.aliased filtered by a primaryjoin). Ideally I'd
like to get something like:
with
q = (
s.query(Document)
.join(Document.current_translation)
.filter(Document.title.like("doc3%"))
.order_by(Document.title)
)
I should have something like:
SELECT (
SELECT sub.title FROM sub
) AS title, (
SELECT sub.description FROM sub
) AS description
FROM
content c
JOIN
document d ON c.id = d.content_id
JOIN (
SELECT
(...)
row_number() OVER (
PARTITION BY
document_translation.content_id
ORDER BY
document_translation.language_id = 'fr' DESC,
document_translation.language_id = 'en' DESC
) AS "index"
FROM
content_translation
JOIN
document_translation
ON content_translation.language_id = document_translation.language_id
AND content_translation.content_id = document_translation.content_id
WHERE
document_translation.language_id IN ('fr', 'en')
) AS sub
ON sub.document_translation_content_id = content.id
AND sub."index" = 1
WHERE
title LIKE 'doc3%'
ORDER BY
title
but I don't see any way in the hybrid property expression to subselect
from the outer .current_translation query ..
(I've updated my POC on
https://gist.github.com/silenius/77466fc260a9cb0f25025bffbf3339cd)
Julien
>
> (Pdb) print(select(Document.title))
> SELECT content_translation.title
> FROM content_translation JOIN document_translation ON
> content_translation.language_id = document_translation.language_id AND
> content_translation.content_id = document_translation.content_id
>
> I know you've had this model working for many years now.
>
>
>
>
>
>
> On Fri, May 28, 2021, at 8:25 AM, Julien Cigar wrote:
> > Hello,
> >
> > I have a project heavily based on joinedload inheritance. It's a
> > CMS-like for which I've added a translation feature some months ago. It
> > worked more or less, but some things are still not working properly.
> >
> > The situation is the following:
> >
> > I have a joinedload inheritance for which the base class is "Content",
> > with subclasses like Folder, Document, etc. Nothing really complicated.
> >
> > I'm also having another joinedload inheritance for which the base class
> > is "ContentTranslation", with subclasses like FolderTranslation,
> > DocumentTranslation, etc.
> >
> > The idea is that each Content-like class has a corresponding
> > -Translation class (with common attributes for all Content-like stuff,
> > like "title", "description", "language_id", etc, located in
> > ContentTranslation class)
> >
> > On each Content-like class, there is "xxx_current_translation" and a
> > "xxx_translations" relationships and I've added hybrid properties which
> > map to the corresponding -Translation class.
> >
> > The problem I have is that I can't .order_by() or .filter() on the
> > hybrid properties. Instead of making a JOIN on the
> > "xxx_current_translation" (which is joinedload) SQLAlchemy adds the base
> > ContentTranslation class. I've also tried with .join() manually and add
> > it with orm.contains_eager() but it doesn't work either..
> >
> > I've made a full POC on
> > https://gist.g
Re: [sqlalchemy] issue with hybrid properties
> content_translation.language_id = document_translation.language_id AND > content_translation.content_id = document_translation.content_id > > I know you've had this model working for many years now. > > > > > > > On Fri, May 28, 2021, at 8:25 AM, Julien Cigar wrote: > > Hello, > > > > I have a project heavily based on joinedload inheritance. It's a > > CMS-like for which I've added a translation feature some months ago. It > > worked more or less, but some things are still not working properly. > > > > The situation is the following: > > > > I have a joinedload inheritance for which the base class is "Content", > > with subclasses like Folder, Document, etc. Nothing really complicated. > > > > I'm also having another joinedload inheritance for which the base class > > is "ContentTranslation", with subclasses like FolderTranslation, > > DocumentTranslation, etc. > > > > The idea is that each Content-like class has a corresponding > > -Translation class (with common attributes for all Content-like stuff, > > like "title", "description", "language_id", etc, located in > > ContentTranslation class) > > > > On each Content-like class, there is "xxx_current_translation" and a > > "xxx_translations" relationships and I've added hybrid properties which > > map to the corresponding -Translation class. > > > > The problem I have is that I can't .order_by() or .filter() on the > > hybrid properties. Instead of making a JOIN on the > > "xxx_current_translation" (which is joinedload) SQLAlchemy adds the base > > ContentTranslation class. I've also tried with .join() manually and add > > it with orm.contains_eager() but it doesn't work either.. > > > > I've made a full POC on > > https://gist.github.com/silenius/77466fc260a9cb0f25025bffbf3339cd > > > > Any idea ? :) > > > > Thanks, > > Julien > > > > -- > > 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 > > <mailto:sqlalchemy%2Bunsubscribe%40googlegroups.com>. > > To view this discussion on the web visit > > https://groups.google.com/d/msgid/sqlalchemy/20210528122520.4gzwrf2w4b6cqno5%40x1. > > > > -- > 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 view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/fb68178f-16ac-45ec-b8c0-c1d5dde8b78a%40www.fastmail.com. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/20210601105841.x5hxbw27jky4o2s2%40x1.
[sqlalchemy] issue with hybrid properties
Hello, I have a project heavily based on joinedload inheritance. It's a CMS-like for which I've added a translation feature some months ago. It worked more or less, but some things are still not working properly. The situation is the following: I have a joinedload inheritance for which the base class is "Content", with subclasses like Folder, Document, etc. Nothing really complicated. I'm also having another joinedload inheritance for which the base class is "ContentTranslation", with subclasses like FolderTranslation, DocumentTranslation, etc. The idea is that each Content-like class has a corresponding -Translation class (with common attributes for all Content-like stuff, like "title", "description", "language_id", etc, located in ContentTranslation class) On each Content-like class, there is "xxx_current_translation" and a "xxx_translations" relationships and I've added hybrid properties which map to the corresponding -Translation class. The problem I have is that I can't .order_by() or .filter() on the hybrid properties. Instead of making a JOIN on the "xxx_current_translation" (which is joinedload) SQLAlchemy adds the base ContentTranslation class. I've also tried with .join() manually and add it with orm.contains_eager() but it doesn't work either.. I've made a full POC on https://gist.github.com/silenius/77466fc260a9cb0f25025bffbf3339cd Any idea ? :) Thanks, Julien -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/20210528122520.4gzwrf2w4b6cqno5%40x1.
Re: [sqlalchemy] jsonb_agg with an InstrumentedAttribute
On Thu, Jun 25, 2020 at 12:07:40PM -0400, Mike Bayer wrote:
> is "question_choice" a table name? use
> json_agg(literal_column("question_choice"))
Thank you, it works with literal_column() :)
>
>
>
> On Thu, Jun 25, 2020, at 5:38 AM, Julien Cigar wrote:
> > Hello,
> >
> > I'd like to use a sql.func.jsonb_agg() with the following
> > InstrumentedAttribute:
> >
> > orm.mapper(
> > Question, tables['amnesia_phc_registry.question'],
> > properties={
> > 'choices': orm.relationship(
> > Choice, back_populates='question'
> > ),
> >
> > 'answers_text': orm.relationship(
> > AnswerText, back_populates='question'
> > )
> > }
> > )
> >
> >
> > q1 = (
> > self.dbsession.query(Question)
> > .join(Question.choices)
> > .join(Choice.experts)
> > .filter(Choice.experts.contains(self.entity))
> > .group_by(Question.id)
> > ).add_columns(
> > sql.func.jsonb_agg(Question.choices)
> > )
> >
> > but I'm getting a list of [True, True], for example:
> > (, [True, True,
> > True, True])
> >
> > this is because the generated query is
> >
> > jsonb_agg(amnesia_phc_registry.question.id =
> > amnesia_phc_registry.question_choice.question_id) rather than
> > jsonb_agg(amnesia_phc_registry.question_choice)
> >
> > any idea how could I achieve this ?
> >
> > Thanks!
> > Julien
> >
> > --
> > 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 view this discussion on the web visit
> > https://groups.google.com/d/msgid/sqlalchemy/20200625093846.63f6ssijswsvx6gu%40x1.
> >
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/61045d8f-1733-4f51-8b93-4af41d043b7d%40www.fastmail.com.
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/20200626044614.nuykffy6yre6zejw%40x1.
[sqlalchemy] jsonb_agg with an InstrumentedAttribute
Hello,
I'd like to use a sql.func.jsonb_agg() with the following
InstrumentedAttribute:
orm.mapper(
Question, tables['amnesia_phc_registry.question'],
properties={
'choices': orm.relationship(
Choice, back_populates='question'
),
'answers_text': orm.relationship(
AnswerText, back_populates='question'
)
}
)
q1 = (
self.dbsession.query(Question)
.join(Question.choices)
.join(Choice.experts)
.filter(Choice.experts.contains(self.entity))
.group_by(Question.id)
).add_columns(
sql.func.jsonb_agg(Question.choices)
)
but I'm getting a list of [True, True], for example:
(, [True, True,
True, True])
this is because the generated query is
jsonb_agg(amnesia_phc_registry.question.id =
amnesia_phc_registry.question_choice.question_id) rather than
jsonb_agg(amnesia_phc_registry.question_choice)
any idea how could I achieve this ?
Thanks!
Julien
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/20200625093846.63f6ssijswsvx6gu%40x1.
Re: [sqlalchemy] change an existing relationship
On Fri, Sep 27, 2019 at 09:59:49AM -0400, Mike Bayer wrote:
>
>
> On Fri, Sep 27, 2019, at 9:34 AM, Julien Cigar wrote:
> > On Fri, Sep 27, 2019 at 09:03:53AM -0400, Mike Bayer wrote:
> > >
> > >
> > > On Fri, Sep 27, 2019, at 6:26 AM, Julien Cigar wrote:
> > > > On Thu, Sep 26, 2019 at 11:39:52AM -0400, Mike Bayer wrote:
> > > > > that's not supported, I would suggest trying to solve your problem in
> > > > > a different way.
> > > > >
> > > >
> > > > Ok, that's what I thought.. thanks!
> > > >
> > > > I have a typical joined table inheritance, with Content being the "base"
> > > > class, and childs as Document, Folder, Event, etc.
> > > >
> > > > I have added a transparent translation package with the "one translation
> > > > table per entity" approach (also using joined table inheritance),
> > > > and hybrid properties.
> > > >
> > > > So I have ContentTranslation being the "base" class, and childs like
> > > > DocumentTranslation, FolderTranslation, EventTranslation, etc. which
> > > > contain translation fields specific to each class.
> > > >
> > > > You helped me some weeks ago on this and I ended with (1), in which two
> > > > relationships were added to every children of Content
> > > > ("current_translation" and "translations"), and hybrid properties
> > > > pointing to their dedicated translation class.
> > > >
> > > > It works well, the only minor issue is that I don't have any
> > > > relationship on the base Content class (as you told me in the past that
> > > > overriding relationships could be problematic).
> > > >
> > > > I made some modifications (2) in which the relationships are now
> > > > prefixed with the class name, and added two properties which point to
> > > > the class relationship, so that in the end:
> > > >
> > > > - Content has a "content_current_translation" and
> > > > "content_translations" relationships, and two property
> > > > "current_translation" and "translations" pointing to them
> > > >
> > > > - Document has a "document_current_translation" and
> > > > "document_translations" with also two property current_translation and
> > > > translations pointing to them, but the minor issue is that the
> > > > content_current_translation is also joined loaded, which I'd like to
> > > > avoid.. and I'm wondering what would be the best approach.
> > > >
> > > > I don't know if it's clear, but if it is not I could make a test case
> > >
> > > I'm not sure what the problem is, the code I can see has "lazy='joined'"
> > > in some spots, you'd make that into "lazy='select'" if that's the lazy
> > > loading you want.
> > >
> > > if this is some kind of automatic-generation issue where some of these
> > > generations need to apply a different kind of default loader then you
> > > would need to add parameterization to your code generation scheme, such
> > > as additional arguments to your setup_relationships() function.
> >
> > The problem I have if that the "current_translation" should always be
> > lazy='joined' within it's context.
> >
> > In the base class (Content) "current_translation" points to the
> > "content_current_translation" relationship. In a child class (Document)
> > "current_translation" points to "document_current_translation"
> > relationship, but a child class also contain the
> > "content_current_translation", as Document inherits from Content and
> > that the property has already been setup.
> > So both relationships are joinedloaded at the same time when I'm
> > session.query(Document).
>
>
> there's no way I'm going to parse that without spending a long time looking
> at an example, sorry I know we keep coming back to this code that you've been
> working on but it's not something i can keep in my head in the interim.
Yeah I understand perfectly, no problem :-)
I'll make a test case as soon as I have a little time
>
> >
> > For example when I'm querying for a Document SQLAlchemy generates two
> > queries for the translations (1) and (2). The JOIN on the
> > document_translation table should
Re: [sqlalchemy] change an existing relationship
On Fri, Sep 27, 2019 at 09:03:53AM -0400, Mike Bayer wrote:
>
>
> On Fri, Sep 27, 2019, at 6:26 AM, Julien Cigar wrote:
> > On Thu, Sep 26, 2019 at 11:39:52AM -0400, Mike Bayer wrote:
> > > that's not supported, I would suggest trying to solve your problem in a
> > > different way.
> > >
> >
> > Ok, that's what I thought.. thanks!
> >
> > I have a typical joined table inheritance, with Content being the "base"
> > class, and childs as Document, Folder, Event, etc.
> >
> > I have added a transparent translation package with the "one translation
> > table per entity" approach (also using joined table inheritance),
> > and hybrid properties.
> >
> > So I have ContentTranslation being the "base" class, and childs like
> > DocumentTranslation, FolderTranslation, EventTranslation, etc. which
> > contain translation fields specific to each class.
> >
> > You helped me some weeks ago on this and I ended with (1), in which two
> > relationships were added to every children of Content
> > ("current_translation" and "translations"), and hybrid properties
> > pointing to their dedicated translation class.
> >
> > It works well, the only minor issue is that I don't have any
> > relationship on the base Content class (as you told me in the past that
> > overriding relationships could be problematic).
> >
> > I made some modifications (2) in which the relationships are now
> > prefixed with the class name, and added two properties which point to
> > the class relationship, so that in the end:
> >
> > - Content has a "content_current_translation" and
> > "content_translations" relationships, and two property
> > "current_translation" and "translations" pointing to them
> >
> > - Document has a "document_current_translation" and
> > "document_translations" with also two property current_translation and
> > translations pointing to them, but the minor issue is that the
> > content_current_translation is also joined loaded, which I'd like to
> > avoid.. and I'm wondering what would be the best approach.
> >
> > I don't know if it's clear, but if it is not I could make a test case
>
> I'm not sure what the problem is, the code I can see has "lazy='joined'" in
> some spots, you'd make that into "lazy='select'" if that's the lazy loading
> you want.
>
> if this is some kind of automatic-generation issue where some of these
> generations need to apply a different kind of default loader then you would
> need to add parameterization to your code generation scheme, such as
> additional arguments to your setup_relationships() function.
The problem I have if that the "current_translation" should always be
lazy='joined' within it's context.
In the base class (Content) "current_translation" points to the
"content_current_translation" relationship. In a child class (Document)
"current_translation" points to "document_current_translation"
relationship, but a child class also contain the
"content_current_translation", as Document inherits from Content and
that the property has already been setup.
So both relationships are joinedloaded at the same time when I'm
session.query(Document).
For example when I'm querying for a Document SQLAlchemy generates two
queries for the translations (1) and (2). The JOIN on the
document_translation table should happend in (1).
When I'm setting the mapping for a child class (Document, Event, Folder,
etc) I'm looking for a way to either 1) remove the
"content_current_translation" relationship or 2) disable joined loading
for this relationship.
But when I'm dbsession.query(Content) the "content_current_translation"
must be joined loaded
(1) https://gist.github.com/silenius/482f6a0f46427580aae12ba32b836ee5
(2) https://gist.github.com/silenius/511f3069a871dd17d6e202c6b92edaf8
>
>
> >
> > (1)
> > https://github.com/silenius/amnesia_multilingual/blob/master/amnesia_multilingual/builders.py
> >
> > (2)
> > https://github.com/silenius/amnesia_multilingual/blob/dev/amnesia_multilingual/builders.py
> >
> > Thank you :)
> >
> > Julien
> >
> > >
> > >
> > > On Thu, Sep 26, 2019, at 11:25 AM, Julien Cigar wrote:
> > > > Hello,
> > > >
> > > > I'd like to change a lazy property of an existing relationship (from
> > > > 'joined' to 'noload'). I tried the following:
> > > >
> > > > mapper = orm.class_m
Re: [sqlalchemy] change an existing relationship
On Thu, Sep 26, 2019 at 11:39:52AM -0400, Mike Bayer wrote:
> that's not supported, I would suggest trying to solve your problem in a
> different way.
>
Ok, that's what I thought.. thanks!
I have a typical joined table inheritance, with Content being the "base"
class, and childs as Document, Folder, Event, etc.
I have added a transparent translation package with the "one translation
table per entity" approach (also using joined table inheritance),
and hybrid properties.
So I have ContentTranslation being the "base" class, and childs like
DocumentTranslation, FolderTranslation, EventTranslation, etc. which
contain translation fields specific to each class.
You helped me some weeks ago on this and I ended with (1), in which two
relationships were added to every children of Content
("current_translation" and "translations"), and hybrid properties
pointing to their dedicated translation class.
It works well, the only minor issue is that I don't have any
relationship on the base Content class (as you told me in the past that
overriding relationships could be problematic).
I made some modifications (2) in which the relationships are now
prefixed with the class name, and added two properties which point to
the class relationship, so that in the end:
- Content has a "content_current_translation" and
"content_translations" relationships, and two property
"current_translation" and "translations" pointing to them
- Document has a "document_current_translation" and
"document_translations" with also two property current_translation and
translations pointing to them, but the minor issue is that the
content_current_translation is also joined loaded, which I'd like to
avoid.. and I'm wondering what would be the best approach.
I don't know if it's clear, but if it is not I could make a test case
(1)
https://github.com/silenius/amnesia_multilingual/blob/master/amnesia_multilingual/builders.py
(2)
https://github.com/silenius/amnesia_multilingual/blob/dev/amnesia_multilingual/builders.py
Thank you :)
Julien
>
>
> On Thu, Sep 26, 2019, at 11:25 AM, Julien Cigar wrote:
> > Hello,
> >
> > I'd like to change a lazy property of an existing relationship (from
> > 'joined' to 'noload'). I tried the following:
> >
> > mapper = orm.class_mapper(MyClass)
> > prop = mapper.get_property('some_relationship')
> > prop.lazy = 'noload'
> >
> > but it doesn't seems to work, as the property is still loaded
> >
> > Any idea if this is possible?
> >
> > Thanks,
> > Julien
> >
> >
> > --
> > 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 view this discussion on the web visit
> > https://groups.google.com/d/msgid/sqlalchemy/20190926152504.GB1535%40p52s.
> >
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/dab6e523-675f-4a88-aa6d-c303360a50e6%40www.fastmail.com.
--
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 Ver
[sqlalchemy] change an existing relationship
Hello,
I'd like to change a lazy property of an existing relationship (from
'joined' to 'noload'). I tried the following:
mapper = orm.class_mapper(MyClass)
prop = mapper.get_property('some_relationship')
prop.lazy = 'noload'
but it doesn't seems to work, as the property is still loaded
Any idea if this is possible?
Thanks,
Julien
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/20190926152504.GB1535%40p52s.
Re: [sqlalchemy] primaryjoin and remote/foreign sides
Just to add that it works like a charm, even with recursive queries. I have a classical parent->child relationship (website structure) and I am able to retrieve the whole structure, including translations (with fallback on a default one) in just _one_ query, this is really amazing :) this is with https://gist.github.com/silenius/5e1c9ec7b138115d9f7271860adca2df and SQLAlchemy generates https://gist.github.com/silenius/2f9df00575daab6d009e43dff92a3902 On Fri, Jun 21, 2019 at 02:32:36AM -0700, Julien Cigar wrote: > > > On Thursday, June 20, 2019 at 4:24:14 PM UTC+2, Mike Bayer wrote: > > > > > > > > On Thu, Jun 20, 2019, at 8:39 AM, Julien Cigar wrote: > > > > > > > > On Wednesday, June 19, 2019 at 9:53:42 PM UTC+2, Mike Bayer wrote: > > > > > > > > On Wed, Jun 19, 2019, at 10:50 AM, Julien Cigar wrote: > > > > That's the (almost) final version if you're interrested: > > https://gist.github.com/silenius/568aca7545e1bc0400b53b6ec157807d > > > > > > great, I can't run it, so are you still getting any warnings about > > properties being overwritten ? > > > > > > > > yes, sorry about that, I'm not that familiar with the Declarative API (I'm > > still using the mapper())) > > > > I get a bunch of SAWarning but it works as expected: > > > > > > 2019-06-20 14:31:45,784 INFO [amnesia.translations][MainThread] > > SQLAlchemy after_configured handler _setup_translation called > > 2019-06-20 14:31:45,784 DEBUG [amnesia.translations][MainThread] Adding > > translation properties: to > > > > /usr/home/jcigar/code/venvs/amnesiabbpf/lib/python3.6/site-packages/sqlalchemy/orm/mapper.py:1826: > > > > SAWarning: Property ContentTranslation.content on mapped class > > FolderTranslation->content_translation being replaced with new property > > FolderTranslation.content; the old property will be discarded > > % (self._props[key], self, prop) > > 2019-06-20 14:31:45,807 DEBUG [amnesia.translations][MainThread] Adding > > translation properties: > > to > > /usr/home/jcigar/code/venvs/amnesiabbpf/lib/python3.6/site-packages/sqlalchemy/orm/mapper.py:1826: > > > > SAWarning: Property ContentTranslation.content on mapped class > > DocumentTranslation->document_translation being replaced with new property > > DocumentTranslation.content; the old property will be discarded > > % (self._props[key], self, prop) > > 2019-06-20 14:31:45,816 DEBUG [amnesia.translations][MainThread] Adding > > translation properties: to > > > > /usr/home/jcigar/code/venvs/amnesiabbpf/lib/python3.6/site-packages/sqlalchemy/orm/mapper.py:1826: > > > > SAWarning: Property ContentTranslation.content on mapped class > > EventTranslation->event_translation being replaced with new property > > EventTranslation.content; the old property will be discarded > > % (self._props[key], self, prop) > > 2019-06-20 14:31:45,825 DEBUG [amnesia.translations][MainThread] Adding > > translation properties: to > 'amnesia.modules.file.translations.model.FileTranslation'> > > /usr/home/jcigar/code/venvs/amnesiabbpf/lib/python3.6/site-packages/sqlalchemy/orm/mapper.py:1826: > > > > SAWarning: Property ContentTranslation.content on mapped class > > FileTranslation->content_translation being replaced with new property > > FileTranslation.content; the old property will be discarded > > % (self._props[key], self, prop) > > > > > > What happens if you don't overwrite the "content" relationship each time > > and just leave the base one in place? the pattern you are doing there is > > not one that has explicit support. IIRC the "content" relationship you > > are adding looks just like the one that's there, except it is against more > > specific subclasses. I can see how that is valuable but it shouldn't have > > any real-world consequence...however if it does, like it is making > > lazyloads more specific perhaps, then I might want to refine that warning > > to allow for more specific relationships against subclasses. > > > > > > > Although I haven't tested all scenarios yet, it seems to work well when I'm > not overwriting the "content" relationship and leave the base one in place > ...! I'll report if anything breaks :) thanks! > > > > > > > > If you'd like to add this configuration to the SQLAlchemy test cases I > > could spend some time to make a clean one .. ? > > > > > > > > maybe, it would be more like enhancing the mapper t
Re: [sqlalchemy] primaryjoin and remote/foreign sides
On Thursday, June 20, 2019 at 4:24:14 PM UTC+2, Mike Bayer wrote: > > > > On Thu, Jun 20, 2019, at 8:39 AM, Julien Cigar wrote: > > > > On Wednesday, June 19, 2019 at 9:53:42 PM UTC+2, Mike Bayer wrote: > > > > On Wed, Jun 19, 2019, at 10:50 AM, Julien Cigar wrote: > > That's the (almost) final version if you're interrested: > https://gist.github.com/silenius/568aca7545e1bc0400b53b6ec157807d > > > great, I can't run it, so are you still getting any warnings about > properties being overwritten ? > > > > yes, sorry about that, I'm not that familiar with the Declarative API (I'm > still using the mapper())) > > I get a bunch of SAWarning but it works as expected: > > > 2019-06-20 14:31:45,784 INFO [amnesia.translations][MainThread] > SQLAlchemy after_configured handler _setup_translation called > 2019-06-20 14:31:45,784 DEBUG [amnesia.translations][MainThread] Adding > translation properties: to > > /usr/home/jcigar/code/venvs/amnesiabbpf/lib/python3.6/site-packages/sqlalchemy/orm/mapper.py:1826: > > SAWarning: Property ContentTranslation.content on mapped class > FolderTranslation->content_translation being replaced with new property > FolderTranslation.content; the old property will be discarded > % (self._props[key], self, prop) > 2019-06-20 14:31:45,807 DEBUG [amnesia.translations][MainThread] Adding > translation properties: > to > /usr/home/jcigar/code/venvs/amnesiabbpf/lib/python3.6/site-packages/sqlalchemy/orm/mapper.py:1826: > > SAWarning: Property ContentTranslation.content on mapped class > DocumentTranslation->document_translation being replaced with new property > DocumentTranslation.content; the old property will be discarded > % (self._props[key], self, prop) > 2019-06-20 14:31:45,816 DEBUG [amnesia.translations][MainThread] Adding > translation properties: to > > /usr/home/jcigar/code/venvs/amnesiabbpf/lib/python3.6/site-packages/sqlalchemy/orm/mapper.py:1826: > > SAWarning: Property ContentTranslation.content on mapped class > EventTranslation->event_translation being replaced with new property > EventTranslation.content; the old property will be discarded > % (self._props[key], self, prop) > 2019-06-20 14:31:45,825 DEBUG [amnesia.translations][MainThread] Adding > translation properties: to 'amnesia.modules.file.translations.model.FileTranslation'> > /usr/home/jcigar/code/venvs/amnesiabbpf/lib/python3.6/site-packages/sqlalchemy/orm/mapper.py:1826: > > SAWarning: Property ContentTranslation.content on mapped class > FileTranslation->content_translation being replaced with new property > FileTranslation.content; the old property will be discarded > % (self._props[key], self, prop) > > > What happens if you don't overwrite the "content" relationship each time > and just leave the base one in place? the pattern you are doing there is > not one that has explicit support. IIRC the "content" relationship you > are adding looks just like the one that's there, except it is against more > specific subclasses. I can see how that is valuable but it shouldn't have > any real-world consequence...however if it does, like it is making > lazyloads more specific perhaps, then I might want to refine that warning > to allow for more specific relationships against subclasses. > > > Although I haven't tested all scenarios yet, it seems to work well when I'm not overwriting the "content" relationship and leave the base one in place ...! I'll report if anything breaks :) thanks! > > > If you'd like to add this configuration to the SQLAlchemy test cases I > could spend some time to make a clean one .. ? > > > > maybe, it would be more like enhancing the mapper to expect this pattern > but yes there are also a lot of things that it implies. > > > > Also, I could also write some doc to add this configuration to the > "Row-Limited Relationships with Window Functions" if you think it's > worthwhile ..? > > > > im not sure since your use case for the "row-limited" part is that you > want a LIMIT in the query which the existing example illustrates. > > > > > > On Wednesday, June 19, 2019 at 4:44:55 PM UTC+2, Julien Cigar wrote: > > > > On Wednesday, June 19, 2019 at 4:09:55 PM UTC+2, Mike Bayer wrote: > > > > On Wed, Jun 19, 2019, at 4:48 AM, Julien Cigar wrote: > > Thank you very much, it almost works ! > > I have one minor issue, as translation_cls is involved in joined load > inheritance the select([translation_cls], ...) results in: > > SELECT > content_tra
Re: [sqlalchemy] primaryjoin and remote/foreign sides
On Wednesday, June 19, 2019 at 9:53:42 PM UTC+2, Mike Bayer wrote:
>
>
>
> On Wed, Jun 19, 2019, at 10:50 AM, Julien Cigar wrote:
>
> That's the (almost) final version if you're interrested:
> https://gist.github.com/silenius/568aca7545e1bc0400b53b6ec157807d
>
>
> great, I can't run it, so are you still getting any warnings about
> properties being overwritten ?
>
>
>
yes, sorry about that, I'm not that familiar with the Declarative API (I'm
still using the mapper()))
I get a bunch of SAWarning but it works as expected:
2019-06-20 14:31:45,784 INFO [amnesia.translations][MainThread]
SQLAlchemy after_configured handler _setup_translation called
2019-06-20 14:31:45,784 DEBUG [amnesia.translations][MainThread] Adding
translation properties: to
/usr/home/jcigar/code/venvs/amnesiabbpf/lib/python3.6/site-packages/sqlalchemy/orm/mapper.py:1826:
SAWarning: Property ContentTranslation.content on mapped class
FolderTranslation->content_translation being replaced with new property
FolderTranslation.content; the old property will be discarded
% (self._props[key], self, prop)
2019-06-20 14:31:45,807 DEBUG [amnesia.translations][MainThread] Adding
translation properties:
to
/usr/home/jcigar/code/venvs/amnesiabbpf/lib/python3.6/site-packages/sqlalchemy/orm/mapper.py:1826:
SAWarning: Property ContentTranslation.content on mapped class
DocumentTranslation->document_translation being replaced with new property
DocumentTranslation.content; the old property will be discarded
% (self._props[key], self, prop)
2019-06-20 14:31:45,816 DEBUG [amnesia.translations][MainThread] Adding
translation properties: to
/usr/home/jcigar/code/venvs/amnesiabbpf/lib/python3.6/site-packages/sqlalchemy/orm/mapper.py:1826:
SAWarning: Property ContentTranslation.content on mapped class
EventTranslation->event_translation being replaced with new property
EventTranslation.content; the old property will be discarded
% (self._props[key], self, prop)
2019-06-20 14:31:45,825 DEBUG [amnesia.translations][MainThread] Adding
translation properties: to
/usr/home/jcigar/code/venvs/amnesiabbpf/lib/python3.6/site-packages/sqlalchemy/orm/mapper.py:1826:
SAWarning: Property ContentTranslation.content on mapped class
FileTranslation->content_translation being replaced with new property
FileTranslation.content; the old property will be discarded
% (self._props[key], self, prop)
If you'd like to add this configuration to the SQLAlchemy test cases I
could spend some time to make a clean one .. ?
Also, I could also write some doc to add this configuration to the
"Row-Limited Relationships with Window Functions" if you think it's
worthwhile ..?
>
> On Wednesday, June 19, 2019 at 4:44:55 PM UTC+2, Julien Cigar wrote:
>
>
>
> On Wednesday, June 19, 2019 at 4:09:55 PM UTC+2, Mike Bayer wrote:
>
>
>
> On Wed, Jun 19, 2019, at 4:48 AM, Julien Cigar wrote:
>
> Thank you very much, it almost works !
>
> I have one minor issue, as translation_cls is involved in joined load
> inheritance the select([translation_cls], ...) results in:
>
> SELECT
> content_translation.language_id AS language_id,
> content_translation.content_id AS content_id,
> content_translation.title AS title,
> content_translation.description AS description,
> content_translation.fts AS fts,
> document_translation.language_id AS language_id,
> document_translation.content_id AS content_id,
> document_translation.body AS body,
> (...)
>
> which lead to a 2019-06-18 17:47:04.498 CEST [51817] ERROR: column
> reference "content_id" is ambiguous at character 3155
> for the foreign(_alias2.content_id) == content_cls.content_id,
>
> I could list and alias individual columns, but I wondered if SQLAlchemy
> could do this automatically ?
>
>
> for the joined inheirtance, you need to compose the SELECT against both
> tables individually with the JOIN you want between them, I would probably
> use something like
>
> select([MyClass]).select_from(MyClass.__mapper__.selectable)
>
>
> That's what I tried initially but it doesn't work in this case ("outer"
> query had ambigous column reference too)
>
>
>
>
>
>
>
>
> Cheers,
> Julien
>
>
> On Wednesday, June 19, 2019 at 7:32:24 AM UTC+2, Mike Bayer wrote:
>
>
> This test doesn't run yet because it looks like you need to have
> initializers for things like Content.current_translation, the setup_class
> fails right now because that isn't handled.
>
> In any case, adapting the window recipe from
> https://docs.sqlalchemy.org/en/13/orm/join_condition
Re: [sqlalchemy] primaryjoin and remote/foreign sides
That's the (almost) final version if you're interrested:
https://gist.github.com/silenius/568aca7545e1bc0400b53b6ec157807d
On Wednesday, June 19, 2019 at 4:44:55 PM UTC+2, Julien Cigar wrote:
>
>
>
> On Wednesday, June 19, 2019 at 4:09:55 PM UTC+2, Mike Bayer wrote:
>>
>>
>>
>> On Wed, Jun 19, 2019, at 4:48 AM, Julien Cigar wrote:
>>
>> Thank you very much, it almost works !
>>
>> I have one minor issue, as translation_cls is involved in joined load
>> inheritance the select([translation_cls], ...) results in:
>>
>> SELECT
>> content_translation.language_id AS language_id,
>> content_translation.content_id AS content_id,
>> content_translation.title AS title,
>> content_translation.description AS description,
>> content_translation.fts AS fts,
>> document_translation.language_id AS language_id,
>> document_translation.content_id AS content_id,
>> document_translation.body AS body,
>> (...)
>>
>> which lead to a 2019-06-18 17:47:04.498 CEST [51817] ERROR: column
>> reference "content_id" is ambiguous at character 3155
>> for the foreign(_alias2.content_id) == content_cls.content_id,
>>
>> I could list and alias individual columns, but I wondered if SQLAlchemy
>> could do this automatically ?
>>
>>
>> for the joined inheirtance, you need to compose the SELECT against both
>> tables individually with the JOIN you want between them, I would probably
>> use something like
>>
>> select([MyClass]).select_from(MyClass.__mapper__.selectable)
>>
>
> That's what I tried initially but it doesn't work in this case ("outer"
> query had ambigous column reference too)
>
>
>>
>>
>>
>>
>>
>> Cheers,
>> Julien
>>
>>
>> On Wednesday, June 19, 2019 at 7:32:24 AM UTC+2, Mike Bayer wrote:
>>
>>
>> This test doesn't run yet because it looks like you need to have
>> initializers for things like Content.current_translation, the setup_class
>> fails right now because that isn't handled.
>>
>> In any case, adapting the window recipe from
>> https://docs.sqlalchemy.org/en/13/orm/join_conditions.html#row-limited-relationships-with-window-functions
>>
>> to the code here can be done directly:
>>
>> _alias = (
>> select(
>> [
>> translation_cls,
>> func.row_number()
>> .over(
>> order_by=[
>> desc(
>> translation_cls.language_id
>> == bindparam(
>> None,
>> callable_=lambda: current_locale(),
>> type_=String(),
>> )
>> ),
>> desc(
>> translation_cls.language_id
>> == bindparam(
>> None,
>> callable_=lambda: fallback_locale(),
>> type_=String(),
>> )
>> ),
>> ],
>> partition_by=translation_cls.content_id,
>> )
>> .label("index"),
>> ]
>> )
>> .where(
>> and_(
>> translation_cls.language_id.in_(
>> (
>> bindparam(
>> None,
>> callable_=lambda: current_locale(),
>> type_=String(),
>> ),
>> bindparam(
>> None,
>> callable_=lambda: fallback_locale(),
>> type_=String(),
>> ),
>> )
>> )
>> )
>> )
>> .alias()
>> )
>>
>> _alias2 = aliased(translation_cls, _alias)
>>
>> content_mapper.add_properties(
>> {
>> "current_translation": relationship(
>> _alias2,
>>
Re: [sqlalchemy] primaryjoin and remote/foreign sides
On Wednesday, June 19, 2019 at 4:09:55 PM UTC+2, Mike Bayer wrote:
>
>
>
> On Wed, Jun 19, 2019, at 4:48 AM, Julien Cigar wrote:
>
> Thank you very much, it almost works !
>
> I have one minor issue, as translation_cls is involved in joined load
> inheritance the select([translation_cls], ...) results in:
>
> SELECT
> content_translation.language_id AS language_id,
> content_translation.content_id AS content_id,
> content_translation.title AS title,
> content_translation.description AS description,
> content_translation.fts AS fts,
> document_translation.language_id AS language_id,
> document_translation.content_id AS content_id,
> document_translation.body AS body,
> (...)
>
> which lead to a 2019-06-18 17:47:04.498 CEST [51817] ERROR: column
> reference "content_id" is ambiguous at character 3155
> for the foreign(_alias2.content_id) == content_cls.content_id,
>
> I could list and alias individual columns, but I wondered if SQLAlchemy
> could do this automatically ?
>
>
> for the joined inheirtance, you need to compose the SELECT against both
> tables individually with the JOIN you want between them, I would probably
> use something like
>
> select([MyClass]).select_from(MyClass.__mapper__.selectable)
>
That's what I tried initially but it doesn't work in this case ("outer"
query had ambigous column reference too)
>
>
>
>
>
> Cheers,
> Julien
>
>
> On Wednesday, June 19, 2019 at 7:32:24 AM UTC+2, Mike Bayer wrote:
>
>
> This test doesn't run yet because it looks like you need to have
> initializers for things like Content.current_translation, the setup_class
> fails right now because that isn't handled.
>
> In any case, adapting the window recipe from
> https://docs.sqlalchemy.org/en/13/orm/join_conditions.html#row-limited-relationships-with-window-functions
>
> to the code here can be done directly:
>
> _alias = (
> select(
> [
> translation_cls,
> func.row_number()
> .over(
> order_by=[
> desc(
> translation_cls.language_id
> == bindparam(
> None,
> callable_=lambda: current_locale(),
> type_=String(),
> )
> ),
> desc(
> translation_cls.language_id
> == bindparam(
> None,
> callable_=lambda: fallback_locale(),
> type_=String(),
> )
> ),
> ],
> partition_by=translation_cls.content_id,
> )
> .label("index"),
> ]
> )
> .where(
> and_(
> translation_cls.language_id.in_(
> (
> bindparam(
> None,
> callable_=lambda: current_locale(),
> type_=String(),
> ),
> bindparam(
> None,
> callable_=lambda: fallback_locale(),
> type_=String(),
> ),
> )
> )
> )
> )
> .alias()
> )
>
> _alias2 = aliased(translation_cls, _alias)
>
> content_mapper.add_properties(
> {
> "current_translation": relationship(
> _alias2,
> primaryjoin=and_(
> foreign(_alias2.content_id) == content_cls.content_id,
> _alias.c.index == 1,
> ),
> lazy="joined",
> uselist=False,
> innerjoin=True,
> viewonly=True,
> bake_queries=False,
> ),
> }
> )
>
>
> On Tue, Jun 18, 2019, at 11:34 AM, Julien Cigar wrote:
>
> Hello Mike,
>
> As always thank you for your quick and useful reply. I might not need
> LATERAL but would be very interrested to see a solution with WINDOW
> functi
Re: [sqlalchemy] primaryjoin and remote/foreign sides
adding use_labels=True to the select fixed the problem :)
On Wednesday, June 19, 2019 at 10:47:57 AM UTC+2, Julien Cigar wrote:
>
> Thank you very much, it almost works !
>
> I have one minor issue, as translation_cls is involved in joined load
> inheritance the select([translation_cls], ...) results in:
>
> SELECT
> content_translation.language_id AS language_id,
> content_translation.content_id AS content_id,
> content_translation.title AS title,
> content_translation.description AS description,
> content_translation.fts AS fts,
> document_translation.language_id AS language_id,
> document_translation.content_id AS content_id,
> document_translation.body AS body,
> (...)
>
> which lead to a 2019-06-18 17:47:04.498 CEST [51817] ERROR: column
> reference "content_id" is ambiguous at character 3155
> for the foreign(_alias2.content_id) == content_cls.content_id,
>
> I could list and alias individual columns, but I wondered if SQLAlchemy
> could do this automatically ?
>
> Cheers,
> Julien
>
> On Wednesday, June 19, 2019 at 7:32:24 AM UTC+2, Mike Bayer wrote:
>>
>>
>> This test doesn't run yet because it looks like you need to have
>> initializers for things like Content.current_translation, the setup_class
>> fails right now because that isn't handled.
>>
>> In any case, adapting the window recipe from
>> https://docs.sqlalchemy.org/en/13/orm/join_conditions.html#row-limited-relationships-with-window-functions
>>
>> to the code here can be done directly:
>>
>> _alias = (
>> select(
>> [
>> translation_cls,
>> func.row_number()
>> .over(
>> order_by=[
>> desc(
>> translation_cls.language_id
>> == bindparam(
>> None,
>> callable_=lambda: current_locale(),
>> type_=String(),
>> )
>> ),
>> desc(
>> translation_cls.language_id
>> == bindparam(
>> None,
>> callable_=lambda: fallback_locale(),
>> type_=String(),
>> )
>> ),
>> ],
>> partition_by=translation_cls.content_id,
>> )
>> .label("index"),
>> ]
>> )
>> .where(
>> and_(
>> translation_cls.language_id.in_(
>> (
>> bindparam(
>> None,
>> callable_=lambda: current_locale(),
>> type_=String(),
>> ),
>> bindparam(
>> None,
>> callable_=lambda: fallback_locale(),
>> type_=String(),
>> ),
>> )
>> )
>> )
>> )
>> .alias()
>> )
>>
>> _alias2 = aliased(translation_cls, _alias)
>>
>> content_mapper.add_properties(
>> {
>> "current_translation": relationship(
>> _alias2,
>> primaryjoin=and_(
>> foreign(_alias2.content_id) == content_cls.content_id,
>> _alias.c.index == 1,
>> ),
>> lazy="joined",
>> uselist=False,
>> innerjoin=True,
>> viewonly=True,
>> bake_queries=False,
>> ),
>> }
>> )
>>
>>
>> On Tue, Jun 18, 2019, at 11:34 AM, Julien Cigar wrote:
>>
>> Hello Mike,
>>
>> As always thank you for your quick and useful reply. I might not need
>> LATERAL but would be very interrested to see a solution with WINDOW
>> functions ..
>>
>> I've added a small test case with some comment on what I'd
Re: [sqlalchemy] primaryjoin and remote/foreign sides
Thank you very much, it almost works !
I have one minor issue, as translation_cls is involved in joined load
inheritance the select([translation_cls], ...) results in:
SELECT
content_translation.language_id AS language_id,
content_translation.content_id AS content_id,
content_translation.title AS title,
content_translation.description AS description,
content_translation.fts AS fts,
document_translation.language_id AS language_id,
document_translation.content_id AS content_id,
document_translation.body AS body,
(...)
which lead to a 2019-06-18 17:47:04.498 CEST [51817] ERROR: column
reference "content_id" is ambiguous at character 3155
for the foreign(_alias2.content_id) == content_cls.content_id,
I could list and alias individual columns, but I wondered if SQLAlchemy
could do this automatically ?
Cheers,
Julien
On Wednesday, June 19, 2019 at 7:32:24 AM UTC+2, Mike Bayer wrote:
>
>
> This test doesn't run yet because it looks like you need to have
> initializers for things like Content.current_translation, the setup_class
> fails right now because that isn't handled.
>
> In any case, adapting the window recipe from
> https://docs.sqlalchemy.org/en/13/orm/join_conditions.html#row-limited-relationships-with-window-functions
>
> to the code here can be done directly:
>
> _alias = (
> select(
> [
> translation_cls,
> func.row_number()
> .over(
> order_by=[
> desc(
> translation_cls.language_id
> == bindparam(
> None,
> callable_=lambda: current_locale(),
> type_=String(),
> )
> ),
> desc(
> translation_cls.language_id
> == bindparam(
> None,
> callable_=lambda: fallback_locale(),
> type_=String(),
> )
> ),
> ],
> partition_by=translation_cls.content_id,
> )
> .label("index"),
> ]
> )
> .where(
> and_(
> translation_cls.language_id.in_(
> (
> bindparam(
> None,
> callable_=lambda: current_locale(),
> type_=String(),
> ),
> bindparam(
> None,
> callable_=lambda: fallback_locale(),
> type_=String(),
> ),
> )
> )
> )
> )
> .alias()
> )
>
> _alias2 = aliased(translation_cls, _alias)
>
> content_mapper.add_properties(
> {
> "current_translation": relationship(
> _alias2,
> primaryjoin=and_(
> foreign(_alias2.content_id) == content_cls.content_id,
> _alias.c.index == 1,
> ),
> lazy="joined",
> uselist=False,
> innerjoin=True,
> viewonly=True,
> bake_queries=False,
> ),
> }
> )
>
>
> On Tue, Jun 18, 2019, at 11:34 AM, Julien Cigar wrote:
>
> Hello Mike,
>
> As always thank you for your quick and useful reply. I might not need
> LATERAL but would be very interrested to see a solution with WINDOW
> functions ..
>
> I've added a small test case with some comment on what I'd like to
> achieve. Basically what I would like is to be able to select the "current"
> translation in one query, and that it is transparent (it's a Pyramid
> plugin), that's why I'm also using hybrid properties
>
>
> On Tuesday, June 18, 2019 at 3:38:36 PM UTC+2, Mike Bayer wrote:
>
>
>
> On Tue, Jun 18, 2019, at 6:21 AM, Julien Cigar wrote:
>
> Hello,
>
> I'm trying to add a 'read-only' relationship involving a subquery and I
> have some problems with remote and foreign sides, SQLAlchemy returns:
>
> sqlalchemy.exc.ArgumentError: Rel
Re: [sqlalchemy] primaryjoin and remote/foreign sides
Hello Mike, As always thank you for your quick and useful reply. I might not need LATERAL but would be very interrested to see a solution with WINDOW functions .. I've added a small test case with some comment on what I'd like to achieve. Basically what I would like is to be able to select the "current" translation in one query, and that it is transparent (it's a Pyramid plugin), that's why I'm also using hybrid properties On Tuesday, June 18, 2019 at 3:38:36 PM UTC+2, Mike Bayer wrote: > > > > On Tue, Jun 18, 2019, at 6:21 AM, Julien Cigar wrote: > > Hello, > > I'm trying to add a 'read-only' relationship involving a subquery and I > have some problems with remote and foreign sides, SQLAlchemy returns: > > sqlalchemy.exc.ArgumentError: Relationship Folder.current_translation > could not determine any unambiguous local/remote column pairs based on join > condition and remote_side arguments. Consider using the remote() > annotation to accurately mark those elements of the join condition that are > on the remote side of the relationship. > > I've copied my code here > https://gist.github.com/silenius/e2c7fb393864b3f1fee8131336dd8b61 > > > > Any idea what could be wrong ..? > > Basically I have a "translation" table and I'd like to JOIN that table for > the current translation or the "fallback" language, so something like: > select c.id, t.* from content c JOIN LATERAL (select ct.* from > content_translation ct WHERE ct.content_id=c.id order by > ct.language_id='fr' desc, ct.language_id='en' desc limit 1) as t ON > t.content_id = c.id WHERE c.id=4855; > > > > hiya - > > unfortunately JOIN LATERAL is not supported, at least in all cases, as the > target of a relationship(), because loading such as lazy loading does not > use JOIN at all, and it's not clear if the LATERAL construct would work > with other forms of relationship loading as well.That is, it *might* > work for some cases, though it's never been tested, and likely wont work > for most/all cases. Also in your example I don't see any usage of the > lateral() modifier on your subquery. > > It would be best to compose the ON clause of the join using more > traditional methods, e.g. that the "right" side of the join is a subquery > that does not use any correlation, and the ON clause relates the left and > right sides together. > > Within the example given, the "primaryjoin" argument refers to the ON > clause of a JOIN, so generally a subquery would not be located here. > Additionally, "as_scalar()", which is being renamed to "scalar_subquery()", > indicates that this query is a so-called "scalar" subquery that returns > exactly one row in one column and therefore acts as a column expression to > be used in the WHERE clause, but this would not work as an ON clause in a > JOIN by itself unless it were equated to something (but again, you'd need > LATERAL for correlation to work in the ON clause). > > Looking at the actual SQL you're looking for, everything about it seems to > be fully "traditional" in how the join is composed except for the detail > that you're trying to get the first row only that matches in > content_translation.We have a recipe for row-limited relationships > using window functions which will likely fit here directly, at > https://docs.sqlalchemy.org/en/13/orm/join_conditions.html#row-limited-relationships-with-window-functions > > . you'd want to add "uselist=False" to your relationship() if you are > looking for a many-to-one style relationship. The window function works by > including a "row number", eg. index of a row, partitioned against the > groupings within which you'd want to be limiting, in this case it can > perhaps be partition_by=[ct.content_id], and it then orders within those > partitions where you could apply your interesting "lang='fr' desc, > lang='en' desc" trick above.Then the limit is applied in the ON clause > by asking for "partition.c.index == 1". > > if you can share rudimentary mappings I can show you the composition, > although the example as given in the docs should translate fairly directly > here. > > > > > > > > Thank you! > > Julien > > > -- > 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" gro
[sqlalchemy] Re: primaryjoin and remote/foreign sides
On Tuesday, June 18, 2019 at 12:20:55 PM UTC+2, Julien Cigar wrote: > > Hello, > > I'm trying to add a 'read-only' relationship involving a subquery and I > have some problems with remote and foreign sides, SQLAlchemy returns: > > sqlalchemy.exc.ArgumentError: Relationship Folder.current_translation > could not determine any unambiguous local/remote column pairs based on join > condition and remote_side arguments. Consider using the remote() > annotation to accurately mark those elements of the join condition that are > on the remote side of the relationship. > > I've copied my code here > https://gist.github.com/silenius/e2c7fb393864b3f1fee8131336dd8b61 > > Any idea what could be wrong ..? > > Basically I have a "translation" table and I'd like to JOIN that table for > the current translation or the "fallback" language, so something like: > select c.id, t.* from content c JOIN LATERAL (select ct.* from > content_translation ct WHERE ct.content_id=c.id order by > ct.language_id='fr' desc, ct.language_id='en' desc limit 1) as t ON > t.content_id = c.id WHERE c.id=4855; > > Thank you! > > Julien > I've taken another approach, as it looks like that I need something like https://docs.sqlalchemy.org/en/13/orm/join_conditions.html#row-limited-relationships-with-window-functions It works almost with: https://gist.github.com/silenius/b866232305e22b05e3f1f8705f4401a2 which produce something like https://gist.github.com/silenius/161c9a1263e7251e0213843598786640 The only remaining problem is that translation_cls (line 14 of first paste) is involved in a joined load inheritance scenario and that I'm getting a: sqlalchemy.exc.ProgrammingError: (psycopg2.errors.AmbiguousColumn) column reference "content_id" is ambiguous which is because SQLAlchemy doesn't alias properly in the subselect .. any idea how to fix this ? 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. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/fb55e73a-10d1-4a4f-b410-f614e2d8a736%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] primaryjoin and remote/foreign sides
Hello, I'm trying to add a 'read-only' relationship involving a subquery and I have some problems with remote and foreign sides, SQLAlchemy returns: sqlalchemy.exc.ArgumentError: Relationship Folder.current_translation could not determine any unambiguous local/remote column pairs based on join condition and remote_side arguments. Consider using the remote() annotation to accurately mark those elements of the join condition that are on the remote side of the relationship. I've copied my code here https://gist.github.com/silenius/e2c7fb393864b3f1fee8131336dd8b61 Any idea what could be wrong ..? Basically I have a "translation" table and I'd like to JOIN that table for the current translation or the "fallback" language, so something like: select c.id, t.* from content c JOIN LATERAL (select ct.* from content_translation ct WHERE ct.content_id=c.id order by ct.language_id='fr' desc, ct.language_id='en' desc limit 1) as t ON t.content_id = c.id WHERE c.id=4855; Thank you! Julien -- 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. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/16dddc84-d927-44e0-95dc-da9f579da733%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] virtual relationship..?
On Thu, May 09, 2019 at 10:17:30PM -0400, Mike Bayer wrote: > is the general idea, one attribute that is merging two totally > different relationships? You'd just build a @property that creates a > view of the two relationships when accessed. Kind of like an > association proxy, but less flexible and read-only. OK I'll make a separate property, I had some vague idea about using some QueryEvents to manipulate the query, but it sounds a bit complicated and hazardous Thanks :) > > On Thu, May 9, 2019 at 6:38 AM Julien Cigar wrote: > > > > Dear SQLAlchemy users, > > > > I have a (Pyramid) application (CMS-like) for which I'm adding > > authorization. > > > > The core consists of "users", "roles", and "permissions", where an > > "user" can have many "roles", and a "role" can have many > > "permissions" (see #1). > > > > Nothing really new, but where it gets a bit complicated is that I have > > two types of "roles": "classical" and "virtual" (which are all stored > > in the database, in a "role" table, wether they are classical or > > virtual). > > > > How to know if a user "has a" role depends of the role type. > > > > For the "classical" ones and entry should exist in the intermediary > > table (many-to-many). However, "virtual" roles are assigned dynamically > > by the application at the beginning of each request (and available in > > some request.effective_principals property) and depends of some > > context (if the user is logged, etc), so there is no entry in the > > intermediary table. > > > > For beauty and simplicity I'd like to have an User.roles property (which > > in my current version fetches the intermediary table, so "classical" > > roles only) which contains both types of roles (classical and virtual > > ones). > > Actually I have some wrapper function above the .roles property which > > does that, but I don't like it too much. > > > > The virtual ones should be excluded from any "state" management of > > course (I have a trigger at the database level which forbids a link > > between a virtual role and an account). > > > > What would be a good way to do that in SQLAlchemy? > > > > (1) https://gist.github.com/silenius/f7e4f4da9370e5db182e41d7ae93d324 > > > > Thank you, > > Julien > > > > -- > > 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. > > To view this discussion on the web visit > > https://groups.google.com/d/msgid/sqlalchemy/20190509103817.GC39998%40home.lan. > > 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. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/CA%2BRjkXGUt1y1HPr0Vb9bRtiZi8Ge6u%2Byt6957mw8jbZOc4Cb3Q%40mail.gmail.com. > For more options, visit https://groups.google.com/d/optout. -- Julien Cigar Belgian Biodiversity Platform (http://www.biodiversity.b
[sqlalchemy] virtual relationship..?
Dear SQLAlchemy users, I have a (Pyramid) application (CMS-like) for which I'm adding authorization. The core consists of "users", "roles", and "permissions", where an "user" can have many "roles", and a "role" can have many "permissions" (see #1). Nothing really new, but where it gets a bit complicated is that I have two types of "roles": "classical" and "virtual" (which are all stored in the database, in a "role" table, wether they are classical or virtual). How to know if a user "has a" role depends of the role type. For the "classical" ones and entry should exist in the intermediary table (many-to-many). However, "virtual" roles are assigned dynamically by the application at the beginning of each request (and available in some request.effective_principals property) and depends of some context (if the user is logged, etc), so there is no entry in the intermediary table. For beauty and simplicity I'd like to have an User.roles property (which in my current version fetches the intermediary table, so "classical" roles only) which contains both types of roles (classical and virtual ones). Actually I have some wrapper function above the .roles property which does that, but I don't like it too much. The virtual ones should be excluded from any "state" management of course (I have a trigger at the database level which forbids a link between a virtual role and an account). What would be a good way to do that in SQLAlchemy? (1) https://gist.github.com/silenius/f7e4f4da9370e5db182e41d7ae93d324 Thank you, Julien -- 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. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/20190509103817.GC39998%40home.lan. For more options, visit https://groups.google.com/d/optout. signature.asc Description: PGP signature
Re: [sqlalchemy] mapping a class against multiple tables + relationship() .. ?
On Thu, Apr 26, 2018 at 11:52:48AM -0400, Mike Bayer wrote: > Attached is your script worked into separate tests. For test_five, > we can do it like this: > > > s.query(Document).filter(Document.translations.any(DocumentTranslation.body > == 'doc1_body_en')).all() > > For the tests which state "# XXX the current_translation isn't > joinedloaded()", it looks like they are, I added assertions that the > object is present without SQL being emitted; if I remove lazy="joined" > then it fails, so that joinedload is working. > > For the other general issue that a query with > Document/DocumentTranslation pulls in other tables, that's because the > relationship has no idea that Document only refers to > DocumentTranslation objects and nothing else. We can make these > relationships explicit. That's in the second attachment. For > test_three, the queries only involve the content/document tables and > there are no LEFT OUTER joins. > > For the content_type_id thing, I guess the trigger is so that you can > use raw SQL? SQLAlchemy will do those columns for you within the > ORM. But you can also set polymorphic_on to be a correlated > subquery. the third attachment shows that as well. > > In all three cases no changes were needed at all to the persistence side. > > The tests include a setup_class() which I don't know how to do in > unittest so I run these with py.test: > > py.test test_thing_3.py -k test_three -s > Hi Mike, It works almost like a charm. One minor issue I have is in the test_six I've added in the attached test_thing_3.py. When I'm querying through the base class I'm wondering why SQLAlchemy issues a query for a property which is already lazy='joined' loaded in the previously query, and I wonder if there is way to avoid that .. ? Thanks ! Julien > > > > On Thu, Apr 26, 2018 at 6:54 AM, Julien Cigar <julien.ci...@gmail.com> wrote: > > Hello Mike, > > > > I finally had the time to make a little full POC, see attached file. > > > > As said previously I'm used to the old mapper() and never used the > > declarative until now, so forgive me if I missed something in the > > script.. > > > > Some explanations on the context and on what I'd like to do: > > > > It's a $work-made CMS that is used in some projects here at $work. > > It is developed with the Pyramid framework which allow us to split > > in a very simple and clean way "subprojects" that use our core CMS > > package. > > > > At the core joinedload inheritance is used. "Content" is the base class > > from which other polymorphic entities (Event, Document, Folder, ...) > > inherits. > > > > The discriminator (polymorphic_on) is on a "content_type_id" column in > > the content table which is a foreign key to a content_type table. In my > > real app I use a function to retrieve the content_type.id, which is > > "faked" in my POC (_fake_ids). > > > > Every polymorphic entity has common properties/columns (like > > "title", "description") and specific properties/columns (like "body" for > > a Document, etc). Nothing special, common properties go in the Content > > class and specific properties in their own subclass. > > > > Now I'd like to add a "multilingual layer" to the app. Common properties > > will me moved to a dedicated content_translation table, and each > > polymorphic entity will have their own dedicated table > > (document_translation, event_translation, etc). See attached file > > (sa.png) for the db model. (Note that some entities may not have a > > dedicated _translation table, like Folder, which has no more than a > > "title" and "description") > > > > I'm not sure how to map this in SQLAlchemy as the "translation" parts > > (which are also mapped in a joinedload inheritance) are "part of" or > > "closely linked to" the translatable entity (ex: DocumentTranslation -> > > Document, EventTranslation -> Event, etc). > > > > In an ideal scenario I'd like to have a .translations property > > (relationship()) available in each entity which is lazy='subquery' > > loaded and for which only related _translation tables are JOIN. > > I'm not sure if I should add/overwrite the "translations" relationship > > in every entity (Event, Document, ...).. > > > > In advance, a big "thank you" for the time spent analyzing this :) > > > > Julien > > > > On Fri, Apr 13, 2018 at 10:34:46AM +0200, Julien Cigar wrote
Re: [sqlalchemy] Re: __init__ method
On Thu, Apr 26, 2018 at 11:30:13PM -0700, Jose Miguel Ibáñez wrote: > Please, find below an example of what I mean: > > class User(Base): > id = Column(Integer, primary_key=True) > name = Column(String) > fullname = Column(String) > > def __init__(self, name, fullname): > > self.name = name > self.fullname = fullname > > > is the __init__() method recommended ? The __init__() method is optional when you use Declarative, as the Declarative extension provides a default constructor which accepts keyword names that match the mapped columns (Of course you are free to define any explicit __init__ to override the default one) > > > El jueves, 26 de abril de 2018, 18:48:53 (UTC+2), Jose Miguel Ibáñez > escribió: > > > > Hi all ! > > > > when defining a class (derived from Base), when is recommended to define > > the __init__() method ? I know this consideration https://goo.gl/2umBJv, > > but I can't see the diference when creating objects for database > > population. It seems __init_() is never required. > > > > Thanks ! > > José M. > > > > > > -- > 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+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. signature.asc Description: PGP signature
Re: [sqlalchemy] QueuePool limit overflow and Pyramid sub-requests
On Thu, Apr 26, 2018 at 09:09:55AM -0400, Mike Bayer wrote: > I'm not familiar with this concept but it seems to me that your web > server can have at most N concurrent requests and that there would > need to be some system that also sets a limit on the number of > subrequests. If you are planning to have thousands of concurrent > subrequests at a time and you'd like them to all use independent > database connections then you'd need to configure your pool to allow > thousands of overflow connections, however, now you need to look at > how many processes you will be running and how many connections your > database itself allows. > > The point is that there are hard limits on how many connections you > can have to your database, which is a good thing. Any system that > generates lots of database connections similarly needs to work within > these limits, so you'd need to plan for this. > > > > > On Wed, Apr 25, 2018 at 9:15 PM, <jens.troe...@gmail.com> wrote: > > Hello, > > > > I would like to understand the interplay between a SQLA session and a > > Pyramid’s subrequest. When a request is handled, a new session is created > > for that request as per the Pyramid/SQLA cookiecutter, and it looks to me > > like subrequests create a new session too. > > > > When I set the pool_size of the engine to N and max_overflow to M then I can > > issue only a max of N+M subrequests, after which I get an exception: > > > > Traceback (most recent call last): > > File "/…/site-packages/sqlalchemy/pool.py", line 1122, in _do_get > > return self._pool.get(wait, self._timeout) > > File "/…/site-packages/sqlalchemy/util/queue.py", line 156, in get > > raise Empty > > sqlalchemy.util.queue.Empty > > > > During handling of the above exception, another exception occurred: > > > > […] > > File "/…/site-packages/sqlalchemy/engine/base.py", line 2147, in > > _wrap_pool_connect > > return fn() > > File "/…/site-packages/sqlalchemy/pool.py", line 387, in connect > > return _ConnectionFairy._checkout(self) > > File "/…/site-packages/sqlalchemy/pool.py", line 766, in _checkout > > fairy = _ConnectionRecord.checkout(pool) > > File "/…/site-packages/sqlalchemy/pool.py", line 516, in checkout > > rec = pool._do_get() > > File "/…/site-packages/sqlalchemy/pool.py", line 1131, in _do_get > > (self.size(), self.overflow(), self._timeout)) > > sqlalchemy.exc.TimeoutError: QueuePool limit of size 5 overflow 0 reached, > > connection timed out, timeout 30 > > > > for > > > > sqlalchemy.pool_size = 5 > > sqlalchemy.max_overflow = 0 > > > > When I up the pool size to fit all subrequests, then everything works fine > > and the SQLA log shows me a ROLLBACK for each subrequest and one COMMIT at > > the end which I think is the main request. > > > > Now I could set pool size to 0 to indicate no pool size limit, but I’m not > > sure if that would be the correct solution here. > > > > What’s the recommended approach here? I don't know what's your underlying database, but at $work with PostgreSQL we use Pgbouncer + SQLAlchemy NullPool. I think this is the recommended approach with a forking-like (gunicorn, ...) WSGI server For that, we slightly modify the Pyramid templates to be able to use sqlalchemy.poolclass = NullPool in a .ini file: https://gist.github.com/silenius/59b3760fb9cbad71aedebc67fd74ca6c#file-sa-py-L22-L25 > > > > Thanks! > > Jens > > > > -- > > 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 > descrip
Re: [sqlalchemy] mapping a class against multiple tables + relationship() .. ?
Hello Mike, I finally had the time to make a little full POC, see attached file. As said previously I'm used to the old mapper() and never used the declarative until now, so forgive me if I missed something in the script.. Some explanations on the context and on what I'd like to do: It's a $work-made CMS that is used in some projects here at $work. It is developed with the Pyramid framework which allow us to split in a very simple and clean way "subprojects" that use our core CMS package. At the core joinedload inheritance is used. "Content" is the base class from which other polymorphic entities (Event, Document, Folder, ...) inherits. The discriminator (polymorphic_on) is on a "content_type_id" column in the content table which is a foreign key to a content_type table. In my real app I use a function to retrieve the content_type.id, which is "faked" in my POC (_fake_ids). Every polymorphic entity has common properties/columns (like "title", "description") and specific properties/columns (like "body" for a Document, etc). Nothing special, common properties go in the Content class and specific properties in their own subclass. Now I'd like to add a "multilingual layer" to the app. Common properties will me moved to a dedicated content_translation table, and each polymorphic entity will have their own dedicated table (document_translation, event_translation, etc). See attached file (sa.png) for the db model. (Note that some entities may not have a dedicated _translation table, like Folder, which has no more than a "title" and "description") I'm not sure how to map this in SQLAlchemy as the "translation" parts (which are also mapped in a joinedload inheritance) are "part of" or "closely linked to" the translatable entity (ex: DocumentTranslation -> Document, EventTranslation -> Event, etc). In an ideal scenario I'd like to have a .translations property (relationship()) available in each entity which is lazy='subquery' loaded and for which only related _translation tables are JOIN. I'm not sure if I should add/overwrite the "translations" relationship in every entity (Event, Document, ...).. In advance, a big "thank you" for the time spent analyzing this :) Julien On Fri, Apr 13, 2018 at 10:34:46AM +0200, Julien Cigar wrote: > On Thu, Apr 12, 2018 at 08:25:06PM -0400, Mike Bayer wrote: > > try sending me a long a full POC and Ill try to play with it, any > > yep I'll do it ..! I miss some time for now.. but it's definitively on > my todo list. I don't think I have an overly complicated use case, it's > just that I pay a lot of attention on the generated SQL queries. > > > reason you arne't using declarative? the classical mappings are hard > > to work with. > > mostly for historical reasons, but also because I prefer the classical > mapping approach (mostly for separation of concerns). > > My application (internal CMS) is based on the Pyramid framework and it's > easier to extend the "core" package when the "mapping" part is separated > (maybe it's also possible with the declarative approach, I must admit > that I haven't looked at it in details). > > > > > On Wed, Apr 11, 2018 at 8:32 AM, Mike Bayer <mike...@zzzcomputing.com> > > wrote: > > > On Wed, Apr 11, 2018 at 6:15 AM, Julien Cigar <julien.ci...@gmail.com> > > > wrote: > > >> On Tue, Apr 10, 2018 at 11:53:09AM -0400, Mike Bayer wrote: > > >>> On Tue, Apr 10, 2018 at 9:28 AM, Julien Cigar <julien.ci...@gmail.com> > > >>> wrote: > > >>> > Hello, > > >>> > > > >>> > I wondered if it is possible to use a class mapped against multiple > > >>> > tables as a relationship() in another class? > > >>> > > >>> it is, there's examples at > > >>> http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#relationship-to-non-primary-mapper > > >>> > > >>> that is map your related class to whatever you want in a non primary > > >>> mapper. If you are just selecting data you have a lot of options. > > >>> > > >> > > >> Hi Mike, > > >> > > >> Thanks for the link :) I tried and it seems to work, except in an > > >> inheritance scenario (1). I guess that in a non primary mapper I can't > > >> put my polymorphic_on condition on a Join clause? > > >> > > >> (1) https://gist.github.com/silenius/a237baf8c4bcd79550dc884f2eeb1998 > > > > > > this is a lot of code to follow, but if the point of the mutli-table > &
Re: [sqlalchemy] mapping a class against multiple tables + relationship() .. ?
On Thu, Apr 12, 2018 at 08:25:06PM -0400, Mike Bayer wrote:
> try sending me a long a full POC and Ill try to play with it, any
yep I'll do it ..! I miss some time for now.. but it's definitively on
my todo list. I don't think I have an overly complicated use case, it's
just that I pay a lot of attention on the generated SQL queries.
> reason you arne't using declarative? the classical mappings are hard
> to work with.
mostly for historical reasons, but also because I prefer the classical
mapping approach (mostly for separation of concerns).
My application (internal CMS) is based on the Pyramid framework and it's
easier to extend the "core" package when the "mapping" part is separated
(maybe it's also possible with the declarative approach, I must admit
that I haven't looked at it in details).
>
> On Wed, Apr 11, 2018 at 8:32 AM, Mike Bayer <mike...@zzzcomputing.com> wrote:
> > On Wed, Apr 11, 2018 at 6:15 AM, Julien Cigar <julien.ci...@gmail.com>
> > wrote:
> >> On Tue, Apr 10, 2018 at 11:53:09AM -0400, Mike Bayer wrote:
> >>> On Tue, Apr 10, 2018 at 9:28 AM, Julien Cigar <julien.ci...@gmail.com>
> >>> wrote:
> >>> > Hello,
> >>> >
> >>> > I wondered if it is possible to use a class mapped against multiple
> >>> > tables as a relationship() in another class?
> >>>
> >>> it is, there's examples at
> >>> http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#relationship-to-non-primary-mapper
> >>>
> >>> that is map your related class to whatever you want in a non primary
> >>> mapper. If you are just selecting data you have a lot of options.
> >>>
> >>
> >> Hi Mike,
> >>
> >> Thanks for the link :) I tried and it seems to work, except in an
> >> inheritance scenario (1). I guess that in a non primary mapper I can't
> >> put my polymorphic_on condition on a Join clause?
> >>
> >> (1) https://gist.github.com/silenius/a237baf8c4bcd79550dc884f2eeb1998
> >
> > this is a lot of code to follow, but if the point of the mutli-table
> > thing you're doing is to game the joined inheritance into doing
> > something, then that's probably not the appropriate use for it. It
> > looks like you're trying to add all kinds of columns to
> > "ContentTranslation" from the other mappers like Content.
> >
> >
> >>
> >> Thanks!
> >> Julien
> >>
> >>>
> >>> >
> >>> > something like:
> >>> >
> >>> > ### Content
> >>> >
> >>> > content_translation_join = sql.join(
> >>> > t_content, t_content_translation
> >>> > )
> >>> >
> >>> > orm.mapper(
> >>> > ContentTranslation, content_translation_join,
> >>> > polymorphic_on=t_content.c.content_type_id
> >>> > )
> >>> >
> >>> > orm.mapper(
> >>> > Content, t_content,
> >>> > polymorphic_on=t_content.c.content_type_id,
> >>> > properties={
> >>> > 'translations': orm.relationship(
> >>> > ContentTranslation,
> >>> > # more stuff here
> >>> > )
> >>> > }
> >>> > )
> >>> >
> >>> > ### Document
> >>> >
> >>> > orm.mapper(
> >>> > DocumentTranslation, t_document_translation,
> >>> > inherits=ContentTranslation,
> >>> > polymorphic_identity=some_id
> >>> > )
> >>> >
> >>> > orm.mapper(
> >>> > Document, t_document,
> >>> > inherits=Content,
> >>> > polymorphic_identity=some_id
> >>> > )
> >>> >
> >>> > I tried (1) but it doesn't seems to work so I want to be sure that it's
> >>> > not possible :)
> >>> >
> >>> > Thanks!
> >>> > Julien
> >>> >
> >>> > (1)
> >>> > https://gist.github.com/silenius/561b13f4b987c36434cd81e2c08cab6e#file-foo-py
> >>> >
> >>> >
> >>> >
> >>> > --
> >>> > 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.
>
Re: [sqlalchemy] Use of in_() for relationships
On Thu, Apr 12, 2018 at 06:54:08AM -0700, Anvith Shivakumara wrote: > I get the 'NotImplementedError: in_() is not supported for relationship' > exception. maybe you want .has() or .any() ? for ex: session.query(SomeClass).filter( SomeClass.somecollection.any(someproperty='blabla') ) > > Is there any plan to implement this in the near future? ls there any > particular reason for not having this implemented? > > Thanks > Anvith > > -- > 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+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. signature.asc Description: PGP signature
Re: [sqlalchemy] mapping a class against multiple tables + relationship() .. ?
On Tue, Apr 10, 2018 at 11:53:09AM -0400, Mike Bayer wrote:
> On Tue, Apr 10, 2018 at 9:28 AM, Julien Cigar <julien.ci...@gmail.com> wrote:
> > Hello,
> >
> > I wondered if it is possible to use a class mapped against multiple
> > tables as a relationship() in another class?
>
> it is, there's examples at
> http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#relationship-to-non-primary-mapper
>
> that is map your related class to whatever you want in a non primary
> mapper. If you are just selecting data you have a lot of options.
>
Hi Mike,
Thanks for the link :) I tried and it seems to work, except in an
inheritance scenario (1). I guess that in a non primary mapper I can't
put my polymorphic_on condition on a Join clause?
(1) https://gist.github.com/silenius/a237baf8c4bcd79550dc884f2eeb1998
Thanks!
Julien
>
> >
> > something like:
> >
> > ### Content
> >
> > content_translation_join = sql.join(
> > t_content, t_content_translation
> > )
> >
> > orm.mapper(
> > ContentTranslation, content_translation_join,
> > polymorphic_on=t_content.c.content_type_id
> > )
> >
> > orm.mapper(
> > Content, t_content,
> > polymorphic_on=t_content.c.content_type_id,
> > properties={
> > 'translations': orm.relationship(
> > ContentTranslation,
> > # more stuff here
> > )
> > }
> > )
> >
> > ### Document
> >
> > orm.mapper(
> > DocumentTranslation, t_document_translation,
> > inherits=ContentTranslation,
> > polymorphic_identity=some_id
> > )
> >
> > orm.mapper(
> > Document, t_document,
> > inherits=Content,
> > polymorphic_identity=some_id
> > )
> >
> > I tried (1) but it doesn't seems to work so I want to be sure that it's
> > not possible :)
> >
> > Thanks!
> > Julien
> >
> > (1)
> > https://gist.github.com/silenius/561b13f4b987c36434cd81e2c08cab6e#file-foo-py
> >
> >
> >
> > --
> > 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+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.
signature.asc
Description: PGP signature
[sqlalchemy] mapping a class against multiple tables + relationship() .. ?
Hello,
I wondered if it is possible to use a class mapped against multiple
tables as a relationship() in another class?
something like:
### Content
content_translation_join = sql.join(
t_content, t_content_translation
)
orm.mapper(
ContentTranslation, content_translation_join,
polymorphic_on=t_content.c.content_type_id
)
orm.mapper(
Content, t_content,
polymorphic_on=t_content.c.content_type_id,
properties={
'translations': orm.relationship(
ContentTranslation,
# more stuff here
)
}
)
### Document
orm.mapper(
DocumentTranslation, t_document_translation,
inherits=ContentTranslation,
polymorphic_identity=some_id
)
orm.mapper(
Document, t_document,
inherits=Content,
polymorphic_identity=some_id
)
I tried (1) but it doesn't seems to work so I want to be sure that it's
not possible :)
Thanks!
Julien
(1)
https://gist.github.com/silenius/561b13f4b987c36434cd81e2c08cab6e#file-foo-py
--
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.
signature.asc
Description: PGP signature
Re: [sqlalchemy] orm.relationship() with dynamic part ?
On Thu, Apr 05, 2018 at 12:26:32PM -0400, Mike Bayer wrote: > you can do a bindparam with a callable, which I *think* should work here: > > ContentTranslation.language_id == bindparam(None, callable_=lambda: > get_current_request().locale_name) > > you want to set bake_queries=False on this relationship(). > > if it doesn't work, there's other ways to do this too. > > Thanks, it works well with bindparam(...) ! Just curious: what are the other ways of doing this ? > > On Thu, Apr 5, 2018 at 10:27 AM, Julien Cigar <julien.ci...@gmail.com> wrote: > > Hello, > > > > I wondered what would be the "best" way to achieve something like: > > https://gist.github.com/silenius/f4f98acc372e228093298002c0736894#file-foo-py-L5 > > ? > > > > With "get_current_request().locale_name" replaced by "en" I'm getting > > something like > > https://gist.githubusercontent.com/silenius/7f9f0e55cf8ea700222f67f88313e00f/raw/947151d597b8d95513580316ebb79f886be37b5d/sa.sql > > which is exactly what I want ..! > > > > Thanks :) > > > > Julien > > > > > > -- > > 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+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. signature.asc Description: PGP signature
[sqlalchemy] orm.relationship() with dynamic part ?
Hello, I wondered what would be the "best" way to achieve something like: https://gist.github.com/silenius/f4f98acc372e228093298002c0736894#file-foo-py-L5 ? With "get_current_request().locale_name" replaced by "en" I'm getting something like https://gist.githubusercontent.com/silenius/7f9f0e55cf8ea700222f67f88313e00f/raw/947151d597b8d95513580316ebb79f886be37b5d/sa.sql which is exactly what I want ..! Thanks :) Julien -- 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. signature.asc Description: PGP signature
Re: [sqlalchemy] joined load inheritance with extra joins.. possible?
On Tue, Apr 03, 2018 at 10:05:22AM -0400, Mike Bayer wrote: > On Tue, Apr 3, 2018 at 9:59 AM, Julien Cigar <julien.ci...@gmail.com> wrote: > > On Tue, Apr 03, 2018 at 12:20:53PM +0200, Julien Cigar wrote: > >> On Thu, Mar 22, 2018 at 10:58:56AM +0100, Julien Cigar wrote: > >> > 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? > >> > > >> > >> Hello Mike, > >> > >> I tried with relationships, but I'm not too satisfied with the generated > >> SQL, which makes me belived that I'm doing something wrong.. > >> > >> I've GIST the relevant parts of my mappers and the generated SQL at > >> https://gist.github.com/silenius/0fedf2bcb92b7e5c6f27732678c5baa0 > >> > >> I'm wondering if overriding the 'translations' relationship as I do is > >> the correct approach ..? In theory the "ContentTranslation" should be > >> somewhat "dynamic", but I wonder if SQLAlchemy supports something like > >> this .. ? By "dynamic" I mean it should be DocumentTranslation, or > >> EventTranslation, ... regarding of the polymorphic_identity of the "other > >> side" of the relationship > >> > >> Thanks ! > >> > >> Julien > > > > OK, I think I found the "correct way" to do it, thanks to the new > > polymorphic_load parameter of SQLAlchemy 1.2: > > https://gist.github.com/silenius/5afc06e670f5f7dda548dc5a0d0583df > > > > (I've also added a trigger on the content_translation table so > > "duplicate" the content_type_id column (as setting polymorphic_on on > > an SQL statement isn't supported)) > > > > With polymorphic_load='inline' and lazy='subquery' I have only two SQL > > statements to load the whole entities + associated translations (while > > being "strict" on the SQL relationships) > > > > any comments ? :) > > > > > wow, I already don't even remember what polymorphic_load does.I > need to work your example into a declarative demo so I can play with > it. but I am encouraged. (... and, although I use SQLAlchemy since many years, I'm always impressed how flexible and well written it is) I'm currently at $work but I can write a small declarative demo as soon as I got home > > > > > Thanks, > > Julien > > > >> > >> > 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 <julien.ci...@gmail.com> > >> > > 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
Re: [sqlalchemy] joined load inheritance with extra joins.. possible?
On Tue, Apr 03, 2018 at 12:20:53PM +0200, Julien Cigar wrote: > On Thu, Mar 22, 2018 at 10:58:56AM +0100, Julien Cigar wrote: > > 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? > > > > Hello Mike, > > I tried with relationships, but I'm not too satisfied with the generated > SQL, which makes me belived that I'm doing something wrong.. > > I've GIST the relevant parts of my mappers and the generated SQL at > https://gist.github.com/silenius/0fedf2bcb92b7e5c6f27732678c5baa0 > > I'm wondering if overriding the 'translations' relationship as I do is > the correct approach ..? In theory the "ContentTranslation" should be > somewhat "dynamic", but I wonder if SQLAlchemy supports something like > this .. ? By "dynamic" I mean it should be DocumentTranslation, or > EventTranslation, ... regarding of the polymorphic_identity of the "other > side" of the relationship > > Thanks ! > > Julien OK, I think I found the "correct way" to do it, thanks to the new polymorphic_load parameter of SQLAlchemy 1.2: https://gist.github.com/silenius/5afc06e670f5f7dda548dc5a0d0583df (I've also added a trigger on the content_translation table so "duplicate" the content_type_id column (as setting polymorphic_on on an SQL statement isn't supported)) With polymorphic_load='inline' and lazy='subquery' I have only two SQL statements to load the whole entities + associated translations (while being "strict" on the SQL relationships) any comments ? :) Thanks, Julien > > > 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 <julien.ci...@gmail.com> > > > 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 'so
Re: [sqlalchemy] joined load inheritance with extra joins.. possible?
On Thu, Mar 22, 2018 at 10:58:56AM +0100, Julien Cigar wrote: > 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? > Hello Mike, I tried with relationships, but I'm not too satisfied with the generated SQL, which makes me belived that I'm doing something wrong.. I've GIST the relevant parts of my mappers and the generated SQL at https://gist.github.com/silenius/0fedf2bcb92b7e5c6f27732678c5baa0 I'm wondering if overriding the 'translations' relationship as I do is the correct approach ..? In theory the "ContentTranslation" should be somewhat "dynamic", but I wonder if SQLAlchemy supports something like this .. ? By "dynamic" I mean it should be DocumentTranslation, or EventTranslation, ... regarding of the polymorphic_identity of the "other side" of the relationship Thanks ! Julien > 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 <julien.ci...@gmail.com> > > 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
Re: [sqlalchemy] joined load inheritance with extra joins.. possible?
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 <julien.ci...@gmail.com> 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
[sqlalchemy] joined load inheritance with extra joins.. possible?
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. signature.asc Description: PGP signature
Re: [sqlalchemy] loading of relationships
On Thu, Sep 24, 2015 at 11:16:37AM -0400, Mike Bayer wrote: > > > On 9/24/15 9:32 AM, Julien Cigar wrote: > > Hello, > > > > I'm using SQLAlchemy 1.0.8 with joinedload inheritance. On one of the > > Child I have a relationship property and I wondered if there is an easy > > way to innerjoin=True this relation only in a non-polymorphic context ? > > > > In my case I have the base class "Content", a child "Event", and "Event" > > has a relationship to "Country". > > > > So I when I do Session.query(Event) it should INNER JOIN Country, but > > with orm.with_polymorphic(Content, [Event]) the Country relationship > > should be LEFT OUTER JOIN, otherwhise the query returns no result ... > > > > I wondered if there was already something in SQLAlchemy do handle this > > case (other than joinedload(), etc ..) ? > > the relationship() is bound to Event only. If you set innerjoin=True on > that relationship(), that handles query(Event), but when you do > with_polymorphic(Content, [Event]), the joins *should* be nesting on the > right side in modern versions, e.g. SELECT content.*, event.* FROM > content LEFT OUTER JOIN (event INNER JOIN country). I haven't tested with git HEAD but in 1.0.8 the joins aren't nested (unless I did something wrong) > > At least ideally. If it's not, that's something to be reported and > fixed but there'd be no automatic workaround for now. > I'll make a bug report + test case as soon as I have little time .. As always, thanks for your quick reply! > > > > > > Thanks! > > Julien > > > > -- > 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. -- 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. -- 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. pgpjnFthq74bz.pgp Description: PGP signature
[sqlalchemy] loading of relationships
Hello, I'm using SQLAlchemy 1.0.8 with joinedload inheritance. On one of the Child I have a relationship property and I wondered if there is an easy way to innerjoin=True this relation only in a non-polymorphic context ? In my case I have the base class "Content", a child "Event", and "Event" has a relationship to "Country". So I when I do Session.query(Event) it should INNER JOIN Country, but with orm.with_polymorphic(Content, [Event]) the Country relationship should be LEFT OUTER JOIN, otherwhise the query returns no result ... I wondered if there was already something in SQLAlchemy do handle this case (other than joinedload(), etc ..) ? Thanks! Julien -- 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. -- 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. pgpt_Ep3CzTHT.pgp Description: PGP signature
Re: [sqlalchemy] relationship problem
On Mon, Mar 02, 2015 at 12:15:51PM -0500, Michael Bayer wrote: Julien Cigar jci...@ulb.ac.be wrote: On Sun, Mar 01, 2015 at 01:53:30PM +0100, Julien Cigar wrote: On Fri, Feb 27, 2015 at 11:38:05PM -0500, Michael Bayer wrote: On Feb 26, 2015, at 5:56 AM, Julien Cigar jci...@ulb.ac.be wrote: On Wed, Feb 25, 2015 at 06:10:55PM -0500, Michael Bayer wrote: Julien Cigar jci...@ulb.ac.be wrote: On Thu, Feb 19, 2015 at 11:31:10AM -0500, Michael Bayer wrote: Julien Cigar jci...@ulb.ac.be wrote: On Thu, Feb 19, 2015 at 02:45:43PM +0100, Julien Cigar wrote: Hello, I'm using SQLAlchemy 0.9.8 with PostgreSQL and the reflection feature of SQLAlchemy. I have the following tables (only relevant parts are show): https://gist.github.com/silenius/390bb9937490730741f2 and the problematic mapper is the one of my association object: https://gist.github.com/silenius/1559a7db65ed30a1b079 SQLAlchemy complains with the following error: sqlalchemy.exc.InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Original exception was: Could not locate any simple equality expressions involving locally mapped foreign key columns for primary join condition 'pool_invite_result.pool_invite_pool_id = pool_invite.pool_id AND pool_invite.pool_id = pool.id' on relationship PoolAccountResult.pool. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or are annotated in the join condition with the foreign() annotation. To allow comparison operators other than '==', the relationship can be marked as viewonly=True. The problem is that in the PoolAccountResult mapper I want a relationship to the Pool but the link is made through an intermediate table (pool_invite) .. Any idea how to handle this with SQLAlchemy ? Thanks :) Julien ... and I'm answering to myself: it seems to work with https://gist.github.com/silenius/e7e59c96a7277fb5879f does it sound right ? Sure. Also, you could use automap which does figure these out in simple cases: http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html always with this, any idea why SQLAlchemy inserts NULL and NULL for my two relationship (line 51-79) instead of the pool_obj and dup.owner ids (line 89-90), https://dpaste.de/1Trz ..? getting a 404 on that link. Hi Mike, Thanks for your help! I took the time to make a complete test case, available from here https://gist.github.com/silenius/96d6ed2544d14753853f That's a very strange use of secondary, and I'm not sure I understand the rationale for an odd schema like this. It should be pretty clear that when a table is set up as secondary, it is only used as a linkage between those immediate classes and there are no features to track the state of this table otherwise as though it were a mapped class. Using the same secondary table in two unrelated relationships is not the intended use. It's true that the schema is a little odd, in SQL it translates as https://gist.github.com/silenius/6a67edc9e78101faef96 (simplified). The scenario is that an account can be invited to a pool (table pool_invite at line 45-57) and can submit one (or more) results for that invitation (I have to record the submitted date too). This scenario is a good candidate for an association object, the only thing is that later someone can decide to remove all those submissions, but I have to remember that there was an invitation. That's why I have references to the table pool_invite in pool_invite_result (line 71-73) instead of pool and result :) Nevertheless, if you manipulate between Pool and User, that has no impact whatsoever on PoolInviteResult... Especially since we're dealing with a secondary table and not a first class mapped entity.You should add event listeners as needed on attributes such that when an in-Python change occurs between Pool and User, the desired change occurs for PoolInviteResult as well. It has impacts, if I remove a Pool or an User all invitations (pool_invite) must be removed too, as well as all submissions (pool_invite_result). Anyway, I'll take a look at ORM Events :) thanks! Another thing that is a little cloudy to me is why it doesn't work with an alternate primaryjoin condition, such as https://gist.github.com/silenius/300729e312dad6b9b847 the relationship() has a simple job. It is given table A and table B, and it needs to locate where columns from A are matched up to columns from B. The relationship in that gist does not have this pattern within the primary join condition; it is injecting the “pool_invite” table as an association table, which is what the “secondary” argument is used for. This argument tells relationship() that instead of searching for direct column
Re: [sqlalchemy] relationship problem
On Sun, Mar 01, 2015 at 01:53:30PM +0100, Julien Cigar wrote: On Fri, Feb 27, 2015 at 11:38:05PM -0500, Michael Bayer wrote: On Feb 26, 2015, at 5:56 AM, Julien Cigar jci...@ulb.ac.be wrote: On Wed, Feb 25, 2015 at 06:10:55PM -0500, Michael Bayer wrote: Julien Cigar jci...@ulb.ac.be wrote: On Thu, Feb 19, 2015 at 11:31:10AM -0500, Michael Bayer wrote: Julien Cigar jci...@ulb.ac.be wrote: On Thu, Feb 19, 2015 at 02:45:43PM +0100, Julien Cigar wrote: Hello, I'm using SQLAlchemy 0.9.8 with PostgreSQL and the reflection feature of SQLAlchemy. I have the following tables (only relevant parts are show): https://gist.github.com/silenius/390bb9937490730741f2 and the problematic mapper is the one of my association object: https://gist.github.com/silenius/1559a7db65ed30a1b079 SQLAlchemy complains with the following error: sqlalchemy.exc.InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Original exception was: Could not locate any simple equality expressions involving locally mapped foreign key columns for primary join condition 'pool_invite_result.pool_invite_pool_id = pool_invite.pool_id AND pool_invite.pool_id = pool.id' on relationship PoolAccountResult.pool. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or are annotated in the join condition with the foreign() annotation. To allow comparison operators other than '==', the relationship can be marked as viewonly=True. The problem is that in the PoolAccountResult mapper I want a relationship to the Pool but the link is made through an intermediate table (pool_invite) .. Any idea how to handle this with SQLAlchemy ? Thanks :) Julien ... and I'm answering to myself: it seems to work with https://gist.github.com/silenius/e7e59c96a7277fb5879f does it sound right ? Sure. Also, you could use automap which does figure these out in simple cases: http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html always with this, any idea why SQLAlchemy inserts NULL and NULL for my two relationship (line 51-79) instead of the pool_obj and dup.owner ids (line 89-90), https://dpaste.de/1Trz ..? getting a 404 on that link. Hi Mike, Thanks for your help! I took the time to make a complete test case, available from here https://gist.github.com/silenius/96d6ed2544d14753853f That's a very strange use of secondary, and I'm not sure I understand the rationale for an odd schema like this. It should be pretty clear that when a table is set up as secondary, it is only used as a linkage between those immediate classes and there are no features to track the state of this table otherwise as though it were a mapped class. Using the same secondary table in two unrelated relationships is not the intended use. It's true that the schema is a little odd, in SQL it translates as https://gist.github.com/silenius/6a67edc9e78101faef96 (simplified). The scenario is that an account can be invited to a pool (table pool_invite at line 45-57) and can submit one (or more) results for that invitation (I have to record the submitted date too). This scenario is a good candidate for an association object, the only thing is that later someone can decide to remove all those submissions, but I have to remember that there was an invitation. That's why I have references to the table pool_invite in pool_invite_result (line 71-73) instead of pool and result :) Nevertheless, if you manipulate between Pool and User, that has no impact whatsoever on PoolInviteResult... Especially since we're dealing with a secondary table and not a first class mapped entity.You should add event listeners as needed on attributes such that when an in-Python change occurs between Pool and User, the desired change occurs for PoolInviteResult as well. It has impacts, if I remove a Pool or an User all invitations (pool_invite) must be removed too, as well as all submissions (pool_invite_result). Anyway, I'll take a look at ORM Events :) thanks! Another thing that is a little cloudy to me is why it doesn't work with an alternate primaryjoin condition, such as https://gist.github.com/silenius/300729e312dad6b9b847 I'm using PostgreSQL, and I checked that all constraints are properly created on server-side but I haven't checked with sqllite:// Thanks, Julien -- 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. -- You received
Re: [sqlalchemy] relationship problem
On Fri, Feb 27, 2015 at 11:38:05PM -0500, Michael Bayer wrote: On Feb 26, 2015, at 5:56 AM, Julien Cigar jci...@ulb.ac.be wrote: On Wed, Feb 25, 2015 at 06:10:55PM -0500, Michael Bayer wrote: Julien Cigar jci...@ulb.ac.be wrote: On Thu, Feb 19, 2015 at 11:31:10AM -0500, Michael Bayer wrote: Julien Cigar jci...@ulb.ac.be wrote: On Thu, Feb 19, 2015 at 02:45:43PM +0100, Julien Cigar wrote: Hello, I'm using SQLAlchemy 0.9.8 with PostgreSQL and the reflection feature of SQLAlchemy. I have the following tables (only relevant parts are show): https://gist.github.com/silenius/390bb9937490730741f2 and the problematic mapper is the one of my association object: https://gist.github.com/silenius/1559a7db65ed30a1b079 SQLAlchemy complains with the following error: sqlalchemy.exc.InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Original exception was: Could not locate any simple equality expressions involving locally mapped foreign key columns for primary join condition 'pool_invite_result.pool_invite_pool_id = pool_invite.pool_id AND pool_invite.pool_id = pool.id' on relationship PoolAccountResult.pool. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or are annotated in the join condition with the foreign() annotation. To allow comparison operators other than '==', the relationship can be marked as viewonly=True. The problem is that in the PoolAccountResult mapper I want a relationship to the Pool but the link is made through an intermediate table (pool_invite) .. Any idea how to handle this with SQLAlchemy ? Thanks :) Julien ... and I'm answering to myself: it seems to work with https://gist.github.com/silenius/e7e59c96a7277fb5879f does it sound right ? Sure. Also, you could use automap which does figure these out in simple cases: http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html always with this, any idea why SQLAlchemy inserts NULL and NULL for my two relationship (line 51-79) instead of the pool_obj and dup.owner ids (line 89-90), https://dpaste.de/1Trz ..? getting a 404 on that link. Hi Mike, Thanks for your help! I took the time to make a complete test case, available from here https://gist.github.com/silenius/96d6ed2544d14753853f That's a very strange use of secondary, and I'm not sure I understand the rationale for an odd schema like this. It should be pretty clear that when a table is set up as secondary, it is only used as a linkage between those immediate classes and there are no features to track the state of this table otherwise as though it were a mapped class. Using the same secondary table in two unrelated relationships is not the intended use. It's true that the schema is a little odd, in SQL it translates as https://gist.github.com/silenius/6a67edc9e78101faef96 (simplified). The scenario is that an account can be invited to a pool (table pool_invite at line 45-57) and can submit one (or more) results for that invitation (I have to record the submitted date too). This scenario is a good candidate for an association object, the only thing is that later someone can decide to remove all those submissions, but I have to remember that there was an invitation. That's why I have references to the table pool_invite in pool_invite_result (line 71-73) instead of pool and result :) Nevertheless, if you manipulate between Pool and User, that has no impact whatsoever on PoolInviteResult... Especially since we're dealing with a secondary table and not a first class mapped entity.You should add event listeners as needed on attributes such that when an in-Python change occurs between Pool and User, the desired change occurs for PoolInviteResult as well. It has impacts, if I remove a Pool or an User all invitations (pool_invite) must be removed too, as well as all submissions (pool_invite_result). Anyway, I'll take a look at ORM Events :) thanks! I'm using PostgreSQL, and I checked that all constraints are properly created on server-side but I haven't checked with sqllite:// Thanks, Julien -- 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. -- 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
Re: [sqlalchemy] SQL Alchemy on Insert to DB
On Sun, Mar 01, 2015 at 10:57:31AM -0800, Javier Pajuelo wrote: Thanks. I followed the same principle, but had to change the set_attr to first check if the attribute was an instance of datetime, because my db accepted string of a datetime and not datetime. I have a question, what does *first()* mean in this context? http://docs.sqlalchemy.org/en/rel_0_9/orm/query.html?highlight=first#sqlalchemy.orm.query.Query.first for _candidate_data in candidates: _existing = session.query(CompassCandidate).filter(CompassCandidate.id == _ candidate_data.id).first() Thanks. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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. -- 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. pgpqDZW6Ne1GU.pgp Description: PGP signature
Re: [sqlalchemy] SQL Alchemy on Insert to DB
On Sun, Mar 01, 2015 at 08:06:31AM -0800, Javier Pajuelo wrote:
Thanks for the help, but in the code below it shows that setattr expects 3
arguments but got two instead.
What is this code really doing?
I know that my candidate fields are :
name, id, link, timeStamp.
Help. Thanks
On Tuesday, February 17, 2015 at 3:12:11 PM UTC-5, Jonathan Vanasco wrote:
The simplest way using your code would be:
for _candidate_data in candidates:
_existing = session.query(CompassCandidate).filter(CompassCandidate.id == _
candidate_data.id).first()
if not _existing:
# add the new item
session.add(_candidate_data)
else:
# translate the attributes from the new item to the existing item
# it's in the session, so you don't need to add
for _attr in ('name', 'link', timeStamp'):
setattr(_existing, getattr(_candidate_data, _attr)
try with:
setattr(_existing, _attr, getattr(_candidate_data, _attr))
session.flush()
session.commit()
--
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.
--
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.
--
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.
pgp8p9wbRNERC.pgp
Description: PGP signature
Re: [sqlalchemy] relationship problem
On Wed, Feb 25, 2015 at 06:10:55PM -0500, Michael Bayer wrote: Julien Cigar jci...@ulb.ac.be wrote: On Thu, Feb 19, 2015 at 11:31:10AM -0500, Michael Bayer wrote: Julien Cigar jci...@ulb.ac.be wrote: On Thu, Feb 19, 2015 at 02:45:43PM +0100, Julien Cigar wrote: Hello, I'm using SQLAlchemy 0.9.8 with PostgreSQL and the reflection feature of SQLAlchemy. I have the following tables (only relevant parts are show): https://gist.github.com/silenius/390bb9937490730741f2 and the problematic mapper is the one of my association object: https://gist.github.com/silenius/1559a7db65ed30a1b079 SQLAlchemy complains with the following error: sqlalchemy.exc.InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Original exception was: Could not locate any simple equality expressions involving locally mapped foreign key columns for primary join condition 'pool_invite_result.pool_invite_pool_id = pool_invite.pool_id AND pool_invite.pool_id = pool.id' on relationship PoolAccountResult.pool. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or are annotated in the join condition with the foreign() annotation. To allow comparison operators other than '==', the relationship can be marked as viewonly=True. The problem is that in the PoolAccountResult mapper I want a relationship to the Pool but the link is made through an intermediate table (pool_invite) .. Any idea how to handle this with SQLAlchemy ? Thanks :) Julien ... and I'm answering to myself: it seems to work with https://gist.github.com/silenius/e7e59c96a7277fb5879f does it sound right ? Sure. Also, you could use automap which does figure these out in simple cases: http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html always with this, any idea why SQLAlchemy inserts NULL and NULL for my two relationship (line 51-79) instead of the pool_obj and dup.owner ids (line 89-90), https://dpaste.de/1Trz ..? getting a 404 on that link. Hi Mike, Thanks for your help! I took the time to make a complete test case, available from here https://gist.github.com/silenius/96d6ed2544d14753853f I'm using PostgreSQL, and I checked that all constraints are properly created on server-side but I haven't checked with sqllite:// Thanks, Julien -- 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. -- 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. -- 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. -- 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. -- 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. -- 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
Re: [sqlalchemy] relationship problem
On Thu, Feb 19, 2015 at 11:31:10AM -0500, Michael Bayer wrote: Julien Cigar jci...@ulb.ac.be wrote: On Thu, Feb 19, 2015 at 02:45:43PM +0100, Julien Cigar wrote: Hello, I'm using SQLAlchemy 0.9.8 with PostgreSQL and the reflection feature of SQLAlchemy. I have the following tables (only relevant parts are show): https://gist.github.com/silenius/390bb9937490730741f2 and the problematic mapper is the one of my association object: https://gist.github.com/silenius/1559a7db65ed30a1b079 SQLAlchemy complains with the following error: sqlalchemy.exc.InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Original exception was: Could not locate any simple equality expressions involving locally mapped foreign key columns for primary join condition 'pool_invite_result.pool_invite_pool_id = pool_invite.pool_id AND pool_invite.pool_id = pool.id' on relationship PoolAccountResult.pool. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or are annotated in the join condition with the foreign() annotation. To allow comparison operators other than '==', the relationship can be marked as viewonly=True. The problem is that in the PoolAccountResult mapper I want a relationship to the Pool but the link is made through an intermediate table (pool_invite) .. Any idea how to handle this with SQLAlchemy ? Thanks :) Julien ... and I'm answering to myself: it seems to work with https://gist.github.com/silenius/e7e59c96a7277fb5879f does it sound right ? Sure. Also, you could use automap which does figure these out in simple cases: http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html always with this, any idea why SQLAlchemy inserts NULL and NULL for my two relationship (line 51-79) instead of the pool_obj and dup.owner ids (line 89-90), https://dpaste.de/1Trz ..? Thanks, Julien -- 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. -- 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. -- 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. -- 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. -- 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. -- 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. pgp9ParhCgn47.pgp Description: PGP signature
[sqlalchemy] relationship problem
Hello, I'm using SQLAlchemy 0.9.8 with PostgreSQL and the reflection feature of SQLAlchemy. I have the following tables (only relevant parts are show): https://gist.github.com/silenius/390bb9937490730741f2 and the problematic mapper is the one of my association object: https://gist.github.com/silenius/1559a7db65ed30a1b079 SQLAlchemy complains with the following error: sqlalchemy.exc.InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Original exception was: Could not locate any simple equality expressions involving locally mapped foreign key columns for primary join condition 'pool_invite_result.pool_invite_pool_id = pool_invite.pool_id AND pool_invite.pool_id = pool.id' on relationship PoolAccountResult.pool. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or are annotated in the join condition with the foreign() annotation. To allow comparison operators other than '==', the relationship can be marked as viewonly=True. The problem is that in the PoolAccountResult mapper I want a relationship to the Pool but the link is made through an intermediate table (pool_invite) .. Any idea how to handle this with SQLAlchemy ? Thanks :) Julien -- 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. -- 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. pgp5HUAVlOWTs.pgp Description: PGP signature
Re: [sqlalchemy] relationship problem
On Thu, Feb 19, 2015 at 02:45:43PM +0100, Julien Cigar wrote: Hello, I'm using SQLAlchemy 0.9.8 with PostgreSQL and the reflection feature of SQLAlchemy. I have the following tables (only relevant parts are show): https://gist.github.com/silenius/390bb9937490730741f2 and the problematic mapper is the one of my association object: https://gist.github.com/silenius/1559a7db65ed30a1b079 SQLAlchemy complains with the following error: sqlalchemy.exc.InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Original exception was: Could not locate any simple equality expressions involving locally mapped foreign key columns for primary join condition 'pool_invite_result.pool_invite_pool_id = pool_invite.pool_id AND pool_invite.pool_id = pool.id' on relationship PoolAccountResult.pool. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or are annotated in the join condition with the foreign() annotation. To allow comparison operators other than '==', the relationship can be marked as viewonly=True. The problem is that in the PoolAccountResult mapper I want a relationship to the Pool but the link is made through an intermediate table (pool_invite) .. Any idea how to handle this with SQLAlchemy ? Thanks :) Julien ... and I'm answering to myself: it seems to work with https://gist.github.com/silenius/e7e59c96a7277fb5879f does it sound right ? -- 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. -- 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. -- 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. -- 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. pgpDJ4I7_2j3x.pgp Description: PGP signature
Re: [sqlalchemy] relationship problem
On Thu, Feb 19, 2015 at 11:31:10AM -0500, Michael Bayer wrote: Julien Cigar jci...@ulb.ac.be wrote: On Thu, Feb 19, 2015 at 02:45:43PM +0100, Julien Cigar wrote: Hello, I'm using SQLAlchemy 0.9.8 with PostgreSQL and the reflection feature of SQLAlchemy. I have the following tables (only relevant parts are show): https://gist.github.com/silenius/390bb9937490730741f2 and the problematic mapper is the one of my association object: https://gist.github.com/silenius/1559a7db65ed30a1b079 SQLAlchemy complains with the following error: sqlalchemy.exc.InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Original exception was: Could not locate any simple equality expressions involving locally mapped foreign key columns for primary join condition 'pool_invite_result.pool_invite_pool_id = pool_invite.pool_id AND pool_invite.pool_id = pool.id' on relationship PoolAccountResult.pool. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or are annotated in the join condition with the foreign() annotation. To allow comparison operators other than '==', the relationship can be marked as viewonly=True. The problem is that in the PoolAccountResult mapper I want a relationship to the Pool but the link is made through an intermediate table (pool_invite) .. Any idea how to handle this with SQLAlchemy ? Thanks :) Julien ... and I'm answering to myself: it seems to work with https://gist.github.com/silenius/e7e59c96a7277fb5879f does it sound right ? Sure. Also, you could use automap which does figure these out in simple cases: http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html Thanks, looks like I missed this extension... I'll definitively take a look at it! -- 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. -- 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. -- 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. -- 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. -- 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. -- 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. pgpLyl504xqIF.pgp Description: PGP signature
Re: [sqlalchemy] SQLAlchemy 0.9.0 Released
Thank you Mike! I'm pretty sure that SQLAlchemy will stay my favourite Python library in 2014 :) On Mon, Dec 30, 2013 at 07:14:40PM -0500, Michael Bayer wrote: Hey list - SQLAlchemy release 0.9.0 is now available. After about a year's worth of development, 0.9.0 is the first official release of the 0.9 series of SQLAlchemy. As always, 0.9 includes many major architectural improvements and new features. To get an overview of SQLAlchemy 0.9's new features, see the What's New in 0.9 document at http://docs.sqlalchemy.org/en/rel_0_9/changelog/migration_09.html. This document should also serve as a guide for those behavioral changes which may require adjustments to existing applications. Highlights of SQLAlchemy 0.9.0 include in-place Python 3 support, major improvements to the eager loading system including a more expressive API and critical performance improvements to the rendering of more complex joins, support for Postgresql JSON types, SQL expression improvements such as textual selectable objects, INSERT from SELECT support, improved APIs for SELECT..FOR UPDATE, schema and DDL improvements, and many other refinements. Architecturally, the move to Python 3 in place has allowed lots of consolidation to take place, and there has also been a major reorganization of module layout both within Core and ORM. Existing production deployments which have not yet been tested in SQLAlchemy 0.9.0 should ensure that they specify a version less than 0.9.0 in their requirements files, as version 0.9.0 now becomes the default version of SQLAlchemy downloaded from Pypi when no version specifics are given. Full changelog for the SQLAlchemy 0.9 series is at http://docs.sqlalchemy.org/en/rel_0_9/changelog/changelog_09.html; this document will also link out in many cases to the migration document mentioned above. Download SQLAlchemy 0.9.0 at: http://www.sqlalchemy.org/download.html and on Pypi at https://pypi.python.org/pypi/SQLAlchemy/0.9.0. Happy new year! - mike -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- 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/groups/opt_out.
Re: [sqlalchemy] question about race conditions
On Thu, Dec 05, 2013 at 10:25:46AM -0800, Jonathan Vanasco wrote: i'm looking at moving some raw sql in twisted to SqlAlchemy and have a question. I have a multi-threaded twisted daemon that tends to generate a lot of race conditions on a few tables that are frequently hit. I get integrity errors from something like this : domain = SELECT * FROM domains WHERE if not domain : domain = INSERT INTO domain VALUES the fix was : domain = SELECT * FROM domains WHERE if not domain : try: savepoint = db.savepoint() INSERT INTO domain VALUES except psycopg2.IntegrityError : savepoint,release() domain = SELECT * FROM domains WHERE A maybe better way would be http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE is there a way to catch an integrity error like this with SqlAlchemy ? i'm trying to get away from directly using psycopg2, it's getting too annoying to maintain raw sql. -- 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/groups/opt_out. -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- 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/groups/opt_out.
Re: [sqlalchemy] contains_eager and order_by
On Mon, Oct 07, 2013 at 11:28:21AM -0400, Michael Bayer wrote:
On Oct 7, 2013, at 6:06 AM, Julien Cigar jci...@ulb.ac.be wrote:
Hello,
I have the following query:
entity = orm.with_polymorphic(
Content, [Event, News]
)
q = db.Session.query(entity)
The Event class has a country property:
orm.mapper(Event, table['event'], inherits = Content,
polymorphic_identity = _get_type_id('event'),
properties = {
'country' : orm.relationship(
Country, lazy = 'joined'
)
})
I would like to order my query above with .order_by(Country.name)
So I tried:
q = q.outerjoin(Event.country).options(
orm.contains_eager(Event.country)
).order_by(
Country.name
)
but SQLAlchemy complains with:
ArgumentError: Can't find property 'country' on any entity
specified in this Query. Note the full path from root
(Mapper|Content|content) to target entity must be specified.
you need to state all work with Event in terms of the with_polymorphic
construct you've created:
outerjoin(entity.Event.country)
Thank you, it works as expected with:
q = q.outerjoin(entity.Event.country).options(
orm.contains_eager(entity.Event.country)
).order_by(
Country.name
)
this is documented about midway through
http://docs.sqlalchemy.org/en/rel_0_8/orm/inheritance.html#with-polymorphic ,
though maybe a little buried.
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--
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/groups/opt_out.
[sqlalchemy] virtual-like entities, concrete table inheritance
Hello, I'm currently implementing a RBAC-like model for a webapp with the usual suspects: users, roles, permissions, etc where a Role has one or more Permissions, and an User can be in 1 or more Role. I would like to some virtual-like Role that are automatically attribued in some situations. For example AnonymousUser is the user is not logged, AuthenticatedUser is the user is authenticated, Owner if the user owns an object, etc. Those roles should always exist and should not be part of the unit-of-work process, in fact I don't want to store them in the database. I wondered if there is an elegant way to do that kind of stuff transparently with SQLAlchemy? Another question: I have different kind of Permission (some of them are linked to a module, some to a content_type, some are core permissions, etc) and I planned to use one separate table per permission type with concrete table inheritance. I only used joined load inheritance (which work very well) and from what I understood from the documentation concrete table inheritance are somewhat discouraged? Thank you :-) Julien -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] EAV Optimizations
On 05/14/2013 16:58, Michael Bayer wrote: When you are storing data with key/values, where the set of keys is part of the data. Storing configurational data is the main use case.HSTORE not an option because it is postgresql-specific. Yes it may be acceptable to store configurational data (although I would probably use HSTORE under PostgreSQL). But a large EAV based project sounds terribly bad to me. On May 14, 2013, at 10:49 AM, Julien Cigar jci...@ulb.ac.be wrote: On 05/14/2013 16:09, Michael Bayer wrote: EAVs have a definite place and with judicious use they are extremely useful. just curious: could you give an example where EAV is useful ? On May 14, 2013, at 4:37 AM, Julien Cigar jci...@ulb.ac.be wrote: On 05/14/2013 02:22, Lycovian wrote: A few months ago I watched a video conference where Mike demo'd some optimizations for SQLAlchemy when using EAV schemas. Does anyone know if these optimizations will make it into the product that we have access to? I'm about to start a large EAV based project with SA and I was curious. please don't ... EAV is evil and has tons of disadvantages. I don't know about your database, but in PostgreSQL you have HSTORE and JSON which could often be used to replace EAV Mike -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] EAV Optimizations
On 05/14/2013 02:22, Lycovian wrote: A few months ago I watched a video conference where Mike demo'd some optimizations for SQLAlchemy when using EAV schemas. Does anyone know if these optimizations will make it into the product that we have access to? I'm about to start a large EAV based project with SA and I was curious. please don't ... EAV is evil and has tons of disadvantages. I don't know about your database, but in PostgreSQL you have HSTORE and JSON which could often be used to replace EAV Mike -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] EAV Optimizations
On 05/14/2013 16:09, Michael Bayer wrote: EAVs have a definite place and with judicious use they are extremely useful. just curious: could you give an example where EAV is useful ? On May 14, 2013, at 4:37 AM, Julien Cigar jci...@ulb.ac.be wrote: On 05/14/2013 02:22, Lycovian wrote: A few months ago I watched a video conference where Mike demo'd some optimizations for SQLAlchemy when using EAV schemas. Does anyone know if these optimizations will make it into the product that we have access to? I'm about to start a large EAV based project with SA and I was curious. please don't ... EAV is evil and has tons of disadvantages. I don't know about your database, but in PostgreSQL you have HSTORE and JSON which could often be used to replace EAV Mike -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Re: How to change polymorphic_identity dynamically
On 05/06/2013 13:46, sajuptpm wrote: Hi, I also tried like this, But not working instance_of_B.__mapper_args__[polymorphic_identity] = type_c DBSession.add(instance_of_B) transaction.commit(); and instance_of_B.__mapper__.polymorphic_identity = type_c DBSession.add(instance_of_B) transaction.commit(); You should not modify polymorphic_identity directly: http://docs.sqlalchemy.org/en/rel_0_8/orm/mapper_config.html?highlight=polymorphic_identity#sqlalchemy.orm.mapper.Mapper.polymorphic_identity This is a /read only/ attribute determined during mapper construction. Behavior is undefined if directly modified. Thanks, -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] TypeDecorator to SQL expression ..?
Hello, With the following custom type: class JSONEncodedDict(types.TypeDecorator): Represents an immutable structure as a JSON-encoded string. impl = types.TEXT def process_bind_param(self, value, dialect): return json.dumps(value) if value is not None else None def process_result_value(self, value, dialect): return json.loads(value) if value is not None else None is there a method to override to automatically transform instances of this class when they're used in a SQL expression (for example an myquery.order_by(col), where col is an instance of JSONEncodedDict)? Thank you! Julien -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Automatic created and modified timestamp columns (best practice?!)
On 03/27/2013 14:26, Moritz Schlarb wrote: Hi there everyone, I am kind of looking for a best practice on how to implement automatically setting and updating columns for created and modified timestamps in SQLAlchemy, preferrably database-agnostic. First of all, is DateTime the appropriate column type or should it be timestamp instead? Both render to datetime on the Python side, so the arguments of generic SQL discussions on that topic aren't so relevant here. Now for the automatic updating, I have two variants: 1) created = Column(DateTime, nullable=False, server_default=func.now()) modified = Column(DateTime, nullable=False, server_default=func.now(), server_onupdate=func.now()) Which only work if the database supports an ON UPDATE statement, which e.g. sqlite doesn't seem to. 2) created = Column(DateTime, nullable=False, server_default=func.now()) modified = Column(DateTime, nullable=False, server_default=func.now(), onupdate=func.now()) Which would account for that, or are there databases that don't even support a DEFAULT value? But the second solution isn't really aesthetic - since the modified timestamp will now always be updated by SQLAlchemy. Isn't there a way to make SQLAlchemy decide whether to omit data for modified or not based on the actual database dialect used? Hope my questions came out clear and maybe someone can help me! Cheers If you use the ORM part of SQLAlchemy then I would use a 'before_update' event for that. It has the advantage is that the event can be propagated (thanks to propagate=True), which can be really usefull if you use inheritance. For example: def update_updated_listener(mapper, connection, target): target.updated = datetime.now() event.listen(YourClass, 'before_update', update_updated_listener, propagate=True) I usually create a 'last_update' column on the mapped class, something like: 'last_update' : orm.column_property( sql.func.coalesce(table['content'].c.updated, table['content'].c.added) ) -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] advices
On 03/18/2013 18:47, Michael Bayer wrote:
On Mar 15, 2013, at 3:19 PM, Julien Cigar jci...@ulb.ac.be wrote:
On 03/14/2013 19:56, Michael Bayer wrote:
On Mar 12, 2013, at 5:13 AM, Julien Cigar jci...@ulb.ac.be wrote:
Hello,
I have written a CMS which is, among other, based on the joined load
inheritance feature of SQLAlchemy.
It is quite simple: the user is able to add objects in containers and can select the
default polymorphic loading for a container. In gross it can dynamically select which tables will
be joined. For that I'm using the new orm.with_polymorphic() stuff of version 0.8.
It works well, and now I would also be able to let the user to set a default ORDER BY,
... for a container.
Basically I would like to be able to select a default ORDER BY from the
orm.with_polymorphic() join above.
For the moment I'm using a bytea (I'm using PostgreSQL) column, named default_order
which is mapped in a PickleType on the SQLAlchemy side. This column is just a serialized list
which looks like:
[{'column': 'starts', 'polymorphic_identity': 5, 'weight': 1, 'order': 'desc'},
{'column': 'weight', 'polymorphic_identity': None, 'weight': 2, 'order': 'asc'},
{'column': 'last_update', 'polymorphic_identity': None, 'weight': 3, 'order':
'asc'}]
So it tells which column from which primary mapper should be used and in which
order.
I'm using the following code http://pastie.org/6459613 to transform this in an
ORDER BY clause.
Not all columns should be selectable, so I used something like this in my
mapped classes:
Content.__order__ = [Content.weight, Content.last_update]
Event.__order__ = [Event.starts, Event.ends]
File.__order__ = [File.file_size]
I need some advices on how would you do to maintain the consistency of
everything. By consistency I mean that I would like to avoid cases where the user select
a column that doesn't exist or is not allowed, a polymorphic_identity that doesn't exist,
maintain the synchronization between the polymorphic loading of a container and the
serialized list, etc
Would you create a custom type based on PickleType or a simple @validates() or
.. ?
well I'd stay away from PickleType like the plague and at least use a JSON
column instead.
just curious: why would you stay away from the PickleType?
In the future I would like to use the new JSON column type in PostgreSQL 9.1+,
but for now we're still on 9.0.x and I thought a bytea + PickleType would be
better/faster than a TEXT + json.dumps() .. ?
you don't need PG's JSON type, just a text one like this one:
http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#marshal-json-strings
why JSON and not pickle, basically would you prefer your dictionary of strings
to look like:
(dp0\nS'username'\np1\nS'some user'\np2\nsS'full name'\np3\nS'user b.
user'\np4\nsS'address'\np5\nS'123 anywhere street'\np6\ns.
or
'{username: some user, full name: user b. user, address: 123 anywhere
street}'
?
plus would you prefer your data to have code injection vulnerabilities or not.
Indeed ... I'll change that
Thank you!
I can't answer the bigger question here since it's dependent on the flow of your application. For
example, i don't see how you're about to select some rows, but then there's an object
already loaded in memory where you can pull out these various database-persisted attributes that
describe other SQL queries. Then @validates, that's used for persisting data, so I guess this
means users are persisting objects that describe SQL queries ? Sure, if someone tries to say
set_order_by('somecol'), and you want to check Content.__order__, @validates seems the
most expedient approach.But if this is a GUI, typically you are not allowing invalid selections
from the GUI in the first place, so something would be scanning those __order__ collections ahead
of time its kind of a highly specific application design question.
Sorry to not have been more accurate on this. It's a webapp based on CherryPy, the
workflow is pretty simple: the user can select, through a select, which
mappers should be joined for a container, and it's saved in a:
'polymorphic_children' : orm.relationship(
ContentType,
secondary = table['folder_polymorphic_loading']
)
When the user selects mappers in the select box above, there is an ajax call
that retrieve which columns are available as possible default ORDER BY clause for the
container (this is the .__order__ stuff). This is saved in the default_order property
(bytea + PickleType)
Once everything is saved and that the user browse the container, those entities are
joined thanks to orm.with_polymorphic(). The webapp checks if the default_order property
is set (which is the one for which I use atm a bytea + PickleType). So I have to maintain
a kind of bidirectional check between the polymorphic_children and
default_order properties, to avoid that I end's up with a query that use a column from a
table which is not joined (because, for example
Re: [sqlalchemy] advices
On 03/14/2013 19:56, Michael Bayer wrote:
On Mar 12, 2013, at 5:13 AM, Julien Cigar jci...@ulb.ac.be wrote:
Hello,
I have written a CMS which is, among other, based on the joined load
inheritance feature of SQLAlchemy.
It is quite simple: the user is able to add objects in containers and can select the
default polymorphic loading for a container. In gross it can dynamically select which tables will
be joined. For that I'm using the new orm.with_polymorphic() stuff of version 0.8.
It works well, and now I would also be able to let the user to set a default ORDER BY,
... for a container.
Basically I would like to be able to select a default ORDER BY from the
orm.with_polymorphic() join above.
For the moment I'm using a bytea (I'm using PostgreSQL) column, named default_order
which is mapped in a PickleType on the SQLAlchemy side. This column is just a serialized list
which looks like:
[{'column': 'starts', 'polymorphic_identity': 5, 'weight': 1, 'order': 'desc'},
{'column': 'weight', 'polymorphic_identity': None, 'weight': 2, 'order': 'asc'},
{'column': 'last_update', 'polymorphic_identity': None, 'weight': 3, 'order':
'asc'}]
So it tells which column from which primary mapper should be used and in which
order.
I'm using the following code http://pastie.org/6459613 to transform this in an
ORDER BY clause.
Not all columns should be selectable, so I used something like this in my
mapped classes:
Content.__order__ = [Content.weight, Content.last_update]
Event.__order__ = [Event.starts, Event.ends]
File.__order__ = [File.file_size]
I need some advices on how would you do to maintain the consistency of
everything. By consistency I mean that I would like to avoid cases where the user select
a column that doesn't exist or is not allowed, a polymorphic_identity that doesn't exist,
maintain the synchronization between the polymorphic loading of a container and the
serialized list, etc
Would you create a custom type based on PickleType or a simple @validates() or
.. ?
well I'd stay away from PickleType like the plague and at least use a JSON
column instead.
just curious: why would you stay away from the PickleType?
In the future I would like to use the new JSON column type in PostgreSQL
9.1+, but for now we're still on 9.0.x and I thought a bytea +
PickleType would be better/faster than a TEXT + json.dumps() .. ?
I can't answer the bigger question here since it's dependent on the flow of your application. For
example, i don't see how you're about to select some rows, but then there's an object
already loaded in memory where you can pull out these various database-persisted attributes that
describe other SQL queries. Then @validates, that's used for persisting data, so I guess this
means users are persisting objects that describe SQL queries ? Sure, if someone tries to say
set_order_by('somecol'), and you want to check Content.__order__, @validates seems the
most expedient approach.But if this is a GUI, typically you are not allowing invalid selections
from the GUI in the first place, so something would be scanning those __order__ collections ahead
of time its kind of a highly specific application design question.
Sorry to not have been more accurate on this. It's a webapp based on
CherryPy, the workflow is pretty simple: the user can select, through a
select, which mappers should be joined for a container, and it's saved
in a:
'polymorphic_children' : orm.relationship(
ContentType,
secondary = table['folder_polymorphic_loading']
)
When the user selects mappers in the select box above, there is an
ajax call that retrieve which columns are available as possible default
ORDER BY clause for the container (this is the .__order__ stuff). This
is saved in the default_order property (bytea + PickleType)
Once everything is saved and that the user browse the container, those
entities are joined thanks to orm.with_polymorphic(). The webapp checks
if the default_order property is set (which is the one for which I use
atm a bytea + PickleType). So I have to maintain a kind of
bidirectional check between the polymorphic_children and default_order
properties, to avoid that I end's up with a query that use a column from
a table which is not joined (because, for example, the user decides that
this table should not be joined automatically anymore) in the ORDER BY
clause.
Anyway, I ended up with two @validates : http://pastie.org/6553052 and
it seems to work well :) But I'm always open to better solutions!
Thanks,
Julien
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] advices
Hello,
I have written a CMS which is, among other, based on the joined load
inheritance feature of SQLAlchemy.
It is quite simple: the user is able to add objects in containers
and can select the default polymorphic loading for a container. In
gross it can dynamically select which tables will be joined. For that
I'm using the new orm.with_polymorphic() stuff of version 0.8.
It works well, and now I would also be able to let the user to set a
default ORDER BY, ... for a container.
Basically I would like to be able to select a default ORDER BY from the
orm.with_polymorphic() join above.
For the moment I'm using a bytea (I'm using PostgreSQL) column, named
default_order which is mapped in a PickleType on the SQLAlchemy
side. This column is just a serialized list which looks like:
[{'column': 'starts', 'polymorphic_identity': 5, 'weight': 1, 'order':
'desc'},
{'column': 'weight', 'polymorphic_identity': None, 'weight': 2,
'order': 'asc'},
{'column': 'last_update', 'polymorphic_identity': None, 'weight': 3,
'order': 'asc'}]
So it tells which column from which primary mapper should be used and in
which order.
I'm using the following code http://pastie.org/6459613 to transform this
in an ORDER BY clause.
Not all columns should be selectable, so I used something like this in
my mapped classes:
Content.__order__ = [Content.weight, Content.last_update]
Event.__order__ = [Event.starts, Event.ends]
File.__order__ = [File.file_size]
I need some advices on how would you do to maintain the consistency of
everything. By consistency I mean that I would like to avoid cases where
the user select a column that doesn't exist or is not allowed, a
polymorphic_identity that doesn't exist, maintain the synchronization
between the polymorphic loading of a container and the serialized list, etc
Would you create a custom type based on PickleType or a simple
@validates() or .. ?
Thank you!
Julien
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] window functions marked deferred
On 03/08/2013 18:09, Michael Bayer wrote: On Mar 8, 2013, at 5:31 AM, Julien Cigar jci...@ulb.ac.be wrote: Hello, Shouldn't SQLAlchemy emit (at least) a warning when a window function is mapped with an orm.column_property(), marked deferred=True, and accessed without query.options(orm.undefer()) context (.. or maybe it's the role of the dev to call it in a non-deferred context?) ? For example: 'row_number' : orm.column_property( sql.func.row_number().\ over(partition_by=table['content'].c.container_id, order_by=table['content'].c.weight.desc()), deferred = True ) will always return 1 in a deferred context .. trying to catch that scenario seems a bit arbitrary to me, there's all kinds of SQL that won't work as a deferred column property, and how do we know that particular SQL is not what's intended ? You're right .. it was a silly idea (: -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] trying 0.8.0b2
Hello, I'm trying to upgrade a rather big webapp from 0.7.10 to 0.8.0b2 and I'm facing a strange RuntimeError: maximum recursion depth exceeded error: http://pastebin.ca/2314149 Any idea what could be wrong? (I'll start digging but I would like to avoid a loss of time if it's just a simple mistake/oblivion on my part ...) Thank you, Julien -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] trying 0.8.0b2
I forgot to add my mappers definition: http://pastie.org/6176391 On 02/15/2013 15:04, Julien Cigar wrote: Hello, I'm trying to upgrade a rather big webapp from 0.7.10 to 0.8.0b2 and I'm facing a strange RuntimeError: maximum recursion depth exceeded error: http://pastebin.ca/2314149 Any idea what could be wrong? (I'll start digging but I would like to avoid a loss of time if it's just a simple mistake/oblivion on my part ...) Thank you, Julien -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] trying 0.8.0b2
I'll give a try with tip .. :) btw the old svn url is still used while fetching sqlalchemy==dev: (amnesia)jcigar@dev pip install -U sqlalchemy==dev ~/projects/amnesia Downloading/unpacking sqlalchemy==dev Error urlopen error [Errno 8] hostname nor servname provided, or not known while getting http://svn.sqlalchemy.org/sqlalchemy/trunk#egg=SQLAlchemy-dev (from http://pypi.python.org/simple/SQLAlchemy/) Thanks On 02/15/2013 15:31, Michael Bayer wrote: something to do with your mappings and likely lots of eager loads. I'd try tip just to confirm, and seems like a regression, so if you can try to reproduce with a test case that would be helpful. On Feb 15, 2013, at 9:04 AM, Julien Cigar jci...@ulb.ac.be wrote: Hello, I'm trying to upgrade a rather big webapp from 0.7.10 to 0.8.0b2 and I'm facing a strange RuntimeError: maximum recursion depth exceeded error: http://pastebin.ca/2314149 Any idea what could be wrong? (I'll start digging but I would like to avoid a loss of time if it's just a simple mistake/oblivion on my part ...) Thank you, Julien -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] trying 0.8.0b2
Same with tip, except that I get a RuntimeError: maximum recursion depth exceeded in cmp instead: 'http://pastebin.ca/2314168 I'll dig a little more tonight ... On 02/15/2013 15:49, Julien Cigar wrote: I'll give a try with tip .. :) btw the old svn url is still used while fetching sqlalchemy==dev: (amnesia)jcigar@dev pip install -U sqlalchemy==dev ~/projects/amnesia Downloading/unpacking sqlalchemy==dev Error urlopen error [Errno 8] hostname nor servname provided, or not known while getting http://svn.sqlalchemy.org/sqlalchemy/trunk#egg=SQLAlchemy-dev (from http://pypi.python.org/simple/SQLAlchemy/) Thanks On 02/15/2013 15:31, Michael Bayer wrote: something to do with your mappings and likely lots of eager loads. I'd try tip just to confirm, and seems like a regression, so if you can try to reproduce with a test case that would be helpful. On Feb 15, 2013, at 9:04 AM, Julien Cigar jci...@ulb.ac.be wrote: Hello, I'm trying to upgrade a rather big webapp from 0.7.10 to 0.8.0b2 and I'm facing a strange RuntimeError: maximum recursion depth exceeded error: http://pastebin.ca/2314149 Any idea what could be wrong? (I'll start digging but I would like to avoid a loss of time if it's just a simple mistake/oblivion on my part ...) Thank you, Julien -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] contains_eager + aliased
Hello, Any idea why the following doesn't work ? : Topic2= orm.aliased(Topic) q= Occurrence.query.\ join(Occurrence.datasheet).\ options(orm.contains_eager(Occurrence.datasheet)).\ join(Topic2).\ options(orm.contains_eager(DataSheet.topic,alias=Topic2)).\ outerjoin(Occurrence.species).\ options(orm.contains_eager(Occurrence.species)) I get a: ArgumentError: Can't find property 'topic' on any entity specified in this Query. Note the full path from root (Mapper|Occurrence|occurrences) to target entity must be specified. and I don't understand why .. Thanks, Julien -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. attachment: jcigar.vcf
Re: [sqlalchemy] contains_eager + aliased
In fact the problem is more that, with the following: http://pastie.org/5489005 I don't know how to tell SQLAlchemy that it should join the DataSheet relation only once for the query and not once per join(). One option could be to add a .options(orm.noload(MyObject.datasheet)) for every mapped object but it seems a bit lame. I thought doing somehting like: Foo = orm.aliased(DataSheet) q = Occurrence.query.\ join(Foo).\ join(DataSheet.topic).\ outerjoin(Occurrence.species).\ outerjoin(Occurrence.site).\ options( orm.contains_eager(Occurrence.datasheet, alias=Foo), orm.contains_eager(Species.datasheet, alias=Foo), orm.contains_eager(Site.datasheet, alias=Foo), orm.contains_eager(Occurrence.species, Occurrence.site) ) but it doesn't seems to work ... Any idea ? :) Thank you, Julien On 12/06/2012 13:47, Julien Cigar wrote: Hello, Any idea why the following doesn't work ? : Topic2= orm.aliased(Topic) q= Occurrence.query.\ join(Occurrence.datasheet).\ options(orm.contains_eager(Occurrence.datasheet)).\ join(Topic2).\ options(orm.contains_eager(DataSheet.topic,alias=Topic2)).\ outerjoin(Occurrence.species).\ options(orm.contains_eager(Occurrence.species)) I get a: ArgumentError: Can't find property 'topic' on any entity specified in this Query. Note the full path from root (Mapper|Occurrence|occurrences) to target entity must be specified. and I don't understand why .. Thanks, Julien -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. attachment: jcigar.vcf
Re: [sqlalchemy] contains_eager + aliased
On 12/06/2012 15:45, Michael Bayer wrote: On Dec 6, 2012, at 7:47 AM, Julien Cigar wrote: Hello, Any idea why the following doesn't work ? : Topic2= orm.aliased(Topic) q= Occurrence.query.\ join(Occurrence.datasheet).\ options(orm.contains_eager(Occurrence.datasheet)).\ join(Topic2).\ options(orm.contains_eager(DataSheet.topic,alias=Topic2)).\ outerjoin(Occurrence.species).\ options(orm.contains_eager(Occurrence.species)) I get a: ArgumentError: Can't find property 'topic' on any entity specified in this Query. Note the full path from root (Mapper|Occurrence|occurrences) to target entity must be specified. and I don't understand why .. so paths work like this: query(Occurence).join(Occurrence.datasheet).join(Datasheet.topic).join(Occurrence.species) here are the major paths we've cerated: (Occurence, Occurrence.datasheet, ) (Occurence, Occurrence.datasheet, Datasheet, Datasheet.topic) (Occurence, Occurrence.species) There's actually paths for every attribute on all three of those classes worked up too, but for the purpose of relationship loading, what you send as an eager loading option has to match one of those paths. so: contains_eager(Occurrence.datasheet, Datasheet.topic, alias=Topic2) Thank you, it looks like I misunderstood how those paths worked Thanks, Julien -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. jcigar.vcf -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. attachment: jcigar.vcf
Re: [sqlalchemy] contains_eager + aliased
On 12/06/2012 16:02, Julien Cigar wrote: On 12/06/2012 15:45, Michael Bayer wrote: On Dec 6, 2012, at 7:47 AM, Julien Cigar wrote: Hello, Any idea why the following doesn't work ? : Topic2= orm.aliased(Topic) q= Occurrence.query.\ join(Occurrence.datasheet).\ options(orm.contains_eager(Occurrence.datasheet)).\ join(Topic2).\ options(orm.contains_eager(DataSheet.topic,alias=Topic2)).\ outerjoin(Occurrence.species).\ options(orm.contains_eager(Occurrence.species)) I get a: ArgumentError: Can't find property 'topic' on any entity specified in this Query. Note the full path from root (Mapper|Occurrence|occurrences) to target entity must be specified. and I don't understand why .. so paths work like this: query(Occurence).join(Occurrence.datasheet).join(Datasheet.topic).join(Occurrence.species) here are the major paths we've cerated: (Occurence, Occurrence.datasheet, ) (Occurence, Occurrence.datasheet, Datasheet, Datasheet.topic) (Occurence, Occurrence.species) There's actually paths for every attribute on all three of those classes worked up too, but for the purpose of relationship loading, what you send as an eager loading option has to match one of those paths. so: contains_eager(Occurrence.datasheet, Datasheet.topic, alias=Topic2) Thank you, it looks like I misunderstood how those paths worked Another thing I wondered is if there is a shortcut (not joinedload()) for: Bar.query.join(Foo).options(orm.contains_eager(Bar.foo)).filter(Foo.id==1) I thought something like: Bar.query.join(Foo, prop='foo').filter(Foo.id==1) (to be able to specify the property directly in the join) Thanks, Julien -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. jcigar.vcf -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. attachment: jcigar.vcf
Re: [sqlalchemy] CircularDependencyError
On 07/04/2012 17:29, Julien Cigar wrote: On 07/04/2012 16:38, Michael Bayer wrote: On Jul 4, 2012, at 9:16 AM, Julien Cigar wrote: Hello, I have a rather strange CircularDependencyError with the following use-case: - I have a content table and a data table. - content is mapped with Content and data is mapped with File. - File is a Content (joined table inheritance), so File has a foreign key to Content (through the content_id column) - Content has foreign key too File through the icon_content_id column. I'm doing the following: foo = Content.query.get(429) Session.delete(foo) Session.commit() ... but it fails with a CircularDependencyError: Circular dependency detected. Cycles: (..) error (object with id 429 if a File in this case) I posted my mappers here http://pastie.org/4198679 (problematic relation is at line 136-143) and my table definition here http://pastie.org/4198678 The exception message technically shows the cycles though it can be hard to read. You could have a cycle between Content.children, or perhaps interleaving File.icon/Content.icon.There's a lot of possibilities here since everything is self referential. The post_update on File.icon suggests Content.children might be involved. If there's a UOW bug you'd need to give me a minimal test case (test case here would not have most of these ancillary classes - just Content, File, a few data records, error message. Remove one thing at a time from a test case and keep running it until you isolate the minimal conditions). I think there is an UOW bug as I removed almost everything from my mappers and I still get an error. I'll make a test case as soon as I have a little time ! Hello, I've added a ticket with a test case (using PostgreSQL and psycopg2): http://www.sqlalchemy.org/trac/ticket/2527 I tried to remove one relationship() at a time, but I'm still getting a CircularDependencyError ... Julien -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. attachment: jcigar.vcf
[sqlalchemy] CircularDependencyError
Hello, I have a rather strange CircularDependencyError with the following use-case: - I have a content table and a data table. - content is mapped with Content and data is mapped with File. - File is a Content (joined table inheritance), so File has a foreign key to Content (through the content_id column) - Content has foreign key too File through the icon_content_id column. I'm doing the following: foo = Content.query.get(429) Session.delete(foo) Session.commit() ... but it fails with a CircularDependencyError: Circular dependency detected. Cycles: (..) error (object with id 429 if a File in this case) I posted my mappers here http://pastie.org/4198679 (problematic relation is at line 136-143) and my table definition here http://pastie.org/4198678 Thanks, Julien -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. attachment: jcigar.vcf
Re: [sqlalchemy] CircularDependencyError
On 07/04/2012 16:38, Michael Bayer wrote: On Jul 4, 2012, at 9:16 AM, Julien Cigar wrote: Hello, I have a rather strange CircularDependencyError with the following use-case: - I have a content table and a data table. - content is mapped with Content and data is mapped with File. - File is a Content (joined table inheritance), so File has a foreign key to Content (through the content_id column) - Content has foreign key too File through the icon_content_id column. I'm doing the following: foo = Content.query.get(429) Session.delete(foo) Session.commit() ... but it fails with a CircularDependencyError: Circular dependency detected. Cycles: (..) error (object with id 429 if a File in this case) I posted my mappers here http://pastie.org/4198679 (problematic relation is at line 136-143) and my table definition here http://pastie.org/4198678 The exception message technically shows the cycles though it can be hard to read. You could have a cycle between Content.children, or perhaps interleaving File.icon/Content.icon.There's a lot of possibilities here since everything is self referential. The post_update on File.icon suggests Content.children might be involved. If there's a UOW bug you'd need to give me a minimal test case (test case here would not have most of these ancillary classes - just Content, File, a few data records, error message. Remove one thing at a time from a test case and keep running it until you isolate the minimal conditions). I think there is an UOW bug as I removed almost everything from my mappers and I still get an error. I'll make a test case as soon as I have a little time ! -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. attachment: jcigar.vcf
Re: [sqlalchemy] join + joined inheritance
On 04/20/2012 21:38, Michael Bayer wrote:
On Apr 20, 2012, at 9:32 AM, Julien Cigar wrote:
Hello,
I'm using joined load inheritance in one of my project, and I have the
following query:
q = Content.query.outerjoin((File, File.content_id == Content.id), File.mime,
Mime.major)
SQLAlchemy generates a subquery for the outer join.
Any idea how to ask SQLAlchemy to generate a shorter FROM clause (no subquery) ?
This is what I get:
FROM content LEFT OUTER JOIN (SELECT content.id AS content_id, content.added AS
content_added, content.updated AS content_updated, content.title AS
content_title, content.description AS content_description, content.effective AS
content_effective, content.expiration AS content_expiration,
content.exclude_nav AS content_exclude_nav, content.weight AS content_weight,
content.customized AS content_customized, content.content_type_id AS
content_content_type_id, content.icon_content_id AS content_icon_content_id,
content.container_id AS content_container_id, content.owner_id AS
content_owner_id, content.polymorphic_loading AS content_polymorphic_loading,
content.state_id AS content_state_id, data.content_id AS data_content_id,
data.mime_id AS data_mime_id, data.original_name AS data_original_name,
data.file_size AS data_file_size
FROM content JOIN data ON content.id = data.content_id) AS anon_1 ON
anon_1.data_content_id = content.id LEFT OUTER JOIN mime ON mime.id =
anon_1.data_mime_id LEFT OUTER JOIN mime_major ON mime_major.id = mime.major_id
This is what I want:
FROM content LEFT OUTER JOIN data ON content.id = data.content_id LEFT OUTER
JOIN mime ON mime.id = anon_1.data_mime_id LEFT OUTER JOIN mime_major ON
mime_major.id = mime.major_id
My mappers looks like:
orm.mapper(Content, table['content'],
polymorphic_on = table['content'].c.content_type_id,
...)
orm.mapper(File, table['data'], inherits = Content,
polymorphic_identity = _get_type_id('file'),
...)
right this is the behavior of when you join to a joined inh structure. It represents essentially A
JOIN B, and not every database backend can seamlessly handle the idea of C JOIN (A JOIN B),
though most can these days except for SQLite. There's some other nasty scoping problems on the SQLA
expression side which come into play, however, and changing the mechanics of query.join()/outerjoin() to try
to optimize this is not something likely to ever happen, as far as I can see (I've looked into
seeing how it could be done).
In particular here, File is already a composite of Content, so joining from
Content- File is pretty awkward.
in this particular case, you can probably get what you want just by loading Content with the
with_polymorphic option that will outer join to the related file table:
query(Content).with_polymorphic([File]).outerjoin(File.mime, Mime.major)
Thanks, it worked with the with_polymorphic() way
Otherwise, if you want to create a join among the components of a joined
inheritance structure while maintaining explicit control over those components,
you use the Table objects directly, that is File.__table__, and such. There's
a detailed description of this at
http://docs.sqlalchemy.org/en/latest/orm/inheritance.html#basic-control-of-which-tables-are-queried
and
http://docs.sqlalchemy.org/en/latest/orm/inheritance.html#advanced-control-of-which-tables-are-queried
.
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
attachment: jcigar.vcf
[sqlalchemy] join + joined inheritance
Hello,
I'm using joined load inheritance in one of my project, and I have the
following query:
q = Content.query.outerjoin((File, File.content_id == Content.id),
File.mime, Mime.major)
SQLAlchemy generates a subquery for the outer join.
Any idea how to ask SQLAlchemy to generate a shorter FROM clause (no
subquery) ?
This is what I get:
FROM content LEFT OUTER JOIN (SELECT content.id AS content_id,
content.added AS content_added, content.updated AS content_updated,
content.title AS content_title, content.description AS
content_description, content.effective AS content_effective,
content.expiration AS content_expiration, content.exclude_nav AS
content_exclude_nav, content.weight AS content_weight,
content.customized AS content_customized, content.content_type_id AS
content_content_type_id, content.icon_content_id AS
content_icon_content_id, content.container_id AS content_container_id,
content.owner_id AS content_owner_id, content.polymorphic_loading AS
content_polymorphic_loading, content.state_id AS content_state_id,
data.content_id AS data_content_id, data.mime_id AS data_mime_id,
data.original_name AS data_original_name, data.file_size AS data_file_size
FROM content JOIN data ON content.id = data.content_id) AS
anon_1 ON anon_1.data_content_id = content.id LEFT OUTER JOIN mime ON
mime.id = anon_1.data_mime_id LEFT OUTER JOIN mime_major ON
mime_major.id = mime.major_id
This is what I want:
FROM content LEFT OUTER JOIN data ON content.id = data.content_id LEFT
OUTER JOIN mime ON mime.id = anon_1.data_mime_id LEFT OUTER JOIN
mime_major ON mime_major.id = mime.major_id
My mappers looks like:
orm.mapper(Content, table['content'],
polymorphic_on = table['content'].c.content_type_id,
...)
orm.mapper(File, table['data'], inherits = Content,
polymorphic_identity = _get_type_id('file'),
...)
Thanks!,
Julien
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
attachment: jcigar.vcf
Re: [sqlalchemy] Re: xml approach
On 03/23/2012 10:37, lars van gemerden wrote:
Hi Julian,
Thanks, I am looking into it and it looks interesting. Have you done
much testing yet? How do you (plan to; haven't looked at too much
detail yet) check for circular references (like backrefs)?
circular references aren't handled yet .. at the moment all what you can
do is something like:
a = Page.query.get(454)
SAMappedSerializer(a).json(exclude_columns=('body', ),
include_relations=('type', 'tags'))
'{updated: 2012-03-21T16:52:43Z, added: 2009-10-16T14:23:24Z,
description: The Belgian Biodiversity Platform, effective: null,
tags: [{description: null, id: 26, name: Belspo},
{description: null, id: 29, name: Belgian Biodiversity
Platform}], icon_content_id: null, count_children: 0, customized:
false, weight: 23, owner_id: 1, content_id: 454, expiration:
null, polymorphic_loading: null, state_id: 3, title: About us,
exclude_nav: false, type: {description: null, icon: page.png,
id: 2, name: page}, id: 454, container_id: 1,
content_type_id: 2}'
SAMappedSerializer(a).xml(exclude_columns=('body', ),
include_relations=('type', 'tags'))
contentupdated2012-03-21T16:52:43Z/updatedadded2009-10-16T14:23:24Z/addeddescriptionThe
Belgian Biodiversity Platform/descriptioneffective
/tagstags_0description
/id26/idnameBelspo/name/tags_0tags_1description
/id29/idnameBelgian Biodiversity
Platform/name/tags_1/tagsicon_content_id
/count_children0/count_childrencustomizedFalse/customizedweight23/weightowner_id1/owner_idcontent_id454/content_idexpiration
/polymorphic_loading /state_id3/state_idtitleAbout
us/titleexclude_navFalse/exclude_navtypedescription
/iconpage.png/iconid2/idnamepage/name/typeid454/idcontainer_id1/container_idcontent_type_id2/content_type_id/content
etc
Cheers, Lars
On Thursday, March 22, 2012 11:36:41 AM UTC+1, lars van gemerden wrote:
Hi,
I am looking for a way to convert SQLalchemy objects to XML and
back, in order to support a webapplication. I made a mixin class
that does a decent job on 'normal' python objects (basically works
for in the same cases as pickle, though more testing is required).
I would prefer to have a simple mixin interface with something like:
def toXML():
'convert attributes to xml'
return xmlstring
def fromXML(xmlstring)
'update attributes from xml'
The most simple case would be that the web user request indicates
the primary key of the object, gets the corrsponding xml string in
return, changes fields in the xml string and posts it back, which
leads to an update of the object.
I have formulated a couple of initial questions:
- what attributes should be converted to and from xml?
- how do i handle foreign keys on the 'other' side?
Note that the object remains in memory during the user
interaction, so it does not have to be reinitialized from scratch.
Cheers, Lars
--
You received this message because you are subscribed to the Google
Groups sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/QNBLxpPxDBgJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
attachment: jcigar.vcf
[sqlalchemy] with_lockmode('share') ?
Hello,
I'm using SQLAlchemy 0.7.6 and PostgreSQL 9.0.7 which support a FOR
UPDATE and a FOR SHARE clause to lock selected rows.
I noticed that the .with_lockmode() method of the Query object
(http://docs.sqlalchemy.org/en/latest/orm/query.html?highlight=with_lock#sqlalchemy.orm.query.Query.with_lockmode)
only accepts 'update' for PostgreSQL. Could it be an oblivion .. ?
(I tried with the .with_lockmode('read'), but it also generates a FOR
UPDATE on PostgreSQL)
Thank you,
Julien
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
attachment: jcigar.vcf
Re: [sqlalchemy] with_lockmode('share') ?
Hi Mike,
I would be happy to provide a patch (when I find some time).
I never made any patch for SQLAlchemy, what's the preferred method to do
it? Attach an `hg diff` to the ticket or a pull request on Bitbucket .. ?
Thanks,
Julien
On 03/21/2012 15:08, Michael Bayer wrote:
the PG dialect has only FOR UPDATE and FOR UPDATE NOWAIT at the moment,
easy enough to add more options though it would be helpful if someone could volunteer a patch on it
(with tests as always!):
http://www.sqlalchemy.org/trac/ticket/2445
On Mar 21, 2012, at 6:00 AM, Julien Cigar wrote:
Hello,
I'm using SQLAlchemy 0.7.6 and PostgreSQL 9.0.7 which support a FOR UPDATE and
a FOR SHARE clause to lock selected rows.
I noticed that the .with_lockmode() method of the Query object
(http://docs.sqlalchemy.org/en/latest/orm/query.html?highlight=with_lock#sqlalchemy.orm.query.Query.with_lockmode)
only accepts 'update' for PostgreSQL. Could it be an oblivion .. ?
(I tried with the .with_lockmode('read'), but it also generates a FOR UPDATE on
PostgreSQL)
Thank you,
Julien
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
jcigar.vcf
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
attachment: jcigar.vcf
[sqlalchemy] Query.count() with joined load relations and innerjoin=True
Hello,
Is there a reason why a .count() on a Query object doesn't (LEFT) JOIN
all the relations marked with lazy='joined' in the Mapper?
I guess it's to avoid unnecessary JOINs, but sometimes it can lead to
strange results if the relation is marked with innerjoin=True, for example:
q = Occurrence.query.join(DataSheet).join(Topic).\
options(orm.contains_eager('datasheet')).\
filter(Topic.id == form.data['topic_id'])
count = q.count() # returns 327
all = q.all() # result set is empty
the query.count() returns a positive number, and the line after
query.all() returns nothing.
In this case this is because I have the following in my mapper definition:
'species' : orm.relationship(Species, backref='occurences',
lazy='joined', innerjoin=True)
So the Species table is joined, but for this specific data set species
names haven't been determined yet (this is temporary).. so all the
species_id in Occurrence are empty.
Now I know that I should put innerjoin=True only if each Occurrence has
a Species (so that all species_id are not NULL), but to avoid confusion
I think that all relationships marked with innerjoin=True should be
joined for the count() ... ?
Thanks,
Julien
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
attachment: jcigar.vcf
Re: [sqlalchemy] Query.count() with joined load relations and innerjoin=True
On 02/17/2012 15:38, Michael Bayer wrote:
what version of SQLA is this ? count() was changed in 0.7 to run the query as
is each time, wrapping it in a subquery.
Otherwise, can I see more of a complete example I can run here / sample SQL ?
Hi Mike,
This is with version 0.7.5.
My Python code is here http://www.pastie.org/3401784 and the
corresponding SQL is here http://pastie.org/3401782 (the first one is
the COUNT and the second one the full query)
Thank you!
Julien
On Feb 17, 2012, at 5:41 AM, Julien Cigar wrote:
Hello,
Is there a reason why a .count() on a Query object doesn't (LEFT) JOIN all the
relations marked with lazy='joined' in the Mapper?
I guess it's to avoid unnecessary JOINs, but sometimes it can lead to strange
results if the relation is marked with innerjoin=True, for example:
q = Occurrence.query.join(DataSheet).join(Topic).\
options(orm.contains_eager('datasheet')).\
filter(Topic.id == form.data['topic_id'])
count = q.count() # returns 327
all = q.all() # result set is empty
the query.count() returns a positive number, and the line after query.all()
returns nothing.
In this case this is because I have the following in my mapper definition:
'species' : orm.relationship(Species, backref='occurences', lazy='joined',
innerjoin=True)
So the Species table is joined, but for this specific data set species names
haven't been determined yet (this is temporary).. so all the species_id in
Occurrence are empty.
Now I know that I should put innerjoin=True only if each Occurrence has a
Species (so that all species_id are not NULL), but to avoid confusion I think
that all relationships marked with innerjoin=True should be joined for the
count() ... ?
Thanks,
Julien
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
jcigar.vcf
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
attachment: jcigar.vcf
Re: [sqlalchemy] Query.count() with joined load relations and innerjoin=True
On 02/17/2012 16:26, Michael Bayer wrote:
Yeah everything is working by design.count() doesn't run any of those
lazy='joined' joins, as those are not
ok, good to know ! I thought it was a bug
intended to change the results. That you get zero results on a regular query probably
means some of the related records that you're using innerjoin=True on don't
actually have a row. If a collection or related row is empty/nonpresent, you won't get
a parent row back.
yes.. that's actually the case
I'd try turning off the eagers to see which one is messing up the results.
Thanks for support,
Julien
On Feb 17, 2012, at 9:55 AM, Julien Cigar wrote:
On 02/17/2012 15:38, Michael Bayer wrote:
what version of SQLA is this ? count() was changed in 0.7 to run the query as
is each time, wrapping it in a subquery.
Otherwise, can I see more of a complete example I can run here / sample SQL ?
Hi Mike,
This is with version 0.7.5.
My Python code is here http://www.pastie.org/3401784 and the corresponding SQL
is here http://pastie.org/3401782 (the first one is the COUNT and the second
one the full query)
Thank you!
Julien
On Feb 17, 2012, at 5:41 AM, Julien Cigar wrote:
Hello,
Is there a reason why a .count() on a Query object doesn't (LEFT) JOIN all the
relations marked with lazy='joined' in the Mapper?
I guess it's to avoid unnecessary JOINs, but sometimes it can lead to strange
results if the relation is marked with innerjoin=True, for example:
q = Occurrence.query.join(DataSheet).join(Topic).\
options(orm.contains_eager('datasheet')).\
filter(Topic.id == form.data['topic_id'])
count = q.count() # returns 327
all = q.all() # result set is empty
the query.count() returns a positive number, and the line after query.all()
returns nothing.
In this case this is because I have the following in my mapper definition:
'species' : orm.relationship(Species, backref='occurences', lazy='joined',
innerjoin=True)
So the Species table is joined, but for this specific data set species names
haven't been determined yet (this is temporary).. so all the species_id in
Occurrence are empty.
Now I know that I should put innerjoin=True only if each Occurrence has a
Species (so that all species_id are not NULL), but to avoid confusion I think
that all relationships marked with innerjoin=True should be joined for the
count() ... ?
Thanks,
Julien
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
jcigar.vcf
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
jcigar.vcf
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
attachment: jcigar.vcf
[sqlalchemy] 0.7.4 and multiple schemas
Hello,
I upgraded to 0.7.4 together with PostgreSQL 9.0.5.
I have a database with several schemas, and it looks like the handle of
schemas changed in 0.7.4
I altered the default database search_path with:
{{{
xxx=# alter DATABASE xxx SET search_path TO public,gis,cr2010;
}}}
and I used to do something like:
{{{
meta = MetaData()
engine = engine_from_config(myconfig)
meta.reflect()
}}}
but in this case nothing is reflected .. (meta.tables is empty), is it
normal? Also is it intended that SQLAlchemy doesn't care about the
default search_path ..?
I wondered if I should use the new Inspector
(http://docs.sqlalchemy.org/en/latest/core/schema.html#fine-grained-reflection-with-inspector)
for such case ?
Thank you!,
Julien
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
attachment: jcigar.vcf
Re: [sqlalchemy] money type for Postgresql
Using FLOAT for monetary amounts is an extremely bad idea because of the inexactness of storage and arithmetic .. Using MONEY is discouraged because it is too locale-sensitive NUMERIC should be used instead On 12/28/2011 11:48, Martijn Moeling wrote: I use Float for money at the moment. I am moving from Mysql to Postgres and have not had any issues but i'm not sure if Float actually works correctly. Floats are being used for both broken number values and for money values. should I change to numeric for Postgres as I do not see that Column Type mentioned in this tread. Martijn On Dec 27, 2011, at 20:39 , dgardner wrote: Quick hack, figured I would share since there seemed to be other people asking about it. I couldn't get it to work with autoload=True for table reflection. --- from sqlalchemy import types from decimal import Decimal class Money(types.UserDefinedType): def get_col_spec(self): return 'money' def result_processor(self, dialect, coltype): def process(value): # Strip off the currency symbol return Decimal(value[1:]) return process -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] money type for Postgresql
don't use the MONEY type in PostgreSQL, use NUMERIC instead. On 12/27/2011 20:39, dgardner wrote: Quick hack, figured I would share since there seemed to be other people asking about it. I couldn't get it to work with autoload=True for table reflection. --- from sqlalchemy import types from decimal import Decimal class Money(types.UserDefinedType): def get_col_spec(self): return 'money' def result_processor(self, dialect, coltype): def process(value): # Strip off the currency symbol return Decimal(value[1:]) return process -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] how would you do that with SQLAlchemy ..?
Hello, I'm implementing an authentication system in my webapp, with the usual users / roles. I have a table human, a table role and a table human_role. I have one special user anonymous which has no password, no email, etc and two special roles : anonymous user and authenticated user. If an user is authenticated it has automatically the role authenticated user in it's roles. On the other way, if the user is just visiting the website without authenticating it should be an anonymous user and this user should automatically get the anonymous user in it's roles. Those two roles and the anonymous user should always exist. To avoid unnecessary SQL queries, and to simplify the implementation I would like to avoid storing those roles and the anonymous user in the database. I wondered if there is a way to treat those two roles the same way as the other ones except in all the orm/save/update/cascade/... operations .. ? For example I have an user foo, which has roles: reviewer and reader. I would like to do something like: foo_user = User.query.filter_by(login='foo').one() then a property like foo_user.roles which should return ['authenticated', 'reviewer', 'reader'], so 'authenticated' should be automatically added (but, again, ignored in all the orm/save/update/cascade/... operations) I hope it's clear :-) Thanks, Julien -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. attachment: jcigar.vcf
[sqlalchemy] joinedload(), contains_eager(), ...
Hello,
I'm busy to update an application to the last SQLAlchemy version.
I have to following mapped object, with a relation:
orm.mapper(Image, table.images, properties = {
'owner' : orm.relationship(
Participant,
uselist = False
)
})
In previous version of SQLAlchemy I used something like:
# Image.query.options(orm.joinedload('owner')).\
order_by(Participant.name)
Now it seems that I have to use contains_eager() too to be able to
.order_by() on the relationship, is it correct ?
# Image.query.outerjoin(Participant).\
options(orm.contains_eager('owner')).\
order_by(Participant.name)
If I understand well joinedload() always generates an anonymous alias
for the joined relationship ?
Thanks,
Julien
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
attachment: jcigar.vcf
Re: [sqlalchemy] Dynamic query
Given all this magic with .with_polymorphic(), I'm now wondering if
it's really worth to have a new Query class (by subclassing orm.Query)
per mapped object just to apply some filters...
At first I found quite elegant to have one Query object per mapped
class, but now I'm wondering if it's not better after all to have a
bunch of @staticmethod in the model ...
What do you think ?
On 10/13/2010 11:21, Julien Cigar wrote:
On 10/12/2010 18:05, Julien Cigar wrote:
On 10/12/2010 17:09, Michael Bayer wrote:
On Oct 12, 2010, at 7:39 AM, Julien Cigar wrote:
Hello,
any idea why with
# Query
class BaseQuery(orm.Query):
@dynamic
def method_a(self):
...
def method_b(self):
...
class FooQuery(BaseQuery):
...
class BarQuery(FooQuery):
@dynamic
def method_c(self):
...
# Models
class BaseModel(object):
query = Session.query_property(BaseQuery)
#
myQuery = type('PolymorphicQuery, (content.BaseQuery, ),
func_list)(BaseModel)
where func_list containing all the functions decorated by @dynamic
the following fail? :
- myQuery.get(45) fails with: AttributeError: 'NoneType' object has
no attribute 'identity_map'
- myQuery.method_a().all() fails with: AttributeError: 'NoneType'
object has no attribute '_autoflush'
- etc
OK I think I found a solution, I need to pass session=Session.registry()
to my custom query:
model.content.ContentQuery.__mro__
(class 'amnesia.model.content.ContentQuery', class
'amnesia.model.root.RootQuery', class 'sqlalchemy.orm.query.Query',
type 'object')
PolymorphicQuery = type('PolymorphicQuery',
(model.content.ContentQuery, ), {})
q1 = PolymorphicQuery(model.Content)
q1.get(25)
Traceback (most recent call last):
File console, line 1, in module
File
/home/jcigar/venvs/pylons0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.6.4-py2.5.egg/sqlalchemy/orm/query.py,
line 595, in get
return self._get(key, ident)
File
/home/jcigar/venvs/pylons0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.6.4-py2.5.egg/sqlalchemy/orm/query.py,
line 1803, in _get
instance = self.session.identity_map.get(key)
AttributeError: 'NoneType' object has no attribute 'identity_map'
q2 = PolymorphicQuery(model.Content, session=meta.Session.registry())
q2.get(25)
amnesia.model.event.Event object at 0x939046c
I hope I'm not doing something wrong :p
My goal is to be able to build a custom Query object to use with the
.with_polymorphic() function ..
I've just grepped through all the source, examples and tests plus the
wiki trying to find what @dynamic is. Seems like something I'd have
come up with in the past but I've no clue at the moment what that is.
Sorry, I forgot to mention that it's just a custom decorator of mine
which add the function name to a list ... forget about it, it's just to
build the third argument of type() (func_list in my case)
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
attachment: jcigar.vcf
Re: [sqlalchemy] Dynamic query
On 10/12/2010 18:05, Julien Cigar wrote:
On 10/12/2010 17:09, Michael Bayer wrote:
On Oct 12, 2010, at 7:39 AM, Julien Cigar wrote:
Hello,
any idea why with
# Query
class BaseQuery(orm.Query):
@dynamic
def method_a(self):
...
def method_b(self):
...
class FooQuery(BaseQuery):
...
class BarQuery(FooQuery):
@dynamic
def method_c(self):
...
# Models
class BaseModel(object):
query = Session.query_property(BaseQuery)
#
myQuery = type('PolymorphicQuery, (content.BaseQuery, ),
func_list)(BaseModel)
where func_list containing all the functions decorated by @dynamic
the following fail? :
- myQuery.get(45) fails with: AttributeError: 'NoneType' object has
no attribute 'identity_map'
- myQuery.method_a().all() fails with: AttributeError: 'NoneType'
object has no attribute '_autoflush'
- etc
OK I think I found a solution, I need to pass session=Session.registry()
to my custom query:
model.content.ContentQuery.__mro__
(class 'amnesia.model.content.ContentQuery', class
'amnesia.model.root.RootQuery', class 'sqlalchemy.orm.query.Query',
type 'object')
PolymorphicQuery = type('PolymorphicQuery',
(model.content.ContentQuery, ), {})
q1 = PolymorphicQuery(model.Content)
q1.get(25)
Traceback (most recent call last):
File console, line 1, in module
File
/home/jcigar/venvs/pylons0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.6.4-py2.5.egg/sqlalchemy/orm/query.py,
line 595, in get
return self._get(key, ident)
File
/home/jcigar/venvs/pylons0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.6.4-py2.5.egg/sqlalchemy/orm/query.py,
line 1803, in _get
instance = self.session.identity_map.get(key)
AttributeError: 'NoneType' object has no attribute 'identity_map'
q2 = PolymorphicQuery(model.Content, session=meta.Session.registry())
q2.get(25)
amnesia.model.event.Event object at 0x939046c
I hope I'm not doing something wrong :p
My goal is to be able to build a custom Query object to use with the
.with_polymorphic() function ..
I've just grepped through all the source, examples and tests plus the
wiki trying to find what @dynamic is. Seems like something I'd have
come up with in the past but I've no clue at the moment what that is.
Sorry, I forgot to mention that it's just a custom decorator of mine
which add the function name to a list ... forget about it, it's just to
build the third argument of type() (func_list in my case)
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
attachment: jcigar.vcf
[sqlalchemy] Dynamic query
Hello,
any idea why with
# Query
class BaseQuery(orm.Query):
@dynamic
def method_a(self):
...
def method_b(self):
...
class FooQuery(BaseQuery):
...
class BarQuery(FooQuery):
@dynamic
def method_c(self):
...
# Models
class BaseModel(object):
query = Session.query_property(BaseQuery)
#
myQuery = type('PolymorphicQuery, (content.BaseQuery, ),
func_list)(BaseModel)
where func_list containing all the functions decorated by @dynamic the
following fail? :
- myQuery.get(45) fails with: AttributeError: 'NoneType' object has no
attribute 'identity_map'
- myQuery.method_a().all() fails with: AttributeError: 'NoneType' object
has no attribute '_autoflush'
- etc
My goal is to be able to build a custom Query object to use with the
.with_polymorphic() function ..
Thanks,
Julien
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
attachment: jcigar.vcf
Re: [sqlalchemy] Dynamic query
On 10/12/2010 17:09, Michael Bayer wrote:
On Oct 12, 2010, at 7:39 AM, Julien Cigar wrote:
Hello,
any idea why with
# Query
class BaseQuery(orm.Query):
@dynamic
def method_a(self):
...
def method_b(self):
...
class FooQuery(BaseQuery):
...
class BarQuery(FooQuery):
@dynamic
def method_c(self):
...
# Models
class BaseModel(object):
query = Session.query_property(BaseQuery)
#
myQuery = type('PolymorphicQuery, (content.BaseQuery, ), func_list)(BaseModel)
where func_list containing all the functions decorated by @dynamic the
following fail? :
- myQuery.get(45) fails with: AttributeError: 'NoneType' object has no
attribute 'identity_map'
- myQuery.method_a().all() fails with: AttributeError: 'NoneType' object has no
attribute '_autoflush'
- etc
My goal is to be able to build a custom Query object to use with the
.with_polymorphic() function ..
I've just grepped through all the source, examples and tests plus the wiki
trying to find what @dynamic is. Seems like something I'd have come up with
in the past but I've no clue at the moment what that is.
Sorry, I forgot to mention that it's just a custom decorator of mine
which add the function name to a list ... forget about it, it's just to
build the third argument of type() (func_list in my case)
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
attachment: jcigar.vcf
