Re: [sqlalchemy] Re: Elixir question
On 02/03/2012 12:08 PM, lars van gemerden wrote:
I should probably make the pair method:
def pair(name1, name2):
p1, p2 = Pairs(name1), Pairs(name2)
p1.other = p2
p2.other = p1
On Feb 3, 11:57 am, lars van gemerdenl...@rational-it.com wrote:
Hi, I am trying to sote pairs in a table as follows:
#--
from elixir import *
metadata.bind = sqlite:///:memory:
metadata.bind.echo = False
class Pairs(Entity):
name = Field(String(50), primary_key = True)
other = OneToOne('Pairs', inverse = 'other')
You can't have a OneToOne as inverse for a OneToOne, even less for
itself. Valid relationship pairs are:
ManyToOne - OneToOne
ManyToOne - OneToMany
ManyToMany - ManyToMany
In your case you want:
class Pairs(Entity):
name = Field(String(50), primary_key = True)
other1 = ManyToOne('Pairs', inverse = 'other2')
other2 = OneToOne('Pairs', inverse = 'other1')
and if your database really only stores pairs, a property might make it
more elegant:
@property
def other(self):
return self.other1 if self.other1 is not None else self.other2
As a side note, you probably do not want to use Elixir for a new
project, as Elixir is not maintained anymore.
-G.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@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: Elixir question
OK, thank you,
I went back to SQLA and came up with this for now (simplified):
class Pairs(Base):
__tablename__ = 'Pairs'
name = Column(String(20), primary_key=True)
other_name = Column(String(20), ForeignKey('Pairs.name'), nullable
= False)
other = relationship('Pairs',
primaryjoin = 'Pairs.name ==
Pairs.other_name',
remote_side=[name])
def __init__(self, name):
self.name = name
def __repr__(self):
return (%s, %s) % (self.name, self.other.name)
def pair(name1, name2):
p1, p2 = Pairs(name1), Pairs(name2)
p1.other_name = name2
p2.other_name = name1
return p1, p2
if __name__ == '__main__':
p1, p2 = pair('apple', 'pear')
session.add_all([p1, p2])
session.commit()
for p in session.query(Pairs).all():
print p
assert p1.other.other is p1
--
Note that there is no backref on other and that the primaryjoin is
completely written out (otherwise a got a mysterious (to me) error,
when using joined inheritance at the same time).
This solution is key to my datamodel. Does anyone see any drawbacks?
Cheers, Lars
On Feb 5, 10:50 am, Gaëtan de Menten gdemen...@gmail.com wrote:
On 02/03/2012 12:08 PM, lars van gemerden wrote:
I should probably make the pair method:
def pair(name1, name2):
p1, p2 = Pairs(name1), Pairs(name2)
p1.other = p2
p2.other = p1
On Feb 3, 11:57 am, lars van gemerdenl...@rational-it.com wrote:
Hi, I am trying to sote pairs in a table as follows:
#--
from elixir import *
metadata.bind = sqlite:///:memory:
metadata.bind.echo = False
class Pairs(Entity):
name = Field(String(50), primary_key = True)
other = OneToOne('Pairs', inverse = 'other')
You can't have a OneToOne as inverse for a OneToOne, even less for
itself. Valid relationship pairs are:
ManyToOne - OneToOne
ManyToOne - OneToMany
ManyToMany - ManyToMany
In your case you want:
class Pairs(Entity):
name = Field(String(50), primary_key = True)
other1 = ManyToOne('Pairs', inverse = 'other2')
other2 = OneToOne('Pairs', inverse = 'other1')
and if your database really only stores pairs, a property might make it
more elegant:
@property
def other(self):
return self.other1 if self.other1 is not None else self.other2
As a side note, you probably do not want to use Elixir for a new
project, as Elixir is not maintained anymore.
-G.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@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: Elixir question
Sorry, scrap the remark about primaryjoin ... inheritance. INheritance
wasn't the problem.
On Feb 5, 1:27 pm, lars van gemerden l...@rational-it.com wrote:
OK, thank you,
I went back to SQLA and came up with this for now (simplified):
class Pairs(Base):
__tablename__ = 'Pairs'
name = Column(String(20), primary_key=True)
other_name = Column(String(20), ForeignKey('Pairs.name'), nullable
= False)
other = relationship('Pairs',
primaryjoin = 'Pairs.name ==
Pairs.other_name',
remote_side=[name])
def __init__(self, name):
self.name = name
def __repr__(self):
return (%s, %s) % (self.name, self.other.name)
def pair(name1, name2):
p1, p2 = Pairs(name1), Pairs(name2)
p1.other_name = name2
p2.other_name = name1
return p1, p2
if __name__ == '__main__':
p1, p2 = pair('apple', 'pear')
session.add_all([p1, p2])
session.commit()
for p in session.query(Pairs).all():
print p
assert p1.other.other is p1
--
Note that there is no backref on other and that the primaryjoin is
completely written out (otherwise a got a mysterious (to me) error,
when using joined inheritance at the same time).
This solution is key to my datamodel. Does anyone see any drawbacks?
Cheers, Lars
On Feb 5, 10:50 am, Gaëtan de Menten gdemen...@gmail.com wrote:
On 02/03/2012 12:08 PM, lars van gemerden wrote:
I should probably make the pair method:
def pair(name1, name2):
p1, p2 = Pairs(name1), Pairs(name2)
p1.other = p2
p2.other = p1
On Feb 3, 11:57 am, lars van gemerdenl...@rational-it.com wrote:
Hi, I am trying to sote pairs in a table as follows:
#--
from elixir import *
metadata.bind = sqlite:///:memory:
metadata.bind.echo = False
class Pairs(Entity):
name = Field(String(50), primary_key = True)
other = OneToOne('Pairs', inverse = 'other')
You can't have a OneToOne as inverse for a OneToOne, even less for
itself. Valid relationship pairs are:
ManyToOne - OneToOne
ManyToOne - OneToMany
ManyToMany - ManyToMany
In your case you want:
class Pairs(Entity):
name = Field(String(50), primary_key = True)
other1 = ManyToOne('Pairs', inverse = 'other2')
other2 = OneToOne('Pairs', inverse = 'other1')
and if your database really only stores pairs, a property might make it
more elegant:
@property
def other(self):
return self.other1 if self.other1 is not None else self.other2
As a side note, you probably do not want to use Elixir for a new
project, as Elixir is not maintained anymore.
-G.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@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: Expiring only unchanged stale data
Thanks Michael, the 'solution' is seems was to refactor my code so that operations are clearly performed in the correct order, and in some cases to explicitly load and/or update the tree parameters when they might have changed. Now I'm not only doing before_flush (which I've restricted to session/ORM queries only), but also before/after insert, update, and delete (where I only do SQL expression queries). On Feb 4, 8:52 am, Michael Bayer mike...@zzzcomputing.com wrote: On Feb 4, 2012, at 11:23 AM, Mark Friedenbach wrote: Hi, I'm running into a problem with my nested sets implementation. Inserting, moving, or removing a node can potentially affect one or more of many other nodes' tree properties (tree id, left, right, depth, or parent relationship). For efficiency's sake this change occurs as a single, rather complex SQL expression query that handles the magic of updating all the other node values. Just as a precaution I've added a session.expire_all() after the session.execute(query), so that the tree values will be reloaded as I move on to process other node operations in the same transaction. However what I've discovered is that expire_all() causes *all* as-of- yet unpersisted changes to be lost. As an example of what I mean, here's an actual shell log: obj = session.query(...) obj.name u'root1' obj.name = 'root66' session.add(obj) session.expire_all() session.commit() obj.name u'root1' It may be possible that I can restructure the order in which I do things so that stale data isn't an issue. But out of curiosity, is there a way to expire only *unchanged* stale data? This is how I naïvely expected expire_all() to work. all means everything, that method is called typically after rollback() or commit() in conjunction with the transaction. While there is a way to detect history on all attributes and expire just those with no net change, this is a time consuming operation and should not be necessary. In this case, you know that the only values that are being updated outside of the normal flush process are the left and right columns (and whatever denormalized data you're storing such as depth), so you should just be expiring those, and it should be either within the after_flush event: http://docs.sqlalchemy.org/en/latest/orm/events.html?highlight=after%... These attributes will refresh themselves when next accessed. Or if you have a means available of populating some of these attributes with their correct value instead of just expiring, you can use attributes.set_committed_value(): http://docs.sqlalchemy.org/en/latest/orm/session.html?highlight=set_c... Reading your paragraph again, if you're actually doing the math for left/right/depth in Python and need the value of those attributes to be correct as the flush() proceeds, I'd consider doing the math in SQL, as you can't assume all the nodes are going to be loaded into memory. Alternatively, a good API for this case would have been an expire_all(mapped_class, ['attribute', 'names']), a sort of compromise between expire() and expire_all(). You can roll this yourself: for obj in session.identity_map.values(): if isinstance(obj, myclass): session.expire(obj, ['a', 'b']) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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 sqlalchemy@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] Disabling auto-conversion to Decimal in Oracle connections
How do I set up an engine so that Oracle Number fields are not
converted to Decimal?
I'm dealing with a bunch of code with hand-rolled SQL query strings,
and as the first step was going to use connections from SqlAlchemy's
connection pool, as I change the queries one-by-one. The only problem
is that our main queries get 500 Number columns and SqlAlchemy is
converting them to Decimals, slowing the queries down by a factor of
10.
Here's an example
engine = sqlalchemy.create_engine(oracle+cx_oracle://+connString)
conn = engine.pool.connect()
cursor = conn.cursor()
cursor.execute(SELECT * FROM MY_TABLE)
r = cursor.fetchone()
r[-1]
Decimal('0.878935370620606')
The conversion is done via the outputtypehandler of the connection
object. I can circumvent it by either setting
cursor.connection.outputtypehandler = None
or
cursor.outputtypehandler = lambda *args : None
but is there a better, more standard way to convert the Numbers to
floats and not Decimals for arbitrary queries?
(Setting engine.dialect.supports_native_decimal to False doesn't work.
I believe the dialect is creating the output type handler before any
connection is opened.)
--
--Anthony
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@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] Disabling auto-conversion to Decimal in Oracle connections
On Feb 5, 2012, at 7:50 PM, Anthony Foglia wrote:
How do I set up an engine so that Oracle Number fields are not
converted to Decimal?
I'm dealing with a bunch of code with hand-rolled SQL query strings,
and as the first step was going to use connections from SqlAlchemy's
connection pool, as I change the queries one-by-one. The only problem
is that our main queries get 500 Number columns and SqlAlchemy is
converting them to Decimals, slowing the queries down by a factor of
10.
Here's an example
engine = sqlalchemy.create_engine(oracle+cx_oracle://+connString)
conn = engine.pool.connect()
cursor = conn.cursor()
cursor.execute(SELECT * FROM MY_TABLE)
r = cursor.fetchone()
r[-1]
Decimal('0.878935370620606')
The conversion is done via the outputtypehandler of the connection
object. I can circumvent it by either setting
cursor.connection.outputtypehandler = None
or
cursor.outputtypehandler = lambda *args : None
but is there a better, more standard way to convert the Numbers to
floats and not Decimals for arbitrary queries?
(Setting engine.dialect.supports_native_decimal to False doesn't work.
I believe the dialect is creating the output type handler before any
connection is opened.)
The output handler is there because otherwise cx_oracle immediately converts to
Python float, which is then a lossy format. So we need to turn that off on
cx_oracle's side immediately. There's a lot of discussion of this at
http://docs.sqlalchemy.org/en/latest/dialects/oracle.html#precision-numerics .
My first suggestion would be to just use cdecimal. That way performance would
not be an issue - I patch it in at program start time using the example at
http://docs.sqlalchemy.org/en/latest/core/types.html#sqlalchemy.types.Numeric .
I've been using this approach in production financial applications for
several years without issue.
If you use the Float() type, the Decimal will be coerced into a regular
float(), but you're looking for performance here so that's not the solution.
There's not a public API right now to turn off this handling - it would imply
the _OracleNumeric type and other parts of the cx_oracle dialect would need to
be further complicated to support two modes of operation, and it was enormously
difficult to get precision numeric round trips as it is.A monkeypatch that
would force it off would be:
engine = create_engine(...)
engine.dialect._to_decimal = float
another way you could do it would be to use a connection pool event. Using
the connect event:
from sqlalchemy import event
@event.listens_for(engine, connect)
def connect(connection, rec):
connection.outputtypehandler = None
Note that disabling the outputtypehandler will also mess up unicode handling,
unless you replace it with another output handler that returns a unicode
cursor.var().
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@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] Disabling auto-conversion to Decimal in Oracle connections
Thanks for the quick reply. A few follow-up questions... On Sun, Feb 5, 2012 at 9:03 PM, Michael Bayer mike...@zzzcomputing.com wrote: My first suggestion would be to just use cdecimal. That way performance would not be an issue - I patch it in at program start time using the example at http://docs.sqlalchemy.org/en/latest/core/types.html#sqlalchemy.types.Numeric . I've been using this approach in production financial applications for several years without issue. That looks ideal. I'll have to see about adding that to our systems. If you use the Float() type, the Decimal will be coerced into a regular float(), but you're looking for performance here so that's not the solution. Does that mean when I eventually I set up a table with a column of type Float(asdecimal=False), I'll still be hit by the conversion penalty as the read data is converted to a Decimal first by the connection, and then to a Float by whatever handles the columns? It sounds like yes. There's not a public API right now to turn off this handling - it would imply the _OracleNumeric type and other parts of the cx_oracle dialect would need to be further complicated to support two modes of operation, and it was enormously difficult to get precision numeric round trips as it is. A monkeypatch that would force it off would be: engine = create_engine(...) engine.dialect._to_decimal = float another way you could do it would be to use a connection pool event. Using the connect event: from sqlalchemy import event @event.listens_for(engine, connect) def connect(connection, rec): connection.outputtypehandler = None Note that disabling the outputtypehandler will also mess up unicode handling, unless you replace it with another output handler that returns a unicode cursor.var(). I'm not too worried about unicode conversions. All the text in the database should be ASCII, and I've seen no attempts at unicode handling anywhere in the code base. If I do shut off the outputtypehandler on the connection, will that cause any other problems as I start adding Table objects with the appropriate Columns? Will the connection outputtypehandler be reset when it goes back into the pool? Or should I just keep one connection from returning to the pool solely for these old style queries? -- --Anthony -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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.
