[sqlalchemy] inserts rows in wrong order
I'm inserting rows into a reflected table as: m = db.MetaData() t = db.Table(tablename,m,autoload = True, autoload_with = db.engine) # create a list of dictionaries called output_batch. then do ... db.engine.execute(t.insert(),output_batch) I'm using flask-sqlalchemy hence the db object. The insertion works but it is in a different order. Further down, I try to query for the first 10 rows of the data that was inserted hoping to get the rows that correspond to the first 10 list values. But that's not the case. Is this expected behavior? Is there something I can do to keep the order of the dictionaries in the list the same as the order the rows appear in the table? Thanks. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Insert new column in an existing database
Thanks, I will give it a try.
Cheers
Jothy
On Fri, Dec 4, 2015 at 3:49 AM, Mike Bayer wrote:
>
> hello -
>
> you need to use the SQLAlchemy API fully, the code example below is
> using the SQLite DBAPI directly.
>
> Go through the tutorial at
> http://docs.sqlalchemy.org/en/rel_1_0/core/tutorial.html to see how to
> properly invoke SQL with SQLAlchemy.
>
>
>
> On 12/03/2015 12:19 AM, Jothybasu Selvaraj wrote:
> > Dear All
> >
> > I just started to use sqlalchemy and I am also quite new to databases.
> >
> > I have a sqlite database saved with a table with name "Table1" and now I
> > want to insert a colum with PickleType. The below code works for TEXT,
> > INTEGER, etc... but not for PickleType.
> >
> >
> >
> >
> >
> curResults13={'Output':2.01,'QualityIndex':0.66,'FlatnessX':1.5,'FlatnessY=':1.8}
> >
> > conn = sqlite3.connect("C:\\MLC QA\LA4.db")
> > c = conn.cursor()
> > c.execute("ALTER TABLE {tn} ADD COLUMN '{cn}' {ct} DEFAULT '{df}'"\
> > .format(tn='Table1', cn='Results', ct='ARRAY', df=curResults13))
> > conn.commit()
> > conn.close()
> >
> >
> >
> >
> > Can any of you guide me on this issue?
> >
> >
> >
> > Thanks
> >
> >
> > Jothy
> >
> >
> >
> > --
> > 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 http://groups.google.com/group/sqlalchemy.
> > For more options, visit https://groups.google.com/d/optout.
>
> --
> You received this message because you are subscribed to a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy/aii1o5rV8o8/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Bulk insert using bulk_insert_mappings
Sweet! It was the commit.
Thanks!
Greg--
On Thursday, December 3, 2015 at 10:48:45 AM UTC-6, Michael Bayer wrote:
>
>
>
> On 12/03/2015 11:28 AM, Horcle wrote:
> > I am trying to do a bulk insert of a large list of dictionaries of the
> form:
> >
> > results = [{'attribute': u'SEX', 'value_d': 0.0, 'value_s': u'M',
> > 'sid': 1L},
> >{'attribute': u'SEX', 'value_d': 0.0, 'value_s': u'M',
> > 'sid': 2L},
> >{'attribute': u'SEX', 'value_d': 0.0, 'value_s': u'M',
> > 'sid': 3L},
> >{'attribute': u'SEX', 'value_d': 0.0, 'value_s': u'M',
> > 'sid': 4L},
> >...
> > ]
> >
> > After reading about 'executemany' and bulk_insert_mappings, I decided to
> > try the later, since it looked much simpler to work with.
> >
> > Here is the code to execute this, with the naive assumption that this
> > would work with a list of dictionaries:
> >
> > Session = sessionmaker(bind=engine)
> > s = Session()
> > s.bulk_insert_mappings(Results,results)
> >
> > My Results model is:
> >
> > class Results(db.Model):
> > __tablename__ = 'results'
> > id = Column(Integer, primary_key=True, autoincrement=True)
> > sid = Column(Integer)
> > attribute = Column(String(2048))
> > value_s = Column(String(2048))
> > value_d = Column(Float)
> >
> > db is the SQLAlchemy object for my app:
> >
> > db = SQLAlchemy(app)
> >
> > No errors are thrown when I run this, but the data are not being
> inserted.
>
> 1. are you emitting s.commit() to commit your transaction?
>
> 2. what's in that "results" object? it should definitely do something
> unless the collection is empty.
>
> 3. turn on echo=True on your engine to see what SQL is emitted.
>
> Also note that the "SQLAlchemy(app)" object there is a flask thing, that
> provides its own Session. There's not much reason for it if you are
> making a Session() yourself with an "engine" from somewhere.
>
>
>
>
>
>
>
> >
> > End of naive assumption: The documentation says I need a "list of
> > mapping dictionaries." I assume since my dictionaries are key-value
> > pairs, I need to do something like
> >
> > dict = {k:v for k,v in (x.split(':') for x in results) }
> >
> > I tried this, but I then got an error that
> >
> > AttributeError: 'dict' object has no attribute 'split'
> >
> > Not sure where to go with this now...
> >
> > Thanks in advance!
> >
> > Greg--
> >
> > --
> > 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+...@googlegroups.com
> > .
> > To post to this group, send email to sqlal...@googlegroups.com
>
> > .
> > Visit this group at http://groups.google.com/group/sqlalchemy.
> > For more options, visit https://groups.google.com/d/optout.
>
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Insert new column in an existing database
hello -
you need to use the SQLAlchemy API fully, the code example below is
using the SQLite DBAPI directly.
Go through the tutorial at
http://docs.sqlalchemy.org/en/rel_1_0/core/tutorial.html to see how to
properly invoke SQL with SQLAlchemy.
On 12/03/2015 12:19 AM, Jothybasu Selvaraj wrote:
> Dear All
>
> I just started to use sqlalchemy and I am also quite new to databases.
>
> I have a sqlite database saved with a table with name "Table1" and now I
> want to insert a colum with PickleType. The below code works for TEXT,
> INTEGER, etc... but not for PickleType.
>
>
>
>
> curResults13={'Output':2.01,'QualityIndex':0.66,'FlatnessX':1.5,'FlatnessY=':1.8}
>
> conn = sqlite3.connect("C:\\MLC QA\LA4.db")
> c = conn.cursor()
> c.execute("ALTER TABLE {tn} ADD COLUMN '{cn}' {ct} DEFAULT '{df}'"\
> .format(tn='Table1', cn='Results', ct='ARRAY', df=curResults13))
> conn.commit()
> conn.close()
>
>
>
>
> Can any of you guide me on this issue?
>
>
>
> Thanks
>
>
> Jothy
>
>
>
> --
> 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 http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Bulk insert using bulk_insert_mappings
On 12/03/2015 11:28 AM, Horcle wrote:
> I am trying to do a bulk insert of a large list of dictionaries of the form:
>
> results = [{'attribute': u'SEX', 'value_d': 0.0, 'value_s': u'M',
> 'sid': 1L},
>{'attribute': u'SEX', 'value_d': 0.0, 'value_s': u'M',
> 'sid': 2L},
>{'attribute': u'SEX', 'value_d': 0.0, 'value_s': u'M',
> 'sid': 3L},
>{'attribute': u'SEX', 'value_d': 0.0, 'value_s': u'M',
> 'sid': 4L},
>...
> ]
>
> After reading about 'executemany' and bulk_insert_mappings, I decided to
> try the later, since it looked much simpler to work with.
>
> Here is the code to execute this, with the naive assumption that this
> would work with a list of dictionaries:
>
> Session = sessionmaker(bind=engine)
> s = Session()
> s.bulk_insert_mappings(Results,results)
>
> My Results model is:
>
> class Results(db.Model):
> __tablename__ = 'results'
> id = Column(Integer, primary_key=True, autoincrement=True)
> sid = Column(Integer)
> attribute = Column(String(2048))
> value_s = Column(String(2048))
> value_d = Column(Float)
>
> db is the SQLAlchemy object for my app:
>
> db = SQLAlchemy(app)
>
> No errors are thrown when I run this, but the data are not being inserted.
1. are you emitting s.commit() to commit your transaction?
2. what's in that "results" object? it should definitely do something
unless the collection is empty.
3. turn on echo=True on your engine to see what SQL is emitted.
Also note that the "SQLAlchemy(app)" object there is a flask thing, that
provides its own Session. There's not much reason for it if you are
making a Session() yourself with an "engine" from somewhere.
>
> End of naive assumption: The documentation says I need a "list of
> mapping dictionaries." I assume since my dictionaries are key-value
> pairs, I need to do something like
>
> dict = {k:v for k,v in (x.split(':') for x in results) }
>
> I tried this, but I then got an error that
>
> AttributeError: 'dict' object has no attribute 'split'
>
> Not sure where to go with this now...
>
> Thanks in advance!
>
> Greg--
>
> --
> 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 http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: load_only doesn't affect joins
Thanks for caring ;) On Dec 3, 2015 6:08 PM, "Jonathan Vanasco" wrote: > Thanks for posting a full self-contained working example of your problem! > > -- > You received this message because you are subscribed to a topic in the > Google Groups "sqlalchemy" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sqlalchemy/DTqEnAKqipY/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Bulk insert using bulk_insert_mappings
I am trying to do a bulk insert of a large list of dictionaries of the form:
results = [{'attribute': u'SEX', 'value_d': 0.0, 'value_s': u'M',
'sid': 1L},
{'attribute': u'SEX', 'value_d': 0.0, 'value_s': u'M',
'sid': 2L},
{'attribute': u'SEX', 'value_d': 0.0, 'value_s': u'M',
'sid': 3L},
{'attribute': u'SEX', 'value_d': 0.0, 'value_s': u'M',
'sid': 4L},
...
]
After reading about 'executemany' and bulk_insert_mappings, I decided to
try the later, since it looked much simpler to work with.
Here is the code to execute this, with the naive assumption that this would
work with a list of dictionaries:
Session = sessionmaker(bind=engine)
s = Session()
s.bulk_insert_mappings(Results,results)
My Results model is:
class Results(db.Model):
__tablename__ = 'results'
id = Column(Integer, primary_key=True, autoincrement=True)
sid = Column(Integer)
attribute = Column(String(2048))
value_s = Column(String(2048))
value_d = Column(Float)
db is the SQLAlchemy object for my app:
db = SQLAlchemy(app)
No errors are thrown when I run this, but the data are not being inserted.
End of naive assumption: The documentation says I need a "list of mapping
dictionaries." I assume since my dictionaries are key-value pairs, I need
to do something like
dict = {k:v for k,v in (x.split(':') for x in results) }
I tried this, but I then got an error that
AttributeError: 'dict' object has no attribute 'split'
Not sure where to go with this now...
Thanks in advance!
Greg--
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: load_only doesn't affect joins
Thanks for posting a full self-contained working example of your problem! -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] load_only doesn't affect joins
The lazy me you mean :)
I saw that option in the documents and my comment was not regarding the
complexity of the solution.
I just thought that a 'load_only' method is better understood if it loads
only the specified attributes.
With an abstraction layer, the user often "sees" things differently than
the way they really are.
For example, when I load the Foo object and I get the bars list, I don't
want to care about 'bars' being a relationship (I know it is but I already
handled that when I defined my model). For me, it's an attribute of type
list...
Of course some operations are relationship-specific but those that are not
should not (IMO) force the user to consider them differently.
Since I have my own wrapper around session.query, I added the following
lines to it:
query = session.query(model)
if properties is not None:
props = [p for p in properties if p in model.__table__.c]
query = query.options(sa.orm.load_only(*props))
noload = set([x.key for x in model.__mapper__.relationships]) - set(
props)
for x in noload:
query = query.options(sa.orm.lazyload(x))
...
That currently does what I want...
On Thursday, December 3, 2015 at 5:23:55 PM UTC+2, Michael Bayer wrote:
>
>
>
> On 12/03/2015 10:08 AM, Mike Bayer wrote:
> >
> >
> > On 12/03/2015 06:16 AM, Ofir Herzas wrote:
> >> load_only as stated
> >> in http://docs.sqlalchemy.org/en/latest/orm/loading_columns.html does
> >> the following:
> >> "An arbitrary set of columns can be selected as “load only” columns,
> >> which will be loaded *while deferring all other columns* on a given
> >> entity..."
> >
> >
> > Hi Ofir -
> >
> > As it says, "on a given entity", an entity refers to a class-oriented
> > object. A relationship refers to a different set of entities so these
> > must be referred to independently.
> >
> > The "load_only" function in that code example is hyperlinked, which is
> > intended to encourage the reader to click and read through the more
> > detailed documentation for this function which is at:
> >
> >
> http://docs.sqlalchemy.org/en/rel_1_0/orm/loading_columns.html?highlight=load_only#sqlalchemy.orm.load_only
>
> >
> >
> >
> >>
> >> However, joined relationships are not affected by this, meaning that
> the
> >> produced sql still joins all 'joined' relationships
> >>
> >> Consider the following example:
> >>
> >> |
> >> importsqlalchemy assa
> >> fromsqlalchemy.ext.declarative importdeclarative_base
> >>
> >> Base=declarative_base()
> >>
> >> classFoo(Base):
> >> __tablename__ ='foo'
> >> id =sa.Column(sa.Integer,primary_key=True)
> >> bars =sa.orm.relationship("Bar",lazy="joined")
> >>
> >> classBar(Base):
> >> __tablename__ ='bar'
> >> id =sa.Column(sa.Integer,primary_key=True)
> >> foo_id =sa.Column(sa.Integer,sa.ForeignKey('foo.id'))
> >> baz =sa.Column(sa.Integer)
> >>
> >> e =sa.create_engine("sqlite://",echo=True)
> >> session =sa.orm.Session(e)
> >> session.query(Foo).options(sa.orm.load_only('id')).all()
> >> |
> >>
> >> The query produced is the following:
> >> |
> >> SELECT foo.id AS foo_id,bar_1.id AS bar_1_id,bar_1.foo_id AS
> >> bar_1_foo_id,bar_1.baz AS bar_1_baz
> >> FROM foo LEFT OUTER JOIN bar AS bar_1 ON foo.id =bar_1.foo_id
> >> |
> >>
> >> I understand that I can use the lazyload option to prevent the join but
> >> I thought it would be nice if the load_only handled it
> out-of-the-box...
> >
> > there's "out of the box" and there's "assuming a specific and arbitrary
> > intent" - you have lazy="joined" in your mapping so if you don't want
> > that to occur you need to tell the query to turn it off, load_only('a',
> > 'b').lazyload('bars').
>
> another thought, you'll probably like this:
>
> load_only('a', 'b').lazyload('*')
>
>
> that should be easier
>
>
>
>
> >
> >
> >
> >
> >
> >>
> >> --
> >> 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+...@googlegroups.com
> >> .
> >> To post to this group, send email to sqlal...@googlegroups.com
>
> >> .
> >> Visit this group at http://groups.google.com/group/sqlalchemy.
> >> For more options, visit https://groups.google.com/d/optout.
> >
>
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] load_only doesn't affect joins
On 12/03/2015 10:08 AM, Mike Bayer wrote:
>
>
> On 12/03/2015 06:16 AM, Ofir Herzas wrote:
>> load_only as stated
>> in http://docs.sqlalchemy.org/en/latest/orm/loading_columns.html does
>> the following:
>> "An arbitrary set of columns can be selected as “load only” columns,
>> which will be loaded *while deferring all other columns* on a given
>> entity..."
>
>
> Hi Ofir -
>
> As it says, "on a given entity", an entity refers to a class-oriented
> object. A relationship refers to a different set of entities so these
> must be referred to independently.
>
> The "load_only" function in that code example is hyperlinked, which is
> intended to encourage the reader to click and read through the more
> detailed documentation for this function which is at:
>
> http://docs.sqlalchemy.org/en/rel_1_0/orm/loading_columns.html?highlight=load_only#sqlalchemy.orm.load_only
>
>
>
>>
>> However, joined relationships are not affected by this, meaning that the
>> produced sql still joins all 'joined' relationships
>>
>> Consider the following example:
>>
>> |
>> importsqlalchemy assa
>> fromsqlalchemy.ext.declarative importdeclarative_base
>>
>> Base=declarative_base()
>>
>> classFoo(Base):
>> __tablename__ ='foo'
>> id =sa.Column(sa.Integer,primary_key=True)
>> bars =sa.orm.relationship("Bar",lazy="joined")
>>
>> classBar(Base):
>> __tablename__ ='bar'
>> id =sa.Column(sa.Integer,primary_key=True)
>> foo_id =sa.Column(sa.Integer,sa.ForeignKey('foo.id'))
>> baz =sa.Column(sa.Integer)
>>
>> e =sa.create_engine("sqlite://",echo=True)
>> session =sa.orm.Session(e)
>> session.query(Foo).options(sa.orm.load_only('id')).all()
>> |
>>
>> The query produced is the following:
>> |
>> SELECT foo.id AS foo_id,bar_1.id AS bar_1_id,bar_1.foo_id AS
>> bar_1_foo_id,bar_1.baz AS bar_1_baz
>> FROM foo LEFT OUTER JOIN bar AS bar_1 ON foo.id =bar_1.foo_id
>> |
>>
>> I understand that I can use the lazyload option to prevent the join but
>> I thought it would be nice if the load_only handled it out-of-the-box...
>
> there's "out of the box" and there's "assuming a specific and arbitrary
> intent" - you have lazy="joined" in your mapping so if you don't want
> that to occur you need to tell the query to turn it off, load_only('a',
> 'b').lazyload('bars').
another thought, you'll probably like this:
load_only('a', 'b').lazyload('*')
that should be easier
>
>
>
>
>
>>
>> --
>> 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 http://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.
>
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] firebird3 support
Hi Treeve - these look pretty good, is there any chance you can submit as a single patch to https://github.com/zzzeek/sqlalchemy/pulls, and additionally what are the results when you've run the tests? - mike On 12/03/2015 04:27 AM, Treeve Jelbert wrote: > I have been using sqlalchemy with firebird3 for some time on a small > project. Now that Firbeird3 has reached RC2 status, I think it may be > appropriate to submit the attached patches which are based on the > rel_1_0 git branch. > > I use the fdb-1.4.9 driver > > > Regards, Treeve > -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] load_only doesn't affect joins
On 12/03/2015 06:16 AM, Ofir Herzas wrote:
> load_only as stated
> in http://docs.sqlalchemy.org/en/latest/orm/loading_columns.html does
> the following:
> "An arbitrary set of columns can be selected as “load only” columns,
> which will be loaded *while deferring all other columns* on a given
> entity..."
Hi Ofir -
As it says, "on a given entity", an entity refers to a class-oriented
object. A relationship refers to a different set of entities so these
must be referred to independently.
The "load_only" function in that code example is hyperlinked, which is
intended to encourage the reader to click and read through the more
detailed documentation for this function which is at:
http://docs.sqlalchemy.org/en/rel_1_0/orm/loading_columns.html?highlight=load_only#sqlalchemy.orm.load_only
>
> However, joined relationships are not affected by this, meaning that the
> produced sql still joins all 'joined' relationships
>
> Consider the following example:
>
> |
> importsqlalchemy assa
> fromsqlalchemy.ext.declarative importdeclarative_base
>
> Base=declarative_base()
>
> classFoo(Base):
> __tablename__ ='foo'
> id =sa.Column(sa.Integer,primary_key=True)
> bars =sa.orm.relationship("Bar",lazy="joined")
>
> classBar(Base):
> __tablename__ ='bar'
> id =sa.Column(sa.Integer,primary_key=True)
> foo_id =sa.Column(sa.Integer,sa.ForeignKey('foo.id'))
> baz =sa.Column(sa.Integer)
>
> e =sa.create_engine("sqlite://",echo=True)
> session =sa.orm.Session(e)
> session.query(Foo).options(sa.orm.load_only('id')).all()
> |
>
> The query produced is the following:
> |
> SELECT foo.id AS foo_id,bar_1.id AS bar_1_id,bar_1.foo_id AS
> bar_1_foo_id,bar_1.baz AS bar_1_baz
> FROM foo LEFT OUTER JOIN bar AS bar_1 ON foo.id =bar_1.foo_id
> |
>
> I understand that I can use the lazyload option to prevent the join but
> I thought it would be nice if the load_only handled it out-of-the-box...
there's "out of the box" and there's "assuming a specific and arbitrary
intent" - you have lazy="joined" in your mapping so if you don't want
that to occur you need to tell the query to turn it off, load_only('a',
'b').lazyload('bars').
>
> --
> 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 http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] load_only doesn't affect joins
load_only as stated in
http://docs.sqlalchemy.org/en/latest/orm/loading_columns.html does the
following:
"An arbitrary set of columns can be selected as “load only” columns, which
will be loaded *while deferring all other columns* on a given entity..."
However, joined relationships are not affected by this, meaning that the
produced sql still joins all 'joined' relationships
Consider the following example:
import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Foo(Base):
__tablename__ = 'foo'
id = sa.Column(sa.Integer, primary_key=True)
bars = sa.orm.relationship("Bar", lazy="joined")
class Bar(Base):
__tablename__ = 'bar'
id = sa.Column(sa.Integer, primary_key=True)
foo_id = sa.Column(sa.Integer, sa.ForeignKey('foo.id'))
baz = sa.Column(sa.Integer)
e = sa.create_engine("sqlite://", echo=True)
session = sa.orm.Session(e)
session.query(Foo).options(sa.orm.load_only('id')).all()
The query produced is the following:
SELECT foo.id AS foo_id, bar_1.id AS bar_1_id, bar_1.foo_id AS bar_1_foo_id,
bar_1.baz AS bar_1_baz
FROM foo LEFT OUTER JOIN bar AS bar_1 ON foo.id = bar_1.foo_id
I understand that I can use the lazyload option to prevent the join but I
thought it would be nice if the load_only handled it out-of-the-box...
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] firebird3 support
I have been using sqlalchemy with firebird3 for some time on a small
project. Now that Firbeird3 has reached RC2 status, I think it may be
appropriate to submit the attached patches which are based on the
rel_1_0 git branch.
I use the fdb-1.4.9 driver
Regards, Treeve
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
From 326d81ee83d3d7ae5c1bbf73c921d7ae3fd1796d Mon Sep 17 00:00:00 2001
From: Treeve Jelbert
Date: Sat, 28 Dec 2013 13:51:03 +0100
Subject: [PATCH 1/4] detect firebird version 3
---
lib/sqlalchemy/dialects/firebird/base.py | 58 +++-
1 file changed, 57 insertions(+), 1 deletion(-)
diff --git a/lib/sqlalchemy/dialects/firebird/base.py
b/lib/sqlalchemy/dialects/firebird/base.py
index c34829c..1f19628 100644
--- a/lib/sqlalchemy/dialects/firebird/base.py
+++ b/lib/sqlalchemy/dialects/firebird/base.py
@@ -98,7 +98,7 @@ RESERVED_WORDS = set([
"execute", "exists", "exit", "external", "extract", "fetch", "file",
"filter", "float", "for", "foreign", "from", "full", "function",
"gdscode", "generator", "gen_id", "global", "grant", "group",
-"having", "hour", "if", "in", "inactive", "index", "inner",
+"having", "hour", "identity", "if", "in", "inactive", "index", "inner",
"input_type", "insensitive", "insert", "int", "integer", "into", "is",
"isolation", "join", "key", "leading", "left", "length", "level",
"like", "long", "lower", "manual", "max", "maximum_segment", "merge",
@@ -409,6 +409,7 @@ class FBDialect(default.DefaultDialect):
# will be autodetected off upon
# first connect
_version_two = True
+_version_three = False
def initialize(self, connection):
super(FBDialect, self).initialize(connection)
@@ -419,6 +420,14 @@ class FBDialect(default.DefaultDialect):
self.server_version_info >= (6, )
)
+self._version_three = ('firebird' in self.server_version_info and \
+self.server_version_info >= (3, )
+)
+if self._version_three:
+self.ddl_compiler = FB3DDLCompiler
+self.execution_ctx_cls = FB3ExecutionContext
+
+
if not self._version_two:
# TODO: whatever other pre < 2.0 stuff goes here
self.ischema_names = ischema_names.copy()
@@ -736,3 +745,50 @@ class FBDialect(default.DefaultDialect):
self.normalize_name(row['field_name']))
return list(indexes.values())
+
+# this dialect is only for Firebird-3
+# the other fdb dialect is for Firebird-2+
+# older versions of Firebird are not supported, nor is InterBase
+class FB3ExecutionContext(default.DefaultExecutionContext):
+def fire_sequence(self, seq, type_):
+"""Get the next value from the sequence using ``select next value``."""
+
+return self._execute_scalar(
+"SELECT NEXT VALUE FOR %s FROM RDB$DATABASE;" % \
+self.dialect.identifier_preparer.format_sequence(seq),
+type_
+)
+
+class FB3DDLCompiler(sql.compiler.DDLCompiler):
+"""Firebird syntactic idiosyncrasies"""
+
+"""Firebird-3.0 adds support for identity columns"""
+def get_column_specification(self, column, **kwargs):
+colspec = self.preparer.format_column(column)
+impl_type = column.type.dialect_impl(self.dialect)
+colspec += " " + self.dialect.type_compiler.process(column.type)
+
+if column.primary_key and \
+column is column.table._autoincrement_column:
+colspec += " GENERATED BY DEFAULT AS IDENTITY"
+else:
+default = self.get_column_default_string(column)
+if default is not None:
+colspec += " DEFAULT " + default
+
+if not column.nullable:
+colspec += " NOT NULL"
+return colspec
+
+def visit_create_sequence(self, create):
+"""Generate a ``CREATE SEQUENCE`` statement for the sequence."""
+
+# no syntax for these
+# http://www.firebirdsql.org/manual/generatorguide-sqlsyntax.html
+if create.element.increment is not None:
+raise NotImplemented(
+"Firebird SEQUENCE doesn't support INCREMENT BY")
+
+
+return "CREATE SEQUENCE %s" % \
+ self.preparer.format_sequence(create.element)
--
2.6.3
From c40c69149352edc51722bf51a2d4000b9707974a Mon Sep 17 00:00:00 2001
From: Treeve Jelbert
Date: Sun, 10 Mar 2013 13:19:06 +0100
Subject: [PATCH 2/4] fb3 - add native boolean support
Conflicts:
