librelist archives

Re: [flask] how to select data from one table to insert into other in sqllite

From:

Mauricio de Abreu Antunes

Date:

2013-07-16 @ 21:42

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