librelist archives

« back to archive

how to select data from one table to insert into other in sqllite

how to select data from one table to insert into other in sqllite

From:
Uday Choudhary
Date:
2013-07-16 @ 12:33
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

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

From:
Mauricio de Abreu Antunes
Date:
2013-07-16 @ 12:51
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

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

From:
Uday Choudhary
Date:
2013-07-16 @ 13:03
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
>

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

From:
Mauricio de Abreu Antunes
Date:
2013-07-16 @ 13:08
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

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

From:
Uday Choudhary
Date:
2013-07-16 @ 13:17
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
>

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

From:
Mauricio de Abreu Antunes
Date:
2013-07-16 @ 13:40
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

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

From:
Mauricio de Abreu Antunes
Date:
2013-07-16 @ 13:42
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

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

From:
Uday Choudhary
Date:
2013-07-16 @ 16:23
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
>

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