Re: [sqlalchemy] transaction control when mixing SQL and sqla
That doesn't sound right. Session.commit() issues the commit regardless of
whether or not flush() had any work to do. The usage of Session.execute()
takes place in the scope of the Session's transaction so work is definitely
begun as well.
from sqlalchemy import create_engine
from sqlalchemy.orm import Session
e = create_engine('sqlite:///test.db', echo=True)
s = Session(e)
s.execute("CREATE TABLE foo (data VARCHAR)")
s.execute("INSERT INTO foo (data) VALUES ('data1')")
s.commit()
s.close()
e = create_engine('sqlite:///test.db', echo=True)
assert e.execute("SELECT * FROM foo").fetchall() == [('data1',)]
On Dec 16, 2010, at 2:37 PM, Kent wrote:
> From time to time I find I need or prefer "dropping into SQL" for
> certain tasks, in the midst of making the majority of my database
> changes through sqlalchemy's objects; for example, I may use
> session.execute(sql).
>
> I would like these changes to be committed later along with the sqla
> session objects *if and only if* the session issues a commit.
> However, I've observed that if the *only* database changes were made
> via session.execute(), then session.commit() believes there is nothing
> to commit, so the "commit" isn't issued to the database.
>
> Is there a better way to approach what I am trying to accomplish?
> Is there a way to tell a session "by the way, you have changes that
> you don't know about, so when it comes time to commit, please do so"?
>
> Thanks very much, as always,
> Kent
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Session.add performance
On Fri, Dec 17, 2010 at 5:10 AM, Michael Bayer wrote: >> A major part of development resources as of late have been focused on add() >> and cascade_iterator(). I would advise trying out the 0.7 tip from >> mercurial where we've cut out a lot of overhead out of many areas of the >> flush including add() + cascade_iterator (see >> http://techspot.zzzeek.org/2010/12/12/a-tale-of-three-profiles/ for some >> profiling output). That's great news! I'll give 0.7 a try, thanks for the heads up. >> Things like inlining Session.__contains__ are good ideas if they are shown >> to be prominent in a slow profile, so if you want to send along a test >> script to me that illustrates your bottlenecks I can work on its pain points >> and add it to our suite. Thanks for your offer, I might just take you up on that. Reducing my app to a test case without sending you any of my customer's data might be tricky though so I'll see how I go with 0.7 first. > I uploaded runsnakerun details from add() + cascade_iterator for current > 0.6.6 and 0.7 tips, which is against the test program run in that post. Its > a total of 11,000 add() calls. 0.7 is on the top. 0.7's larger percentage > overall is due to performance increases elsewhere. The fact that its a > lower percentage than your case is probably due to the lower number of > relationships() in the test script, since cascade_iterator() increases in > time for each relationship. Interesting. By the way, in case you didn't know it gprof2dot is another nice way of visualizing cProfiler output. It doesn't convey relative percentages as well as runSnakeRun does but has the advantage of better visualizing the call graph. http://code.google.com/p/jrfonseca/wiki/Gprof2Dot -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] transaction control when mixing SQL and sqla
From time to time I find I need or prefer "dropping into SQL" for certain tasks, in the midst of making the majority of my database changes through sqlalchemy's objects; for example, I may use session.execute(sql). I would like these changes to be committed later along with the sqla session objects *if and only if* the session issues a commit. However, I've observed that if the *only* database changes were made via session.execute(), then session.commit() believes there is nothing to commit, so the "commit" isn't issued to the database. Is there a better way to approach what I am trying to accomplish? Is there a way to tell a session "by the way, you have changes that you don't know about, so when it comes time to commit, please do so"? Thanks very much, as always, Kent -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: (threadlocal) engine.begin() does not return transaction object in SA 0.6.x
On Dec 16, 4:39 pm, Michael Bayer wrote: > The reason that change happened is because the logic of the threadlocal > engine was changed to no longer support the mixing of explicit Transaction > objects in conjunction with the "thread-scoped" transaction of the thread > local engine. It was the returned Transaction object that implemented > __exit__() which is why the context manager feature worked. That the > reworked threadlocal engine no longer returned any object at all and > therefore was no longer a context manager amounts to the inadvertent removal > of a desirable behavior. > ok, I see. Thanks for the comments. > > > So will this be the future behaviour of engine.begin() that it returns just > > None? > > its not a big deal for TLEngine to return some dummy object that emits a > commit/rollback from begin() so ticket 2004 has been added for this. ok, that's very nice. Thanks for taking care of this. Ralph -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] How can I differentiate between relationship?
Hi all,
I have this table:
class Comparation(Base):
"""Represents comparation between two post"""
__tablename__ = "comparations"
_id = Column("id", Integer, primary_key=True)
_number = Column("number", Integer)
_total = Column("total", Integer)
_average = Column("average", Float)
_postAId = Column("postA_id", Integer, ForeignKey("posts.id"))
_postBId = Column("postB_id", Integer, ForeignKey("posts.id"))
_postA = relationship("Post", uselist=False)
_postB = relationship("Post", uselist=False)
It has two ForeignKeys pointing the same table, but it also has two
relationships related to those ForeignKeys. How can I relate _postAId
to _postA?
Thanks in advance!
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Session.add performance
On Dec 16, 2010, at 10:31 AM, Michael Bayer wrote: > > On Dec 16, 2010, at 12:39 AM, Julian Scheid wrote: > >> In an application that is heavy on inserts and updates, cProfile >> output is dominated by Session.add in which about 45% of time is >> spent. Most of that time, in turn, is spent in cascade_iterator (43%). >> I can provide more detailed information if needed. >> >> The application does aggressive caching of data and has set >> expire_on_commit=False, in order to keep database load down. Is that >> the reason for Session.add slowness? >> >> Is there a way I can speed this up while keeping a similar level of >> cache aggressiveness? >> >> For example, in one test run Session.__contains__ was invoked 25m >> times over the course of only a few minutes, accounting for 27% of >> total time spent. Could it be a good idea to try and override this >> function with one that's optimized for this specific use case? >> >> Also, so far I haven't spent any effort expunging objects from the >> session as soon as possible. Some objects might linger for longer >> than necessary. Would they contribute to Session.add's overhead? > > A major part of development resources as of late have been focused on add() > and cascade_iterator(). I would advise trying out the 0.7 tip from > mercurial where we've cut out a lot of overhead out of many areas of the > flush including add() + cascade_iterator (see > http://techspot.zzzeek.org/2010/12/12/a-tale-of-three-profiles/ for some > profiling output). > > Things like inlining Session.__contains__ are good ideas if they are shown to > be prominent in a slow profile, so if you want to send along a test script to > me that illustrates your bottlenecks I can work on its pain points and add it > to our suite. I uploaded runsnakerun details from add() + cascade_iterator for current 0.6.6 and 0.7 tips, which is against the test program run in that post. Its a total of 11,000 add() calls. 0.7 is on the top. 0.7's larger percentage overall is due to performance increases elsewhere. The fact that its a lower percentage than your case is probably due to the lower number of relationships() in the test script, since cascade_iterator() increases in time for each relationship. http://imgur.com/a/SNkhq > > > >> >> -- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To post to this group, send email to sqlalch...@googlegroups.com. >> To unsubscribe from this group, send email to >> sqlalchemy+unsubscr...@googlegroups.com. >> For more options, visit this group at >> http://groups.google.com/group/sqlalchemy?hl=en. >> > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalch...@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Session.merge() failure when primary key is assigned via a relationship
It's been brought up before, and basically it's way above and beyond whats
possible right now with merge() for it to be able to run in-flush dependency
rules on incoming objects in order to determine if it already has an effective
primary key, and what that primary key should be.I tend to lean towards
"not a bug" since the documented behavior of merge() is that the first thing it
does is "examines the primary key of the instance". The "Alpha" object here
is not the primary key of "Foo". Considering a hypothetical feature of such,
the challenge would be to not duplicate code across flush() and merge(), and
just as importantly to somehow perform the check in such a way that doesn't add
latency to all invocations of merge(). So my current thinking would be
"feature add, not feasible for some time to come".
On Dec 16, 2010, at 12:55 AM, Erik Swanson wrote:
> I'm not sure whether this a bug or not:
>
> When a mapped object has a foreign key reference to another object as
> part of its own primary key, setting that column's value via a
> relationship property causes session.merge() to not SELECT the object
> first and thus encounter an IntegrityError when it tries to INSERT the
> object.
>
> I have written a reduction of this problem. When run as a script, the
> second and later invocations will die with an IntegrityError.
>
> """
> #!/usr/bin/env python
> from sqlalchemy import Column, Unicode, Integer, ForeignKey,
> create_engine
> from sqlalchemy.orm import sessionmaker, relationship
> from sqlalchemy.orm.exc import NoResultFound
> from sqlalchemy.ext.declarative import declarative_base
>
> Base = declarative_base()
>
> class Alpha(Base):
>__tablename__ = 'alpha'
>id = Column(Integer, primary_key=True, nullable=False)
>val = Column(Unicode, nullable=False)
>
>def __init__(self, val):
>self.val = val
>
> class Foo(Base):
>__tablename__ = 'foo'
>alpha_id = Column(Integer, ForeignKey('alpha.id'),
>primary_key=True, nullable=False)
>alpha = relationship(Alpha)
>foo_key = Column(Unicode, primary_key=True, nullable=False)
>foo_val = Column(Integer, nullable=False)
>
>def __init__(self, alpha, foo_key, foo_val):
>self.alpha = alpha
>self.foo_key = foo_key
>self.foo_val = foo_val
>
> engine = create_engine('sqlite:///regr-test.db', echo=True)
> metadata = Base.metadata
> metadata.create_all(engine)
> Session = sessionmaker(bind=engine)
> session = Session()
>
> try:
>a = session.query(Alpha).one()
> except NoResultFound:
>a = Alpha(u'blah')
>session.add(a)
>session.commit()
>
> f = Foo(a, u'mykey', 99)
> merged_f = session.merge(f)
> session.add(merged_f)
> session.commit()
> """
>
> What makes me think this might be a bug is that the script suddenly
> starts working as intended if Foo.__init__ is modified to set
> "self.alpha_id = alpha.id" instead of "self.alpha = alpha".
>
> Is this a bug, or is there some aspect of using a relationship to
> implicitly set self.alpha_id that I'm not understanding?
>
> --
> Erik Swanson
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] (threadlocal) engine.begin() does not return transaction object in SA 0.6.x
On Dec 16, 2010, at 7:02 AM, Ralph Heinkel wrote: > Hi Michael and others, > > we are about upgrading from SA 0.5 to SA 0.6, and some of our code is failing > because it was expecting to obtain a transaction obj from engine.begin() (for > threadlocal engines). In SA 0.4 this even returned a context manager so that > we were able to do > > with engine.begin(): > ... some transactions ... > > Has this been removed for some purpose? I found it very useful, and it fits > with sessions where you can write > > with Session.begin(): > ... some transactions ... The reason that change happened is because the logic of the threadlocal engine was changed to no longer support the mixing of explicit Transaction objects in conjunction with the "thread-scoped" transaction of the thread local engine. It was the returned Transaction object that implemented __exit__() which is why the context manager feature worked. That the reworked threadlocal engine no longer returned any object at all and therefore was no longer a context manager amounts to the inadvertent removal of a desirable behavior. > > > So will this be the future behaviour of engine.begin() that it returns just > None? its not a big deal for TLEngine to return some dummy object that emits a commit/rollback from begin() so ticket 2004 has been added for this. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: Accessing multiple databases with same schema
Thanks for the idea posted.
I shall try this too.
Thanks,
Gayatri
On Fri, Dec 3, 2010 at 11:25 PM, Eric N wrote:
> I had a similar issue that I resolved by creating a set_data_model
> function that when I called in it would set some global variable to
> the table objects based on the product passed in to the function. I'm
> using multiple schemas in a Postgres database and wanted to try to
> limit the number of connections and this was the best way to do that.
> The other benefit is that none of my external scripts need to know
> anything about the schemas or keeping track of different sessions. The
> basic idea is shown below.
>
> Table1 = None
> table1_table = None
> Table2 = None
> table2_table = None
>
> def set_data_model(product):
> global Table1
> global table1_table
> global Table2
> global table2_table
> if product == 'A':
>Table1 = productA.Table1
>table1_table = productA.table1_table
>Table2 = productA.Table2
>table2_table = productA.table2_table
> if product == 'B':
>...
>
>
> On Dec 2, 11:28 am, g3 N wrote:
> > Thanks for the suggestions.
> > I'll try them.
> >
> > Thanks,
> > Gayatri
> >
> > On Thu, Dec 2, 2010 at 8:19 PM, Nagy Viktor
> wrote:
> > > I would say that if no connection between the databases are required
> then
> > > two sessions are pretty fine.
> > > e.g no query like "db1.table1 join db2.table2" exists
> >
> > > otherwise, it might still work to use one session by importing/definig
> your
> > > table classes twice, and adding all of them to your session with
> > > Session.configure(binds={db1.table1: engine1, db2.table1: engine2})
> >
> > > On Thu, Dec 2, 2010 at 3:29 PM, gayatri
> wrote:
> >
> > >> Hello All,
> >
> > >> We have a client-server application. On server side we have two
> > >> databases with same set of tables.
> >
> > >> Based on the client request, server has to get data from the
> > >> corresponding database.
> >
> > >> So, in this scenario, I would like to know which of the following
> > >> approaches is better?
> >
> > >> 1.To have two sessions and have a lookup based on the client request.
> >
> > >> 2.Have a single session and use sharding.
> >
> > >> Thanks,
> > >> Gayatri
> >
> > >> --
> > >> You received this message because you are subscribed to the Google
> Groups
> > >> "sqlalchemy" group.
> > >> To post to this group, send email to sqlalch...@googlegroups.com.
> > >> To unsubscribe from this group, send email to
> > >> sqlalchemy+unsubscr...@googlegroups.com
>
> >
> > >> .
> > >> For more options, visit this group at
> > >>http://groups.google.com/group/sqlalchemy?hl=en.
> >
> > > --
> > > You received this message because you are subscribed to the Google
> Groups
> > > "sqlalchemy" group.
> > > To post to this group, send email to sqlalch...@googlegroups.com.
> > > To unsubscribe from this group, send email to
> > > sqlalchemy+unsubscr...@googlegroups.com
>
> >
> > > .
> > > For more options, visit this group at
> > >http://groups.google.com/group/sqlalchemy?hl=en.
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com
> .
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: Boolean and CheckConstraint
Indexes should be copied by tometadata() and we have test coverage for that (make sure you're on 0.6.5, that's when it was added) so if you can illustrate a failing test we can add another bug report for that. On Dec 16, 2010, at 4:30 AM, Mr.Rech wrote: > Hi Michael, > thanks for you answer and for the patch. In the meanwhile I > implemented the iteration-through-constraints solution as you > suggested. However I've noticed that the indexes defined for each > table must be copied manually (even when using .tometadata() method). > Is there any better (more obvious way) to copy them from one schema to > another that I can't see by myself? > > Thanks, > Andrea > > On Dec 14, 4:46 pm, Michael Bayer wrote: >> On Dec 14, 2010, at 3:58 AM, Mr.Rech wrote: >> >> >> >>> Hi everybody, >>> yesterday I was try to copy a table via SA when I noticed a strange >>> behaviour that I think is a bug. >> >>> Here it is a summary of my issue: what I'm trying to do is to copy a >>> list of tables from one schema to another (my backend is Postgresql, >>> but it only marginally matters), and from the docs seems that >>> table_obj.tometadata(meta, schema='new_schema') is the way to go. >> >>> However running my code I got an unexpected error complaining about >>> some unneeded constraints. It turned out one of my tables has a >>> boolean column, and when copying it the CheckConstraint() on it is >>> passed down to the DLL even if Postgresql supports boolean type >>> natively. >> >>> Reading the SA source code for .tometadata() method I noticed that it >>> calls the CheckConstraint.copy() method and the latter doesn't respect >>> self._create_rule attribute when returning a copy of the constraint >>> itself. I think this is a bug, but maybe I'm wrong. I've also searched >>> the bug reports but without success. Can anyone conferm this? As a >>> temporary fix is there any other way to copy tables from one schema to >>> another? >> >> The _create_rule is backend-agnostic and should be copied along with a >> Constraint, so if that fixes the issue then its pretty likely that's your >> bug. The most effort free workaround for the moment would be to use >> create_constraint=False on your Boolean type.If you're looking for more >> effort then you'd iterate through table.constraints to find the >> CheckConstraint, copy the _create_rule over to the other one. >> >> And I'd like to congratulate you on achieving a special day in SQLAlchemy >> history, ticket # 2000 ! which has been added for this issue. It also >> includes a patch that fixes the bug if you want to just patch for now. >> http://www.sqlalchemy.org/trac/ticket/2000 >> >> >> >>> Thanks, >>> Andrea >> >>> -- >>> You received this message because you are subscribed to the Google Groups >>> "sqlalchemy" group. >>> To post to this group, send email to sqlalch...@googlegroups.com. >>> To unsubscribe from this group, send email to >>> sqlalchemy+unsubscr...@googlegroups.com. >>> For more options, visit this group >>> athttp://groups.google.com/group/sqlalchemy?hl=en. > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalch...@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Sharding
I did use a plain session and set "bind" to either database as needed. It's
working fine.
Thank you.
On Fri, Dec 3, 2010 at 12:24 AM, Michael Bayer wrote:
>
> On Dec 2, 2010, at 1:34 PM, gayatri wrote:
>
> > Hi All,
> >
> > I'm totally new to the usage of Sharding. I have just tried a sample
> > with sqlalchemy ShardedSession.
> >
> > I have two databases (old and new) created with a Projects table. I
> > have a global shardID that indicates which database to operate on.
> > shardID - 'n' - for new DB and
> > shardID - 'o' - for old DB
> >
> > With the initial shardID pointing to the new DB, I have programmed the
> > following steps.
> >
> > Added a record to Projects table, and queried it -- received back the
> > new DB record
> >
> > Changed the shardID to 'o'
> >
> > Added a record to Projects table, and queried it -- received back the
> > old DB record
> >
> > Changed the shardID to 'n'
> >
> > Queried Projects table - Here, as the shardID is pointing to the new
> > DB i should get records from the newDB projects table, whereas I'm
> > receiving the record from th old DB
> >
> > But, If I add another record to the Projects table before this step,
> > then the qury returns me the new DB records.
> >
> > Can anyone let me know the reason for this behaviour? Or is there any
> > mistake in the program?
> >
> > Below is the code which I have used:
>
> The core concept of horizontal sharding is that different records belong
> exclusively to one database node or another, which are blended together as
> results that are loaded from one or both nodes simultaneously based on
> rules. Your approach below uses a global flag, meaning the session can only
> reference one shard at a time, so any query against the Session is only
> capable of returning results from one database at a time.If you only
> want to look at one database at a time for all records, there's no reason to
> use ShardedSession, just use a plain session and set "bind" to either
> database as needed.
>
>
>
>
> >
> > #test_shard.py
> >
> > from sqlalchemy import create_engine
> > from sqlalchemy import Table, Column, Integer, String, MetaData
> > from sqlalchemy.orm import sessionmaker, mapper
> > from sqlalchemy.ext.horizontal_shard import ShardedSession
> >
> >
> > #code added to use sharding
> > shardID = 'n' #default
> > oldDBEngine = create_engine('sqlite:///:memory:')
> > newDBEngine = create_engine('sqlite:///:memory:')
> > DBSession = sessionmaker(class_=ShardedSession)
> > DBSession.configure(shards={'o':oldDBEngine, 'n':newDBEngine})
> > metadata= MetaData()
> >
> >
> > def shard_chooser(mapper, instance, clause=None):
> >return shardID
> >
> > def id_chooser(query, ident):
> >return shardID
> >
> > def query_chooser(query):
> >return shardID
> >
> > # further configure create_session to use these functions
> > DBSession.configure(shard_chooser=shard_chooser,
> > id_chooser=id_chooser,
> > query_chooser=query_chooser)
> >
> >
> > projects_table = Table('projects', metadata,
> > Column('id', Integer, primary_key=True), #
> > primary key
> > Column('name', String(40)), # foreign key
> > Column('prefix', String(6))
> > )
> >
> > class Project(object):
> >def __init__(self, name, prefix):
> >self.name = name
> >self.prefix = prefix
> >
> > mapper(Project, projects_table)
> >
> >
> > # create tables
> > for dbEngine in (newDBEngine, oldDBEngine):
> >metadata.drop_all(dbEngine)
> >metadata.create_all(dbEngine)
> >
> >
> > test_shard
> > session = DBSession()
> >
> > print "-- add to new db with shardID", shardID #default
> > shardID in 'n' ponting to new database
> > p1_new = Project('p1_new', 'p1_v1_new')
> > session.add(p1_new)
> > session.commit()
> > print
> >
> > print "-- get from new db with shardID", shardID
> > query= session.query(Project)
> > projects = query.all()
> > for p in projects:
> >print p.id, p.name, p.prefix
> > print
> >
> > shardID = 'o'
> > print "-- add to old db with shardID", shardID
> > p1_old = Project('p1_old', 'p1_v1_old')
> > session.add(p1_old)
> > session.commit()
> > print
> >
> > print "-- get from old db with shardID", shardID
> > query= session.query(Project)
> > projects = query.all()
> > for p in projects:
> >print p.id, p.name, p.prefix
> > print
> >
> >
> > shardID = 'n'
> >
> > ##print "-- add to new db with shardID", shardID
> > ##p2_new = Project('p2_new', 'p2_v2_new')
> > ##session.add(p2_new)
> > ##session.commit()
> > ##print
> >
> > print "-- get from new db with shardID", shardID
> > query= session.query(Project)
> > projects = query.all()
> > for p in projects:
> >print p.id, p.name, p.prefix
> >
> >
> > Thanks,
> > Gayatri
> >
> >
> >
> > --
> > You received this message because you are su
Re: [sqlalchemy] Session.add performance
On Dec 16, 2010, at 12:39 AM, Julian Scheid wrote: > In an application that is heavy on inserts and updates, cProfile > output is dominated by Session.add in which about 45% of time is > spent. Most of that time, in turn, is spent in cascade_iterator (43%). > I can provide more detailed information if needed. > > The application does aggressive caching of data and has set > expire_on_commit=False, in order to keep database load down. Is that > the reason for Session.add slowness? > > Is there a way I can speed this up while keeping a similar level of > cache aggressiveness? > > For example, in one test run Session.__contains__ was invoked 25m > times over the course of only a few minutes, accounting for 27% of > total time spent. Could it be a good idea to try and override this > function with one that's optimized for this specific use case? > > Also, so far I haven't spent any effort expunging objects from the > session as soon as possible. Some objects might linger for longer > than necessary. Would they contribute to Session.add's overhead? A major part of development resources as of late have been focused on add() and cascade_iterator(). I would advise trying out the 0.7 tip from mercurial where we've cut out a lot of overhead out of many areas of the flush including add() + cascade_iterator (see http://techspot.zzzeek.org/2010/12/12/a-tale-of-three-profiles/ for some profiling output). Things like inlining Session.__contains__ are good ideas if they are shown to be prominent in a slow profile, so if you want to send along a test script to me that illustrates your bottlenecks I can work on its pain points and add it to our suite. > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalch...@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: Why does Session.merge only look at primary key and not all unique keys?
On Dec 15, 2010, at 11:50 PM, Russell Warren wrote: > > Ok again... but you also agree that the use of surrogate keys is > standard (and some say "necessary") practice. In the case of Sqlite > (as in my example), adding this surrogate key automatically makes the > schema "unreasonable" because you now need to have the primary key as > the meaningless surrogate key (single id column), and the natural key > ends up being relegated to just another unique index. Unfortunately, > the latter renders the object/table useless for use with the useful > session.merge() function. I don't recall the details, but I think > there may be a similar PostgreSQL limitation regarding autoincrements > as well. Well that's only if you're trying to use merge() in some way where the objects you place in it ahead of time are "the same" based on unique keys but are not "the same" based on identity. That's not an invalid use case but its beyond the scope of what merge() and the identity map is designed to work with, hence the external recipes. With merge() you would typically pass in an object for the first time, get its .id back via flush or autoflush, then associate that .id with your unique key combination before continuing to merge() further objects. Unique object is doing mostly this. > Regarding RunSnakeRun, I've used it as well and like it. However, I'm > a bit of a resolution junkie and the box frames in RunSnakeRun's > visual output are limited in how they will squish all the box borders > together. > If you haven't given kcachegrind a shot yet for viewing > cProfile results, you might want to give it a trial run as the visual > output is a better representation of timing scale, and the viewing is > more powerful as well (if resolution is too weak if an argument). > Some tips to get it to work well for python are here: > http://stackoverflow.com/questions/1896032/using-cprofile-results-with-kcachegrind if i understand correctly i would imagine that same issue exists with any display of that typeI double click on a particular box to get a detailed view of everything inside of it. kcachegrind seems to have the same kind of output in the lower left corner, if i can figure out how to run this on OSX ill give it a shot, thanks. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Session.merge() failure when primary key is assigned via a relationship
I'm not sure whether this a bug or not:
When a mapped object has a foreign key reference to another object as
part of its own primary key, setting that column's value via a
relationship property causes session.merge() to not SELECT the object
first and thus encounter an IntegrityError when it tries to INSERT the
object.
I have written a reduction of this problem. When run as a script, the
second and later invocations will die with an IntegrityError.
"""
#!/usr/bin/env python
from sqlalchemy import Column, Unicode, Integer, ForeignKey,
create_engine
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.orm.exc import NoResultFound
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Alpha(Base):
__tablename__ = 'alpha'
id = Column(Integer, primary_key=True, nullable=False)
val = Column(Unicode, nullable=False)
def __init__(self, val):
self.val = val
class Foo(Base):
__tablename__ = 'foo'
alpha_id = Column(Integer, ForeignKey('alpha.id'),
primary_key=True, nullable=False)
alpha = relationship(Alpha)
foo_key = Column(Unicode, primary_key=True, nullable=False)
foo_val = Column(Integer, nullable=False)
def __init__(self, alpha, foo_key, foo_val):
self.alpha = alpha
self.foo_key = foo_key
self.foo_val = foo_val
engine = create_engine('sqlite:///regr-test.db', echo=True)
metadata = Base.metadata
metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
try:
a = session.query(Alpha).one()
except NoResultFound:
a = Alpha(u'blah')
session.add(a)
session.commit()
f = Foo(a, u'mykey', 99)
merged_f = session.merge(f)
session.add(merged_f)
session.commit()
"""
What makes me think this might be a bug is that the script suddenly
starts working as intended if Foo.__init__ is modified to set
"self.alpha_id = alpha.id" instead of "self.alpha = alpha".
Is this a bug, or is there some aspect of using a relationship to
implicitly set self.alpha_id that I'm not understanding?
--
Erik Swanson
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] (threadlocal) engine.begin() does not return transaction object in SA 0.6.x
Hi Michael and others, we are about upgrading from SA 0.5 to SA 0.6, and some of our code is failing because it was expecting to obtain a transaction obj from engine.begin() (for threadlocal engines). In SA 0.4 this even returned a context manager so that we were able to do with engine.begin(): ... some transactions ... Has this been removed for some purpose? I found it very useful, and it fits with sessions where you can write with Session.begin(): ... some transactions ... So will this be the future behaviour of engine.begin() that it returns just None? Just being curious. Thanks, Ralph -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Why does rollback fail here?
Hi,
can anybody explain to me why the update statement on conn1 below is
not rolled back? conn2 should be in autocommit mode, but why does it
commit transactions of conn1?
Any help could be appreciated.
Thanks
Ralph
---
from sqlalchemy import *
engine = create_engine('sqlite:///s.db', echo=True)
conn0 = engine.connect()
conn1 = engine.connect()
conn2 = engine.connect()
conn0.execute('create table atesttable (id int, name varchar(20))')
conn0.execute("insert into atesttable (id, name) values (1, 'otto')")
conn0.execute("insert into atesttable (id, name) values (2, 'gustav')")
tr = conn1.begin()
conn1.execute("update atesttable set name='ottox28' where id=1")
conn2.execute("update atesttable set name='gustavXX' where id=2")
tr.rollback()
assert conn0.execute("select name from atesttable where
id=1").fetchone()[0] == 'otto' # <--- this fails!!
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Boolean and CheckConstraint
Hi Michael, thanks for you answer and for the patch. In the meanwhile I implemented the iteration-through-constraints solution as you suggested. However I've noticed that the indexes defined for each table must be copied manually (even when using .tometadata() method). Is there any better (more obvious way) to copy them from one schema to another that I can't see by myself? Thanks, Andrea On Dec 14, 4:46 pm, Michael Bayer wrote: > On Dec 14, 2010, at 3:58 AM, Mr.Rech wrote: > > > > > Hi everybody, > > yesterday I was try to copy a table via SA when I noticed a strange > > behaviour that I think is a bug. > > > Here it is a summary of my issue: what I'm trying to do is to copy a > > list of tables from one schema to another (my backend is Postgresql, > > but it only marginally matters), and from the docs seems that > > table_obj.tometadata(meta, schema='new_schema') is the way to go. > > > However running my code I got an unexpected error complaining about > > some unneeded constraints. It turned out one of my tables has a > > boolean column, and when copying it the CheckConstraint() on it is > > passed down to the DLL even if Postgresql supports boolean type > > natively. > > > Reading the SA source code for .tometadata() method I noticed that it > > calls the CheckConstraint.copy() method and the latter doesn't respect > > self._create_rule attribute when returning a copy of the constraint > > itself. I think this is a bug, but maybe I'm wrong. I've also searched > > the bug reports but without success. Can anyone conferm this? As a > > temporary fix is there any other way to copy tables from one schema to > > another? > > The _create_rule is backend-agnostic and should be copied along with a > Constraint, so if that fixes the issue then its pretty likely that's your > bug. The most effort free workaround for the moment would be to use > create_constraint=False on your Boolean type. If you're looking for more > effort then you'd iterate through table.constraints to find the > CheckConstraint, copy the _create_rule over to the other one. > > And I'd like to congratulate you on achieving a special day in SQLAlchemy > history, ticket # 2000 ! which has been added for this issue. It also > includes a patch that fixes the bug if you want to just patch for now. > http://www.sqlalchemy.org/trac/ticket/2000 > > > > > Thanks, > > Andrea > > > -- > > You received this message because you are subscribed to the Google Groups > > "sqlalchemy" group. > > To post to this group, send email to sqlalch...@googlegroups.com. > > To unsubscribe from this group, send email to > > sqlalchemy+unsubscr...@googlegroups.com. > > For more options, visit this group > > athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Use of table aliases
Thanks for links, I found `aliased` in docs but not in tutorials,
I got errors using it but probably I was using it in the wrong way,
now it's clearer.
On Dec 16, 2:39 am, Michael Bayer wrote:
> On Dec 15, 2010, at 9:04 AM, neurino wrote:
>
>
>
>
>
> > Hello I have 2 tables: data and acquisitions,
> > - each Acquisition has many Data
> > - each Data come from a different sensor
> > - the single sensor is identified by the couple Acquisition.id_centr,
> > Data.id_meas
>
> > No I need a query with one colum for each sensor and a row for each
> > Acquisition.datetime
>
> > This is how I get it (in case of two sensors) with SQL:
>
> q = curs.execute("""
> > SELECT a.datetime, d1.value, d2.value
> > FROM acquisitions AS a
> > LEFT JOIN data AS d1
> > ON a.id_acq=d1.id_acq
> > AND a.id_centr=159
> > AND d1.id_meas=1501
> > LEFT JOIN data AS d2
> > ON a.id_acq=d2.id_acq
> > AND a.id_centr=320
> > AND d2.id_meas=1551
> > """)
> for n, row in enumerate(q): print n, row
> > :
> > 0 (u'2010-09-02 12:05:00', 23.98, 25.67)
> > 1 (u'2010-09-02 12:10:00', 23.77, 25.57)
> > 2 (u'2010-09-02 12:15:00', 23.96, 25.57)
> > 3 (u'2010-09-02 12:20:00', 24.78, 25.94)
> > 4 (u'2010-09-02 12:25:00', 25.48, 26.27)
> > 5 (u'2010-09-02 12:30:00', 25.91, 26.46)
> > 6 (u'2010-09-02 12:35:00', 26.14, 26.62)
> > 7 (u'2010-09-02 12:40:00', 26.32, 26.73)
> > 8 (u'2010-09-02 12:45:00', 26.44, 26.80)
> > 9 (u'2010-09-02 12:50:00', 26.55, 26.87)
> > 10 (u'2010-09-02 12:55:00', 26.62, 26.92)
> > 11 (u'2010-09-02 13:00:00', 26.67, 26.94)
> > 12 (u'2010-09-02 13:05:00', 26.69, 26.94)
> > 13 (u'2010-09-02 13:10:00', 26.71, 26.96)
> > 14 (u'2010-09-02 13:15:00', 26.73, 26.98)
>
> > But I can't get the same result with sqlalchemy, here's my mapping:
>
> > data = Table('data', metadata,
> > Column('id_data', Integer, primary_key=True),
> > Column('id_meas', Integer, nullable=False),
> > Column('id_acq', Integer, ForeignKey('acquisitions.id_acq'),
> > nullable=False),
> > Column('value', Float, nullable=False),
> > )
>
> > acquisitions = Table('acquisitions', metadata,
> > Column('id_acq', Integer, primary_key=True),
> > Column('id_centr', Integer, nullable=False),
> > Column('datetime', DateTime, nullable=False),
> > #acquisitions with same id_centr and datetime are duplicates
> > UniqueConstraint('id_centr', 'datetime'),
> > )
>
> > orm.mapper(Data, data, properties={
> > 'acquisitions': orm.relationship(Acquisition, backref='data'),
> > })
> > orm.mapper(Acquisition, acquisitions)
>
> to create aliases during an ORM query you use the aliased() construct.
> There's examples at:
>
> http://www.sqlalchemy.org/docs/orm/tutorial.html#using-aliases
>
> you'd also be using sqlalchemy.and_() to formulate those outerjoin()
> conditions.
>
>
>
>
>
> > Any advice?
>
> > Thanks for your support
> > neurino
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "sqlalchemy" group.
> > To post to this group, send email to sqlalch...@googlegroups.com.
> > To unsubscribe from this group, send email to
> > sqlalchemy+unsubscr...@googlegroups.com.
> > For more options, visit this group
> > athttp://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
