[sqlalchemy] 0.8.x - can't get correlation to work with nested subquery in column property
I am getting stuck trying to upgrade to 0.8.x, as I can't get correlation
to work with nested subquery in column property. Here's a slightly absurd
example for illustration:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Phone(Base):
__tablename__ = 'phones'
id = Column(Integer, primary_key=True)
number = Column(String(20))
contact_id = Column(Integer, ForeignKey('contacts.id'))
contact = relationship('Contact', backref='phones')
class Contact(Base):
__tablename__ = 'contacts'
id = Column(Integer, primary_key=True)
name = Column(String(200))
t = select(
[Phone.number.label('value')],
Phone.contact_id == Contact.id,
).correlate(Contact.__table__).alias('t')
Contact.phone_numbers = column_property(
select(
[func.string_agg(t.c.value, ', ')],
).as_scalar().label('phone_numbers'),
deferred=True,
)
session = Session()
print session.query(Contact).order_by(Contact.phone_numbers)
0.7.x:
SELECT contacts.id AS contacts_id, contacts.name AS contacts_name
FROM contacts ORDER BY (SELECT string_agg(t.value, :string_agg_2) AS
string_agg_1
FROM (SELECT phones.number AS value
FROM phones
WHERE phones.contact_id = contacts.id) AS t)
0.8.x:
SELECT contacts.id AS contacts_id, contacts.name AS contacts_name
FROM contacts ORDER BY (SELECT string_agg(t.value, :string_agg_2) AS
string_agg_1
FROM (SELECT phones.number AS value
FROM phones, contacts
WHERE phones.contact_id = contacts.id) AS t)
I tried `correlate_except` but it doesn't work in this case either. Perhaps
a side effect from ticket:2668?
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] 0.8 - `q.filter(None)` used to do nothing, now generates WHERE NULL
There are a few places in my code that does `q.filter(None)`, e.g. something like this: def base_filter(self): # subclasses may set this, or not pass def base_query(self): q = self.model_cls.query q = q.filter(self.base_filter()) return q Previously, it would generate SELECT * FROM xxx. In 0.8, it now generates SELECT * FROM xxx WHERE NULL, and subsequently no row is returned. The change was introduced 2 months ago in http://sqlalchemy.org/trac/changeset/b221bb4385a4 Anyway, the 0.8 behavior does feel a bit more correct, so perhaps I shall fix my code instead :) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/8EI3RX6lpSkJ. 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] [Q] SQLSoup and pymssql from Linux
On Tuesday, October 9, 2012 10:19:34 PM UTC+8, Michael Bayer wrote: yeah, the whole FreeTDS story is awful, I don't understand any of it either. I'm still at wave a dead chicken stage with FreeTDS ( http://dictionary.reference.com/browse/wave+a+dead+chicken). The ODBC Driver 1.0 for Linux from Microsoft (http://www.microsoft.com/en-us/download/details.aspx?id=28160) works very well for me. It allows me to use the same driver (SQL Server Native Client 11.0) on both Linux-based development machine and Windows-based production server. With this added to odbcinst.ini: [SQL Server Native Client 11.0] Description=Microsoft SQL Server ODBC Driver V1.0 for Linux Driver=/opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1790.0 Threading=1 UsageCount=1 I can then use a simple SQLAlchemy URL: mssql+pyodbc://username:password@server/database?driver=SQL+Server+Native+Client+11.0 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/erYv9loNlmQJ. 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] autocommit=False, autoflush on begin, after_flush event, unintended side effect?
This is semi-related to the latest post from Kent. I just noticed that I
have been abusing the autoflush on begin behavior (by the
_take_snapshot() method in orm/session.py) to create additional instances
within the after_flush Session Event. Here's some sample code to illustrate
that:
from sqlalchemy import event
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, scoped_session, sessionmaker
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.types import Integer, Text
Base = declarative_base()
engine = create_engine('postgresql://postgres@localhost/test')
Session = scoped_session(sessionmaker(
autoflush=False,
autocommit=False,
))
Session.configure(bind=engine)
class Ticket(Base):
__tablename__ = 'tickets'
id = Column(Integer, primary_key=True)
description = Column(Text, nullable=False)
class Notification(Base):
__tablename__ = 'notifications'
id = Column(Integer, primary_key=True)
ticket_id = Column(Integer, ForeignKey('tickets.id'), nullable=False)
ticket = relationship('Ticket', backref='notifications')
content = Column(Text, nullable=False)
def send_notification(session, flush_context):
for instance in session.new:
if isinstance(instance, Ticket):
Notification(
ticket=instance,
content='Ticket %d created' % instance.id,
)
# No flush or commit!
event.listen(Session, 'after_flush', send_notification)
Base.metadata.create_all(engine)
ticket = Ticket(description='test')
Session.add(ticket)
Session.commit()
query = Session.query(Notification).filter_by(
content='Ticket %d created' % ticket.id
)
assert query.count()
Although the code only does Session.commit() once, it actually executes 2
INSERT statements in 2 separate transactions. I am pretty sure this is not
an intended use case, right?
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/euIwN8AVPoYJ.
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] Documentation for joined table inheritance - Engineer.id renders as people.id
In the documentation for joined table inheritance [1], it mentions that: Note that above, the Engineer.id attribute, since it shares the same attribute name as the Person.id attribute, will in fact represent the people.id and engineers.id columns together, and will render inside a query as people.id. If I run: print str(Session.query(Engineer.id).order_by(Engineer.id)) the query generated would be: SELECT engineers.id AS engineers_id FROM people JOIN engineers ON people.id = engineers.id ORDER BY engineers.id Shouldn't the two engineers.id in the query be people.id? In this case, I would prefer to get people.id to help the poor query optimizer of mssql... [1] http://docs.sqlalchemy.org/en/rel_0_7/orm/extensions/declarative.html#joined-table-inheritance -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/XBKebmsrS4wJ. 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] Column alias created by SQLAlchemy is exeeding max column length of db2.
On Wednesday, June 24, 2009 10:25:09 PM UTC+8, Michael Bayer wrote: Beeno wrote: Hello Sqlalchemy appears to be aliasing each column in a SELECT statement as a concatenation of the schema, table name and column name. The resulting alias exceeds DB2's 30 character limit for a column. For example: If the name of the schema was 'myshemaname', the table was called 'mytablename' and the column was appropriately named 'mycolumnname' the resuling alias of the column would be myschemaname_mytablename_mycolumnname and the following DB2 SQL exception will be generated: sqlalchemy.exceptions.ProgrammingError: (ProgrammingError) ibm_db_dbi::ProgrammingError: [IBM][CLI Driver][DB2/AIX64] SQL0107N The name myschemaname_mytablename_mycolumnname is too long. The maximum length is 30. SQLSTATE=4 Is the any workaround for this? Is there anyway for force Sqlalchemy to use only the colum name when creating an alias? this is technically a bug in the DB2 dialect as they should be setting the max_identifier_length attribute correctly on their dialect.You can set it manually: engine = create_engine(...) engine.dialect.max_identifier_length = 30 On Wednesday, June 24, 2009 10:25:09 PM UTC+8, Michael Bayer wrote: Beeno wrote: Hello Sqlalchemy appears to be aliasing each column in a SELECT statement as a concatenation of the schema, table name and column name. The resulting alias exceeds DB2's 30 character limit for a column. For example: If the name of the schema was 'myshemaname', the table was called 'mytablename' and the column was appropriately named 'mycolumnname' the resuling alias of the column would be myschemaname_mytablename_mycolumnname and the following DB2 SQL exception will be generated: sqlalchemy.exceptions.ProgrammingError: (ProgrammingError) ibm_db_dbi::ProgrammingError: [IBM][CLI Driver][DB2/AIX64] SQL0107N The name myschemaname_mytablename_mycolumnname is too long. The maximum length is 30. SQLSTATE=4 Is the any workaround for this? Is there anyway for force Sqlalchemy to use only the colum name when creating an alias? this is technically a bug in the DB2 dialect as they should be setting the max_identifier_length attribute correctly on their dialect.You can set it manually: engine = create_engine(...) engine.dialect.max_identifier_length = 30 Sorry to dig up an old thread. On DB2 for z/OS, the identifier length limit is 30 for column and column alias, and 128 for almost everything else: http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z9.doc.sqlref%2Fsrc%2Ftpc%2Fdb2z_limits.htm I can set `label_length = 30` to apply the limit for column alias. What would be the best way to apply the limit for column only, without affecting table, constraint, etc? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/7Xv-YryBvNUJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Something in Pyramid is preventing gc on SQLAlchemy objects
I too encountered the same problem that wasted me days, until I caught pdtb_sqla_queries with my very limited memory profiling skill. I have actually filed a bug here: https://github.com/Pylons/pyramid_debugtoolbar/issues/51 and there is a separate issue to make it less leaky: https://github.com/Pylons/pyramid_debugtoolbar/issues/52 On Feb 23, 9:29 pm, Vlad K. v...@haronmedia.com wrote: /facepalm Okay, debugtoolbar does that. If I disable it from config, everything works fine. My fault. Been hunting for this memory leak for two days straight and it never occurred to me that even the command line bootstrapped Pyramid, when using development.ini, will have the debug toolbar on. Sorry for wasting your time, folks. I'm sending this email to the SQLAlchemy list too, where I asked about this problem, for further reference in case anyone else has the same problem .oO V Oo. On 02/23/2012 02:24 PM, Vlad K. wrote: Hi all. I have a Pyramid bootstrapped script that has to process thousands of rows (on relatively complex model relationships) per call from command line. Pyramid is bootstrapped to reuse models and helper functions from the main web application this script is part of. The problem I'm having is that in each iteration and/or individual transaction, the memory keeps growing, seems like SQLAlchemy objects are not released. A self-sufficient example script is here: https://gist.github.com/d669e958c54869c69831 Without bootstrapping Pyramid (comment out line 266 and set env=None or something), the gcdelta is 0 or negative. With Pyramid bootstrapped (note that nothing else touches the application or request, only bootstrap is called) the gcdelta is in thousands per 200 iterations. Fedora 16 x86_64 PostgreSQL 9.1 Pyramid 1.2.1 (except sqlalchemy and psycopg2, all dependencies are default given with 1.2.1 installation) SQLAlchemy 0.7.5 Psycopg2 2.4.4 Transaction 1.1.1 Zope.Sqlalchemy 0.6.1 -- 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] Get a contains_eager collection to follow order_by
Is it possible to get a contains_eager collection to follow the
order_by defined in the relationship? It seems like with eager
loading, the order_by defined will just be ignored (which I think make
sense, just wondering if there is a better way than manual sorting).
Here's some sample code to illustrate:
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import contains_eager, relationship, Session
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.types import Integer, Unicode
Base = declarative_base()
class Contact(Base):
__tablename__ = 'contacts'
id = Column(Integer, primary_key=True)
name = Column(Unicode, nullable=False)
phones = relationship('Phone', back_populates='contact',
order_by='Phone.id')
class Phone(Base):
__tablename__ = 'phones'
id = Column(Integer, primary_key=True)
number = Column(Unicode, nullable=False)
contact_id = Column(Integer, ForeignKey('contacts.id'),
nullable=False)
contact = relationship('Contact', back_populates='phones')
engine = create_engine('sqlite:///:memory:')
engine.echo = True
Base.metadata.create_all(engine)
session = Session(bind=engine)
c = Contact(name=u'Stan Marsh')
c.phones.append(Phone(number=u'999'))
c.phones.append(Phone(number=u'456'))
session.add(c)
session.commit()
session.expire(c)
c = session.query(Contact).one()
# [999, 456]
print [x.number for x in c.phones]
session.expire(c)
query =
session.query(Contact).outerjoin(Contact.phones).order_by(Phone.number)
# If commented, print [999, 456]. Otherwise, print [456, 999]
query = query.options(contains_eager('phones'))
c = query.one()
print [x.number for x in c.phones]
--
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] Mapping a Class against Multiple Tables - how to define relationship
Using the second example in http://www.sqlalchemy.org/docs/orm/mapper_config.html#mapping-a-class-against-multiple-tables, how should I define relationship properties KeywordUser.keyword and KeywordUser.user? I tried different primaryjoin's, but when used as filter, e.g. KeywordUser.user.has(name='xxx'), it won't correlate with the j joined table. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: using @declared_attr to define a column in an actual class (non-mixin)
Thanks for clearing that up. __abstract__ = True looks like a nice addition. In this case, I decided to go with the metaclass approach, i.e. declaring the attributes as usual, and then delete them if necessary in the metaclass. On Sep 23, 1:36 pm, Michael Bayer mike...@zzzcomputing.com wrote: @declared_attr when used for attributes outside of __table_args__, __tablename__ and __mapper_args__ is only recognized on a mixin, or on a class that uses a new directive __abstract__ = True. It's skipped on mapped classes when used for plain column attributes since inheriting from a mapped class means you're using mapper inheritance - and in the usual case of single- or joined-table inheritance, the subclass specifically should not get copies of columns on the superclass. So really @declared_attr returning a column on the mapped class should be raising an error here, perhaps I'll make it emit a warning for the time being since it will not do anything useful. If you'd like to put a non-mixin class in the middle of your hierarchy that can define columns that immediate subclasses should have, use the tip of 0.7 (0.7.3 not released yet) and put the directive __abstract__ = True on the class - the @declared_attr's on columns should be recognized in that case. You wouldn't want to have __tablename__ = 'test' on such a class either since it isn't mapped. On Sep 23, 2011, at 12:48 AM, Yap Sok Ann wrote: With this code: from sqlalchemy.ext.declarative import declarative_base, declared_attr from sqlalchemy.schema import Column from sqlalchemy.types import Integer, String Base = declarative_base() class Mixin(object): @declared_attr def attr2(cls): return Column(String(20), nullable=False) class Test(Base, Mixin): __tablename__ = 'test' id = Column(Integer, primary_key=True) @declared_attr def attr1(cls): return Column(String(20), nullable=False) if __name__ == '__main__': print Test.attr1.__class__ print Test.attr2.__class__ Test.attr1 will be a sqlalchemy.schema.Column, while Test.attr2 will be a sqlalchemy.orm.attributes.InstrumentedAttribute. Why are they behave differently? Anyway, what I want to achieve is to selectively define a column based on some external flag, so I was trying to put in if..else block inside @declared_attr to return either None or Column. Is there a better way to do it, e.g. using a metaclass? Thanks. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] using @declared_attr to define a column in an actual class (non-mixin)
With this code: from sqlalchemy.ext.declarative import declarative_base, declared_attr from sqlalchemy.schema import Column from sqlalchemy.types import Integer, String Base = declarative_base() class Mixin(object): @declared_attr def attr2(cls): return Column(String(20), nullable=False) class Test(Base, Mixin): __tablename__ = 'test' id = Column(Integer, primary_key=True) @declared_attr def attr1(cls): return Column(String(20), nullable=False) if __name__ == '__main__': print Test.attr1.__class__ print Test.attr2.__class__ Test.attr1 will be a sqlalchemy.schema.Column, while Test.attr2 will be a sqlalchemy.orm.attributes.InstrumentedAttribute. Why are they behave differently? Anyway, what I want to achieve is to selectively define a column based on some external flag, so I was trying to put in if..else block inside @declared_attr to return either None or Column. Is there a better way to do it, e.g. using a metaclass? Thanks. -- 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] discriminator_on_association.py - creating a Customer instance without specifying the addresses attribute
Using the discriminator_on_association.py example, if I create a Customer instance without specifying the addresses attribute, the for- loop at the end will throw exception: AttributeError: 'NoneType' object has no attribute 'addresses' It works if I specify addresses=[], but that sounds like too much work :P Is there a workaround for this? Thanks. -- 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] problem with nested column_property's label in subquery
With the following code:
#
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import column_property, relation
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.sql.expression import select
from sqlalchemy.types import Integer, String
Base = declarative_base()
Session = scoped_session(sessionmaker(
autoflush=False,
autocommit=True,
))
engine = create_engine('mssql+pyodbc://localhost/test')
Session.configure(bind=engine)
class Author(Base):
__tablename__ = 'authors'
id = Column(Integer, primary_key=True)
first_name = Column(String(50), nullable=False)
last_name = Column(String(50), nullable=False)
name = column_property((first_name + ' ' +
last_name).label('name'))
class Book(Base):
__tablename__ = 'books'
id = Column(Integer, primary_key=True)
title = Column(String(50), nullable=False)
author_id = Column(Integer, ForeignKey('authors.id'),
nullable=False,
index=True)
author = relation(Author, backref='books')
author_name = column_property(
select(
[Author.name],
author_id == Author.id,
).label('author_name')
)
Base.metadata.create_all(engine)
Session.query(Book).order_by('id').all()
Session.query(Book).order_by('id').limit(5).offset(5).all()
#
the 2nd query will fail with this error:
sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('42S22', [42S22]
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name
'name'. (207) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server
Driver][SQL Server]Statement(s) could not be prepared. (8180))
u'SELECT anon_1.name, anon_1.books_id, anon_1.books_title,
anon_1.books_author_id \nFROM (SELECT (SELECT authors.first_name + ?
+authors.last_name AS name \nFROM authors \nWHERE books.author_id =
authors.id) AS author_name, books.id AS books_id, books.title AS
books_title, books.author_id AS books_author_id, ROW_NUMBER() OVER
(ORDER BY id) AS mssql_rn \nFROM books) AS anon_1 \nWHERE mssql_rn ?
AND mssql_rn = ?' (' ', 5, 10)
Somehow it selects anon_1.name instead of anon_1.contact_name. I got
the error on both 0.6.7 and 0.7b4.
--
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] Examples for Index() in 07Migration wiki
In the 07Migration wiki, there is this line:
Index('name', name=idx_name)
which doesn't work for me. I have to change it to:
Index('idx_name', 'name')
Is it a typo or some python 3 only syntax?
--
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] declarative - automatically add a primary key if the table doesn't have one
This is related to topic need 0.6_beta2-compat declarative meta http://groups.google.com/group/sqlalchemy/browse_thread/thread/ae7cb9d2ab0b9cca Prior to version 0.6, I use the following code to automatically add a primary key if the table doesn't have one defined: from sqlalchemy.ext.declarative import declarative_base, DeclarativeMeta from sqlalchemy.schema import Column from sqlalchemy.types import Integer class Meta(DeclarativeMeta): def __init__(cls, classname, bases, dict_): for attr in dict_.itervalues(): if isinstance(attr, Column) and attr.primary_key: break else: dict_['id'] = Column(Integer, primary_key=True) return super(Meta, cls).__init__(classname, bases, dict_) Base = declarative_base(metaclass=Meta) Of course, that doesn't work anymore in 0.6. The suggestion from the aforementioned threads is to replace: dict_['id'] = Column(Integer, primary_key=True) with cls.id = Column(Integer, primary_key=True) Unfortunately, that alone doesn't work in this case. The problem is that the Base class itself will be the first one to go through the Meta.__init__() method, so the whole thing essentially becomes: Base.id = Column(Integer, primary_key=True) For it to work, I have to wrap the code in an if-block, i.e. class Meta(DeclarativeMeta): def __init__(cls, classname, bases, dict_): if classname != 'Base': for attr in dict_.itervalues(): if isinstance(attr, Column) and attr.primary_key: break else: cls.id = Column(Integer, primary_key=True) return super(Meta, cls).__init__(classname, bases, dict_) which looks rather ugly. Is there a cleaner way to achieve this? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: declarative - automatically add a primary key if the table doesn't have one
On Sep 22, 11:37 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Sep 22, 2010, at 4:30 AM, Yap Sok Ann wrote: This is related to topic need 0.6_beta2-compat declarative meta http://groups.google.com/group/sqlalchemy/browse_thread/thread/ae7cb9... Prior to version 0.6, I use the following code to automatically add a primary key if the table doesn't have one defined: from sqlalchemy.ext.declarative import declarative_base, DeclarativeMeta from sqlalchemy.schema import Column from sqlalchemy.types import Integer class Meta(DeclarativeMeta): def __init__(cls, classname, bases, dict_): for attr in dict_.itervalues(): if isinstance(attr, Column) and attr.primary_key: break else: dict_['id'] = Column(Integer, primary_key=True) return super(Meta, cls).__init__(classname, bases, dict_) Base = declarative_base(metaclass=Meta) Of course, that doesn't work anymore in 0.6. The suggestion from the aforementioned threads is to replace: dict_['id'] = Column(Integer, primary_key=True) with cls.id = Column(Integer, primary_key=True) Unfortunately, that alone doesn't work in this case. The problem is that the Base class itself will be the first one to go through the Meta.__init__() method, so the whole thing essentially becomes: Base.id = Column(Integer, primary_key=True) For it to work, I have to wrap the code in an if-block, i.e. class Meta(DeclarativeMeta): def __init__(cls, classname, bases, dict_): if classname != 'Base': for attr in dict_.itervalues(): if isinstance(attr, Column) and attr.primary_key: break else: cls.id = Column(Integer, primary_key=True) return super(Meta, cls).__init__(classname, bases, dict_) which looks rather ugly. Is there a cleaner way to achieve this? I didn't think metaclasses were supposed to be pretty ? Checking that you're not the base is pretty standard metaclass stuff. If the hardcoded name is the issue, you can look in bases: if object not in bases: or something more generic: for k in cls.__mro__[1:]: if isinstance(k, Meta): # you're a Base subclass Good point. I shall stick with the name checking solution then. Thank you for your help. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: declarative base - can a relationship be used within a column_property?
On Aug 22, 2:12 am, Michael Bayer mike...@zzzcomputing.com wrote:
On Aug 21, 2010, at 1:38 PM, Michael Bayer wrote:
On Aug 19, 2010, at 6:38 AM, Yap Sok Ann wrote:
With declarative base, is it possible to use a relationship within a
column_property?
you mean, as I am seeing below, to use the any() operator produced by a
relationship...
Here's some sample code to illustrate what I want to
achieve:
players = relation('Player', back_populates='team')
# This doesn't work
#has_star_player = column_property(players.any(star=True))
# This works
has_star_player = column_property(
exists().where(id == Player.team_id).where(Player.star ==
True))
# This also works
Team.__mapper__.add_property(
'has_star_player2',
column_property(Team.players.any(star=True)),
)
so that's your answer - the two approaches you have are fine. For the
other one, you'd call players.comparator.any(), but that also doesn't work
since the relationship is not aware of its parent mapper at that point.
oh well, lets make this easier, declarative documents this:
Team.has_star_player_2 = column_property(Team.players.any(star=True))
Cool, that's definitely the best approach. Thank you.
--
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] declarative base - can a relationship be used within a column_property?
With declarative base, is it possible to use a relationship within a
column_property? Here's some sample code to illustrate what I want to
achieve:
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import column_property, relation, sessionmaker
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.sql.expression import exists
from sqlalchemy.types import Boolean, Integer, String
Base = declarative_base()
class Player(Base):
__tablename__ = 'players'
id = Column(Integer, primary_key=True)
name = Column(String, nullable=False)
star = Column(Boolean, nullable=False, default=False)
team_id = Column(Integer, ForeignKey('teams.id'), nullable=False)
team = relation('Team', back_populates='players')
class Team(Base):
__tablename__ = 'teams'
id = Column(Integer, primary_key=True)
name = Column(String, nullable=False)
players = relation('Player', back_populates='team')
# This doesn't work
#has_star_player = column_property(players.any(star=True))
# This works
has_star_player = column_property(
exists().where(id == Player.team_id).where(Player.star ==
True))
# This also works
Team.__mapper__.add_property(
'has_star_player2',
column_property(Team.players.any(star=True)),
)
engine = create_engine('sqlite://', echo=True)
Base.metadata.create_all(engine)
sess = sessionmaker(engine)()
arsenal = Team(name='Arsenal')
sess.add(arsenal)
fabregas = Player(name='Fabregas', team=arsenal, star=True)
sess.add(fabregas)
barcelona = Team(name='Barcelona')
sess.add(barcelona)
query = sess.query(Team.name).filter(Team.has_star_player == True)
print query.all()
query = sess.query(Team.name).filter(Team.has_star_player2 == True)
print query.all()
--
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] Using MapperExtension for timestamp - how to avoid related instances from being pulled in
I use a MapperExtension to automatically put the current timestamp
into a updated_at column for all entities. This works fine except when
related entities get involved. Let's say I have a Many-To-One relation
between employees and departments. If I switch the department for an
employee, even though nothing has really changed for the department
instance, both employee and department instances will get a new
timestamp. I would like to know what is the best way to just update
the employee and skip the department.
Here's the sample code:
##
from datetime import datetime
from dateutil.tz import tzlocal
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.declarative import declarative_base,
DeclarativeMeta
from sqlalchemy.orm import relation, scoped_session, sessionmaker
from sqlalchemy.orm.interfaces import MapperExtension
from sqlalchemy.schema import Column, ForeignKey, MetaData
from sqlalchemy.types import DateTime, String
engine = create_engine('sqlite:///:memory:')
metadata = MetaData()
metadata.bind = engine
Session = scoped_session(sessionmaker())
class AuditMeta(DeclarativeMeta):
def __init__(cls, classname, bases, dict_):
dict_['updated_at'] = Column(DateTime(timezone=True))
return DeclarativeMeta.__init__(cls, classname, bases, dict_)
class AuditTracker(MapperExtension):
def before_update(self, mapper, connection, instance):
instance.updated_at = datetime.now(tzlocal())
class AuditEntity(object):
__mapper_args__ = {'extension': AuditTracker()}
AuditBase = declarative_base(name='AuditBase', cls=AuditEntity,
metaclass=AuditMeta, metadata=metadata)
class Employee(AuditBase):
__tablename__ = 'employees'
name = Column(String(50), primary_key=True)
department_name = Column(String(50),
ForeignKey('departments.name',
onupdate='cascade'),
nullable=True, index=True)
department = relation('Department', backref='employees')
class Department(AuditBase):
__tablename__ = 'departments'
name = Column(String(50), primary_key=True)
metadata.create_all()
employee = Employee(name='Mary')
department = Department(name='Accounting')
Session.add(employee)
Session.add(department)
Session.commit()
employee.department = department
Session.commit()
print employee.updated_at
print department.updated_at # how to make this None?
##
By the way, the actual database got a lot of related entities, and
deadlocks were happening everywhere since I added the MapperExtension.
Haven't seen one for years :)
Regards,
Yap
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
