On Mon, Apr 15, 2019 at 9:53 AM Markus Elfring <markus.elfr...@web.de> wrote: >> >> insert.from_select() will accept the Query object directly. > > > Such data addition depends on the detail that a table object exists before, > doesn't it?
INSERT FROM SELECT refers to an existing table, yes, I mention it because you referred to this construct in one of the stack overflow answers you mentioned. > > >> >> if using the CreateTableAs recipe, > > > Do you refer to any additional information source here? this is from the StackOverflow answer you referred towards of which I am also the author: https://stackoverflow.com/questions/30575111/how-to-create-a-new-table-from-select-statement-in-sqlalchemy#answer-30577608 > > >> >> it can be modified to coerce a Query passed to it into >> a Core statement internally. > > > Do you suggest to work with an extra conversion approach for a while? If you're asking, if you should pass query.statement to the hypotheical CreateTableAs construct, or if the CreateTableAs construct should do it, it's your choice. It is a goal of Core objects that an ORM query passed can be interpreted as its underlying SELECT statement which suggests the latter. > > I would prefer the mapping of a dynamically generated database table to an > additional Python object instead. I'm beginning to understand what it is you might want to do. The most expedient approach is to use the CreateTableAs construct, then reflect the new database table using autoload, or to build the Table object directly: q = session.query(<things>) session.execute(CreateTableAs('t2', q)) class T2(Base): __table__ = Table('t2', Base.metadata, autoload_with=session.connection()) Note the above requires that the Table has a primary key, however, I'm not sure if CREATE TABLE .. AS also generates primary key constraints on the new table. If not, these need to be added as well: class T2(Base): __table__ = Table('t2', Base.metadata, autoload_with=session.connection()) __mapper_args__ = {"primary_key": ["id", "a"]} # names of columns to be considered primary key To create the table directly, the basic idea would be like the following, where I'm also trying to copy out which columns would be considered a primary key: def mapping_for_query(Base, q, name): select = q.statement # dynamically create a Table object, try to copy primary key flag also from queried tables table = Table(name, Base.metadata, *[Column(col.name, col.type, primary_key=col.primary_key) for col in select.inner_columns]) # dynamically create a mapped class against this table return type(name, (Base, ), {"__table__": table}) With the above, you also need to run the CreateTableAs() construct to actually generate the table in the database. These various units could likely be composed into a polished function that is fairly usable for simple queries. Determining the "primary key" from a SELECT statement however is not easily generalizable, so your functions may need to be passed additional hints from the programmer as to which columns would be part of the primary key. The ORM cannot map a class without a primary key configured. > > Regards, > Markus > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > 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 - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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.
