Re: [sqlalchemy] Many-to-Many Table
On 7/23/15 2:18 PM, Rich Shepard wrote:
Originally posted here on June 4th, but no one responded. I'm now
back on
this project and this should be the last table I need to add to the
schema.
I would appreciate your review of the below class and whether it is
good to
go or needs your modifications.
Section 2.1.15 in the 1.0.8 manual describes building an association
table
for many-to-many relationships. My application has a table that
associates
multiple locations with multiple types of data collected. That is, each
location can have multiple types of data collected, and each data type
can
be collected at multiple locations. There are other attributes associated
with each row. Will the following table declaration work? Or, do I
separate
site and param into a separate table from the other columns?
conceptually not a big deal but API-wise has many mistakes. Sequences
don't work with unicode, there is no value parameter, the table has no
primary key.
--
class Monitoring(Base):
__table_name__ = 'monitoring'
permit_nbr = Column(Unicode(24), ForeignKey('permits.nbr'))
permit = relationship(Permits, back_populates = 'locations')
data_type = Column(Unicode(16), CheckConstraint(data_type IN
('surface \
water', 'ground water', 'air', 'benthos', 'fish', 'microbes', \
'physical','weather')))
site = Column(Unicode(12), Sequence('location_seq'), nullable =
False, \
unique = True, ForeignKen('locations.site_id'))
param = Column(Unicode(24), nullable = False, unique = True,
ForeignKey(\
'conditions.param_name'))
mcl = Column(Float)
monit_freq = Column(Unicode(12), value = 'Month', nullable = False, \
CheckConstraint(monitor_freq IN ('Hour','Shift','Day','Week','2x
month',\
'Month','Quarter','Semi-Annual','Annual')))
rpt_freq = Column(Unicode(12), value = 'Month', nullable = False, \
CheckConstraint(rpt_freq IN ('Hour','Shift','Day','Week','2x
month',\
'Month','Quarter','Semi-Annual','Annual')))
start_date = Column(Date, value = today, nullable = False)
end_date = Column(Date)
site = relationship(Locations, back_populates = 'monitoring')
param = relationship(Conditions, back_populates = 'monitoring')
-
Rich
--
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] Many-to-Many Table
On Thu, 23 Jul 2015, Mike Bayer wrote: conceptually not a big deal but API-wise has many mistakes. Sequences don't work with unicode, there is no value parameter, the table has no primary key. Mike, True, I don't want a sequence but the location ID, there needs to be a value column, and others should be moved into the parameter table. I thought a many-to-many table did not need a primary key because it would be accessed by (in this case) location or paramter. Thanks, Rich
[sqlalchemy] Many-to-Many Table
Originally posted here on June 4th, but no one responded. I'm now back on
this project and this should be the last table I need to add to the schema.
I would appreciate your review of the below class and whether it is good to
go or needs your modifications.
Section 2.1.15 in the 1.0.8 manual describes building an association table
for many-to-many relationships. My application has a table that associates
multiple locations with multiple types of data collected. That is, each
location can have multiple types of data collected, and each data type can
be collected at multiple locations. There are other attributes associated
with each row. Will the following table declaration work? Or, do I separate
site and param into a separate table from the other columns?
--
class Monitoring(Base):
__table_name__ = 'monitoring'
permit_nbr = Column(Unicode(24), ForeignKey('permits.nbr'))
permit = relationship(Permits, back_populates = 'locations')
data_type = Column(Unicode(16), CheckConstraint(data_type IN ('surface \
water', 'ground water', 'air', 'benthos', 'fish', 'microbes', \
'physical','weather')))
site = Column(Unicode(12), Sequence('location_seq'), nullable = False, \
unique = True, ForeignKen('locations.site_id'))
param = Column(Unicode(24), nullable = False, unique = True, ForeignKey(\
'conditions.param_name'))
mcl = Column(Float)
monit_freq = Column(Unicode(12), value = 'Month', nullable = False, \
CheckConstraint(monitor_freq IN ('Hour','Shift','Day','Week','2x month',\
'Month','Quarter','Semi-Annual','Annual')))
rpt_freq = Column(Unicode(12), value = 'Month', nullable = False, \
CheckConstraint(rpt_freq IN ('Hour','Shift','Day','Week','2x month',\
'Month','Quarter','Semi-Annual','Annual')))
start_date = Column(Date, value = today, nullable = False)
end_date = Column(Date)
site = relationship(Locations, back_populates = 'monitoring')
param = relationship(Conditions, back_populates = 'monitoring')
-
Rich
[sqlalchemy] Many-to-Many Table
Section 2.1.15 in the 1.0.4 manual describes building an association table
for many-to-many relationships. My application has a table that associates
multiple locations with multiple types of data collected. That is, each
location can have multiple types of data collected, and each data type can
be collected at multiple locations. There are other attributes associated
with each row. Will the following table declaration work? Or, do I separate
site and param into a separate table from the other columns?
--
class Monitoring(Base):
__table_name__ = 'monitoring'
permit_nbr = Column(Unicode(24), ForeignKey('permits.nbr'))
permit = relationship(Permits, back_populates = 'locations')
data_type = Column(Unicode(16), CheckConstraint(data_type IN ('surface \
water', 'ground water', 'air', 'benthos', 'fish', 'microbes', \
'physical','weather')))
site = Column(Unicode(12), Sequence('location_seq'), nullable = False, \
unique = True, ForeignKen('locations.site_id'))
param = Column(Unicode(24), nullable = False, unique = True, ForeignKey(\
'conditions.param_name'))
mcl = Column(Float)
monit_freq = Column(Unicode(12), value = 'Month', nullable = False, \
CheckConstraint(monitor_freq IN ('Hour','Shift','Day','Week','2x month',\
'Month','Quarter','Semi-Annual','Annual')))
rpt_freq = Column(Unicode(12), value = 'Month', nullable = False, \
CheckConstraint(rpt_freq IN ('Hour','Shift','Day','Week','2x month',\
'Month','Quarter','Semi-Annual','Annual')))
start_date = Column(Date, value = today, nullable = False)
end_date = Column(Date)
site = relationship(Locations, back_populates = 'monitoring')
param = relationship(Conditions, back_populates = 'monitoring')
-
Rich
Re: [sqlalchemy] SQLAlchemy Many-to-many table with multiple foreign key entries
Ahah, got it. I added a primary key to monthlyusage and now it works. Thanks! On Friday, November 1, 2013 12:44:10 AM UTC-4, Michael Bayer wrote: I don’t see a primary key on your monthlyusage table, which is where the issue is occurring, so it depends on which col is the PK. typically the pk would be a composite of the company_id and domain_id columns. On Oct 31, 2013, at 10:20 PM, thinkwel...@gmail.com javascript: wrote: http://stackoverflow.com/questions/19716518/sqlalchemy-many-to-many-table-with-multiple-foreign-key-entires# I'm new with sqlalchemy and I want to do this as simply as possible, yet correctly. I want to track domain use across multiple companies on a monthly basis, so I set up the following tables: -- 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] SQLAlchemy Many-to-many table with multiple foreign key entries
http://stackoverflow.com/questions/19716518/sqlalchemy-many-to-many-table-with-multiple-foreign-key-entires#
I'm new with sqlalchemy and I want to do this as simply as possible, yet
correctly. I want to track domain use across multiple companies on a
monthly basis, so I set up the following tables:
class Company(Base):
__tablename__ = 'company'
id = Column(Integer, primary_key = True)
name = Column('name', String)
class Domains(Base):
__tablename__ = 'domains'
id = Column(Integer, primary_key=True)
name = Column('name', String, unique=True)
class MonthlyUsage(Base):
'''
Track domain usage across all
companies on a monthly basis.
'''
__tablename__ = 'monthlyusage'
month = Column(DateTime)
company_id = Column(Integer, ForeignKey('company.id'))
domain_id = Column(Integer, ForeignKey('domains.id'))
# ...other columns snipped out...
company = relationship('Company', backref='company_assoc')
domain = relationship('Domains', backref='domain_assoc')
This works fine, until I add usage details for the second month. Then I get
duplicate key value errors:
*sqlalchemy.exc.IntegrityError: (IntegrityError) duplicate key value
violates unique constraint monthlyusage_pkey*
Does this mean I have to split out the monthlyusage into another m2m
table? That seems unnecessarily complicated, since all that needs to be
unique is the month, company_id, and domain_id fields.
Any suggestions for my layout here, to keep it as simple as possible, yet
still correct?
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/groups/opt_out.
Re: [sqlalchemy] SQLAlchemy Many-to-many table with multiple foreign key entries
I don’t see a primary key on your monthlyusage table, which is where the issue
is occurring, so it depends on which col is the PK. typically the pk would be
a composite of the company_id and domain_id columns.
On Oct 31, 2013, at 10:20 PM, thinkwelldesi...@gmail.com wrote:
I'm new with sqlalchemy and I want to do this as simply as possible, yet
correctly. I want to track domain use across multiple companies on a monthly
basis, so I set up the following tables:
class Company(Base):
__tablename__ = 'company'
id = Column(Integer, primary_key = True)
name = Column('name', String)
class Domains(Base):
__tablename__ = 'domains'
id = Column(Integer, primary_key=True)
name = Column('name', String, unique=True)
class MonthlyUsage(Base):
'''
Track domain usage across all
companies on a monthly basis.
'''
__tablename__ = 'monthlyusage'
month = Column(DateTime)
company_id = Column(Integer, ForeignKey('company.id'))
domain_id = Column(Integer, ForeignKey('domains.id'))
# ...other columns snipped out...
company = relationship('Company', backref='company_assoc')
domain = relationship('Domains', backref='domain_assoc')
This works fine, until I add usage details for the second month. Then I get
duplicate key value errors:
*sqlalchemy.exc.IntegrityError: (IntegrityError) duplicate key value violates
unique constraint monthlyusage_pkey
Does this mean I have to split out the monthlyusage into another m2m table?
That seems unnecessarily complicated, since all that needs to be unique is
the month, company_id, and domain_id fields.
Any suggestions for my layout here, to keep it as simple as possible, yet
still correct?
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/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.
For more options, visit https://groups.google.com/groups/opt_out.
