[sqlalchemy] Polymorphic inheritance with dynamically created tables: issues with undefined polymorphic identity

[Radu Suciu]

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? 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?

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:

class Dataset(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:

def create_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)
}

return type(
dataset_name,
(Dataset,),
props
)

The error that I run up against if I neglect running my dataset creation 
function:

AssertionError: No such polymorphic_identity 1 is defined

and the larger stack trace:

  File "/env/lib/python3.5/site-packages/sqlalchemy/orm/loading.py", line 86
, in instances
util.raise_from_cause(err)
  File "/env/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 
200, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/env/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 
184, in reraise
raise value
  File "/env/lib/python3.5/site-packages/sqlalchemy/orm/loading.py", line 71
, in instances
rows = [proc(row) for row in fetch]
  File "/env/lib/python3.5/site-packages/sqlalchemy/orm/loading.py", line 71
, in 
rows = [proc(row) for row in fetch]
  File "/env/lib/python3.5/site-packages/sqlalchemy/orm/loading.py", line 
594, in polymorphic_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, in configure_subclass_mapper
discriminator)
AssertionError: No such polymorphic_identity 1 is defined

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.
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] Help with a custom "seconds_interval()" construct

[Kent]

I'm interested in being able to use second time intervals on PostgreSQL, 
Oracle 8 *and *modern Oracle versions, agnostically.

The native python timedelta works great for the postgres and cx_Oracle 
drivers.  However *cx_Oracle connected to Oracle 8 won't support this* so 
I'm building my own construct to help.

We want support for something like this:

*current_timestamp() + timedelta(seconds=15)*

(Assume "current_timestamp()" works on all these databases -- I've already 
built that.)

*PostgreSQL*, works great with python's timedelta:
>>> DBSession.execute(select([current_timestamp() + 
timedelta(seconds=15)])).scalar()
MainThread: 09:15:20,871 INFO  [sqlalchemy.engine.base.Engine] BEGIN 
(implicit)
MainThread: 09:15:20,872 INFO  [sqlalchemy.engine.base.Engine] SELECT 
localtimestamp + %(param_1)s AS anon_1
MainThread: 09:15:20,872 INFO  [sqlalchemy.engine.base.Engine] {'param_1': 
datetime.timedelta(0, 15)}
datetime.datetime(2016, 5, 28, 9, 15, 35, 872999)

*Modern Oracle*, works great with python's timedelta:
>>> DBSession.execute(select([current_timestamp() + 
timedelta(seconds=15)])).scalar()
MainThread: 09:28:15,009 INFO  [sqlalchemy.engine.base.Engine] BEGIN 
(implicit)
MainThread: 09:28:15,010 INFO  [sqlalchemy.engine.base.Engine] SELECT 
systimestamp + :param_1 AS anon_1 FROM DUAL
MainThread: 09:28:15,010 INFO  [sqlalchemy.engine.base.Engine] {'param_1': 
datetime.timedelta(0, 15)}
datetime.datetime(2016, 5, 28, 9, 28, 30, 11530)

*Oracle 8: *no
>>> DBSession.execute(select([current_timestamp() + 
timedelta(seconds=15)])).scalar()
MainThread: 08:29:37,659 INFO  [sqlalchemy.engine.base.Engine] SELECT 
sysdate + :param_1 AS anon_1 FROM DUAL
MainThread: 08:29:37,659 INFO  [sqlalchemy.engine.base.Engine] {'param_1': 
datetime.datetime(1970, 1, 1, 0, 0, 15)}
Traceback (most recent call last):
  File "", line 1, in 
  File 
"/home/train/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.7.607kbdev-py2.6-linux-x86_64.egg/sqlalchemy/orm/scoping.py",
 
line 114, in do
return getattr(self.registry(), name)(*args, **kwargs)
  File 
"/home/train/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.7.607kbdev-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py",
 
line 804, in execute
clause, params or {})
  File 
"/home/train/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.7.607kbdev-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py",
 
line 1450, in execute
params)
  File 
"/home/train/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.7.607kbdev-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py",
 
line 1583, in _execute_clauseelement
compiled_sql, distilled_params
  File 
"/home/train/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.7.607kbdev-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py",
 
line 1697, in _execute_context
context)
  File 
"/home/train/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.7.607kbdev-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py",
 
line 1690, in _execute_context
context)
  File 
"/home/train/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.7.607kbdev-py2.6-linux-x86_64.egg/sqlalchemy/engine/default.py",
 
line 335, in do_execute
cursor.execute(statement, parameters)
DatabaseError: (DatabaseError) ORA-00975: date + date not allowed
 'SELECT sysdate + :param_1 AS anon_1 FROM DUAL' {'param_1': 
datetime.datetime(1970, 1, 1, 0, 0, 15)}


I've already constructed a class called "seconds_interval" that works for 
Oracle 8, but I don't know how to use the default behavior when not Oracle 
8:

*Oracle 8 usage:*
>>> DBSession.execute(select([current_timestamp() + *seconds_interval(15)*
])).scalar()
MainThread: 08:37:06,539 INFO  [sqlalchemy.engine.base.Engine] SELECT 
sysdate +* :seconds_1/86400 *AS anon_1 FROM DUAL
MainThread: 08:37:06,539 INFO  [sqlalchemy.engine.base.Engine] 
{'seconds_1': 15}
datetime.datetime(2016, 5, 28, 8, 37, 25)


class seconds_interval(FunctionElement):
type = Interval()
name = 'seconds'

def __init__(self, *args, **kwargs):
FunctionElement.__init__(self, *args, **kwargs)
self.seconds = args[0]

@compiles(seconds_interval)
def compile_seconds_interval(element, compiler, **kw):
if compiler.dialect.name == 'oracle' and \
   compiler.dialect.server_version_info < (9,):
return "%s/86400" % compiler.process(element.clauses)
else:
# use timedelta as bind param
from datetime import timedelta
td = timedelta(seconds=element.seconds)
*return ...???*

Can anyone help me with the else: above to use the native python timedelta 
as a bind param?  (Or trash it completely if there is a better strategy?)

Much thanks in advance!
Kent



-- 
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