there are also ways to create exactly the "SELECT INTO" thing if you really want that, it would involve creating a custom construct using the API described at http://docs.sqlalchemy.org/en/latest/core/compiler.html. However it would still look a lot like the above because we still need to build a table-like object with Column objects on it, if you want to continue using that temp table as a SQLAlchemy expression language object.
Also of course all of this SQL can be sent just as a straight string with session.execute() if you just need to do it once. On Sat, Dec 16, 2017 at 9:18 PM, Mike Bayer <[email protected]> wrote: > "SELECT INTO #temptable" is a SQL server specific trick that just > creates #temptable on the fly. SQL standard is INSERT from SELECT. > Since you're looking to dynamically create tmptbl, this implies that > there needs to be a table() object generated given the set of columns > from the original table. While "SELECT INTO" is a SQL server trick > that creates the table on the fly, this doesn't help us because we > don't get back a "tmptbl" object with columns. So there's not much > point in using it. > > So we go to the recipe I already gave you on the issue you posted: > https://bitbucket.org/zzzeek/sqlalchemy/issues/4148/feature-request-mssql-select-into#comment-41933692 > > you'd like the columns to be dynamic. OK, here's that: > > from sqlalchemy import * > from sqlalchemy.sql import quoted_name > from sqlalchemy.orm import Session > from sqlalchemy.ext.declarative import declarative_base > > > def select_into(name, query): > temp_table = Table( > quoted_name(name, quote=False), > MetaData(), > *[Column(attr['name'], attr['type']) for attr > in query.column_descriptions] > ) > conn = query.session.connection() > temp_table.create(conn) > conn.execute(temp_table.insert().from_select( > [attr['name'] for attr in query.column_descriptions], query.statement > )) > > return temp_table > > > Base = declarative_base() > > > class A(Base): > __tablename__ = 'a' > id = Column(Integer, primary_key=True) > x = Column(Integer) > y = Column(Integer) > > e = create_engine("mssql+pymssql://scott:tiger^5HHH@mssql2017/test", > echo=True) > Base.metadata.drop_all(e) > Base.metadata.create_all(e) > > s = Session(e) > > s.add_all([A(x=1, y=2), A(x=2, y=3)]) > s.commit() > > q = s.query(A.x, A.y) > > tmp_table = select_into("#temp", q) > > subqu1 = s.query(tmp_table).filter(tmp_table.c.x == A.x).exists() > print s.query(A).filter(subqu1).all() > > > output: > > CREATE TABLE #temp ( > x INTEGER NULL, > y INTEGER NULL > ) > > > INSERT INTO #temp (x, y) SELECT a.x, a.y > FROM a > SELECT a.id AS a_id, a.x AS a_x, a.y AS a_y > FROM a > WHERE EXISTS (SELECT 1 > FROM #temp > WHERE #temp.x = a.x) > > > > > > > On Sat, Dec 16, 2017 at 3:47 AM, Евгений Рымарев > <[email protected]> wrote: >> Hello! >> How i can create something like that: >> SELECT column >> INTO #tmptbl >> FROM table >> >> SELECT * >> FROM table2 t2 >> WHERE EXISTS(SELECT 1 >> FROM #tmptbl >> WHERE column = t2.column) >> >> in SQLAlchemy I imagine it like this: >> tmptbl = session.query(table.c.column).into('#tmptbl') >> subqu1 = session.query(tmptbl).filter(tmptbl.c.column == >> table2.c.column).exists() >> result = session.query(table2).filter(subqu1) >> >> -- >> 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 [email protected]. >> To post to this group, send email to [email protected]. >> 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
