Re: [sqlalchemy] Parent child relationships
On 2/13/2014 11:45 AM, Michael Bayer wrote:
So for children above you need to spell out primaryjoin completely which is
primaryjoin=and_(Animal.sire_id == Animal.id_, Animal.dam_id == Animal.id).
Thought I was on the right track but now getting the exception below. Here's
the model:
class Animal(Base):
__tablename__ = 'animals'
id_ = Column(Integer, primary_key=True)
sire_id = Column(Integer, ForeignKey('animals.id_'))
dam_id = Column(Integer, ForeignKey('animals.id_'))
sire = relationship('Animal', foreign_keys=[sire_id])
dam = relationship('Animal', foreign_keys=[dam_id])
pjoin = 'and_(Animal.sire_id == Animal.id_, Animal.dam_id == Animal.id_)'
children = relationship('Animal', foreign_keys=[sire_id, dam_id],
primaryjoin=pjoin)
So I attempt to put in the first object, which is to be a bit special:
unknown = Animal(id_=0)
db_session.add(unknown)
unknown.sire = unknown # - get exception here
unknown.dam = unknown
db_session.commit()
TypeError: Incompatible collection type: Animal is not list-like
unknown.sire shows to contain [] so it evidently wants a list of sires? That's
not what I had in mind for the above model. Any help?
Thanks,
Michael
--
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/groups/opt_out.
Re: [sqlalchemy] Parent child relationships
On 2/14/2014 11:50 AM, Michael Bayer wrote:
On Feb 14, 2014, at 12:46 PM, Michael Hipp mich...@redmule.com wrote:
On 2/13/2014 11:45 AM, Michael Bayer wrote:
So for children above you need to spell out primaryjoin completely which is
primaryjoin=and_(Animal.sire_id == Animal.id_, Animal.dam_id == Animal.id).
Thought I was on the right track but now getting the exception below. Here's
the model:
class Animal(Base):
__tablename__ = 'animals'
id_ = Column(Integer, primary_key=True)
sire_id = Column(Integer, ForeignKey('animals.id_'))
dam_id = Column(Integer, ForeignKey('animals.id_'))
sire = relationship('Animal', foreign_keys=[sire_id])
dam = relationship('Animal', foreign_keys=[dam_id])
pjoin = 'and_(Animal.sire_id == Animal.id_, Animal.dam_id == Animal.id_)'
children = relationship('Animal', foreign_keys=[sire_id, dam_id],
primaryjoin=pjoin)
So I attempt to put in the first object, which is to be a bit special:
unknown = Animal(id_=0)
db_session.add(unknown)
unknown.sire = unknown # - get exception here
unknown.dam = unknown
db_session.commit()
TypeError: Incompatible collection type: Animal is not list-like
unknown.sire shows to contain [] so it evidently wants a list of sires? That's
not what I had in mind for the above model. Any help?
well here we're doing a self-referential relationship so in order to make a
many-to-one self ref, as someone else mentioned you need remote_side=sire_id
background:
http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#adjacency-list-relationships
Changed model to read:
sire = relationship('Animal', foreign_keys=[sire_id], remote_side=sire_id)
Same exception: TypeError: Incompatible collection type: Animal is not list-like
Also tried it with remote_side=[sire_id], same exception
Also tried putting it on the 'children' relationship, same exception:
children = relationship('Animal', foreign_keys=[sire_id, dam_id],
primaryjoin=pjoin, remote_side=[sire_id, dam_id])
Thanks,
Michael
--
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/groups/opt_out.
Re: [sqlalchemy] Parent child relationships
On 2/14/2014 1:51 PM, Michael Bayer wrote:
right this is why reading the docs is better, those have been checked...
remote side for m2o refers to the primary key, so:
The docs says: '...directive is added known as remote_side, which is a Column
or collection of Column objects that indicate those which should be considered
to be remote:'
Nothing there particularly hints that it must be the primary key.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Animal(Base):
__tablename__ = 'animals'
id_ = Column(Integer, primary_key=True)
sire_id = Column(Integer, ForeignKey('animals.id_'))
dam_id = Column(Integer, ForeignKey('animals.id_'))
sire = relationship('Animal', foreign_keys=[sire_id], remote_side=id_)
dam = relationship('Animal', foreign_keys=[dam_id], remote_side=id_)
pjoin = 'and_(Animal.sire_id == Animal.id_, Animal.dam_id == Animal.id_)'
children = relationship('Animal', primaryjoin=pjoin)
e = create_engine(sqlite://, echo=True)
Base.metadata.create_all(e)
s = Session(e)
a1, a2, a3, a4 = Animal(), Animal(), Animal(), Animal()
a1.sire = a3
This is working great until I add in this line:
a1.sire = a1
Gives exception:
sqlalchemy.exc.CircularDependencyError: Circular dependency detected. Cycles:
set([ProcessState(ManyToOneDP(Animal.sire), Animal at 0x33f13b0,
delete=False), SaveUpdateState(Animal at 0x33f13b0)]) all edges:
set([(SaveUpdateState(Animal at 0x348b970),
ProcessState(ManyToOneDP(Animal.dam), Animal at 0x33fa430, delete=False)),
(SaveUpdateState(Animal at 0x33f13b0), ProcessState(ManyToOneDP(Animal.sire),
Animal at 0x33f13b0, delete=False))
...
No bull ever sired himself. But the first object I intended to populate in the
db would be the unknown sire who would just have unknown (himself) as a sire.
The 'sire' column needs to be not nullable or I'd just leave it blank.
Any suggestions?
also reading the terms here, what's children supposed to be? wouldn't that AND be
an OR here if I understand correctly?
Yes, quite right.
Thanks for your kind help,
Michael
--
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/groups/opt_out.
Re: [sqlalchemy] Parent child relationships
On 2/14/2014 2:34 PM, Michael Bayer wrote: A basic fact of a self referential relationship is that you're building a tree. The root of the tree has to be NULL and I'd advise against trying to work around that. Now if you wanted to in fact assign the object's own primary key to the foreign key column, you can (manually), but even then, you'd need to know the primary key up front. If you're relying on the database to generate a primary key value, you still won't have that value in time, and the column would still be NULL. It seems to work ok if I just fake the id=0 and a1.sire_id=0, we'll see what happens when I move to PostgreSQL. So for the moment I can move on to other fires. I think where you're at with this you might want to keep it simple to start. Yes. But I've had this working for quite a while now on the Django ORM, never considered that it was something particularly unusual. Thanks, Michael -- 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/groups/opt_out.
Re: [sqlalchemy] Parent child relationships
On 2/14/2014 3:36 PM, Michael Bayer wrote: the django ORM would write an autogenerated primary key value to a foreign key column at the same time in a single INSERT? What magic might they have discovered there? (hint: i am sure they don't do that) Naw. If you recall I was supplying the pkey Animal(id_=0) from the get-go. That's prolly what allowed it to work. And it's the same for SQlAlchemy now that I know to stuff the sire_id rather than feed an object to sire. We'll see if my hack holds up when I try this on a real database :-) Michael -- 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/groups/opt_out.
[sqlalchemy] Parent child relationships
I'm trying to do something like this:
class Animal(Base):
__tablename__ = 'animals'
id_ = Column(Integer, primary_key=True)
sire_id = Column(Integer, ForeignKey('animals.id_'))
dam_id = Column(Integer, ForeignKey('animals.id_'))
sire = relationship('Animal', foreign_keys=[sire_id])
dam = relationship('Animal', foreign_keys=[dam_id])
children = relationship('Animal', foreign_keys=[sire_id, dam_id])
It gives:
sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition
between parent/child tables on relationship Animal.first_owner - there are
multiple foreign key paths linking the tables. Specify the 'foreign_keys'
argument, providing a list of those columns which should be counted as
containing a foreign key reference to the parent table.
I've spent lots of time here, but I'm just not seeing the answer for this
father-mother-children relationship.
http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html
Any help?
Thanks,
Michael
--
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/groups/opt_out.
Re: [sqlalchemy] Parent child relationships
On 2/13/2014 11:06 AM, Josh Kuhn wrote: I think you need to use the remote_side argument for the children relationship, since it's the same table http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#adjacency-list-relationships Thanks. I'm just not sure how to specify it when there are two parents. Michael -- 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/groups/opt_out.
Re: [sqlalchemy] Parent child relationships
On 2/13/2014 11:04 AM, Michael Bayer wrote:
On Feb 13, 2014, at 11:53 AM, Michael Hipp mich...@redmule.com wrote:
I don't see a first_owner relationship defined above, so the above example
is not complete. The approach using foreign_keys is the correct approach to
resolving ambiguity in join conditions, however. If the documented approach
is not working you'd need to provide a succinct self-contained example I can
run.
Sorry, it was tripping over a different error, here's the code with extras
removed (I think!):
class Animal(Base):
__tablename__ = 'animals'
id_ = Column(Integer, primary_key=True)
sire_id = Column(Integer, ForeignKey('animals.id_'))
dam_id = Column(Integer, ForeignKey('animals.id_'))
sire = relationship('Animal', foreign_keys=[sire_id])
dam = relationship('Animal', foreign_keys=[dam_id])
children = relationship('Animal', foreign_keys=[sire_id, dam_id])
Gives:
sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition
between parent/child tables on relationship Animal.children - there are
multiple foreign key paths linking the tables. Specify the 'foreign_keys'
argument, providing a list of those columns which should be counted as
containing a foreign key reference to the parent table.
Thanks for any help,
Michael
--
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/groups/opt_out.
Re: [sqlalchemy] Parent child relationships
On 2/13/2014 11:45 AM, Michael Bayer wrote: primaryjoin=and_(Animal.sire_id == Animal.id_, Animal.dam_id == Animal.id) Thank you. That works great. And thanks for the explanation. Michael -- 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/groups/opt_out.
[sqlalchemy] Using SQ with Django models
Hello, I have a database in PostgreSQL that is built and updated using Django 1.2 and I'd like to access it with SA. Are there any quick pointers you could offer to get me started on the best way to do that (e.g. duplicate Django's models.py in SA, use reflection, etc.)? The tables have a *lot* of fields. Thanks, Michael -- 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/groups/opt_out.
[sqlalchemy] Returning a simple list?
Doing a simple query like this, I just want to get a list of the pkeys in the table: q = select([mytable.c.id,]) pkey_list = engine.execute(q).fetchall() What I get back looks like: [(1,), (2,)] But what I need is just: [1, 2] It's easy enough to make that happen in Python, but was wondering if SQLA could return it directly? Thanks, Michael -- 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] engine.close() ?
Is there anything that should be done to close an engine just before application termination? I don't see a .close() method but there is a .dispose() method. Thanks, Michael -- 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] ORM __eq__
Doing this to test equality between ORM objects: a = sess1.query(Thing).get(1) b = sess2.query(Thing).get(1) # different session Was somewhat surprised to find that: a == bgives False Is this by design? Do I need to add a custom __eq__ method to my declarative Base classes to make a==b come out True? Thanks, Michael Hipp -- 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] A long-lived ORM object
I have an ORM object that gets loaded once during program run and never changes. sess = Session() unchanging = sess.query(Unchanging).get(1) sess.close() # it is now detached I then need to tell other objects about about it, having a many-to-one relationship to 'unchanging': sess1 = Session() thing1 = Thing() thing1.unchanging = unchanging sess1.add(thing1) sess1.commit() sess2 = Session() thing2 = Thing() thing2.unchanging = unchanging sess2.add(thing2) # fails InvalidRequestError sess2.commit() This doesn't work, of course, because 'unchanging' becomes attached to sess1. Is there some way I can keep 'unchanging' detached and use it over and over again? Or make a copy of it before attaching it to 'thing'? Or must I load a fresh copy every time I need to use it somewhere? Thanks, Michael -- 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] A long-lived ORM object
On 2012-02-08 9:50 AM, Michael Bayer wrote: unchanging = sess1.merge(unchanging, dont_load=True) Thanks, Michael this seems to do what I need. But I notice in the docs the kwarg appears to be 'load=False'. Am I looking at the wrong thing? http://docs.sqlalchemy.org/en/latest/orm/session.html#adding-new-or-existing-items Thanks, Michael -- 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] Using a standalone SEQUENCE
Is there an example or other explanation of how to use a SEQUENCE that is not a primary key on a table, particularly how to create the sequence, get next value, reset seq, etc. I see the base docs here, but it's not obvious to me how exactly to use this class. http://docs.sqlalchemy.org/en/latest/core/schema.html#sqlalchemy.schema.Sequence Thanks, Michael Hipp -- 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] empty a many-many table
On 2012-01-05 1:24 PM, Michael Bayer wrote:
On Jan 5, 2012, at 9:57 AM, Michael Hipp wrote:
Working from the many-many example in the tutorial [1], it has an association
table like this:
post_keywords = Table('post_keywords', Base.metadata,
Column('post_id', Integer, ForeignKey('posts.id')),
Column('keyword_id', Integer, ForeignKey('keywords.id'))
)
session.query(BlogPost).delete()
if you use ON DELETE CASCADE on the foreign keys referred to by post_keywords,
then those rows will delete automatically when saying query(BlogPost).delete().
Thanks, Michael. Setting ondelete='cascade' works well.
Is there a call for Table() objects that is equivalent to the
sess.query(Base).delete() way of deleting everything?
I have some possible occasions to do a brute force cleanup (e.g. unit
testing) but I'd prefer not having to revert to SQL.
Thanks,
Michael
--
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] empty a many-many table
Working from the many-many example in the tutorial [1], it has an association
table like this:
post_keywords = Table('post_keywords', Base.metadata,
Column('post_id', Integer, ForeignKey('posts.id')),
Column('keyword_id', Integer, ForeignKey('keywords.id'))
)
Normally to just empty everything from a table I'd do this:
session.query(BlogPost).delete()
But that doesn't work when there's an association table pointing to it. What's
the correct way to delete everything from an association table. Tried these:
session.query(post_keywords).delete()
session.query('post_keywords').delete()
Thanks,
Michael
[1]
http://www.sqlalchemy.org/docs/orm/tutorial.html#building-a-many-to-many-relationship
--
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] Re: 0.7 event migration
On 2011-12-28 10:58 AM, Michael Bayer wrote: detach(), also nice. This seems most descriptive of what is actually taking place. I poured over the docs for some time looking for the detach() method. Michael -- 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] Necessary to call session.close()?
How important is it to call session.close() when done with a session? Will things be automatically cleaned-up if all references to a session go out of scope? Thanks, Michael -- 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] Dirty columns?
On 2011-12-08 4:11 PM, Michael Bayer wrote: On Dec 8, 2011, at 4:55 PM, Michael Hipp wrote: I'm getting a dirty indication on a particular ORM object from session.is_modified(rec, passive=True) and also that same rec shows up in session.dirty. But I can't figure out where/how it's been modified. Is there some way to determine up which columns are dirty? assuming you're not using any old style mutable attributes, a debugging technique you can use immediately is to look in committed_state: from sqlalchemy.orm.attributes import instance_state instance_state(myobject).committed_state this dictionary shows you what was loaded from the database that's been replaced with something new. This isn't totally public API right now but it is expedient. That works swimmingly. And turns out it was telling the truth that something had been modified :-) Thanks, Michael -- 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] Enforce range on Column(Numeric)
Could someone point me to a doc page that explains how to enforce a
range limit on a Numeric type. I have some monetary values that I want
to force to always be = Decimal('0.00').
Thanks,
Michael
--
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] Troubles with LEFT OUTER JOIN
Can someone help me understand why I can't seem to do a simple left outer join
between these two tables:
q = self.session.query(Car, Invoice.id_)
q = q.outerjoin(Car, Invoice)
sqlalchemy.exc.ArgumentError: Can't find any foreign key relationships between
'cars' and 'cars'.
Thanks,
Michael
---
class Car(Base):
__tablename__ = 'cars'
id_ = Column(Integer, primary_key=True)
class Invoice(Base):
__tablename__ = 'invoices'
id_ = Column(Integer, primary_key=True)
car_id = Column(Integer, ForeignKey('cars.id_'))
car = relationship(Car, backref=backref('invoices', order_by=id_),
primaryjoin=Invoice.car_id==Car.id_)
--
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] Is outerjoin() not generative?
This works: Seller = aliased(Dealer) Buyer = aliased(Dealer) q = self.session.query(Car, Seller.name, Buyer.name) q = q.outerjoin((Car.seller, Seller), (Car.buyer, Buyer)) This doesn't: Seller = aliased(Dealer) Buyer = aliased(Dealer) q = self.session.query(Car, Seller.name, Buyer.name) q = q.outerjoin(Car.seller, Seller) q = q.outerjoin(Car.buyer, Buyer) sqlalchemy.exc.ArgumentError: Can't determine join between 'Join object on cars(71352432) and dealers(71704656)' and '%(78454064 dealers)s'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly. Is this by design? Thanks, Michael -- 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] SELECT ARRAY(SELECT ...
Can someone show me the gist of how to construct an SA query that produces SQL* of the form SELECT ARRAY(SELECT ... FROM ... WHERE ...) as array_col Thanks, Michael * http://www.postgresql.org/docs/8.4/interactive/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS -- 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: SELECT ARRAY(SELECT ...
On 11/9/2010 3:55 PM, Gunnlaugur Briem wrote:
select([
func.ARRAY(
select([t.c.value])
.where(t.c.id3)
.as_scalar()
)
.label('array_col')
])
Thank you! That works swimmingly.
I now know why my search didn't turn up anything as it is evidently one of
those If the function name is unknown to SQLAlchemy, it will be rendered
exactly as is.
Thanks,
Michael
--
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: InternalError
On 11/6/2010 12:15 AM, Christopher Grebs wrote: this is just some specific PostgreSQL behaviour. If there's an error in a transaction it closes the transaction and nothing can be done with it anymore. You need to catch such errors and close the session or at least commit to close the transaction. Ok, I was working from the debugger trying to get a broken query fixed and I probably hit it with all sorts of weird things. Good to know. Thanks, Michael -- 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] InternalError
I'm doing a simple query like this, to get a list of dealers with open invoices:
session.query(Invoice.dealer_id, Dealer.name).
outerjoin((Dealer, Invoice.dealer)).
group_by(Invoice.dealer_id, Dealer.name).
all()
Which produces this SQL:
SELECT invoices.dealer_id AS invoices_dealer_id,
dealers.name AS dealers_name
FROM invoices
LEFT OUTER JOIN dealers ON dealers.id_ = invoices.dealer_id
GROUP BY invoices.dealer_id, dealers.name
That SQL runs perfectly when given directly to PostgreSQL, but SQLAlchemy is
reporting a DB-API error:
sqlalchemy.exc.InternalError: (InternalError) current transaction is
aborted, commands ignored until end of transaction block
The models look like this:
class Dealer(Base):
__tablename__ = 'dealers'
id_ = Column(Integer, primary_key=True)
name = Column(String, nullable=False)
class Invoice(Base):
__tablename__ = 'invoices'
id_ = Column(Integer, primary_key=True)
dealer_id = Column(Integer, ForeignKey('dealers.id_'), nullable=False)
dealer = relationship(Dealer, backref=backref('invoices', order_by=id_))
Any idea what I'm doing wrong?
Thanks,
Michael
--
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] InternalError
On 11/5/2010 2:50 PM, Michael Hipp wrote: That SQL runs perfectly when given directly to PostgreSQL, but SQLAlchemy is reporting a DB-API error: sqlalchemy.exc.InternalError: (InternalError) current transaction is aborted, commands ignored until end of transaction block Nevermind. I noticed lots of other simple queries were dying with the same error. When I restarted the program everything cleared up. Something was just hosed-up I guess. Sorry for the noise. Michael -- 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] Subtransactions
If I do this: item1 = Item() # a new item self.sess.add(item1) self.sess.begin(subtransactions=True) # sub transaction item2 = Item() # another self.sess.add(item2) self.sess.rollback() # rollback sub trans cnt = self.sess.query(Item).count() # how many? That last line produces an exception: InvalidRequestError: This Session's transaction has been rolled back by a nested rollback() call. To begin a new transaction, issue Session.rollback() first. This is not what I expected based on this explanation: http://www.sqlalchemy.org/docs/orm/session.html#sqlalchemy.orm.session.Session.begin I'm guessing this means that once a rollback is issued on a subtransaction, it is expected that you would immediately travel up the call stack and rollback everything else without touching the db until this is done. Is that correct? If so, the documentation linked above could probably be more explicit. In particular, the necessity that the rollback is an all-or-nothing affair. I would also enjoy reading some additional explanation of the difference between nested vs. subtransactions and some appropriate use case for each. The docs make it seem as if they are essentially equivalent, but I'm learning this is not the case. Thanks, Michael -- 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] Subtransactions
On 11/1/2010 10:19 AM, Michael Bayer wrote: SAVEPOINT. This is the technique the average user wants to use. Thanks for that. Pertinent documentation: begin(): When a rollback is issued, the subtransaction will directly roll back the innermost *real* transaction. rollback(): This method rolls back the current transaction or nested transaction regardless of subtransactions being in effect. I think the main thing that seems missing is that once an inner rollback is issued, then the outer ones must immediately follow; no other db action allowed until this is done. (Which isn't the case with nested). Thanks, Michael -- 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] Subtransactions
On 11/1/2010 11:33 AM, Michael Bayer wrote: OK I think in this case, as in many others, is that subtransactions are not an easy to learn feature, hence it is controlled by a flag that is off by default. ... ... for features that aren't intended for typical use Then perhaps say that. This is an advanced feature that will be of no interest to most users - begin_nested() is a much better choice for nesting of transactions. In this case, the full phrase When a rollback is issued, the subtransaction will directly roll back the innermost real transaction, however each subtransaction still must be explicitly rolled back to maintain proper stacking of subtransactions., states the full reality of the behavior. This may describe the full reality of the behavior, but IMHO it is a useful description only to someone who already understands it. There is no requirement that the subsequent rollbacks must immediately follow, you can do any number of things including just discarding the whole session if you wanted. If you say so ... but when I can't even do 'session.query().count()' without getting an exception, it appears to me I'm thoroughly dead-in-the-water until I do all those rollbacks. How is any other conclusion possible? This is where we get into the fact that SQLAlchemy's error messages, which you may have observed are mostly very verbose, are themselves part of the documentation. Yes, in fact I'd say they're considerably better than the majority of things I deal with. My compliments. If we tried to document the specific conditions leading to every ORM error message in the docs, the verbosity of the documentation would grow by a significant margin, the tone would become one of careful not to do this! careful not to do that! which would definitely scare away users in a big way. That's not what I was suggesting. What I always find most helpful is a here is the canonical way to use this feature ..., see example at Is there an example somewhere that shows how to use subtransactions, if so why not link to it and let that be the documentation for this advanced feature that most people won't need anyway. (You can be as verbose and long-winded in the comments of that example as needed, and there such verbosity is considered a good thing rather than a distraction.) In reality, getting an error message is not a bad thing and there's no reason the documentation needs expanded in such a way as to protect its users from ever getting an exception. Again, that's not even close to what I was suggesting. Thanks, Michael -- 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] Subtransactions
On 11/1/2010 3:12 PM, Michael Bayer wrote: new section: http://www.sqlalchemy.org/docs/orm/session.html#using-subtransactions This section now attempts to explain the full purpose and rationale of the subtransactions feature including an example. Hope it's clear. Thank you, looks excellent. Michael -- 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] DataError: invalid input value for enum
I have a model that looks something like this:
TRANS_CODES = ( 'SellCar', 'BuyCar', 'BuyFee', 'SellFee',
'Gas/Fuel', 'Detail', 'Wash/Vac', 'Trans/Hauling',
'Service/Repair', 'DraftFee', 'Misc', 'Cash',
'CheckPaid', 'CheckRcvd', 'FloorPlan', 'Draft')
class Trans(Base):
__tablename__ = 'trans'
code = Column(Enum(*TRANS_CODES, name='trans_codes'),
nullable=False)
When I attempt to save a 'Cash' transaction I get the error below on the
enum value. Any suggestions on how to troubleshoot this?
Thanks,
Michael
---
sqlalchemy.exc.DataError: (DataError) invalid input value for enum
trans_codes: Cash
LINE 1: ...1T15:55:32.943000'::timestamp, false, false, E'', E'Cash', E...
^
'INSERT INTO trans (date, void, printed, agent, code, ch_num, memo,
payee, exp_id, qty, each, payment, deposit, invoice_id, account_id)
VALUES (%(date)s, %(void)s, %(printed)s, %(agent)s, %(code)s,
%(ch_num)s, %(memo)s, %(payee)s, %(exp_id)s, %(qty)s, %(each)s,
%(payment)s, %(deposit)s, %(invoice_id)s, %(account_id)s) RETURNING
trans.id_' {'code': 'Cash', 'account_id': None, 'invoice_id': 2, 'void':
False, 'agent': '', 'qty': Decimal('1'), 'payee': '', 'deposit': False,
'payment': True, 'each': Decimal('1000.00'), 'date':
datetime.datetime(2010, 10, 31, 15, 55, 32, 943000), 'exp_id': '',
'ch_num': '', 'memo': 'TEST AUTOMATIC PAYMENT', 'printed': False}
--
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] orm object, before after
On 8/24/2010 9:47 PM, Michael Bayer wrote:
Michael Hipp wrote:
How do I make a copy of an orm object such that modifications to the
copy do not affect the original?
The mapped object has a member _sa_instance_state that you basically
don't want to transfer to your new object.You want it to have its own
_sa_instance_state and this comes from calling the plain constructor,
which the copy module, if that's what you're using, does not use. You
also want to set attributes normally, not populating __dict__ directly.
So just basically don't use the copy module.
x = MyObject()
for a in dir(myoldobject):
if not a.startswith('_'):
setattr(x, a, getattr(myoldobject, a))
Resurrecting an old thread ...
I'm just now getting around to try this but I'm finding out it doesn't really
work like I'd hoped.
As soon as 'getattr' hits a column with a ForeignKey it immediately tries to
autoflush INSERT 'x'. But 'x' is only half-baked and not ready to be saved.
In fact, I don't ever want to save 'x', and I especially don't want to INSERT
it. It would be a duplicate of 'myoldobject'.
Is there a way to copy an orm object and tell it don't ever save this I just
want to keep it around to look at?
Alternatively I can just copy all the attributes to a dict(), but that's a bit
messy.
Thanks for all your help,
Michael
--
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] orm object, before after
On 10/16/2010 12:52 PM, Michael Bayer wrote:
On Oct 16, 2010, at 1:02 PM, Michael Hipp wrote:
On 8/24/2010 9:47 PM, Michael Bayer wrote:
Michael Hipp wrote:
How do I make a copy of an orm object such that modifications to the
copy do not affect the original?
The mapped object has a member _sa_instance_state that you basically
don't want to transfer to your new object.You want it to have its own
_sa_instance_state and this comes from calling the plain constructor,
which the copy module, if that's what you're using, does not use. You
also want to set attributes normally, not populating __dict__ directly.
So just basically don't use the copy module.
x = MyObject()
for a in dir(myoldobject):
if not a.startswith('_'):
setattr(x, a, getattr(myoldobject, a))
Resurrecting an old thread ...
I'm just now getting around to try this but I'm finding out it doesn't really
work like I'd hoped.
As soon as 'getattr' hits a column with a ForeignKey it immediately tries to
autoflush INSERT 'x'. But 'x' is only half-baked and not ready to be saved.
In fact, I don't ever want to save 'x', and I especially don't want to INSERT
it. It would be a duplicate of 'myoldobject'.
Is there a way to copy an orm object and tell it don't ever save this I just want
to keep it around to look at?
Alternatively I can just copy all the attributes to a dict(), but that's a bit
messy.
dont put it in the Session.
That makes sense. But how do I not do that? As in your example code above I'm
not adding it to a session, at least not intentionally.
Thanks,
Michael
--
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] orm object, before after
On 10/16/2010 1:55 PM, Michael Bayer wrote:
On Oct 16, 2010, at 2:03 PM, Michael Hipp wrote:
On 10/16/2010 12:52 PM, Michael Bayer wrote:
On Oct 16, 2010, at 1:02 PM, Michael Hipp wrote:
On 8/24/2010 9:47 PM, Michael Bayer wrote:
Michael Hipp wrote:
How do I make a copy of an orm object such that modifications to the
copy do not affect the original?
The mapped object has a member _sa_instance_state that you basically
don't want to transfer to your new object.You want it to have its own
_sa_instance_state and this comes from calling the plain constructor,
which the copy module, if that's what you're using, does not use. You
also want to set attributes normally, not populating __dict__ directly.
So just basically don't use the copy module.
x = MyObject()
for a in dir(myoldobject):
if not a.startswith('_'):
setattr(x, a, getattr(myoldobject, a))
Resurrecting an old thread ...
I'm just now getting around to try this but I'm finding out it doesn't really
work like I'd hoped.
As soon as 'getattr' hits a column with a ForeignKey it immediately tries to
autoflush INSERT 'x'. But 'x' is only half-baked and not ready to be saved.
In fact, I don't ever want to save 'x', and I especially don't want to INSERT
it. It would be a duplicate of 'myoldobject'.
Is there a way to copy an orm object and tell it don't ever save this I just want
to keep it around to look at?
Alternatively I can just copy all the attributes to a dict(), but that's a bit
messy.
dont put it in the Session.
That makes sense. But how do I not do that? As in your example code above I'm
not adding it to a session, at least not intentionally.
don't call Session.add(). the code above doesn't add to a session unless
you're using some special extensions like Session.mapper.
As shown exactly in the code above, I do *not* call Session.add().
It appears to be doing the add when it hits the ForeignKey field. Then it not
only does an add it also attempts an INSERT (which thankfully fails since the
object is not ready to be saved).
Michael
--
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] When is 'default' applied?
If I have a column defined like this:
qty = Column(Numeric, nullable=False, default=Decimal('1'))
When is the default applied? At commit()?
Michael
--
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] When is 'default' applied?
On 10/15/2010 5:30 PM, Michael Hipp wrote:
If I have a column defined like this:
qty = Column(Numeric, nullable=False, default=Decimal('1'))
When is the default applied? At commit()?
Nevermind. Finally found it in the docs, appears it happens at INSERT time.
Thanks,
Michael
--
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] Empty a record
On 9/22/2010 10:27 AM, Michael Bayer wrote: Michael, thanks so much for taking the time to compose a very thorough answer. If you could indulge a few clarifications/suggestions ... So here, the value of None for car.auction, merges into the session which becomes a pending change. The flush overwrites car.auct_id with None because car.auction has been set to None. The merge() process takes everything that is present on the incoming object and assigns it to the object that's in the session. So here when merge sets old.auction = None, this is the effect. So you want to merge an object where every attribute is either exactly the value that you want it to be, or it is not loaded or assigned to in any way (i.e. not present in __dict__). If you pop auction from __dict__ before the merge, or just don't assign to auction in the contructor of Car and also dont issue a print car.auction later on, the program succeeds. I have been putting more and more things in the constructors for 2 reasons: 1) It's really convenient esp in unit tests to be able to spec everything on 1 line when creating a lot of objects at once. 2) It has always been good business in Python to make sure all instance vars are given a default value as early as possible. But here, that harmless act of setting auction=None actually triggers things to happen that go considerably beyond my simplistic notion of just making sure things have a default value. This is the 2nd time in as many days that I've been tripped-up by having things in the constructor that didn't *have* to be there. But only now am I coming to realize why. Some explanation of or warning about this in the docs would seem appropriate. As I look over the declarative tutorial, it is somewhat implied that every column should be set in the constructor: http://www.sqlalchemy.org/docs/orm/tutorial.html#creating-table-class-and-mapper-all-at-once-declaratively Here, the issue is that you're mixing the usage of merge() with the usage of objects that are already in the session. new is added to the session via cascade: new = Car() new.id_ = old.id_ new.lane = old.lane new.auct_id = old.auct_id new.auction = old.auction assert new in sess # passes The ways to get around that effect are: - pass cascade=None to your 'cars' backref - this means, when you set somecar.auction = someauction, someauction is already in the session, 'somecar' doesn't get added automatically. cascade also affects what merge() does along relationships so when changing this make sure it has the cascades that you still want. - expunge new before you merge() it, but that's kind of messy. - don't set any relationships that are going to cascade it into the session On that last note I found that if I do: new = Car() new.id_ = old.id_ new = sess.merge(new) new.auction = old.auction # do this *after* merge sess.commit() This seems to work and avoids me having to deal with the cascade stuff (which I don't understand) just yet. Any worries with this approach? I definitely want to add a note about what the state of the given instance is copied means, regarding things in __dict__. Some explanation of how things get in __dict__ and what their presence there means would help us noobs. Also, is it really a good idea to go hacking on __dict__ (e.g. popping things out as mentioned above)? Again, thanks, Michael -- 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] Change echo at will
On 8/26/2010 8:55 PM, Mike Conley wrote: On Thu, Aug 26, 2010 at 4:21 PM, Michael Hipp mich...@hipp.com mailto:mich...@hipp.com wrote: Is there a way to set 'echo' at any time? Everything I can find sets it when the engine is created and doesn't seem to change it afterward. You can assign the engine.echo property to True or False any time after creating the engine. Is there something that would trigger this to be recognized? In trying to use this to see the sql from a key piece of code it seems if I have to do the engine.echo=True well in advance of when I want it to start; and similarly I need to wait well after I want it to stop before I set it to False. Otherwise I miss stuff. Thanks, Michael -- 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] Empty a record
On 9/22/2010 11:21 AM, Michael Hipp wrote: new = Car() new.id_ = old.id_ new = sess.merge(new) new.auction = old.auction # do this *after* merge sess.commit() This seems to work and ... Bah. I spoke too soon - it just doesn't throw an exception. But without explicitly setting every field to its default value, the session thinks nothing has changed and the UPDATE leaves most of the fields untouched. Anyway, it appears I need a new approach to empty/blank a record. Options I can think of are: 1) Find a dict of all the default values for every field and set them explicitly. Does SQLAlchemy have that somewhere? 2) What about an approach of forcing a DELETE, INSERT, COMMIT on the old/new objects. Like this: session.begin(subtransactions=True) id_ = old.id_ # grab important stuff from 'old' auct = old.auction session.delete(old) # kill old session.commit() new = Car() new.id_ = id_ new.auction = auct new = session.merge(new) session.commit() But I'm worried about side effects and issues with the version_id_col. Any thoughts appreciated... Michael -- 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] Empty a record
On 9/22/2010 5:24 PM, Michael Bayer wrote: Here's the problem. The term a blank record is meaningless. Well, no, it's not. It's exactly what I get when I do new=Item() and commit(). It's very well defined, precise, and repeatable. Trying to make other tools guess this for you seems to be taking up days of your time - whereas a simple def set_myself_blank(self) method OTOH would take 30 seconds. I've been writing that 30 second method for 2 days now. So evidently it takes longer than that :-) Problem is that model has about 75 columns in it. Each Column() has a default='foo' parameter. And they are all unique to some extent. So I can *replicate* that information that is already there in a dict somewhere that will have 75 lines in it and then maintain it in sync with the official version. That is a severe violation of DRY and it will inevitably lead to bugs and possibly data corruption. Avoiding such seems a worthy goal. I apologize, truly, that I have greatly overused your assistance on this. Thank you. I have formulated a couple of hackish approaches that will probably work. Guess I'm stuck with them. :-) Michael -- 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] Use regexp in like
On 9/20/2010 10:54 AM, Michael Hipp wrote:
On 9/20/2010 10:09 AM, Michael Hipp wrote:
On 9/20/2010 9:38 AM, Michael Hipp wrote:
Scratch that ... found this message:
http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg18598.html
which says I should be able to do a 'SIMILAR TO' construct which is perhaps
somewhat more lightweight than a full regexp.
Can someone show me what I'm doing wrong here.
letter = 'A[0-9]+'
q = self.session.query(Car).filter_by(hist=False) \
.filter(Car.lane.op('SIMILAR TO') (letter)) \
.order_by(Car.lane)
I'm trying to match something that looks like 'A100'. But it produces a syntax
error:
OperationalError: (OperationalError) near SIMILAR: syntax error
...snip...
WHERE cars.hist = ? AND (cars.lane SIMILAR TO ?) ORDER BY cars.lane' (False,
'A[0-9]+')
Strange. I couldn't see what was actually wrong with that SQL, so I ran it
directly against pg and it works fine. Is it possibly a quoting problem?
Can anyone offer me some suggestions here? Is this a bug?
Thanks,
Michael
--
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] Empty a record
I need to empty an item (row). I thought to do this: new = Item() # create a new empty object new.id_ = old.id_ # copy certain other fields also self.session.expunge(old) self.session.add(new) self.session.commit() But it seems SA still tries to save it with an INSERT. (I thought the presence of the PK was what determined that.) Is there some way to blank a record and save it back with the same PK and a few other select fields that need to be kept? Thanks, Michael -- 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] Use regexp in like
On 9/21/2010 7:23 AM, Michael Bayer wrote:
On Sep 21, 2010, at 8:12 AM, Michael Hipp wrote:
On 9/20/2010 10:54 AM, Michael Hipp wrote:
On 9/20/2010 10:09 AM, Michael Hipp wrote:
On 9/20/2010 9:38 AM, Michael Hipp wrote:
Scratch that ... found this message:
http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg18598.html
which says I should be able to do a 'SIMILAR TO' construct which is perhaps
somewhat more lightweight than a full regexp.
Can someone show me what I'm doing wrong here.
letter = 'A[0-9]+'
q = self.session.query(Car).filter_by(hist=False) \
.filter(Car.lane.op('SIMILAR TO') (letter)) \
.order_by(Car.lane)
I'm trying to match something that looks like 'A100'. But it produces a syntax
error:
OperationalError: (OperationalError) near SIMILAR: syntax error
...snip...
WHERE cars.hist = ? AND (cars.lane SIMILAR TO ?) ORDER BY cars.lane' (False,
'A[0-9]+')
Strange. I couldn't see what was actually wrong with that SQL, so I ran it
directly against pg and it works fine. Is it possibly a quoting problem?
Can anyone offer me some suggestions here? Is this a bug?
I'm not sure how the ? is being used for a bind param. the psycopg2 dialect
uses %(name)s format. SIMILAR TO works fine and you can see %(name)s format
is used:
from sqlalchemy import *
from sqlalchemy.sql import column
e = create_engine('postgresql://scott:ti...@localhost/test', echo=True)
e.execute(select([literal_column('lane').op('SIMILAR TO')('car')]))
2010-09-21 08:19:49,339 INFO sqlalchemy.engine.base.Engine.0x...1570 select
version()
2010-09-21 08:19:49,339 INFO sqlalchemy.engine.base.Engine.0x...1570 {}
2010-09-21 08:19:49,341 INFO sqlalchemy.engine.base.Engine.0x...1570 select
current_schema()
2010-09-21 08:19:49,341 INFO sqlalchemy.engine.base.Engine.0x...1570 {}
2010-09-21 08:19:49,343 INFO sqlalchemy.engine.base.Engine.0x...1570 SELECT
'lane' SIMILAR TO %('lane'_1)s AS anon_1
2010-09-21 08:19:49,343 INFO sqlalchemy.engine.base.Engine.0x...1570
{'lane'_1: 'car'}
I get the same result with psycopg2 2.0.12 and 2.2.2 .
Sigh. I'm a moron.
I'm getting this problem when running unit tests. And it took me a day and a
half to remember that I'm doing that against an SQLite in-memory database, not
pg. Which means the SIMILAR TO op is never going to work, I suppose.
So now I have to figure out how to run unit tests against postgresql or else
live with code having no test coverage. Neither are very attractive.
I apologize for taking your time. Thanks for trying to help.
Michael
--
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] Empty a record
On 9/21/2010 7:31 AM, Michael Hipp wrote: I need to empty an item (row). I thought to do this: new = Item() # create a new empty object new.id_ = old.id_ # copy certain other fields also self.session.expunge(old) self.session.add(new) self.session.commit() But it seems SA still tries to save it with an INSERT. (I thought the presence of the PK was what determined that.) Is there some way to blank a record and save it back with the same PK and a few other select fields that need to be kept? I continue to play with this. It reads like 'merge' would do exactly what I want, but I must be misunderstanding it. http://www.sqlalchemy.org/docs/orm/session.html#merging Changing the 'session.add' line to 'session.merge' still produces IntegrityError: duplicate key value violates unique constraint Trying it with and without the 'expunge' line just changes the error to New instance ... conflicts with persistent instance Can anyone suggest an approach to empty a record? Thanks, Michael -- 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] Empty a record
On 9/21/2010 8:17 PM, Michael Bayer wrote:
It definitely does not attempt an INSERT if id_ is set to a non-None value,
assuming that row already exists in the DB, without something else in your
model/usage causing that to happen.If id_ is None or the given id_ doesn't
exist in the DB, you get an INSERT. auct_id has no direct effect here.
that also makes no sense since if you set auct_id manually, assuming
old.auct_id is not None, it wouldn't be None in the UPDATE statement.
These behaviors (opposite what we expect) are what I'm indeed seeing.
As usual, distilling down the behavior that appears wrong into a single file
Attached.
I'll be thrilled if you can figure out what really stupid thing I'm doing to
cause this.
As always, many thanks for your help.
Michael
--
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.
from datetime import datetime, date
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import (Column, Integer, String, Date, Boolean, Numeric, Enum,
DateTime, ForeignKey)
from sqlalchemy.orm import relationship, backref
Base = declarative_base()
Session = sessionmaker()
#--
class Car(Base):
__tablename__ = 'cars'
id_ = Column(Integer, primary_key=True)
lane = Column(String, nullable=False)
make = Column(String, nullable=False, default='')
auct_id = Column(Integer, ForeignKey('auctions.id_'), nullable=False)
auction = relationship('Auction', backref=backref('cars', order_by=lane))
def __init__(self, lane=None, make='', auction=None):
self.lane = lane
self.make = make
self.auction = auction
def __repr__(self):
return 'Car %d: %s %s' % (self.id_, self.lane, self.make)
#--
class Auction(Base):
__tablename__ = 'auctions'
id_ = Column(Integer, primary_key=True)
date = Column(Date, nullable=False, index=True)
def __init__(self, date=None):
self.date = date
def __repr__(self):
date = self.date.strftime('%Y-%m-%d')
return 'Auction %d: %s' % (self.id_, date)
#--
def main():
url = 'sqlite:///:memory:'
engine = create_engine(url, echo=True)
metadata = Base.metadata
Session.configure(bind=engine)
metadata.create_all(engine)
sess = Session()
# create a minimal car to test against
auct = Auction(date.today()) # simulated current auction
sess.add(auct)
old = Car(lane='A100', make='NISSAN', auction=auct)
sess.add(old)
sess.commit()
# Now try to blank that car record
new = Car() # create a new empty car object
new.id_ = old.id_ # save the pkey
new.lane = old.lane# can't be null
new.auct_id = old.auct_id # can't be null
# Uncomment these 2 lines to see it try to do INSERT instead of UPDATE
#new.auction = old.auction
#sess.expunge(old)
print new.id_, old.id_, new.auct_id, old.auct_id, new.auction, new, old
new = sess.merge(new)
print new.id_, old.id_, new.auct_id, old.auct_id, new.auction, new, old
sess.commit()
if __name__ == __main__:
main()
Re: [sqlalchemy] Use regexp in like
On 9/20/2010 9:38 AM, Michael Hipp wrote:
Scratch that ... found this message:
http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg18598.html
which says I should be able to do a 'SIMILAR TO' construct which is perhaps
somewhat more lightweight than a full regexp.
Can someone show me what I'm doing wrong here.
letter = 'A[0-9]+'
q = self.session.query(Car).filter_by(hist=False) \
.filter(Car.lane.op('SIMILAR TO') (letter)) \
.order_by(Car.lane)
I'm trying to match something that looks like 'A100'. But it produces a syntax
error:
OperationalError: (OperationalError) near SIMILAR: syntax error
...snip...
WHERE cars.hist = ? AND (cars.lane SIMILAR TO ?) ORDER BY cars.lane' (False,
'A[0-9]+')
Thanks,
Michael
--
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] Use regexp in like
On 9/20/2010 10:09 AM, Michael Hipp wrote:
On 9/20/2010 9:38 AM, Michael Hipp wrote:
Scratch that ... found this message:
http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg18598.html
which says I should be able to do a 'SIMILAR TO' construct which is perhaps
somewhat more lightweight than a full regexp.
Can someone show me what I'm doing wrong here.
letter = 'A[0-9]+'
q = self.session.query(Car).filter_by(hist=False) \
.filter(Car.lane.op('SIMILAR TO') (letter)) \
.order_by(Car.lane)
I'm trying to match something that looks like 'A100'. But it produces a syntax
error:
OperationalError: (OperationalError) near SIMILAR: syntax error
...snip...
WHERE cars.hist = ? AND (cars.lane SIMILAR TO ?) ORDER BY cars.lane' (False,
'A[0-9]+')
Strange. I couldn't see what was actually wrong with that SQL, so I ran it
directly against pg and it works fine. Is it possibly a quoting problem?
Michael
--
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] Use regexp in like
On 9/20/2010 10:57 AM, Michael Bayer wrote:
On Sep 20, 2010, at 11:54 AM, Michael Hipp wrote:
On 9/20/2010 10:09 AM, Michael Hipp wrote:
On 9/20/2010 9:38 AM, Michael Hipp wrote:
Scratch that ... found this message:
http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg18598.html
which says I should be able to do a 'SIMILAR TO' construct which is perhaps
somewhat more lightweight than a full regexp.
Can someone show me what I'm doing wrong here.
letter = 'A[0-9]+'
q = self.session.query(Car).filter_by(hist=False) \
.filter(Car.lane.op('SIMILAR TO') (letter)) \
.order_by(Car.lane)
I'm trying to match something that looks like 'A100'. But it produces a syntax
error:
OperationalError: (OperationalError) near SIMILAR: syntax error
...snip...
WHERE cars.hist = ? AND (cars.lane SIMILAR TO ?) ORDER BY cars.lane' (False,
'A[0-9]+')
Strange. I couldn't see what was actually wrong with that SQL, so I ran it
directly against pg and it works fine. Is it possibly a quoting problem?
is this pg8000 ? the ? as binds are suspect. that would be my guess.
I've never used pg8000; this is psycopg2:
import psycopg2
psycopg2.__version__
'2.2.1 (dt dec ext pq3)'
Any help?
Thanks,
Michael
--
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] Use regexp in like
On 9/14/2010 2:23 PM, Michael Hipp wrote: Is it possible to use a regexp in a like() clause? Or some other way to achieve something similar? Can anyone suggest an approach to search a field with a regexp? Thanks, Michael -- 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] Use regexp in like
Is it possible to use a regexp in a like() clause? Or some other way to achieve something similar? Thanks, Michael -- 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] Change echo at will
Is there a way to set 'echo' at any time? Everything I can find sets it when the engine is created and doesn't seem to change it afterward. Thanks, Michael -- 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] orm object, before after
On 8/24/2010 9:47 PM, Michael Bayer wrote:
Michael Hipp wrote:
How do I make a copy of an orm object such that modifications to the
copy do not affect the original?
x = MyObject()
for a in dir(myoldobject):
if not a.startswith('_'):
setattr(x, a, getattr(myoldobject, a))
Thank you. That's perfect - and worthy of being captured somewhere it
can be reused.
I apologize for wasting your time with a poorly worded question.
Thanks,
Michael
--
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] orm object, before after
I'm holding an orm object that will have changes made to it. Once done it will
be passed to the business logic layer that will have to make decisions from the
before and after state of the object...
What's the best way to get an object, save its state ('before'), modify it
('after) without any chance of the modifications creeping into the before?
Assume both copies are from the same session.
Thanks,
Michael
--
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] orm object, before after
On 8/24/2010 1:51 PM, Michael Bayer wrote:
On Aug 24, 2010, at 2:08 PM, Michael Hipp wrote:
I'm holding an orm object that will have changes made to it. Once done it will
be passed to the business logic layer that will have to make decisions from the
before and after state of the object...
What's the best way to get an object, save its state ('before'), modify it
('after) without any chance of the modifications creeping into the before?
Assume both copies are from the same session.
You'd probably call session.flush() (or commit(), depending on how you are
scoping your transaction around this operation) before you do anything to it.
Then, if you'd like the subsequent modifications to not go to the database at
all until some later point, you'd proceed with your subsequent operations with
autoflush turned off - recipes for that are at
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DisableAutoflush .
Thank you. But I didn't understand any of that ... at least as it
relates to my question.
How do I make a copy of an orm object such that modifications to the
copy do not affect the original?
(Obviously I could detach one of them, but then it becomes useless as
none of the attributes can be accessed.)
Could you perhaps repeat the answer in baby-talk language?
Thanks,
Michael
--
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] Adjacency List Relationships using declarative
I'm trying to do exactly what is described here:
http://www.sqlalchemy.org/docs/mappers.html#adjacency-list-relationships
But I'm using declarative. Here's what I have but it fails with the exception
below:
class Option(Base):
__tablename__ = 'options'
id_ = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey('options.id_'))
parent = relationship('Option',
backref=backref('children', order_by=name,
remote_side=['options.c.id_']))
sqlalchemy.exc.ArgumentError: Relationship Option.children could not determine
any local/remote column pairs from remote side argument set
How do I make this work with declarative?
Thanks,
Michael
--
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] Adjacency List Relationships using declarative
On 8/21/2010 6:00 PM, Michael Bayer wrote:
On Aug 21, 2010, at 5:16 PM, Michael Hipp wrote:
How do I make this work with declarative?
If you were to use string literals with remote_side here, its the full expression would
be a string, i.e. remote_side=Option.id_. But that's not needed here since
id_ as a Column is right there, so remote_side=id_ .
I made that one change:
class Option(Base):
__tablename__ = 'options'
id_ = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey('options.id_'))
parent = relationship('Option', backref=backref('children', order_by=name,
remote_side=id_))
Now I get:
TypeError: Incompatible collection type: None is not list-like
File C:\dropbox\dev\auction\play.py, line 286, in module
opts2()
File C:\dropbox\dev\auction\play.py, line 257, in opts2
opt.start()
File C:\dropbox\dev\auction\options\logic.py, line 40, in start
rec = Option(name='__top__')
File C:\dropbox\dev\auction\string, line 4, in __init__
File C:\dev\virtenvs\auction\Lib\site-packages\sqlalchemy\orm\state.py, line
104, in initialize_instance
return manager.events.original_init(*mixed[1:], **kwargs)
File C:\dropbox\dev\auction\options\models.py, line 47, in __init__
self.parent = parent
File C:\dev\virtenvs\auction\Lib\site-packages\sqlalchemy\orm\attributes.py,
line 154, in __set__
instance_dict(instance), value, None)
File C:\dev\virtenvs\auction\Lib\site-packages\sqlalchemy\orm\attributes.py,
line 755, in set
lambda adapter, i: adapter.adapt_like_to_iterable(i))
File C:\dev\virtenvs\auction\Lib\site-packages\sqlalchemy\orm\attributes.py,
line 771, in _set_iterable
new_values = list(adapter(new_collection, iterable))
File C:\dev\virtenvs\auction\Lib\site-packages\sqlalchemy\orm\attributes.py,
line 755, in lambda
lambda adapter, i: adapter.adapt_like_to_iterable(i))
File C:\dev\virtenvs\auction\Lib\site-packages\sqlalchemy\orm\collections.py,
line 530, in adapt_like_to_iterable
given, wanted))
Any help?
Thanks,
Michael
--
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] Which columns changing during orm commit?
On 8/19/2010 5:24 AM, Chris Withers wrote: Michael Hipp wrote: SQLAlchemy seems pretty smart about updating only the changed columns in an orm object... If I have an orm object. Something changes one of the columns. Just before I commit() the session, is there a way to tell which columns will be updated vs those that are unchanged? Any way to ascertain the before/after values on those changed columns? Here's the basics: http://www.sqlalchemy.org/docs/session.html#session-attributes These examples should fill in the rest: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/VersionedMap http://www.sqlalchemy.org/trac/wiki/UsageRecipes/VersionedRows http://www.sqlalchemy.org/docs/examples.html?#module-versioning Thanks. But I believe all those items deal with which orm objects (rows) are changed. I'm asking about columns within an orm object that might be changed. Did I miss something? Thanks, Michael -- 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] Which columns changing during orm commit?
SQLAlchemy seems pretty smart about updating only the changed columns in an orm object... If I have an orm object. Something changes one of the columns. Just before I commit() the session, is there a way to tell which columns will be updated vs those that are unchanged? Any way to ascertain the before/after values on those changed columns? Thanks, Michael -- 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] Performance: orm vs sql
The little diddly below is comparing performance of orm access vs sql
expression language.
When I run it with number=1 I get a 5.8x advantage for sql. When I run it 10
times I get a 2.7x advantage. The actual numbers are, respectively:
1.47375132
0.25630808
5.45569524
1.96911144
Is this a typical/expected difference in performance between the two query
methods?
Michael
def timing1():
orm method
recs = sess.query(dm.Dealer).order_by('name').all()
def timing2():
sql method
dealers = dm.Dealer.__table__
recs = engine.execute(select([dealers], order_by='name')).fetchall()
def timing():
t = timeit.Timer(timing1)
print t.timeit(number=1)
t = timeit.Timer(timing2)
print t.timeit(number=1)
if __name__ == __main__:
db.start(DATABASE)
from common.database import engine
sess = db.Session()
timing()
--
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] Alias for a joined column name
I'm doing something like this where each Item has 2 ForeignKeys to Dealer for
buyer and seller:
seller = dealers.alias('seller')
buyer = dealers.alias('buyer')
engine.execute(select([items, seller.c.name, buyer.c.name]).fetchall()
When I do this the seller and buyer name end up in the results and are both
called 'name' which isn't very useful. If I add 'use_labels=True' it messes up
all the column names from items.
Is there some way to just have it alias them to seller_name and buyer_name?
It's easy to do in pure sql.
Thanks,
Michael
--
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] Alias for a joined column name
On 8/16/2010 5:12 PM, Conor wrote:
On 08/16/2010 04:47 PM, Michael Hipp wrote:
I'm doing something like this where each Item has 2 ForeignKeys to
Dealer for buyer and seller:
seller = dealers.alias('seller')
buyer = dealers.alias('buyer')
engine.execute(select([items, seller.c.name, buyer.c.name]).fetchall()
When I do this the seller and buyer name end up in the results and are
both called 'name' which isn't very useful. If I add 'use_labels=True'
it messes up all the column names from items.
Is there some way to just have it alias them to seller_name and
buyer_name? It's easy to do in pure sql.
Thanks,
Michael
Try this:
engine.execute(select([items, seller.c.name.label(seller_name),
buyer.c.name.label(buyer_name)])).fetchall()
Thank you. That works!
Michael
--
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] DetachedInstanceError
I'm obviously missing some key concept as regards the management of sessions. This seemingly simple usage fails: def get_new(): sess = Session() new = Something() # new orm object sess.add(new) sess.commit() sess.close() return new new = get_new() # request a new Something print new print new.id Those last 2 print lines throw: DetachedInstanceError: Instance Something at 0x2873ed0 is not bound to a Session; attribute refresh operation cannot proceed I seem to keep butting heads with the session needing to be a global eternal thing (opposite what the docs recommend). I could create another session and add 'new' to it, but that seems like a lot of boilerplate when all I wanted to do was get a bit of info from the returned object. Can someone explain how this is supposed to be done? Thanks, Michael -- 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] DetachedInstanceError
Michael Thanks for taking the time to formulate a very thorough answer. (Now if I can make my understanding be as thorough.) If you could suffer me one more question ... it appears there are two* ways to handle this inside a method that may not know where it's called from. def alternative1(thing): sess = Session() sess.merge(thing) thing.name = Foo sess.commit() sess.close() def alternative2(thing, sess=None): if sess is None: sess = Session() sess.merge(thing) thing.name = Foo sess.commit() # incomplete, must do sess.close() Am I getting anywhere close? Can either one be said to be better? Again, thanks. Michael * For now, I'm taking it as an article of faith that I should stay away from expire_on_commit at least until I better understand the implications. On 8/14/2010 12:38 PM, Michael Bayer wrote: On Aug 14, 2010, at 12:53 PM, Michael Hipp wrote: I'm obviously missing some key concept as regards the management of sessions. This seemingly simple usage fails: def get_new(): sess = Session() new = Something() # new orm object sess.add(new) sess.commit() sess.close() return new new = get_new() # request a new Something print new print new.id Those last 2 print lines throw: DetachedInstanceError: Instance Something at 0x2873ed0 is not bound to a Session; attribute refresh operation cannot proceed I seem to keep butting heads with the session needing to be a global eternal thing (opposite what the docs recommend). hehno, the session is completely ad hoc. What you're missing is that the objects associated with the session should also in most situations be treated as ad-hoc - they represent the state of data within a particular transaction. If you use them outside of a transaction, and not associated with a session that would otherwise have the ability to associate them with a transaction, they are considered to be detached. detached is described at: http://www.sqlalchemy.org/docs/session.html#quickie-intro-to-object-states Where you'll note that expired attributes cannot be loaded back from the database. Why are they expired ? Let's look at commit(): http://www.sqlalchemy.org/docs/session.html#committing Second paragraph. Another behavior of commit() http://www.sqlalchemy.org/docs/reference/orm/sessions.html#sqlalchemy.orm.session.Session.commit is that by default it expires the state of all instances present after the commit is complete. Why does it do this ? Well, when we have a detached object, and there's no transaction going on (i.e. no connection that can query the DB), we know nothing about what is in the database at that point, so all state on the object is expired. After all, if it had id =12, but some other transaction has deleted row 12, that object is invalid. Without a transaction associated, it would be wrong for us to tell you otherwise. Because we don't know. Now lets assume you don't like this behavior, and your application is just set of operations at a time and nobody else is updating your row (assumptions SQLAlchemy has chosen not to make). Fine. Turn off expire_on_commit. Then when you detach your objects, all their attributes are still present, and you can access them freely. So what if we made this the default. What kinds of complaints, which btw we never get anymore, would we have then ? Well, we'd have (and we had, all the time) this complaint: sess1 = Session() x1 = sess1.query(X).first() x1.foo = 'bar' sess1.commit() sess2 = Session() x2 = sess2.query(X).first() x2.foo = 'bat' sess2.commit() # x1 is still present in the Session's identity map x1 = sess1.query(X).first() assert x1.foo == 'bat' # ugh SQLALCHEMY IS BROKEN ! so we default to the more transaction hugging behavior by default - where the error message you get is at least very straightforward, instead of a subtle effect like this one. Can someone explain how this is supposed to be done? When you work with mapped objects, you're working with your database. A Session() should be in place and a transaction is in progress. Its only if you want to store mapped objects in some kind of offline cache, or pass them to other usage contexts, that you'd want to keep detached objects around. And when you go to use a detached object, you put it back into a context where it again is a proxy to some ongoing database operation, i.e. put it in the session for the current operation - often this transfer of state is done via merge(), so that if the destination session already has the object in question present, it will reconcile the incoming state with what it already has. The load=False setting of merge() prevents the usage of a SELECT from loading existing state, if you are working with long term immutable data and don't want the extra SELECT emitted. Alternatively, if you really want to pass around detached objects and make use of their detached state, even though that state may be stale or even deleted vs. what's in the database, you can
Re: [sqlalchemy] DetachedInstanceError
On 8/14/2010 2:29 PM, Michael Bayer wrote: The approach above may be fine for your needs but I wouldn't encourage it. The demarcation of transaction boundaries shouldn't be an ad-hoc thing IMO and granular functions shouldn't be deciding whether or not they are setting up a transaction. Thanks. Yes, I was beginning to suspect such. Makes more sense to manage the session and commit/rollback issues at the top of the call stack. I was trying too hard to not have to pass the session down in argument lists, but looks like I should. Thanks, Michael -- 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] Error creating backref
On 8/6/2010 9:04 PM, Michael Hipp wrote:
Can someone help me figure out why I keep getting the error below. Here
are my 3 models. It's a simple many-one on banks-dealer and reps-dealer.
class Dealer(Base):
__tablename__ = 'dealers'
id_ = Column(Integer, primary_key=True)
reps = relationship('Rep', order_by='Rep.lname', backref=dealer)
banks = relationship('Bank', order_by='Bank.id_', backref=dealer)
class Bank(Base):
__tablename__ = 'banks'
id_ = Column(Integer, primary_key=True)
dealer_id = Column(Integer, ForeignKey('dealers.id_'))
dealer = relationship(Dealer, backref=backref('banks', order_by=id_))
class Rep(Base):
__tablename__ = 'reps'
id_ = Column(Integer, primary_key=True)
dealer_id = Column(Integer, ForeignKey('dealers.id_'))
dealer = relationship(Dealer, backref=backref('reps', order_by=id_))
I'm attempting to do this exactly like:
http://www.sqlalchemy.org/docs/ormtutorial.html#building-a-relationship
But when I run it it gives:
sqlalchemy.exc.ArgumentError: Error creating backref 'dealer' on
relationship 'Dealer.reps': property of that name exists on mapper
'Mapper|Rep|reps'
I think I figured it out, it's not necessary to put the backref on both ends.
Michael
--
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] Error creating backref
Can someone help me figure out why I keep getting the error below. Here
are my 3 models. It's a simple many-one on banks-dealer and reps-dealer.
class Dealer(Base):
__tablename__ = 'dealers'
id_ = Column(Integer, primary_key=True)
reps = relationship('Rep', order_by='Rep.lname', backref=dealer)
banks = relationship('Bank', order_by='Bank.id_', backref=dealer)
class Bank(Base):
__tablename__ = 'banks'
id_ = Column(Integer, primary_key=True)
dealer_id = Column(Integer, ForeignKey('dealers.id_'))
dealer = relationship(Dealer, backref=backref('banks', order_by=id_))
class Rep(Base):
__tablename__ = 'reps'
id_ = Column(Integer, primary_key=True)
dealer_id = Column(Integer, ForeignKey('dealers.id_'))
dealer = relationship(Dealer, backref=backref('reps', order_by=id_))
I'm attempting to do this exactly like:
http://www.sqlalchemy.org/docs/ormtutorial.html#building-a-relationship
But when I run it it gives:
sqlalchemy.exc.ArgumentError: Error creating backref 'dealer' on
relationship 'Dealer.reps': property of that name exists on mapper
'Mapper|Rep|reps'
Any help?
Thanks,
Michael
--
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] create_all() fails silently
On 8/4/2010 10:03 PM, Mike Conley wrote: On Wed, Aug 4, 2010 at 9:39 PM, Michael Hipp mich...@hipp.com mailto:mich...@hipp.com wrote: Can someone tell me why this code won't create any tables? The tables are defined in another file that calls declarative_base(). I presume the problem is that it doesn't know which tables to create. If so, how do I tell it what tables to create? Base = declarative_base() database = 'sqlite:///convert/db.sqlite' engine = create_engine(database, echo=True) metadata = Base.metadata metadata.create_all(engine) # Does nothing, says nothing Session = sessionmaker() Session.configure(bind=engine) Thanks, Michael Well, metadata here doesn't refer to the metadata that holds table definitions. What about something like import otherfile Base = otherfile.Base# assuming you use Base = declarative_base() in otherfile Thanks. But by the time I'm done there will be at least a dozen of those otherfiles. Which one do I get Base from? Thanks, Michael -- 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] create_all() fails silently
On 8/5/2010 8:26 AM, Mike Conley wrote: On Thu, Aug 5, 2010 at 6:29 AM, Michael Hipp mich...@hipp.com mailto:mich...@hipp.com wrote: On 8/4/2010 10:03 PM, Mike Conley wrote: Thanks. But by the time I'm done there will be at least a dozen of those otherfiles. Which one do I get Base from? You can put the declaration of Base in a common file that is imported by all the other files and your main program. Reference the Base from that file whenever needed and all code will share the same instance. That works great, thank you. Michael -- 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] Can an object span sessions?
Can this be made to work? session = Session() rec = MyModel() # create a record session.close() # Sometime later session = Session() rec.name = Fred # modify the record session.commit() # try to save modified record session.close() Does the session have to be global to this whole operation? Is there a way to issue the 'commit' on the record instead of the session? Thanks, Michael -- 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] Can an object span sessions?
On 8/5/2010 11:03 AM, Michael Bayer wrote: On Aug 5, 2010, at 11:54 AM, Michael Hipp wrote: Can this be made to work? session = Session() rec = MyModel() # create a record session.close() # Sometime later session = Session() rec.name = Fred # modify the record session.commit() # try to save modified record session.close() Does the session have to be global to this whole operation? Is there a way to issue the 'commit' on the record instead of the session? make sure you give a good read through http://www.sqlalchemy.org/docs/session.html . What you would like here is to make a detached object persistent again. add() and merge() both accomplish that end result though with different ramifications regarding pre-existing state. Thank you. Michael -- 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] Basic query questions ...
Hello, a couple of really basic questions ... 1) How do I write a query beforehand that will be evaluated later at some other place in the code? (Note that the session hopefully won't exist until that later time.) Also, certain parameters may need to be passed to the query at eval time (e.g. id==id_to_get). 2) How do I specify a query to load only certain columns? I think I've read the 'defer' docs, it seems to be a way to specify such in the mapper, not the query; or else do something like mark every column deferred and then undefer then at query time. Is there a simple way to just specify which columns to load at query time? Thanks, Michael -- 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] create_all() fails silently
Can someone tell me why this code won't create any tables? The tables are defined in another file that calls declarative_base(). I presume the problem is that it doesn't know which tables to create. If so, how do I tell it what tables to create? Base = declarative_base() database = 'sqlite:///convert/db.sqlite' engine = create_engine(database, echo=True) metadata = Base.metadata metadata.create_all(engine) # Does nothing, says nothing Session = sessionmaker() Session.configure(bind=engine) Thanks, Michael -- 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] Close the engine and go home
This is a really dumb noob question... I know how to create an engine, a Session, and a session. I see how to do session.close(). How do I close the engine? (i.e. How do I close all connections to the database cleanly so my application can exit and not leave the database server hanging?) Thanks, Michael -- 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] Opportunistic locking or edit sequence
Hello, new to SQLAlchemy. A question... Does SA implement what is sometimes referred to as opportunistic locking or sequence locking? This is intended to prevent near simultaneous but incompatible UPDATESs of the same record in the database by two different clients. Some databases implement this by using a field like 'edit_sequence' which is an incrementing sequence where the value in the db must match that in the UPDATE. Does SA have such a feature or something like it? Thanks, Michael Hipp Heber Springs, Arkansas, USA --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
