[sqlalchemy] Declarative: defining relationship and column in one line
Hi, Sorry if this is a FAQ, but is it possible to define a relationship and its column all at once. e.g. instead of: type_id = db.Column(db.Integer, db.ForeignKey('linktype.id')) type = db.relationship('LinkType') Something like: type = db.relationship('LinkType', colname='type_id') In fact, it'd be good for the colname to default to xxx_id - although allow overriding. This certainly was possible with Elixir. Paul -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Build With Different Backends [RESOLVED]
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 19 May 2015, Mike Bayer wrote: Examples: date and time functions are entirely different on both platforms, schema migration operations e.g. ALTER are generally not supported on SQLite, SQLite has very different behavior regarding foreign key constraints (in that they do nothing unless special directives are emitted per-connection), ... Mike, I wondered about this. It's a scientific application with many date and time columns in tables and relies on foreign keys extensively to associate rows in a details table with a specific row in a header table. A good amount of starting detail is available in the documentation for both backends: http://docs.sqlalchemy.org/en/rel_1_0/dialects/sqlite.html http://docs.sqlalchemy.org/en/rel_1_0/dialects/postgresql.html Because I'm starting to learn sqlalchemy I'll begin with the sqlite3 version. I should be able to have sqlalchemy generate the tables from the declarative base .py file so I don't need to create it in advance with sqlite. The docs mentions that the sqlite version built into python is used. I presume that I can specify a newer version that's available since the resultin .db will be packaged with the rest of the application. Thanks very much, Rich -BEGIN PGP SIGNATURE- Version: GnuPG v1 iD8DBQFVW8ypugEIjC001L0RAuPFAJ9ZbhU6S7p5QLsDeQKnJ7mSWr61QACglHhB XnZZ2otJI7RhxbDohcWPnyM= =/nLQ -END PGP SIGNATURE-
Re: [sqlalchemy] Build With Different Backends
On 5/19/15 1:51 PM, Rich Shepard wrote: I thought there was a thread on this but I cannot find it so please point me to that thread if it exists. An application will be provided in two versions: a single-user version with SQLite3 as the backend, and a multi-user version with PostgreSQL as the backend. Am I correct that the only necessary change in the SQLAlchemy code is the engine specification? There's not a simple yes or no answer to that question. Yes, if you change the engine specification, a simple SQLAlchemy application and even a very complex one will successfully interact with SQLite and Postgresql with no other changes. This is very common. But also, no, in that if your application relies upon special, or even not-so-special features of SQLite or Postgresql and provides no equivalent operation for the other, the use of these features would have to be developed such that an equivalent operation is performed on each backend. Examples: date and time functions are entirely different on both platforms, schema migration operations e.g. ALTER are generally not supported on SQLite, SQLite has very different behavior regarding foreign key constraints (in that they do nothing unless special directives are emitted per-connection), transactional features like SAVEPOINT have caveats on SQLite, Postgresql has a vast array of datatypes like HSTORE, JSON that are not supported on SQLite, SQLite has a very restrictive concurrency model compared to Postgresql and pretty much any other database, SQLite does not support RETURNING, does not support right-nested JOINs, etc. etc. etc., there are literally many hundreds of behavioral and feature differences. However, it's very possible that your application is not making use of any of these features or behaviors, or is making adequate use of SQLAlchemy Core/ORM such that a subset of these differences are abstracted away; examples: RETURNING for primary key retrieval is used internally only if available, right-nested joins are abstracted away in many ORM scenarios, platform-agnostic datatypes like Boolean and DateTime provide emulation for these non-existent types on SQLite. The only way to know is via research of backend capabilities and thorough testing. SQLAlchemy's job is just to expedite the communication with these backends and provide a modicum of commonality between them, but it does not limit all operations to the lowest common denominator of database features; such a system would be largely unusable especially given SQLite's extremely limited capabilities. A good amount of starting detail is available in the documentation for both backends: http://docs.sqlalchemy.org/en/rel_1_0/dialects/sqlite.html http://docs.sqlalchemy.org/en/rel_1_0/dialects/postgresql.html -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] restrict child count?
On 5/19/15 8:57 AM, Richard Gerd Kuesters wrote: hi! this may be a weird question, but is there a way i can restrict the number of children in a relationship? not by limit ... how is that different? Anytime in SQL you want to get only the first N of M, LIMIT or its equivalents must be involved. two options are: 1. write the exact SQL for the primary + relationship you want, then use contains_eager() to specify it as a collection load. the SQL has to be along the lines of SELECT * FROM primary LEFT OUTER JOIN secondary WHERE secondary.id IS NULL or secondary.id IN (select id FROM secondary AS sec_2 LIMIT N WHERE sec_2.primary_id=secondary.primary_id) 2. load the collections individually: from sqlalchemy.orm.attributes import set_committed_value for item in things: child_items = sess.query(Child).with_parent(item).limit(N).all() set_committed_value(item, child_items, child_items) scenario: i have a one to many rel, where the parent have 3 values (row, column, depth) that creates a max child count of row * column * depth (yes, like the 3d stuff) ... so, count(child) = max_child, this sort of stuff. if i could pull of postgres specific constraint for this, even better ... best regards, richard. -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Declarative: defining relationship and column in one line
On 5/19/15 6:54 AM, Paul Johnston wrote: Hi, Sorry if this is a FAQ, but is it possible to define a relationship and its column all at once. e.g. instead of: type_id = db.Column(db.Integer, db.ForeignKey('linktype.id')) type = db.relationship('LinkType') Something like: type = db.relationship('LinkType', colname='type_id') In fact, it'd be good for the colname to default to xxx_id - although allow overriding. This certainly was possible with Elixir. this would be a many_to_one() macro. Two examples of this are at: https://bitbucket.org/zzzeek/pycon2014_atmcraft/src/f50cbe745a197ea7db83569283b703c418481222/atmcraft/model/meta/orm.py?at=master the above is a modernized improvement on an older example: http://techspot.zzzeek.org/2011/05/17/magic-a-new-orm/ Paul -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] restrict child count?
thanks Mike! when i stated about the limit, it was because it must not be taken as a parameter for any query, which select * from blah and select * from bla limit N should be return the same exactly number of rows, including where filters and so on. it is something like a physical rule, where my parent is (really) a box and the children its items (so, i cannot put more items then the box's limit). either way, creating a relationship with limit *can* provide me that sort of behaviour? i mean, i'm just asking this because it may be already done by someone. if not, that's not a problem -- i'll have to managed something by myself :) ps: sorry for my bad english, sometimes i can't make understandable questions :) best regards, richard. On 05/19/2015 11:16 AM, Mike Bayer wrote: On 5/19/15 8:57 AM, Richard Gerd Kuesters wrote: hi! this may be a weird question, but is there a way i can restrict the number of children in a relationship? not by limit ... how is that different? Anytime in SQL you want to get only the first N of M, LIMIT or its equivalents must be involved. two options are: 1. write the exact SQL for the primary + relationship you want, then use contains_eager() to specify it as a collection load. the SQL has to be along the lines of SELECT * FROM primary LEFT OUTER JOIN secondary WHERE secondary.id IS NULL or secondary.id IN (select id FROM secondary AS sec_2 LIMIT N WHERE sec_2.primary_id=secondary.primary_id) 2. load the collections individually: from sqlalchemy.orm.attributes import set_committed_value for item in things: child_items = sess.query(Child).with_parent(item).limit(N).all() set_committed_value(item, child_items, child_items) scenario: i have a one to many rel, where the parent have 3 values (row, column, depth) that creates a max child count of row * column * depth (yes, like the 3d stuff) ... so, count(child) = max_child, this sort of stuff. if i could pull of postgres specific constraint for this, even better ... best regards, richard. -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto: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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto: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. attachment: richard.vcf
Re: [sqlalchemy] restrict child count?
On 5/19/15 10:54 AM, Richard Gerd Kuesters wrote: thanks Mike! when i stated about the limit, it was because it must not be taken as a parameter for any query, which select * from blah and select * from bla limit N should be return the same exactly number of rows, including where filters and so on. it is something like a physical rule, where my parent is (really) a box and the children its items (so, i cannot put more items then the box's limit). either way, creating a relationship with limit *can* provide me that sort of behaviour? I don't understand what the behavior is here. No LIMIT, yet there is a limit. An assertion?I have no idea what you mean. The relationship 1. emits SQL 2. loads the results into objects.Are we talking about 1. or 2. ? i mean, i'm just asking this because it may be already done by someone. if not, that's not a problem -- i'll have to managed something by myself :) ps: sorry for my bad english, sometimes i can't make understandable questions :) best regards, richard. On 05/19/2015 11:16 AM, Mike Bayer wrote: On 5/19/15 8:57 AM, Richard Gerd Kuesters wrote: hi! this may be a weird question, but is there a way i can restrict the number of children in a relationship? not by limit ... how is that different? Anytime in SQL you want to get only the first N of M, LIMIT or its equivalents must be involved. two options are: 1. write the exact SQL for the primary + relationship you want, then use contains_eager() to specify it as a collection load. the SQL has to be along the lines of SELECT * FROM primary LEFT OUTER JOIN secondary WHERE secondary.id IS NULL or secondary.id IN (select id FROM secondary AS sec_2 LIMIT N WHERE sec_2.primary_id=secondary.primary_id) 2. load the collections individually: from sqlalchemy.orm.attributes import set_committed_value for item in things: child_items = sess.query(Child).with_parent(item).limit(N).all() set_committed_value(item, child_items, child_items) scenario: i have a one to many rel, where the parent have 3 values (row, column, depth) that creates a max child count of row * column * depth (yes, like the 3d stuff) ... so, count(child) = max_child, this sort of stuff. if i could pull of postgres specific constraint for this, even better ... best regards, richard. -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto: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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto: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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: restrict child count?
On 5/19/15 12:18 PM, Jonathan Vanasco wrote: I think I might understand you... You have a BOX, which could be a variable amount of sizes, and each size can hold a variable amount of items. You want to create a rule(s) that will ensure you do not have too many things in each box. If that is that case: 1. You could use Triggers in PostgreSQL to ensure that you are within the correct dimensions on insert and update. 2. You might be able use events in sqlalchemy to inspect the collection and figure out if the number of items is allowablw from my POV this is more of a SQL/database question, not really SQLAlchemy.Not a problem to post here but I wish there were some way for me to know which questions are SQLAlchemy-API-specific; other folks and/or stackoverflow can take care of general Postgresql / SQL / schema design questions. -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] restrict child count?
bingo! thanks Simon. that's exactly the question :) well, the checks on the cube are already there (they must have a value higher then 0 to have a volume), but i must not increment the cube children more then it's maximum capacity. i'm considering an event listener as well, but i'm wondering if it can't be done at database level, using postgres? or, what would be the better approach scenario for this? a trigger, an event, both? On 05/19/2015 01:13 PM, Simon King wrote: On Tue, May 19, 2015 at 4:06 PM, Mike Bayer mike...@zzzcomputing.com wrote: On 5/19/15 10:54 AM, Richard Gerd Kuesters wrote: thanks Mike! when i stated about the limit, it was because it must not be taken as a parameter for any query, which select * from blah and select * from bla limit N should be return the same exactly number of rows, including where filters and so on. it is something like a physical rule, where my parent is (really) a box and the children its items (so, i cannot put more items then the box's limit). either way, creating a relationship with limit *can* provide me that sort of behaviour? I don't understand what the behavior is here. No LIMIT, yet there is a limit. An assertion?I have no idea what you mean. The relationship 1. emits SQL 2. loads the results into objects.Are we talking about 1. or 2. ? I'm guessing he's looking for something like this: class Cube(Base): __table_name__ = 'cube' id = sa.Column(sa.Integer, primary_key=True) rows = sa.Column(sa.Integer) cols = sa.Column(sa.Integer) depth = sa.Column(sa.Integer) @property def volume(self): return self.rows * self.cols * self.depth class Cell(Base): __table_name__ = 'cell' id = sa.Column(sa.Integer, primary_key=True) cube_id = sa.Column(sa.ForeignKey(Cube.id)) row = sa.Column(sa.Integer) col = sa.Column(sa.Integer) depth = sa.Column(sa.Integer) __table_args__ = ( sa.UniqueConstraint('cube_id', 'row', 'col', 'depth') ) cube = saorm.relationship(Cube, backref='cells') Now, given an instance of Cube, how can you ensure that it is impossible to add more than Cube.volume cells to the Cube.cells relationship? I imagine it is possible by attaching an event listener to Cube.cells and doing the validation in there. If Richard's data really is structured like this, I'd probably instead want to enforce that: 0 = cell.row cell.cube.rows 0 = cell.col cell.cube.cols 0 = cell.depth cell.cube.depth I think you could probably do this with SQLAlchemy validators. I don't know enough Postgres, but I suspect you could also enforce it at the database level. Simon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. attachment: richard.vcf
[sqlalchemy] restrict child count?
hi! this may be a weird question, but is there a way i can restrict the number of children in a relationship? not by limit ... scenario: i have a one to many rel, where the parent have 3 values (row, column, depth) that creates a max child count of row * column * depth (yes, like the 3d stuff) ... so, count(child) = max_child, this sort of stuff. if i could pull of postgres specific constraint for this, even better ... best regards, richard. -- 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. attachment: richard.vcf
Re: [sqlalchemy] Re: restrict child count?
i know, i'm sorry for that. i first posted it here since my whole application is managed by sqlalchemy, so that's why i asked for something. i mentioned postgresql because it's the database that i use and there's a lot of database specific solutions bundled with sa. but, even if no rdbms agnostic solution can be provided to such situation using sqlalchemy, i'll sure dive into a trigger :) best regards, richard. On 05/19/2015 02:10 PM, Mike Bayer wrote: On 5/19/15 12:18 PM, Jonathan Vanasco wrote: I think I might understand you... You have a BOX, which could be a variable amount of sizes, and each size can hold a variable amount of items. You want to create a rule(s) that will ensure you do not have too many things in each box. If that is that case: 1. You could use Triggers in PostgreSQL to ensure that you are within the correct dimensions on insert and update. 2. You might be able use events in sqlalchemy to inspect the collection and figure out if the number of items is allowablw from my POV this is more of a SQL/database question, not really SQLAlchemy.Not a problem to post here but I wish there were some way for me to know which questions are SQLAlchemy-API-specific; other folks and/or stackoverflow can take care of general Postgresql / SQL / schema design questions. -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto: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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto: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. attachment: richard.vcf
[sqlalchemy] MariaDB and SQLAlchemy
Hello, If there is documentation on this that I missed, please let me know. I have code that worked fine when I was using a MySQL database. My organization has switched to using MariaDB, which I was told was virtually identical to MySQL. It seems can connect to a MariaDB db using the following statement, which doesn't generate any errors: engine = create_engine('mysql+pymysql://[user]:[password@[server]/[db]', pool_recycle=3600) Then the script tries to execute a simple select statement that worked fine on MySQL: check_for_table = SELECT * FROM tb_metadata table_result = session.execute(check_for_table) At this point the script throws an error (sqlalchemy.exc.OperationalError: (OperationalError) (1045, uAccess denied for user XXX) Any ideas? I would like to keep using SQLAlchemy if possible. Thank you, Meg -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] MariaDB and SQLAlchemy
On 5/19/15 6:53 PM, Margaret Tilton wrote: Hello, If there is documentation on this that I missed, please let me know. I have code that worked fine when I was using a MySQL database. My organization has switched to using MariaDB, which I was told was virtually identical to MySQL. It seems can connect to a MariaDB db using the following statement, which doesn't generate any errors: engine = create_engine('mysql+pymysql://[user]:[password@[server]/[db]', pool_recycle=3600) Then the script tries to execute a simple select statement that worked fine on MySQL: check_for_table = SELECT * FROM tb_metadata table_result = session.execute(check_for_table) At this point the script throws an error (sqlalchemy.exc.OperationalError: (OperationalError) (1045, uAccess denied for user XXX) that issue is not within SQLAlchemy, it has to do with the configuration of the username which you are connecting with as well as the host configuration. You should try testing first with the mysql command line client.Take a look at https://mariadb.com/kb/en/mariadb/configuring-mariadb-for-remote-client-access/ for configuration instructions. Any ideas? I would like to keep using SQLAlchemy if possible. Thank you, Meg -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: restrict child count?
I think I might understand you... You have a BOX, which could be a variable amount of sizes, and each size can hold a variable amount of items. You want to create a rule(s) that will ensure you do not have too many things in each box. If that is that case: 1. You could use Triggers in PostgreSQL to ensure that you are within the correct dimensions on insert and update. 2. You might be able use events in sqlalchemy to inspect the collection and figure out if the number of items is allowablw -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] restrict child count?
On Tue, May 19, 2015 at 4:06 PM, Mike Bayer mike...@zzzcomputing.com wrote: On 5/19/15 10:54 AM, Richard Gerd Kuesters wrote: thanks Mike! when i stated about the limit, it was because it must not be taken as a parameter for any query, which select * from blah and select * from bla limit N should be return the same exactly number of rows, including where filters and so on. it is something like a physical rule, where my parent is (really) a box and the children its items (so, i cannot put more items then the box's limit). either way, creating a relationship with limit *can* provide me that sort of behaviour? I don't understand what the behavior is here. No LIMIT, yet there is a limit. An assertion?I have no idea what you mean. The relationship 1. emits SQL 2. loads the results into objects.Are we talking about 1. or 2. ? I'm guessing he's looking for something like this: class Cube(Base): __table_name__ = 'cube' id = sa.Column(sa.Integer, primary_key=True) rows = sa.Column(sa.Integer) cols = sa.Column(sa.Integer) depth = sa.Column(sa.Integer) @property def volume(self): return self.rows * self.cols * self.depth class Cell(Base): __table_name__ = 'cell' id = sa.Column(sa.Integer, primary_key=True) cube_id = sa.Column(sa.ForeignKey(Cube.id)) row = sa.Column(sa.Integer) col = sa.Column(sa.Integer) depth = sa.Column(sa.Integer) __table_args__ = ( sa.UniqueConstraint('cube_id', 'row', 'col', 'depth') ) cube = saorm.relationship(Cube, backref='cells') Now, given an instance of Cube, how can you ensure that it is impossible to add more than Cube.volume cells to the Cube.cells relationship? I imagine it is possible by attaching an event listener to Cube.cells and doing the validation in there. If Richard's data really is structured like this, I'd probably instead want to enforce that: 0 = cell.row cell.cube.rows 0 = cell.col cell.cube.cols 0 = cell.depth cell.cube.depth I think you could probably do this with SQLAlchemy validators. I don't know enough Postgres, but I suspect you could also enforce it at the database level. Simon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Build With Different Backends
I thought there was a thread on this but I cannot find it so please point me to that thread if it exists. An application will be provided in two versions: a single-user version with SQLite3 as the backend, and a multi-user version with PostgreSQL as the backend. Am I correct that the only necessary change in the SQLAlchemy code is the engine specification? Rich