[sqlalchemy] Relationship setup
I have about a dozen tables with an id PK column and a single column with some unique string. I then have a single table that composes 13 rows with an id PK field and all FK refs to rows in the other 12 tables. Bulk inserting data into the initial 12 tables is simple but I am not certain how to setup the final table so I can pass just actual values that otherwise would exist in the earlier 12 tables without passing in their PK id's. How do you setup the relationship in final table to facilitate passing in actual values, allowing them to bypass a lookup for their PK id? Thanks, jlc -- 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] How do you limit/specify the columns that are loaded via a relationship()?
Is there any way to get SQLAlchemy to only load specific columns through a
relationship()?
For example, with this scenario:
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
children = relationship(Child, lazy='joined')
class Child(Base):
__tablename__ = 'child'
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey('parent.id'))
cheap_column = Column(Unicode(10))
expensive_column = Column(LargeBinary)
I'd like Child to get lazily joined when Parent is loaded, but only with
Child's cheap_column and not it's expensive_column.
Thanks,
Seth
--
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] selects, mappers and foreign keys
hi all! situation: i'm mapping a select as a class, using mapper. so far so good. problem: some of my selected columns *are* foreign keys in their respective tables, but i would like to say to sqla that they're foreign keys to another mapped class, which have the pks from which those fks are pointing. the first question is: how? or the second question: is there a way to inherit properties (like fks) OR cheat declaring foreign keys that doesn't exists at the database level ? my 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.
[sqlalchemy] inserting many to many childs relationship generating same id (uuid), raises IntegrityError
Hi guys, i cant seem to find out whats causing this.
first, here are my classes:
class User(Base):
__tablename__ = users
id = Column(GUID, primary_key=True, default=uuid.uuid4())
permissions = (Permission, secondary=UserPermission.__table__)
class Permission(Base):
__tablename__ = permissions
id = Column(GUID, primary_key=True, default=uuid.uuid4())
class UserPermission(Base):
__tablename__ = user_permissions
id = Column(GUID, primary_key=True, default=uuid.uuid4())
user_id = Column(GUID, ForeignKey('users.id'), nullable=False)
permission_id = Column(GUID, ForeignKey('permissions.id'),
nullable=False)
Then, lets say I queried an exising user:
user = Session.query(User).filter(User.username == 'johndoe').first()
And then i added some permissions to the user
user.append(permission1)
user.append(permission2)
user.append(permission3)
Session.add(user)
Session.commit()
and what i got is
IntegrityError: (IntegrityError) duplicate key value violates unique constraint
pk_user_permissions
because on the insert statement all the id generated were all the same.
and to clarify the GUID is the recipe
from
http://docs.sqlalchemy.org/en/rel_0_9/core/types.html#backend-agnostic-guid-type
anyone have any idea?
Thanks in advance
--
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] How do you limit/specify the columns that are loaded via a relationship()?
session.query(Parent).options(defaultload(children).load_only(cheap_column))
or really if you want to cut out expensive_column
session.query(Parent).options(defaultload(children).defer(expensive_column))
http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#deferred
On May 10, 2014, at 6:39 PM, Seth seedifferen...@gmail.com wrote:
Is there any way to get SQLAlchemy to only load specific columns through a
relationship()?
For example, with this scenario:
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
children = relationship(Child, lazy='joined')
class Child(Base):
__tablename__ = 'child'
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey('parent.id'))
cheap_column = Column(Unicode(10))
expensive_column = Column(LargeBinary)
I'd like Child to get lazily joined when Parent is loaded, but only with
Child's cheap_column and not it's expensive_column.
Thanks,
Seth
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] selects, mappers and foreign keys
On May 10, 2014, at 7:13 PM, Richard Gerd Kuesters rich...@humantech.com.br wrote: hi all! situation: i'm mapping a select as a class, using mapper. so far so good. problem: some of my selected columns *are* foreign keys in their respective tables, but i would like to say to sqla that they're foreign keys to another mapped class, which have the pks from which those fks are pointing. the first question is: how? or should be able to use relationship(), set up primaryjoin with foreign() foo = relationship(Remote, primaryjoin=myselect.c.foo == foreign(table.c.foo)) it's a little weird i guess, should work out in modern versions the second question: is there a way to inherit properties (like fks) OR cheat declaring foreign keys that doesn't exists at the database level ? sure, use ForeignKey() on your Column(), doesn't matter if it's not in the DB, or use in relationship foreign_keys / foreign() annotation my 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. -- 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] inserting many to many childs relationship generating same id (uuid), raises IntegrityError
On May 10, 2014, at 10:49 PM, Alexander Luksidadi
alexander.luksid...@gmail.com wrote:
Hi guys, i cant seem to find out whats causing this.
first, here are my classes:
class User(Base):
__tablename__ = users
id = Column(GUID, primary_key=True, default=uuid.uuid4())
permissions = (Permission, secondary=UserPermission.__table__)
class Permission(Base):
__tablename__ = permissions
id = Column(GUID, primary_key=True, default=uuid.uuid4())
class UserPermission(Base):
__tablename__ = user_permissions
id = Column(GUID, primary_key=True, default=uuid.uuid4())
user_id = Column(GUID, ForeignKey('users.id'), nullable=False)
permission_id = Column(GUID, ForeignKey('permissions.id'), nullable=False)
default on Column should point to a function that can be called. Not a
completed UUID value. So default=uuid.uuid4.
--
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] Relationship setup
On May 10, 2014, at 3:34 PM, Joseph Casale jcas...@gmail.com wrote: I have about a dozen tables with an id PK column and a single column with some unique string. I then have a single table that composes 13 rows with an id PK field and all FK refs to rows in the other 12 tables. Bulk inserting data into the initial 12 tables is simple but I am not certain how to setup the final table so I can pass just actual values that otherwise would exist in the earlier 12 tables without passing in their PK id's. How do you setup the relationship in final table to facilitate passing in actual values, allowing them to bypass a lookup for their PK id? standard relationship mechanics would allow this: myobject.some_relationship = some_related_object there's no need to worry about the actual PK or FK values. if that's not your question then perhaps provide more specifics. -- 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] Relationship setup
Hey Michael,
Lets say I have a table TableA:
class TableA(Base):
__tablename__ = 'table_a'
id = Column(Integer, primary_key=True)
name = Column(String(collation='nocase'), unique=True, nullable=False)
Then TableB:
class TableB(Base):
__tablename__ = 'table_b'
id = Column(Integer, primary_key=True)
name_id = Column(Integer, ForeignKey('table_a.id', ondelete='CASCADE'),
nullable=False)
name = relationship(TableA)
Is there any way I can build TableB so that a consumer can pass just the
string value of the
intended reference (table_a.name) to table_b.name and have the orm infer
the reference?
Thanks for the help,
jlc
--
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] Relationship setup
On May 10, 2014, at 11:58 PM, Joseph Casale jcas...@gmail.com wrote:
Hey Michael,
Lets say I have a table TableA:
class TableA(Base):
__tablename__ = 'table_a'
id = Column(Integer, primary_key=True)
name = Column(String(collation='nocase'), unique=True, nullable=False)
Then TableB:
class TableB(Base):
__tablename__ = 'table_b'
id = Column(Integer, primary_key=True)
name_id = Column(Integer, ForeignKey('table_a.id', ondelete='CASCADE'),
nullable=False)
name = relationship(TableA)
Is there any way I can build TableB so that a consumer can pass just the
string value of the
intended reference (table_a.name) to table_b.name and have the orm infer the
reference?
I don't understand. Do you mean at the configuration level? e.g.:
class TableB(Base):
# ...
name = relationship(TableA) # name_id is auto created?
if so, check out the recipe here:
https://bitbucket.org/zzzeek/pycon2014_atmcraft/src/a6d96575bc497ce0c952bb81db9c05d054c98bb5/atmcraft/model/meta/orm.py?at=master#cl-5
/
https://bitbucket.org/zzzeek/pycon2014_atmcraft/src/a6d96575bc497ce0c952bb81db9c05d054c98bb5/atmcraft/model/meta/schema.py?at=master#cl-12
, background is at: http://www.sqlalchemy.org/library.html#buildingtheapp.
this is a modernized version of what I did a few years ago here:
http://techspot.zzzeek.org/2011/05/17/magic-a-new-orm/.
otherwise if you mean at the query level or building objects level, I'm not
sure what you're asking.
--
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] Relationship setup
I don’t understand. Do you mean at the configuration level? e.g.: class TableB(Base): # … name = relationship(TableA) # “name_id” is auto created? Right, So when a person is adding rows to TableB, they have a few ways of doing it. 1. If they know or lookup the actual PK of table_a.name they can add the row to table_b by passing the integer value into table_b.name_id, like someone would do in native SQL. 2. They can pass an object of TableA with the string value of table_a.name in effect producing the lookup otherwise done manually above. 3. If the values and integrity rules permitted, they could pass table_b.name = TableA(name='foo') without even pre populating table_a. As an aside, this would be ideal but I have never known how to add the 'OR IGNORE' prefixes modifier to make that work. TableA only has a few values for which many rows in TableB will reference more than once. What I wanted to know was if it was possible to construct either a table definition for TableB so that someone could simply pass in actual values of table_a.name to meta columns in table_b. For example if #3 above is not possible and table_a has been pre populated: table_a: id name -- 1 foo 2 bar 3 biz To populate table_b: data = [ TableB(name='foo'), TableB(name='bar') TableB(name='biz') ] session.add_all(data) Of course table_b has ~13 columns for which many combinations of values from all the intermediate tables will produce unique rows... Thanks, jlc -- 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] inserting many to many childs relationship generating same id (uuid), raises IntegrityError
Oh god.. why didnt i think of that..
Thank you Michael!
On Saturday, May 10, 2014 11:03:19 PM UTC-4, Michael Bayer wrote:
On May 10, 2014, at 10:49 PM, Alexander Luksidadi
alexander...@gmail.comjavascript:
wrote:
Hi guys, i cant seem to find out whats causing this.
first, here are my classes:
class User(Base):
__tablename__ = users
id = Column(GUID, primary_key=True, default=uuid.uuid4())
permissions = (Permission, secondary=UserPermission.__table__)
class Permission(Base):
__tablename__ = permissions
id = Column(GUID, primary_key=True, default=uuid.uuid4())
class UserPermission(Base):
__tablename__ = user_permissions
id = Column(GUID, primary_key=True, default=uuid.uuid4())
user_id = Column(GUID, ForeignKey('users.id'), nullable=False)
permission_id = Column(GUID, ForeignKey('permissions.id'),
nullable=False)
“default” on Column should point to a function that can be called. Not a
completed UUID value. So “default=uuid.uuid4”.
--
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.
