Hello, I'm very basic at SQLalchemy and i faced this situation. I need to query data from one table and insert that into another table. I tried using *from_select* in this: sel = select([table1.c.a, table1.c.b]).where(table1.c.c > 5) ins = table2.insert().from_select(['a', 'b'], sel) But *from_select* requires sqlalchemy version 0.8.3 and in my flask app i'm using 0.8.2 since 0.8.3 is not available right now. I need a way to query data from one table and insert that into another table. Please suggest a possible solution (for sqlalchemy 0.8.2 only) Regards
Why don't you use ORM instead of core functions? 2013/7/16 Uday Choudhary <mobulite@gmail.com>: > Hello, > > I'm very basic at SQLalchemy and i faced this situation. > > I need to query data from one table and insert that into another table. I > tried using from_select in this: > sel = select([table1.c.a, table1.c.b]).where(table1.c.c > 5) > ins = table2.insert().from_select(['a', 'b'], sel) > > But from_select requires sqlalchemy version 0.8.3 and in my flask app i'm > using 0.8.2 since 0.8.3 is not available right now. > > I need a way to query data from one table and insert that into another > table. > > Please suggest a possible solution (for sqlalchemy 0.8.2 only) > > Regards -- Mauricio de Abreu Antunes Mobile: (51)930-74-525 Skype: mauricio.abreua
I'm trying to use: ins = Albumold.__table__.insert() res = session.query(Album).order_by(Album.title).all() conn = engine.connect() conn.execute(ins, res) Now issue is in last line, i want to insert query *res* data into *ins * which has *Albumold* table. How can i insert *res* into a table? On Tue, Jul 16, 2013 at 6:21 PM, Mauricio de Abreu Antunes < mauricio.abreua@gmail.com> wrote: > Why don't you use ORM instead of core functions? > > 2013/7/16 Uday Choudhary <mobulite@gmail.com>: > > Hello, > > > > I'm very basic at SQLalchemy and i faced this situation. > > > > I need to query data from one table and insert that into another table. I > > tried using from_select in this: > > sel = select([table1.c.a, table1.c.b]).where(table1.c.c > 5) > > ins = table2.insert().from_select(['a', 'b'], sel) > > > > But from_select requires sqlalchemy version 0.8.3 and in my flask app i'm > > using 0.8.2 since 0.8.3 is not available right now. > > > > I need a way to query data from one table and insert that into another > > table. > > > > Please suggest a possible solution (for sqlalchemy 0.8.2 only) > > > > Regards > > > > -- > Mauricio de Abreu Antunes > Mobile: (51)930-74-525 > Skype: mauricio.abreua >
Well, I don't know why you are using Core functions. Do you have models? Meta tables derive from Models SQLAlchemy class? Show them to me so I can help you by building your query and insert with ORM features. By the way, to insert a new object into a table you would need a session.add method, no? 2013/7/16 Uday Choudhary <mobulite@gmail.com>: > I'm trying to use: > > ins = Albumold.__table__.insert() > res = session.query(Album).order_by(Album.title).all() > conn = engine.connect() > conn.execute(ins, res) > > Now issue is in last line, i want to insert query res data into ins which > has Albumold table. > > How can i insert res into a table? > > > On Tue, Jul 16, 2013 at 6:21 PM, Mauricio de Abreu Antunes > <mauricio.abreua@gmail.com> wrote: >> >> Why don't you use ORM instead of core functions? >> >> 2013/7/16 Uday Choudhary <mobulite@gmail.com>: >> > Hello, >> > >> > I'm very basic at SQLalchemy and i faced this situation. >> > >> > I need to query data from one table and insert that into another table. >> > I >> > tried using from_select in this: >> > sel = select([table1.c.a, table1.c.b]).where(table1.c.c > 5) >> > ins = table2.insert().from_select(['a', 'b'], sel) >> > >> > But from_select requires sqlalchemy version 0.8.3 and in my flask app >> > i'm >> > using 0.8.2 since 0.8.3 is not available right now. >> > >> > I need a way to query data from one table and insert that into another >> > table. >> > >> > Please suggest a possible solution (for sqlalchemy 0.8.2 only) >> > >> > Regards >> >> >> >> -- >> Mauricio de Abreu Antunes >> Mobile: (51)930-74-525 >> Skype: mauricio.abreua > > -- Mauricio de Abreu Antunes Mobile: (51)930-74-525 Skype: mauricio.abreua
Yeah i am using models and import them using "*from table_def import Album, Artist*" *table_def.py as:* from sqlalchemy import create_engine, ForeignKey from sqlalchemy import Column, Date, Integer, String from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import relationship, backref engine = create_engine('sqlite:///mymusic.db', echo=True) Base = declarative_base() ######################################################################## class Artist(Base): """""" __tablename__ = "artists" id = Column(Integer, primary_key=True) name = Column(String) #---------------------------------------------------------------------- def __init__(self, name): """""" self.name = name ######################################################################## class Album(Base): """""" __tablename__ = "albums" id = Column(Integer, primary_key=True) title = Column(String) release_date = Column(Date) publisher = Column(String) media_type = Column(String) artist_id = Column(Integer, ForeignKey("artists.id")) artist = relationship("Artist", backref=backref("albums", order_by=id)) #---------------------------------------------------------------------- def __init__(self, title, release_date, publisher, media_type): """""" self.title = title self.release_date = release_date self.publisher = publisher self.media_type = media_type class Albumold(Base): """""" __tablename__ = "albumold" id = Column(Integer, primary_key=True) title = Column(String) release_date = Column(Date) publisher = Column(String) media_type = Column(String) artist_id = Column(Integer, ForeignKey("artists.id")) artist = relationship("Artist", backref=backref("oldalbums", order_by=id)) #---------------------------------------------------------------------- def __init__(self, title="", release_date="", publisher="", media_type=""): """""" self.title = title self.release_date = release_date self.publisher = publisher self.media_type = media_type # create tables Base.metadata.create_all(engine) ///////////////////////////////////////////////////////// I need to fetch data from table *Album* and insert into *Albumold* table after sorting them according to release_date. Note: I need to know how it can work, so that i can apply this script( method) into my flask app models (similar to this but can't share them publically ) which requires such script to fetch data from various tables and insert into one main table. Regards On Tue, Jul 16, 2013 at 6:38 PM, Mauricio de Abreu Antunes < mauricio.abreua@gmail.com> wrote: > Well, I don't know why you are using Core functions. > > Do you have models? Meta tables derive from Models SQLAlchemy class? > > Show them to me so I can help you by building your query and insert > with ORM features. > > By the way, to insert a new object into a table you would need a > session.add method, no? > > 2013/7/16 Uday Choudhary <mobulite@gmail.com>: > > I'm trying to use: > > > > ins = Albumold.__table__.insert() > > res = session.query(Album).order_by(Album.title).all() > > conn = engine.connect() > > conn.execute(ins, res) > > > > Now issue is in last line, i want to insert query res data into ins > which > > has Albumold table. > > > > How can i insert res into a table? > > > > > > On Tue, Jul 16, 2013 at 6:21 PM, Mauricio de Abreu Antunes > > <mauricio.abreua@gmail.com> wrote: > >> > >> Why don't you use ORM instead of core functions? > >> > >> 2013/7/16 Uday Choudhary <mobulite@gmail.com>: > >> > Hello, > >> > > >> > I'm very basic at SQLalchemy and i faced this situation. > >> > > >> > I need to query data from one table and insert that into another > table. > >> > I > >> > tried using from_select in this: > >> > sel = select([table1.c.a, table1.c.b]).where(table1.c.c > 5) > >> > ins = table2.insert().from_select(['a', 'b'], sel) > >> > > >> > But from_select requires sqlalchemy version 0.8.3 and in my flask app > >> > i'm > >> > using 0.8.2 since 0.8.3 is not available right now. > >> > > >> > I need a way to query data from one table and insert that into another > >> > table. > >> > > >> > Please suggest a possible solution (for sqlalchemy 0.8.2 only) > >> > > >> > Regards > >> > >> > >> > >> -- > >> Mauricio de Abreu Antunes > >> Mobile: (51)930-74-525 > >> Skype: mauricio.abreua > > > > > > > > -- > Mauricio de Abreu Antunes > Mobile: (51)930-74-525 > Skype: mauricio.abreua >
Nice, thanks. You already have your models and definitions. It means that you can use ORM features. Maybe you want to try it: http://docs.sqlalchemy.org/en/rel_0_8/core/compiler.html#compiling-sub-elements-of-a-custom-expression-construct 2013/7/16 Uday Choudhary <mobulite@gmail.com>: > Yeah i am using models and import them using "from table_def import Album, > Artist" > > table_def.py as: > from sqlalchemy import create_engine, ForeignKey > from sqlalchemy import Column, Date, Integer, String > from sqlalchemy.ext.declarative import declarative_base > from sqlalchemy.orm import relationship, backref > > engine = create_engine('sqlite:///mymusic.db', echo=True) > Base = declarative_base() > > ######################################################################## > class Artist(Base): > """""" > __tablename__ = "artists" > > id = Column(Integer, primary_key=True) > name = Column(String) > > #---------------------------------------------------------------------- > def __init__(self, name): > """""" > self.name = name > > ######################################################################## > class Album(Base): > """""" > __tablename__ = "albums" > > id = Column(Integer, primary_key=True) > title = Column(String) > release_date = Column(Date) > publisher = Column(String) > media_type = Column(String) > > artist_id = Column(Integer, ForeignKey("artists.id")) > artist = relationship("Artist", backref=backref("albums", order_by=id)) > > #---------------------------------------------------------------------- > def __init__(self, title, release_date, publisher, media_type): > """""" > self.title = title > self.release_date = release_date > self.publisher = publisher > self.media_type = media_type > > class Albumold(Base): > """""" > __tablename__ = "albumold" > > id = Column(Integer, primary_key=True) > title = Column(String) > release_date = Column(Date) > publisher = Column(String) > media_type = Column(String) > > artist_id = Column(Integer, ForeignKey("artists.id")) > artist = relationship("Artist", backref=backref("oldalbums", > order_by=id)) > > #---------------------------------------------------------------------- > def __init__(self, title="", release_date="", publisher="", > media_type=""): > """""" > self.title = title > self.release_date = release_date > self.publisher = publisher > self.media_type = media_type > > # create tables > Base.metadata.create_all(engine) > > ///////////////////////////////////////////////////////// > > I need to fetch data from table Album and insert into Albumold table after > sorting them according to release_date. > > Note: I need to know how it can work, so that i can apply this script( > method) into my flask app models (similar to this but can't share them > publically ) which requires such script to fetch data from various tables > and insert into one main table. > > Regards > > > > > On Tue, Jul 16, 2013 at 6:38 PM, Mauricio de Abreu Antunes > <mauricio.abreua@gmail.com> wrote: >> >> Well, I don't know why you are using Core functions. >> >> Do you have models? Meta tables derive from Models SQLAlchemy class? >> >> Show them to me so I can help you by building your query and insert >> with ORM features. >> >> By the way, to insert a new object into a table you would need a >> session.add method, no? >> >> 2013/7/16 Uday Choudhary <mobulite@gmail.com>: >> > I'm trying to use: >> > >> > ins = Albumold.__table__.insert() >> > res = session.query(Album).order_by(Album.title).all() >> > conn = engine.connect() >> > conn.execute(ins, res) >> > >> > Now issue is in last line, i want to insert query res data into ins >> > which >> > has Albumold table. >> > >> > How can i insert res into a table? >> > >> > >> > On Tue, Jul 16, 2013 at 6:21 PM, Mauricio de Abreu Antunes >> > <mauricio.abreua@gmail.com> wrote: >> >> >> >> Why don't you use ORM instead of core functions? >> >> >> >> 2013/7/16 Uday Choudhary <mobulite@gmail.com>: >> >> > Hello, >> >> > >> >> > I'm very basic at SQLalchemy and i faced this situation. >> >> > >> >> > I need to query data from one table and insert that into another >> >> > table. >> >> > I >> >> > tried using from_select in this: >> >> > sel = select([table1.c.a, table1.c.b]).where(table1.c.c > 5) >> >> > ins = table2.insert().from_select(['a', 'b'], sel) >> >> > >> >> > But from_select requires sqlalchemy version 0.8.3 and in my flask app >> >> > i'm >> >> > using 0.8.2 since 0.8.3 is not available right now. >> >> > >> >> > I need a way to query data from one table and insert that into >> >> > another >> >> > table. >> >> > >> >> > Please suggest a possible solution (for sqlalchemy 0.8.2 only) >> >> > >> >> > Regards >> >> >> >> >> >> >> >> -- >> >> Mauricio de Abreu Antunes >> >> Mobile: (51)930-74-525 >> >> Skype: mauricio.abreua >> > >> > >> >> >> >> -- >> Mauricio de Abreu Antunes >> Mobile: (51)930-74-525 >> Skype: mauricio.abreua > > -- Mauricio de Abreu Antunes Mobile: (51)930-74-525 Skype: mauricio.abreua
Tip: IMHO I would not create a Albumold, but create a new field into Album and create a daily process to define if a album is old. Or a specific out-of-database rule to manage it. 2013/7/16 Mauricio de Abreu Antunes <mauricio.abreua@gmail.com>: > Nice, thanks. > > You already have your models and definitions. It means that you can > use ORM features. > > Maybe you want to try it: > > http://docs.sqlalchemy.org/en/rel_0_8/core/compiler.html#compiling-sub-elements-of-a-custom-expression-construct > > 2013/7/16 Uday Choudhary <mobulite@gmail.com>: >> Yeah i am using models and import them using "from table_def import Album, >> Artist" >> >> table_def.py as: >> from sqlalchemy import create_engine, ForeignKey >> from sqlalchemy import Column, Date, Integer, String >> from sqlalchemy.ext.declarative import declarative_base >> from sqlalchemy.orm import relationship, backref >> >> engine = create_engine('sqlite:///mymusic.db', echo=True) >> Base = declarative_base() >> >> ######################################################################## >> class Artist(Base): >> """""" >> __tablename__ = "artists" >> >> id = Column(Integer, primary_key=True) >> name = Column(String) >> >> #---------------------------------------------------------------------- >> def __init__(self, name): >> """""" >> self.name = name >> >> ######################################################################## >> class Album(Base): >> """""" >> __tablename__ = "albums" >> >> id = Column(Integer, primary_key=True) >> title = Column(String) >> release_date = Column(Date) >> publisher = Column(String) >> media_type = Column(String) >> >> artist_id = Column(Integer, ForeignKey("artists.id")) >> artist = relationship("Artist", backref=backref("albums", order_by=id)) >> >> #---------------------------------------------------------------------- >> def __init__(self, title, release_date, publisher, media_type): >> """""" >> self.title = title >> self.release_date = release_date >> self.publisher = publisher >> self.media_type = media_type >> >> class Albumold(Base): >> """""" >> __tablename__ = "albumold" >> >> id = Column(Integer, primary_key=True) >> title = Column(String) >> release_date = Column(Date) >> publisher = Column(String) >> media_type = Column(String) >> >> artist_id = Column(Integer, ForeignKey("artists.id")) >> artist = relationship("Artist", backref=backref("oldalbums", >> order_by=id)) >> >> #---------------------------------------------------------------------- >> def __init__(self, title="", release_date="", publisher="", >> media_type=""): >> """""" >> self.title = title >> self.release_date = release_date >> self.publisher = publisher >> self.media_type = media_type >> >> # create tables >> Base.metadata.create_all(engine) >> >> ///////////////////////////////////////////////////////// >> >> I need to fetch data from table Album and insert into Albumold table after >> sorting them according to release_date. >> >> Note: I need to know how it can work, so that i can apply this script( >> method) into my flask app models (similar to this but can't share them >> publically ) which requires such script to fetch data from various tables >> and insert into one main table. >> >> Regards >> >> >> >> >> On Tue, Jul 16, 2013 at 6:38 PM, Mauricio de Abreu Antunes >> <mauricio.abreua@gmail.com> wrote: >>> >>> Well, I don't know why you are using Core functions. >>> >>> Do you have models? Meta tables derive from Models SQLAlchemy class? >>> >>> Show them to me so I can help you by building your query and insert >>> with ORM features. >>> >>> By the way, to insert a new object into a table you would need a >>> session.add method, no? >>> >>> 2013/7/16 Uday Choudhary <mobulite@gmail.com>: >>> > I'm trying to use: >>> > >>> > ins = Albumold.__table__.insert() >>> > res = session.query(Album).order_by(Album.title).all() >>> > conn = engine.connect() >>> > conn.execute(ins, res) >>> > >>> > Now issue is in last line, i want to insert query res data into ins >>> > which >>> > has Albumold table. >>> > >>> > How can i insert res into a table? >>> > >>> > >>> > On Tue, Jul 16, 2013 at 6:21 PM, Mauricio de Abreu Antunes >>> > <mauricio.abreua@gmail.com> wrote: >>> >> >>> >> Why don't you use ORM instead of core functions? >>> >> >>> >> 2013/7/16 Uday Choudhary <mobulite@gmail.com>: >>> >> > Hello, >>> >> > >>> >> > I'm very basic at SQLalchemy and i faced this situation. >>> >> > >>> >> > I need to query data from one table and insert that into another >>> >> > table. >>> >> > I >>> >> > tried using from_select in this: >>> >> > sel = select([table1.c.a, table1.c.b]).where(table1.c.c > 5) >>> >> > ins = table2.insert().from_select(['a', 'b'], sel) >>> >> > >>> >> > But from_select requires sqlalchemy version 0.8.3 and in my flask app >>> >> > i'm >>> >> > using 0.8.2 since 0.8.3 is not available right now. >>> >> > >>> >> > I need a way to query data from one table and insert that into >>> >> > another >>> >> > table. >>> >> > >>> >> > Please suggest a possible solution (for sqlalchemy 0.8.2 only) >>> >> > >>> >> > Regards >>> >> >>> >> >>> >> >>> >> -- >>> >> Mauricio de Abreu Antunes >>> >> Mobile: (51)930-74-525 >>> >> Skype: mauricio.abreua >>> > >>> > >>> >>> >>> >>> -- >>> Mauricio de Abreu Antunes >>> Mobile: (51)930-74-525 >>> Skype: mauricio.abreua >> >> > > > > -- > Mauricio de Abreu Antunes > Mobile: (51)930-74-525 > Skype: mauricio.abreua -- Mauricio de Abreu Antunes Mobile: (51)930-74-525 Skype: mauricio.abreua
Okay... so as per your suggestion i reached at this point: ////////////////////////////////////////////////////////////////////////////////////////////////// import datetime from sqlalchemy.ext.compiler import compiles from sqlalchemy import select, create_engine from sqlalchemy import desc, asc from sqlalchemy.sql.expression import Executable, ClauseElement from table_def import Artist, Album, Albumold from sqlalchemy.orm import sessionmaker engine = create_engine('sqlite:///mymusic.db', echo=True) # create a Session Session = sessionmaker(bind=engine) session = Session() class InsertFromSelect(Executable, ClauseElement): def __init__(self, table, select): self.table = table self.select = select @compiles(InsertFromSelect) def visit_insert_from_select(element, compiler, **kw): return "INSERT INTO %s (%s)" % ( compiler.process(element.table, asfrom=True), compiler.process(element.select) ) insert = InsertFromSelect(Albumold.__table__, select([Album.__table__]).order_by(asc(Album.release_date))) print insert session.add(insert) session.commit() // what should come here. print "done" ////////////////////////////////////////////////////////////////////////////////////////////////// But getting error: r*aise exc.UnmappedInstanceError(instance) sqlalchemy.orm.exc.UnmappedInstanceError: Class '__main__.InsertFromSelect' is not mapped* * * So, how do i execute the *insert* statement to let data be inserted into table. Regards On Tue, Jul 16, 2013 at 7:12 PM, Mauricio de Abreu Antunes < mauricio.abreua@gmail.com> wrote: > Tip: IMHO I would not create a Albumold, but create a new field into > Album and create a daily process to define if a album is old. Or a > specific out-of-database rule to manage it. > > 2013/7/16 Mauricio de Abreu Antunes <mauricio.abreua@gmail.com>: > > Nice, thanks. > > > > You already have your models and definitions. It means that you can > > use ORM features. > > > > Maybe you want to try it: > > > > > http://docs.sqlalchemy.org/en/rel_0_8/core/compiler.html#compiling-sub-elements-of-a-custom-expression-construct > > > > 2013/7/16 Uday Choudhary <mobulite@gmail.com>: > >> Yeah i am using models and import them using "from table_def import > Album, > >> Artist" > >> > >> table_def.py as: > >> from sqlalchemy import create_engine, ForeignKey > >> from sqlalchemy import Column, Date, Integer, String > >> from sqlalchemy.ext.declarative import declarative_base > >> from sqlalchemy.orm import relationship, backref > >> > >> engine = create_engine('sqlite:///mymusic.db', echo=True) > >> Base = declarative_base() > >> > >> ######################################################################## > >> class Artist(Base): > >> """""" > >> __tablename__ = "artists" > >> > >> id = Column(Integer, primary_key=True) > >> name = Column(String) > >> > >> > #---------------------------------------------------------------------- > >> def __init__(self, name): > >> """""" > >> self.name = name > >> > >> ######################################################################## > >> class Album(Base): > >> """""" > >> __tablename__ = "albums" > >> > >> id = Column(Integer, primary_key=True) > >> title = Column(String) > >> release_date = Column(Date) > >> publisher = Column(String) > >> media_type = Column(String) > >> > >> artist_id = Column(Integer, ForeignKey("artists.id")) > >> artist = relationship("Artist", backref=backref("albums", > order_by=id)) > >> > >> > #---------------------------------------------------------------------- > >> def __init__(self, title, release_date, publisher, media_type): > >> """""" > >> self.title = title > >> self.release_date = release_date > >> self.publisher = publisher > >> self.media_type = media_type > >> > >> class Albumold(Base): > >> """""" > >> __tablename__ = "albumold" > >> > >> id = Column(Integer, primary_key=True) > >> title = Column(String) > >> release_date = Column(Date) > >> publisher = Column(String) > >> media_type = Column(String) > >> > >> artist_id = Column(Integer, ForeignKey("artists.id")) > >> artist = relationship("Artist", backref=backref("oldalbums", > >> order_by=id)) > >> > >> > #---------------------------------------------------------------------- > >> def __init__(self, title="", release_date="", publisher="", > >> media_type=""): > >> """""" > >> self.title = title > >> self.release_date = release_date > >> self.publisher = publisher > >> self.media_type = media_type > >> > >> # create tables > >> Base.metadata.create_all(engine) > >> > >> ///////////////////////////////////////////////////////// > >> > >> I need to fetch data from table Album and insert into Albumold table > after > >> sorting them according to release_date. > >> > >> Note: I need to know how it can work, so that i can apply this script( > >> method) into my flask app models (similar to this but can't share them > >> publically ) which requires such script to fetch data from various > tables > >> and insert into one main table. > >> > >> Regards > >> > >> > >> > >> > >> On Tue, Jul 16, 2013 at 6:38 PM, Mauricio de Abreu Antunes > >> <mauricio.abreua@gmail.com> wrote: > >>> > >>> Well, I don't know why you are using Core functions. > >>> > >>> Do you have models? Meta tables derive from Models SQLAlchemy class? > >>> > >>> Show them to me so I can help you by building your query and insert > >>> with ORM features. > >>> > >>> By the way, to insert a new object into a table you would need a > >>> session.add method, no? > >>> > >>> 2013/7/16 Uday Choudhary <mobulite@gmail.com>: > >>> > I'm trying to use: > >>> > > >>> > ins = Albumold.__table__.insert() > >>> > res = session.query(Album).order_by(Album.title).all() > >>> > conn = engine.connect() > >>> > conn.execute(ins, res) > >>> > > >>> > Now issue is in last line, i want to insert query res data into ins > >>> > which > >>> > has Albumold table. > >>> > > >>> > How can i insert res into a table? > >>> > > >>> > > >>> > On Tue, Jul 16, 2013 at 6:21 PM, Mauricio de Abreu Antunes > >>> > <mauricio.abreua@gmail.com> wrote: > >>> >> > >>> >> Why don't you use ORM instead of core functions? > >>> >> > >>> >> 2013/7/16 Uday Choudhary <mobulite@gmail.com>: > >>> >> > Hello, > >>> >> > > >>> >> > I'm very basic at SQLalchemy and i faced this situation. > >>> >> > > >>> >> > I need to query data from one table and insert that into another > >>> >> > table. > >>> >> > I > >>> >> > tried using from_select in this: > >>> >> > sel = select([table1.c.a, table1.c.b]).where(table1.c.c > 5) > >>> >> > ins = table2.insert().from_select(['a', 'b'], sel) > >>> >> > > >>> >> > But from_select requires sqlalchemy version 0.8.3 and in my flask > app > >>> >> > i'm > >>> >> > using 0.8.2 since 0.8.3 is not available right now. > >>> >> > > >>> >> > I need a way to query data from one table and insert that into > >>> >> > another > >>> >> > table. > >>> >> > > >>> >> > Please suggest a possible solution (for sqlalchemy 0.8.2 only) > >>> >> > > >>> >> > Regards > >>> >> > >>> >> > >>> >> > >>> >> -- > >>> >> Mauricio de Abreu Antunes > >>> >> Mobile: (51)930-74-525 > >>> >> Skype: mauricio.abreua > >>> > > >>> > > >>> > >>> > >>> > >>> -- > >>> Mauricio de Abreu Antunes > >>> Mobile: (51)930-74-525 > >>> Skype: mauricio.abreua > >> > >> > > > > > > > > -- > > Mauricio de Abreu Antunes > > Mobile: (51)930-74-525 > > Skype: mauricio.abreua > > > > -- > Mauricio de Abreu Antunes > Mobile: (51)930-74-525 > Skype: mauricio.abreua >
Well, this line insert = InsertFromSelect(Albumold.__table__, select([Album.__table__]). already inserted the data. You don't need to execute session.add, since it is not adding a model object (db.Model). you could also session.execute('INSERT INTO t1 (%s)' % your_select_expresion) without using compiler. does anyone have any advice to my tips? 2013/7/16 Uday Choudhary <mobulite@gmail.com>: > Okay... so as per your suggestion i reached at this point: > > ////////////////////////////////////////////////////////////////////////////////////////////////// > import datetime > from sqlalchemy.ext.compiler import compiles > from sqlalchemy import select, create_engine > from sqlalchemy import desc, asc > from sqlalchemy.sql.expression import Executable, ClauseElement > from table_def import Artist, Album, Albumold > from sqlalchemy.orm import sessionmaker > > engine = create_engine('sqlite:///mymusic.db', echo=True) > > # create a Session > Session = sessionmaker(bind=engine) > session = Session() > > class InsertFromSelect(Executable, ClauseElement): > def __init__(self, table, select): > self.table = table > self.select = select > > @compiles(InsertFromSelect) > def visit_insert_from_select(element, compiler, **kw): > return "INSERT INTO %s (%s)" % ( > compiler.process(element.table, asfrom=True), > compiler.process(element.select) > ) > > insert = InsertFromSelect(Albumold.__table__, > select([Album.__table__]).order_by(asc(Album.release_date))) > > print insert > > > session.add(insert) > session.commit() // what should come here. > > print "done" > ////////////////////////////////////////////////////////////////////////////////////////////////// > > But getting error: raise exc.UnmappedInstanceError(instance) > sqlalchemy.orm.exc.UnmappedInstanceError: Class '__main__.InsertFromSelect' > is not mapped > > So, how do i execute the insert statement to let data be inserted into > table. > > Regards > > > > On Tue, Jul 16, 2013 at 7:12 PM, Mauricio de Abreu Antunes > <mauricio.abreua@gmail.com> wrote: >> >> Tip: IMHO I would not create a Albumold, but create a new field into >> Album and create a daily process to define if a album is old. Or a >> specific out-of-database rule to manage it. >> >> 2013/7/16 Mauricio de Abreu Antunes <mauricio.abreua@gmail.com>: >> > Nice, thanks. >> > >> > You already have your models and definitions. It means that you can >> > use ORM features. >> > >> > Maybe you want to try it: >> > >> > >> > http://docs.sqlalchemy.org/en/rel_0_8/core/compiler.html#compiling-sub-elements-of-a-custom-expression-construct >> > >> > 2013/7/16 Uday Choudhary <mobulite@gmail.com>: >> >> Yeah i am using models and import them using "from table_def import >> >> Album, >> >> Artist" >> >> >> >> table_def.py as: >> >> from sqlalchemy import create_engine, ForeignKey >> >> from sqlalchemy import Column, Date, Integer, String >> >> from sqlalchemy.ext.declarative import declarative_base >> >> from sqlalchemy.orm import relationship, backref >> >> >> >> engine = create_engine('sqlite:///mymusic.db', echo=True) >> >> Base = declarative_base() >> >> >> >> >> >> ######################################################################## >> >> class Artist(Base): >> >> """""" >> >> __tablename__ = "artists" >> >> >> >> id = Column(Integer, primary_key=True) >> >> name = Column(String) >> >> >> >> >> >> #---------------------------------------------------------------------- >> >> def __init__(self, name): >> >> """""" >> >> self.name = name >> >> >> >> >> >> ######################################################################## >> >> class Album(Base): >> >> """""" >> >> __tablename__ = "albums" >> >> >> >> id = Column(Integer, primary_key=True) >> >> title = Column(String) >> >> release_date = Column(Date) >> >> publisher = Column(String) >> >> media_type = Column(String) >> >> >> >> artist_id = Column(Integer, ForeignKey("artists.id")) >> >> artist = relationship("Artist", backref=backref("albums", >> >> order_by=id)) >> >> >> >> >> >> #---------------------------------------------------------------------- >> >> def __init__(self, title, release_date, publisher, media_type): >> >> """""" >> >> self.title = title >> >> self.release_date = release_date >> >> self.publisher = publisher >> >> self.media_type = media_type >> >> >> >> class Albumold(Base): >> >> """""" >> >> __tablename__ = "albumold" >> >> >> >> id = Column(Integer, primary_key=True) >> >> title = Column(String) >> >> release_date = Column(Date) >> >> publisher = Column(String) >> >> media_type = Column(String) >> >> >> >> artist_id = Column(Integer, ForeignKey("artists.id")) >> >> artist = relationship("Artist", backref=backref("oldalbums", >> >> order_by=id)) >> >> >> >> >> >> #---------------------------------------------------------------------- >> >> def __init__(self, title="", release_date="", publisher="", >> >> media_type=""): >> >> """""" >> >> self.title = title >> >> self.release_date = release_date >> >> self.publisher = publisher >> >> self.media_type = media_type >> >> >> >> # create tables >> >> Base.metadata.create_all(engine) >> >> >> >> ///////////////////////////////////////////////////////// >> >> >> >> I need to fetch data from table Album and insert into Albumold table >> >> after >> >> sorting them according to release_date. >> >> >> >> Note: I need to know how it can work, so that i can apply this script( >> >> method) into my flask app models (similar to this but can't share them >> >> publically ) which requires such script to fetch data from various >> >> tables >> >> and insert into one main table. >> >> >> >> Regards >> >> >> >> >> >> >> >> >> >> On Tue, Jul 16, 2013 at 6:38 PM, Mauricio de Abreu Antunes >> >> <mauricio.abreua@gmail.com> wrote: >> >>> >> >>> Well, I don't know why you are using Core functions. >> >>> >> >>> Do you have models? Meta tables derive from Models SQLAlchemy class? >> >>> >> >>> Show them to me so I can help you by building your query and insert >> >>> with ORM features. >> >>> >> >>> By the way, to insert a new object into a table you would need a >> >>> session.add method, no? >> >>> >> >>> 2013/7/16 Uday Choudhary <mobulite@gmail.com>: >> >>> > I'm trying to use: >> >>> > >> >>> > ins = Albumold.__table__.insert() >> >>> > res = session.query(Album).order_by(Album.title).all() >> >>> > conn = engine.connect() >> >>> > conn.execute(ins, res) >> >>> > >> >>> > Now issue is in last line, i want to insert query res data into ins >> >>> > which >> >>> > has Albumold table. >> >>> > >> >>> > How can i insert res into a table? >> >>> > >> >>> > >> >>> > On Tue, Jul 16, 2013 at 6:21 PM, Mauricio de Abreu Antunes >> >>> > <mauricio.abreua@gmail.com> wrote: >> >>> >> >> >>> >> Why don't you use ORM instead of core functions? >> >>> >> >> >>> >> 2013/7/16 Uday Choudhary <mobulite@gmail.com>: >> >>> >> > Hello, >> >>> >> > >> >>> >> > I'm very basic at SQLalchemy and i faced this situation. >> >>> >> > >> >>> >> > I need to query data from one table and insert that into another >> >>> >> > table. >> >>> >> > I >> >>> >> > tried using from_select in this: >> >>> >> > sel = select([table1.c.a, table1.c.b]).where(table1.c.c > 5) >> >>> >> > ins = table2.insert().from_select(['a', 'b'], sel) >> >>> >> > >> >>> >> > But from_select requires sqlalchemy version 0.8.3 and in my flask >> >>> >> > app >> >>> >> > i'm >> >>> >> > using 0.8.2 since 0.8.3 is not available right now. >> >>> >> > >> >>> >> > I need a way to query data from one table and insert that into >> >>> >> > another >> >>> >> > table. >> >>> >> > >> >>> >> > Please suggest a possible solution (for sqlalchemy 0.8.2 only) >> >>> >> > >> >>> >> > Regards >> >>> >> >> >>> >> >> >>> >> >> >>> >> -- >> >>> >> Mauricio de Abreu Antunes >> >>> >> Mobile: (51)930-74-525 >> >>> >> Skype: mauricio.abreua >> >>> > >> >>> > >> >>> >> >>> >> >>> >> >>> -- >> >>> Mauricio de Abreu Antunes >> >>> Mobile: (51)930-74-525 >> >>> Skype: mauricio.abreua >> >> >> >> >> > >> > >> > >> > -- >> > Mauricio de Abreu Antunes >> > Mobile: (51)930-74-525 >> > Skype: mauricio.abreua >> >> >> >> -- >> Mauricio de Abreu Antunes >> Mobile: (51)930-74-525 >> Skype: mauricio.abreua > > -- Mauricio de Abreu Antunes Mobile: (51)930-74-525 Skype: mauricio.abreua