On 05/29/2016 01:18 AM, Radu Suciu wrote:
I have a base Dataset class that I'd like to dynamically inherit from.
When a user uploads a new dataset, I create a new table as shown below,
and populate it with data. My two most common use-cases:
1. Retrieving entire dataset. I want to return dataset_x where x is an
integer that corresponds to experiment_id
2. Searching through all datasets. I query against the parent datasets
table and apply filters as needed.
The issue is that when I need to retrieve a dataset, I need to run
through my create_dataset function once more. This isn't such a big deal
for use case #1, I just do create_dataset(x). However, for case #2, I
need to loop through the whole set of child tables. My question is: is
there a way around this?
i dont think so. i think if your program is creating tables, it needs
to run that create_dataset() function right there. If the program
starts up and there are existing tables, it should, as soon as it runs,
locate all of these tables and possible "dataset" values and run
create_dataset for each of them.
Additionally, please tell me if what I'm doing
is basically pointless.. my child classes do not add additional
attributes. I chose this route because it seemed to make sense based on
my two use cases above. Each dataset is on the order of 10,000 rows, and
I expect to have on the order of 10,000 datasets. Should I just stick to
having an additional column for experiment_id and just filter against
then in case #1?
yes. 10000 rows is nothing and creating tables on the fly is generally
looked down upon in database design circles. If you have less than
100K users then you'll have less than 1B rows and 1B rows is pretty
manageable in a single table on a single server unless you're storing
BLOBs (which i would say, don't store those either). You just need to
make sure you are indexing properly for the queries you are doing.
I should note that I am using Flask-SQLAlchemy but I think this maps
well to the pure SQLAlchemy case.. Please tell me if I'm off base on
that assumption based on the below code. My parent class:
|
classDataset(db.Model):
__tablename__ ='datasets'
id =Column(db.Integer,primary_key=True)
foo =Column(db.Integer)
bar =Column(db.String(20))
experiment_id =Column(db.Integer)
__mapper_args__ ={
'polymorphic_identity':'dataset',
'polymorphic_on':experiment_id
}
|
How I create new child classes dynamically:
|
defcreate_dataset(experiment_id):
dataset_name ='dataset_{}'.format(experiment_id)
props ={
'__tablename__':dataset_name,
'__mapper_args__':{'polymorphic_identity':experiment_id},
'id':db.Column(db.Integer,db.ForeignKey('datasets.id'),primary_key=True)
}
returntype(
dataset_name,
(Dataset,),
props
)
|
The error that I run up against if I neglect running my dataset creation
function:
|
AssertionError:Nosuch polymorphic_identity 1isdefined
|
and the larger stack trace:
|
File"/env/lib/python3.5/site-packages/sqlalchemy/orm/loading.py",line
86,ininstances
util.raise_from_cause(err)
File"/env/lib/python3.5/site-packages/sqlalchemy/util/compat.py",line
200,inraise_from_cause
reraise(type(exception),exception,tb=exc_tb,cause=cause)
File"/env/lib/python3.5/site-packages/sqlalchemy/util/compat.py",line
184,inreraise
raisevalue
File"/env/lib/python3.5/site-packages/sqlalchemy/orm/loading.py",line
71,ininstances
rows =[proc(row)forrow infetch]
File"/env/lib/python3.5/site-packages/sqlalchemy/orm/loading.py",line
71,in<listcomp>
rows =[proc(row)forrow infetch]
File"/env/lib/python3.5/site-packages/sqlalchemy/orm/loading.py",line
594,inpolymorphic_instance
_instance =polymorphic_instances[discriminator]
File"/env/lib/python3.5/site-packages/sqlalchemy/util/_collections.py",line
728,in__missing__
self[key]=val =self.creator(key)
File"/env/lib/python3.5/site-packages/sqlalchemy/orm/loading.py",line
578,inconfigure_subclass_mapper
discriminator)
AssertionError:Nosuch polymorphic_identity 1isdefined
|
Thank you for the wonderful ORM!
--
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 https://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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
