[sqlalchemy] Or_ function results in a query containing implicit cross join
If one of from tables used in a query is empty, the query returns no
tables. This is because of implicit cross join. Here is a simplified
example.
Code:
from sqlalchemy import create_engine, Column, Integer, or_
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
if __name__ == '__main__':
engine = create_engine('sqlite://')
Base.metadata.create_all(engine)
session = Session(engine)
print 'Add one A object'
session.add(A(id=1))
session.commit()
without_or = session.query(A.id).filter(A.id == 1)
with_or = session.query(A.id).filter(or_(A.id == 1, B.id == 2))
print 'Without or:', without_or.all()
print 'With or:', with_or.all()
print 'Add one B object'
session.add(B(id=1))
session.commit()
print 'Without or:', without_or.all()
print 'With or:', with_or.all()
Output:
Add one A object
Without or: [(1,)]
With or: []
Add one B object
Without or: [(1,)]
With or: [(1,)]
This is because with_or creates following SQL expression:
SELECT a.id AS a_id
FROM a, b
WHERE a.id = 1 OR b.id = 2
SQL expression's FROM clause is a cartesian product (cross join) of two
tables. If one of tables is empty, cartesian product is empty (naturally).
In this case, changing cross join to full join resolves the problem:
SELECT a.id AS a_id
FROM a FULL JOIN b ON TRUE
WHERE a.id = 1 OR b.id = 2
At least SQLite and PostgreSQL use the implicit cross join.
I don't want to create a issue before I know if I have understood something
wrong.
--
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/d/optout.
[sqlalchemy] How can i use LIKE with an association proxy?
In my user have I have an association proxy so I can access all email
addresses of the user via User.all_emails.
For a simple exact search I simply
.filter(User.all_emails.contains('f...@example.com')).
Is it also possible to use e.g. a LIKE match (besides manually joining the
Emails table and using Email.email.like(...))?
--
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/d/optout.
[sqlalchemy] polymorphic objects
well, i'm having trouble dealing with polymorphic objects. i mean, the functionality is fine, i just don't know how to obtain the main object. let me be clear: i have A, which is my main object, and is inherited by B and C. I would like to work with the A object, even though it's polymorphic identity refers to B or C. I know it's not the default behavior, but can I obtain A from a query? thanks a lot! richard. -- 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/d/optout. attachment: richard.vcf
[sqlalchemy] Automap not reflecting tables in Postgres schemas
Hello,
I'm following the documentation for reflecting database tables using
`automap`:
http://docs.sqlalchemy.org/en/latest/orm/extensions/automap.html#generating-mappings-from-an-existing-metadata.
When I don't specific a schema, and Postgres uses the default `public`
schema, this works as expected, and I find the names of my tables:
m = MetaData()
b = automap_base(bind=engine, metadata=m)
b.prepare(engine, reflect=True)
b.classes.keys()
['ads', 'spatial_ref_sys', 'income']
But when I specific an explicit schema, I don't have access to the tables
in `Base.classes` anymore.
m = MetaData(schema='geography')
b = automap_base(bind=engine, metadata=m)
b.prepare(engine, reflect=True)
b.classes.keys()
[]
The MetaData reflected correctly though:
b.metadata.tables
immutabledict({geography.usa_cbsa_centroids':
Table('usa_cbsa_centroids',
MetaData(bind=Engine(postgresql://asteroids:***@localhost:5432/asteroids)),
Column('GEOID', VARCHAR(length=5), table=u
sa_cbsa_centroids, nullable=False), ...})
Note that the tables and columns are only known at runtime.
Any thoughts?
This is duplicated
from
http://stackoverflow.com/questions/29905160/automap-reflect-tables-within-a-postgres-schema-with-sqlalchemy,
feel free to answer there as well.
Thanks,
Sam
--
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/d/optout.
Re: [sqlalchemy] Access __tablename__ in server_defaults?
Of course it worked! I think I was a bit tired that day...
On Thu, Apr 23, 2015 at 10:23 PM Mike Bayer mike...@zzzcomputing.com
wrote:
if you are using __tablename__ what happens if you just refer to
cls.__tablename__ in that method ?
On 4/23/15 3:46 PM, Jacob Magnusson wrote:
Would it somehow be possible to access the __tablename__ in
server_default? What I'm looking for is something like this:
class PositionMixin(object):
@declared_attrdef position(cls):
return Column(Integer,
server_default=text(nextval('%(__tablename__)s_id_seq')))
--
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/d/optout.
--
You received this message because you are subscribed to a topic in the
Google Groups sqlalchemy group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/sqlalchemy/PFAWqlf1Pm0/unsubscribe.
To unsubscribe from this group and all its topics, 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/d/optout.
--
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/d/optout.
Re: [sqlalchemy] when do I have to do a rollback so the app keep working fine
On 18 Apr 2015, at 02:20, dcgh...@gmail.com wrote: Hi everyone, I have a considerably amount of code that uses SQLAlchemy and I want to implement a decorator that captures the SQLAlchemy exceptions, then make session.rollback() and recall the decorated function, so I don't have to write the try except statement whenever I use SQLAlchemy. For implementing such a decorator I need the exceptions I can certainly capture to make session.rollback() and the app keep working fine because there are exceptions that will cause an endless loop and should never be captured (e.g., generating the same primary key due to a bug and always raising IntegrityError) So, can anyone tell me what are those exceptions that are safe to make session.rollback()? I guess that your intent is to retry transactions that failed because of a serialisation error in the SERIALIZABLE isolation level. My understanding is that, to SQLAlchemy, this is a database-specific issue. You can expect the SQLAlchemy exception to be an OperationalError, but you would need to add additional checks to specifically identify the kind error returned by your database driver. An argument could be made that serialisation errors should be wrapped in a more specific exception class by SQLAlchemy, but I do not believe that is the case at the moment. I am no expert, so please someone correct me if I am wrong. -- 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/d/optout.
[sqlalchemy] Re: Or_ function results in a query containing implicit cross join
I found a bug in my code that didn't create all needed left outer join clauses. Tables missing from joins were added to from clause. Fixing the bug resolved the issue. -- 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/d/optout.
Re: [sqlalchemy] Automap not reflecting tables in Postgres schemas
On 4/27/15 4:29 PM, Sam Zhang wrote:
Hello,
I'm following the documentation for reflecting database tables using
`automap`:
http://docs.sqlalchemy.org/en/latest/orm/extensions/automap.html#generating-mappings-from-an-existing-metadata.
When I don't specific a schema, and Postgres uses the default `public`
schema, this works as expected, and I find the names of my tables:
m = MetaData()
b = automap_base(bind=engine, metadata=m)
b.prepare(engine, reflect=True)
b.classes.keys()
['ads', 'spatial_ref_sys', 'income']
But when I specific an explicit schema, I don't have access to the
tables in `Base.classes` anymore.
m = MetaData(schema='geography')
b = automap_base(bind=engine, metadata=m)
b.prepare(engine, reflect=True)
b.classes.keys()
[]
The MetaData reflected correctly though:
b.metadata.tables
immutabledict({geography.usa_cbsa_centroids':
Table('usa_cbsa_centroids',
MetaData(bind=Engine(postgresql://asteroids:***@localhost:5432/asteroids)),
Column('GEOID', VARCHAR(length=5), table=u
sa_cbsa_centroids, nullable=False), ...})
Note that the tables and columns are only known at runtime.
Here's a demo that works for me. Does it work for you?Do all your
tables have primary keys defined?
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
from sqlalchemy import create_engine, MetaData
engine = create_engine(postgresql://scott:tiger@localhost/test, echo=True)
engine.execute(
create table if not exists test_schema.user (
id serial primary key, name varchar(30)
)
)
engine.execute(
create table if not exists test_schema.address (
id serial primary key,
email_address varchar(30),
user_id integer references test_schema.user(id)
)
)
m = MetaData(schema=test_schema)
Base = automap_base(bind=engine, metadata=m)
# reflect the tables
Base.prepare(engine, reflect=True)
assert Base.classes.keys() == ['user', 'address']
User = Base.classes.user
Address = Base.classes.address
session = Session(engine)
session.add(Address(email_address=f...@bar.com, user=User(name=foo)))
session.commit()
u1 = session.query(User).first()
print(u1.address_collection)
Any thoughts?
This is duplicated
from http://stackoverflow.com/questions/29905160/automap-reflect-tables-within-a-postgres-schema-with-sqlalchemy,
feel free to answer there as well.
Thanks,
Sam
--
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
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto:sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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/d/optout.
Re: [sqlalchemy] when do I have to do a rollback so the app keep working fine
On 4/27/15 7:40 AM, David Allouche wrote: On 18 Apr 2015, at 02:20, dcgh...@gmail.com wrote: Hi everyone, I have a considerably amount of code that uses SQLAlchemy and I want to implement a decorator that captures the SQLAlchemy exceptions, then make session.rollback() and recall the decorated function, so I don't have to write the try except statement whenever I use SQLAlchemy. For implementing such a decorator I need the exceptions I can certainly capture to make session.rollback() and the app keep working fine because there are exceptions that will cause an endless loop and should never be captured (e.g., generating the same primary key due to a bug and always raising IntegrityError) So, can anyone tell me what are those exceptions that are safe to make session.rollback()? I guess that your intent is to retry transactions that failed because of a serialisation error in the SERIALIZABLE isolation level. My understanding is that, to SQLAlchemy, this is a database-specific issue. You can expect the SQLAlchemy exception to be an OperationalError, but you would need to add additional checks to specifically identify the kind error returned by your database driver. An argument could be made that serialisation errors should be wrapped in a more specific exception class by SQLAlchemy, but I do not believe that is the case at the moment. I am no expert, so please someone correct me if I am wrong. that's pretty much the current situation - OperationalError refers to something went wrong with the connection and IntegrityError means something went wrong with the data the query is attempting to modify. In Openstack we have an elaborate system of catching those exceptions we care about across many backends; this is probably more than you need but this is sort of what is needed: https://github.com/openstack/oslo.db/blob/master/oslo_db/sqlalchemy/exc_filters.py -- 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/d/optout.
Re: [sqlalchemy] How can i use LIKE with an association proxy?
FWIW, another option is to pull in all the addresses and use a class method to filter. class User(): def all_emails_like(self, expression): return [e for e in self.all_emails if regex_match(expression, e)] I've found that depending on your app/db and the size of `.all_emails`, shifting the performance to python can be negligible or even faster. -- 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/d/optout.
Re: [sqlalchemy] How can i use LIKE with an association proxy?
That's the first thing I've tried. Unfortunately it doesn't work...
--- 1 User.find_all(User.all_emails.any(UserEmail.email.like('%adrian%')))
/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/ext/associationproxy.pyc
in any(self, criterion, **kwargs)
367
368
-- 369 if self._value_is_scalar:
370 value_expr = getattr(
371 self.target_class, self.value_attr).has(criterion,
**kwargs)
/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.pyc
in __get__(self, obj, cls)
723 if obj is None:
724 return self
-- 725 obj.__dict__[self.__name__] = result = self.fget(obj)
726 return result
727
/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/ext/associationproxy.pyc
in _value_is_scalar(self)
231 def _value_is_scalar(self):
232 return not self._get_property().\
-- 233 mapper.get_property(self.value_attr).uselist
234
235 @util.memoized_property
AttributeError: 'ColumnProperty' object has no attribute 'uselist'
My relationship and association proxy are defined like this:
_all_emails = db.relationship(
'UserEmail',
lazy=True,
viewonly=True,
primaryjoin='User.id == UserEmail.user_id',
collection_class=set,
backref=db.backref('user', lazy=False)
)
On Monday, April 27, 2015 at 5:28:49 PM UTC+2, Michael Bayer wrote:
the has() / any() operators can allow this:
User.all_emails.any(Email.email.like('%foo%'))
it will produce an EXISTS subquery expression, which is not as efficient
in SQL as a regular JOIN.
--
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+...@googlegroups.com javascript:.
To post to this group, send email to sqlal...@googlegroups.com
javascript:.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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/d/optout.
[sqlalchemy] before_create receives a list of tuples in the `tables` kwarg since 1.0
I just tried updating from 0.9.9 to 1.0.2 and noticed that this code is now
broken (tuple object has no attribute schema):
def _before_create(target, connection, **kw):
schemas = {table.schema for table in kw['tables']}
for schema in schemas:
CreateSchema(schema).execute_if(callable_=_should_create_schema).execute(connection)
listen(db.Model.metadata, 'before_create', _before_create)
Is this change intentional? I couldn't find anything about it in the 1.0
changelog.
--
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/d/optout.
Re: [sqlalchemy] How can i use LIKE with an association proxy?
On 4/27/15 1:52 PM, Adrian wrote:
That's the first thing I've tried. Unfortunately it doesn't work...
--- 1
User.find_all(User.all_emails.any(UserEmail.email.like('%adrian%')))
wait, what is UserEmail, that's the association. This would be the
endpoint class.
Can you share all three classes and the important parts of their
mappings please ?
/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/ext/associationproxy.pyc
in any(self, criterion, **kwargs)
367
368
-- 369 if self._value_is_scalar:
370 value_expr = getattr(
371 self.target_class,
self.value_attr).has(criterion, **kwargs)
/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.pyc
in __get__(self, obj, cls)
723 if obj is None:
724 return self
-- 725 obj.__dict__[self.__name__] = result = self.fget(obj)
726 return result
727
/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/ext/associationproxy.pyc
in _value_is_scalar(self)
231 def _value_is_scalar(self):
232 return not self._get_property().\
-- 233 mapper.get_property(self.value_attr).uselist
234
235 @util.memoized_property
AttributeError: 'ColumnProperty' object has no attribute 'uselist'
My relationship and association proxy are defined like this:
_all_emails = db.relationship(
'UserEmail',
lazy=True,
viewonly=True,
primaryjoin='User.id == UserEmail.user_id',
collection_class=set,
backref=db.backref('user', lazy=False)
)
On Monday, April 27, 2015 at 5:28:49 PM UTC+2, Michael Bayer wrote:
the has() / any() operators can allow this:
User.all_emails.any(Email.email.like('%foo%'))
it will produce an EXISTS subquery expression, which is not as
efficient in SQL as a regular JOIN.
--
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+...@googlegroups.com javascript:.
To post to this group, send email to sqlal...@googlegroups.com
javascript:.
Visit this group at http://groups.google.com/group/sqlalchemy
http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout
https://groups.google.com/d/optout.
--
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
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto:sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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/d/optout.
Re: [sqlalchemy] before_create receives a list of tuples in the `tables` kwarg since 1.0
On 4/27/15 2:43 PM, Mike Bayer wrote:
On 4/27/15 2:09 PM, Adrian wrote:
I just tried updating from 0.9.9 to 1.0.2 and noticed that this code
is now broken (tuple object has no attribute schema):
def _before_create(target, connection, **kw):
schemas = {table.schema for table in kw['tables']}
for schema in schemas:
CreateSchema(schema).execute_if(callable_=_should_create_schema).execute(connection)
listen(db.Model.metadata, 'before_create', _before_create)
Is this change intentional? I couldn't find anything about it in the
1.0 changelog.
OK, I think I should change this back because that was not
intentional, so it's a regression, and I've added
https://bitbucket.org/zzzeek/sqlalchemy/issue/3391/tables-collection-passed-to-before-after.
Though I looked at the docstring and it does give me some room to just
make changes:
additional keyword arguments relevant to the event. The contents of
this dictionary may vary across releases, and include the list of
tables being generated for a metadata-level event, the checkfirst
flag, and other elements used by internal events.
But the tables list here is actually there just for the purpose of
end-user event listening so that should probably be maintained as it was.
that is now fixed in
https://bitbucket.org/zzzeek/sqlalchemy/commits/e25ef01fbb70
--
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
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto:sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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/d/optout.
