[sqlalchemy] Users of sqlalchemy-migrate
Hello, I'd like to add a list of projects that use sqlalchemy-migrate[1] to our project website in a way similar to the SQLAlchemy website [2]. I know of TurboGears 2 [3] and OpenStack [4] but would be interested in other projects too. If you have a publicly available website I would link to your project on our project page and in our documentation. Please send a short description of your project and a link to me or the the migrate-users list [5]. Kind regards, Jan Dittberner [1] http://sqlalchemy-migrate.googlecode.com/ [2] http://sqlalchemy.org [3] http://turbogears.org/2.0/docs/main/DatabaseMigration.html [4] http://www.openstack.org/ [5] mailto:migrate-us...@googlegroups.com -- Jan Dittberner - Debian Developer GPG-key: 4096R/558FB8DD 2009-05-10 B2FF 1D95 CE8F 7A22 DF4C F09B A73E 0055 558F B8DD http://www.dittberner.info/ signature.asc Description: Digital signature
[sqlalchemy] Problem/bug with column_property on eagerloaded polymophic table
I have a column_property on a polymorphic base class. When I
joinedload/subqueryload a derived class the colum_property makes the query
fail.
class A(Base):
__tablename__ = a
id = Column(Integer, primary_key=True)
type= Column(String(40), nullable=False)
__mapper_args__ = {'polymorphic_on': type}
A.anything = orm.column_property(A.id + 1000)
class B(A):
__tablename__ = b
account_id = Column(Integer, ForeignKey('a.id'), primary_key=True)
x_id= Column(Integer, ForeignKey('x.id'), nullable=False)
__mapper_args__ = {'polymorphic_identity': 'named'}
class X(Base):
__tablename__ = x
id = Column(Integer, primary_key=True)
b = orm.relationship(B
Calling:
print Session.query(X).options(joinedload(b))
produces:
SELECT x.id AS x_id,
anon_1.a_id AS anon_1_a_id,
anon_1.a_type AS anon_1_a_type,
a.id + %(id_1)s AS anon_2,
anon_1.b_account_id AS anon_1_b_account_id,
anon_1.b_x_id AS anon_1_b_x_idFROM a,
xLEFT OUTER JOIN
(SELECT a.id AS a_id,
a.TYPE AS a_type,
b.account_id AS b_account_id,
b.x_id AS b_x_id
FROM a
JOIN b ON a.id = b.account_id) AS anon_1 ON x.id = anon_1.b_x_id
It seems that the a.id + %(id_1) should changed to anon_1.a_id and a
removed from FROM or better a.id + %(id_1)s should be moved into the
sub select named anon_1. This is probably what you want if the
column_property was actually a subselect itself (which is want I'm actually
trying to do).
Am I correct in thinking that this corner case simply isn't supported yet?
Or is it a bug? Or am I doing something wrong?
Actually the above query doesn't fail outright. Although it does create a
unsuspecting join. But if the column_property instead was something like
class subA(Base):
__tablename__ = subA
id = Column(Integer, primary_key=True)
a_id = Column(Integer, ForeignKey('a.id'), nullable=False)
value = Column(Integer, nullable=False)
A.anything = orm.column_property(
select([func.sum(subA.value)], subA.a_id==A.id))
Then the sql would be:
SELECT x.id AS x_id,
anon_1.a_id AS anon_1_a_id,
anon_1.a_type AS anon_1_a_type,
(SELECT sum(subA.value) AS sum_1
FROM subA
WHERE subA.a_id = a.id) AS anon_2,
anon_1.b_account_id AS anon_1_b_account_id,
anon_1.b_x_id AS anon_1_b_x_idFROM xLEFT OUTER JOIN
(SELECT a.id AS a_id,
a.TYPE AS a_type,
b.account_id AS b_account_id,
b.x_id AS b_x_id
FROM a
JOIN b ON a.id = b.account_id) AS anon_1 ON x.id = anon_1.b_x_id
Which naturally doesn't work at all, since a.id inside the first
subselect doesn't refer to anything.
PS: I have no idea how this email is going to get formatted, please let me
know if it is impossible to read.
--
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/-/1CSullHjqPMJ.
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] Problem/bug with column_property on eagerloaded polymophic table
It's a bug but a small one... I'd be ready to jump off a bridge if this kind of
thing wasn't working in general at this point. Trying your test case, the
column_property() for the moment has to be against the actual Column, not the
mapped property (there's a difference):
class A(Base):
__tablename__ = a
id = Column(Integer, primary_key=True)
type= Column(String(40), nullable=False)
__mapper_args__ = {'polymorphic_on': type}
anything = column_property(id + 1000)
or:
A.anything = column_property(A.__table__.c.id + 1000)
When you access A.id, you get an InstrumentedAttribute, which produces a SQL
expression equivalent to A.__table__.c.id except for an annotation that tells
the ORM to treat it differently, I couldn't say exactly why it goes wrong in
the way it does since it typically adapts it more aggressively, not less
so...the problem here is a.id isn't getting lumped into the adaptation of
the a join b as a subquery off of x. Nice test case, thanks for making it
easy.
Anyway, that's the workaround for now and ticket 2316
http://www.sqlalchemy.org/trac/ticket/2316 is added.
On Oct 30, 2011, at 5:30 PM, Adrian Tejn Kern wrote:
I have a column_property on a polymorphic base class. When I
joinedload/subqueryload a derived class the colum_property makes the query
fail.
class A(Base):
__tablename__ = a
id = Column(Integer, primary_key=True)
type= Column(String(40), nullable=False)
__mapper_args__ = {'polymorphic_on': type}
A.anything = orm.column_property(A.id + 1000)
class B(A):
__tablename__ = b
account_id = Column(Integer, ForeignKey('a.id'), primary_key=True)
x_id= Column(Integer, ForeignKey('x.id'), nullable=False)
__mapper_args__ = {'polymorphic_identity': 'named'}
class X(Base):
__tablename__ = x
id = Column(Integer, primary_key=True)
b = orm.relationship(B
Calling:
print Session.query(X).options(joinedload(b))
produces:
SELECT x.id AS x_id,
anon_1.a_id AS anon_1_a_id,
anon_1.a_type AS anon_1_a_type,
a.id + %(id_1)s AS anon_2,
anon_1.b_account_id AS anon_1_b_account_id,
anon_1.b_x_id AS anon_1_b_x_id
FROM a,
x
LEFT OUTER JOIN
(SELECT a.id AS a_id,
a.TYPE AS a_type,
b.account_id AS b_account_id,
b.x_id AS b_x_id
FROM a
JOIN b ON a.id = b.account_id) AS anon_1 ON x.id = anon_1.b_x_id
It seems that the a.id + %(id_1) should changed to anon_1.a_id and a
removed from FROM or better a.id + %(id_1)s should be moved into the sub
select named anon_1. This is probably what you want if the column_property
was actually a subselect itself (which is want I'm actually trying to do).
Am I correct in thinking that this corner case simply isn't supported yet? Or
is it a bug? Or am I doing something wrong?
Actually the above query doesn't fail outright. Although it does create a
unsuspecting join. But if the column_property instead was something like
class subA(Base):
__tablename__ = subA
id = Column(Integer, primary_key=True)
a_id = Column(Integer, ForeignKey('a.id'), nullable=False)
value = Column(Integer, nullable=False)
A.anything = orm.column_property(
select([func.sum(subA.value)], subA.a_id==A.id))
Then the sql would be:
SELECT x.id AS x_id,
anon_1.a_id AS anon_1_a_id,
anon_1.a_type AS anon_1_a_type,
(SELECT sum(subA.value) AS sum_1
FROM subA
WHERE subA.a_id = a.id) AS anon_2,
anon_1.b_account_id AS anon_1_b_account_id,
anon_1.b_x_id AS anon_1_b_x_id
FROM x
LEFT OUTER JOIN
(SELECT a.id AS a_id,
a.TYPE AS a_type,
b.account_id AS b_account_id,
b.x_id AS b_x_id
FROM a
JOIN b ON a.id = b.account_id) AS anon_1 ON x.id = anon_1.b_x_id
Which naturally doesn't work at all, since a.id inside the first subselect
doesn't refer to anything.
PS: I have no idea how this email is going to get formatted, please let me
know if it is impossible to read.
--
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/-/1CSullHjqPMJ.
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.
--
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] Querying mssql time columns
I think I've found a bug--can anyone else confirm this? It appears that SQLAlchemy cannot query SQLServer time columns because Python datetime.time objects are always promoted to full datetime types. Once promoted the SQLServer returns the error: 'The data types time and datetime are incompatible in the greater than operator. (402) (SQLExecDirectW)'. The type promotion occurs in sqlalchemy/dialects/mssql/base.py:268-276. Interestingly, according to the documentation in this file, SQLAlchemy can already determine whether the DATE and TIME types are supported (in SQLServer 2008 or later), and I have confirmed it emits the correct DDL but still treats all bound time values as DATETIMEs anyway. Is this a bug or am I missing something? Many thanks, Simon. -- 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/-/_30ui_LT8qcJ. 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] Querying mssql time columns
On Oct 31, 2011, at 1:18 AM, Simon Haines wrote: I think I've found a bug--can anyone else confirm this? It appears that SQLAlchemy cannot query SQLServer time columns because Python datetime.time objects are always promoted to full datetime types. Once promoted the SQLServer returns the error: 'The data types time and datetime are incompatible in the greater than operator. (402) (SQLExecDirectW)'. The type promotion occurs in sqlalchemy/dialects/mssql/base.py:268-276. What DBAPI are you using ? The date/time behavior there is all tailored to suit what the DBAPIs want. Also some comparisons aren't possible with SQL server, it has extremely limited date functionality. Basically SQLAlchemy can only do what the DBAPI allows. As the comments there indicate, Pyodbc crashes if a datetime.time() is passed directly, this might be considered a Pyodbc bug if that behavior can be re-confirmed ,as this code is a few years old. Assuming you're using pyodbc, you'd need to get your query to work as follows first: import pyodbc import datetime conn = pyodbc.connect(dsn=ms_2005;UID=scott;PWD=tiger) cursor = conn.cursor() # example: cursor.execute(SELECT ?, datetime.time(12, 15, 0)) print cursor.fetchall() # put your statement here using ? for bound parameters cursor.execute(your statement goes here, your time/etc. parameters go here) print cursor.fetchall() show me a working query with the DBAPI (again assuming pyodbc here) and I will adjust any incompatibilities on the SQLAlchemy side. -- 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.
