[sqlalchemy] Setting language for a db session
Hi all, I am just starting to think about this topic so please dont shoot me if I missed the obvious :). There are many varieties of database errors. So, sometimes in our code, we need to let these errors be returned to the user. E.g. we would like a french guy using this application to have a french translation of these error messages. Ideally, a single application should be able to return errors in multiple languages depending on which language the database session was setup with. From my understanding, some databases like MySQL does not support setting language for a particular DB session. Some others, such as Oracle, allows this. Is this ever considered in SA? or is our only option to write SQL code to explicitly pass the language parameter to a session? Do you guys have a better answer? 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] Self Join
I have 2 tables 'Product' and 'ProductPrice'. I want to get all valid products, a product is valid if it has both a valid 'Selling' and 'Buying' ProductPrice type. A ProductPrice is valid if the valid_from date = date.today() and valid_to = date.today(). Product Table: id | sku | principal +---+--- 1 | sku_1 | kraft 2 | sku_2 | kraft 3 | sku_3 | kraft 4 | sku_4 | kraft ProdutPrice Table: id | type| sku| principal | price | valid_from | valid_to +---+-+-++-+ 1 | Buying | sku_1 | kraft | 122 | 2009-05-05 | 2009-12-12 2 | Buying | sku_1 | kraft | 231 | 2010-01-01 | 2010-02-02 3 | Selling | sku_1 | kraft | 125 | 2009-05-05 | 2009-12-12 4 | Buying | sku_2 | kraft | 122 | 2009-05-05 | 2009-12-12 5 | Buying | sku_2 | kraft | 231 | 2010-01-01 | 2010-02-02 6 | Selling | sku_2 | kraft | 125 | 2009-05-05 | 2009-12-12 7 | Buying | sku_3 | kraft | 122 | 2009-05-05 | 2009-12-12 8 | Selling | sku_4 | kraft | 122 | 2009-05-05 | 2009-12-12 Using Raw SQL here is how I did it: 1. SELECT product.id, type, product.sku, product.principal, price, valid_from, valid_to INTO TEMP FROM product INNER JOIN product_price on product.principal = product_price.principal AND product.sku = product_price.sku WHERE valid_from = current_date AND valid_to = current_date ; 2. SELECT DISTINCT * from TEMP a , TEMP b WHERE a.type='Selling' AND b.type='Buying' AND a.principal = b.principal AND a.sku = b.sku; From this two queries I now have distinct products that have a valid pair of both 'Buying' and 'Selling' Price. (Those products with a valid 'Buying' or 'Selling price only and not have them both are dropped) Using SQLAlchemy here is how I did it: 1. valid_price = and_(ProductPrice.sku == Product.sku, ProductPrice.principal==Product.principal, ProductPrice.valid_from = date.today(), ProductPrice.valid_to = date.today()) valid_products = session.query(Product).join(ProductPrice).filter(valid_price) 2. Now I want to self join valid_products, same thing I did in my Raw SQL solution no. 2 I've been trying but getting weird results. If someone can please help me, here's is my test case http://pastebin.com/m3f8a95c8 rgds, Paulo --~--~-~--~~~---~--~~ 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] 0.55, orm, varying relation join on criteria
For certain orm queries with a 1-to-many relation i want to left outer join and then update the on-clause for that relation. Since the criteria changes between queries I cannot fix the join criteria when specifying my object/table mappings. For example: tables: user, email relation: user.emails (1-many) select * from user left outer join email on email.user_id = user.id and email.name like '%hello%' While this is easy to write as a one off query in my case I need to be able to add variable filtering to the join on-clause and in a way that hopefully works for more complex relations. So e.g. if I have a query built like this: query(user).outerjoin((email, emails)) Is there a general way to add to the primary/secondary join criteria that is pulled from this emails relation? Or is there a better way to express this in SA that I've missed? Thanks, M --~--~-~--~~~---~--~~ 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] many-to-one question on delete
I have a simple foreign key relationship between a Task class and a Context class, where many Tasks can have the same Context. The default value for the Task foreign key context_id is 0. When I delete a Context, the Tasks with that context have their context_id automatically set to None and I would like it to be set to 0. (I believe this is the default cascade behavior as I do not have any cascade set on the mapper.) Right now I then explicitly change each affected Tasks context_id to 0. Do I have to do this explicitly, or is there a way on delete of a Context to have the task updated to a context_id = 0. Thanks for any advice. Steve --~--~-~--~~~---~--~~ 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: ORM and EXISTS?
On 11 Sep., 22:12, Conor conor.edward.da...@gmail.com wrote: This query will get you close to your desired SQL: q = session.query(ATable) q = q.filter( sa.exists( [1], ((BTable.atable_id == ATable.id) (CTable.cval.in_([foo, bar]))), from_obj=orm.join(BTable, CTable))) Great! Works like a charm! Thank you! seppo --~--~-~--~~~---~--~~ 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: How to instantiate objects of a class mapped against multiple tables?
The root of the problem is inheritance. Let's say that I have a Person
class and an Employee class that inherits from it. I also have a
Meeting class that records meetings between two persons.
A query on Meeting will always lazy load Employee's attributes,
regardless of any lazy/eagerload settings. E.g. if I want to print the
list of names of all persons somebody had meetings with and also their
position if they are employees (null if they're not), it will always
be done lazily. This is bad when I have, let's say, 100.000 Meetings.
I guess I can build a custom join and work from that, but if I have
two levels of inheritance on one side and three levels on the other
side, I will have to write a six-way join, and this, I'm sure you'll
agree, sort of defeats the purpose of an object-relational mapper.
Using classes mapped against multiple tables would elegantly solve
this problem, if I could only instantiate them (see my original post).
Here's the code that shows attributes of inherited objects are loaded
lazily:
code
from sqlalchemy import create_engine, Table, Column, Integer, String,
MetaData, ForeignKey
from sqlalchemy.orm import mapper, relation, sessionmaker, eagerload
from sqlalchemy.orm.mapper import validates
DB_URI='postgres://postg...@localhost/postgres' #Replace this
accordingly
db_engine=create_engine(DB_URI, echo=False)
metadata = MetaData()
class Person(object):
def __init__(self, name):
self.name = name
persons = Table('persons',
metadata,
Column('id', Integer, primary_key=True),
Column('type', String(1), nullable=False),
Column('name', String(100), nullable=False))
class Employee(Person):
def __init__(self, name, position):
Person.__init__(self, name)
self.position = position
employees = Table('employees',
metadata,
Column('id', Integer, ForeignKey('persons.id'),
primary_key=True),
Column('position', String(50), nullable=False))
class Meeting(object):
def __init__(self, date, person_from, person_to):
self.date = date
self.person_from = person_from
self.person_to = person_to
meetings = Table('meetings',
metadata,
Column('id', Integer, primary_key=True),
Column('date', String(8), nullable=False),
Column('person_from_id', Integer, ForeignKey
('persons.id'), nullable=False),
Column('person_to_id', Integer, ForeignKey
('persons.id'), nullable=False))
mapper(Person, persons, polymorphic_on=persons.c.type,
polymorphic_identity='P')
mapper(Employee, employees, inherits=Person, polymorphic_identity='E')
mapper(Meeting, meetings, properties={
'person_from': relation(Person, primaryjoin=
(meetings.c.person_from_id==persons.c.id)),
'person_to': relation(Person, primaryjoin=
(meetings.c.person_to_id==persons.c.id)),
})
if __name__ == '__main__':
metadata.create_all(db_engine)
s=sessionmaker(bind=db_engine)()
try:
john = Person('John')
peter = Employee('Peter', 'clerk')
jack = Employee('Jack', 'manager')
m1 = Meeting('20090914', peter, john)
m2 = Meeting('20090915', peter, jack)
s.add_all([john, peter, jack, m1, m2])
s.commit()
db_engine.echo = True
#We now want to print the names and positions of everyone
Peter has ever met with
peters_meetings = s.query(Meeting).options(eagerload
('person_to')).filter_by(person_from=peter).all()
for meeting in peters_meetings:
if meeting.person_to.type == 'P':
print meeting.date, meeting.person_to.name, None
else:
#Each print statement here will emit an SQL SELECT on
the employees table
print meeting.date, meeting.person_to.name,
meeting.person_to.position
finally:
db_engine.echo = False
s.close()
metadata.drop_all(db_engine)
/code
On Sep 11, 7:52 pm, Michael Bayer mike...@zzzcomputing.com wrote:
have you tried using query + join() + contains_eager() ? any query you
like can be used to build the object graph of your choice along
relations().
bojanbwrote:
Here's something I've been struggling with recently. I'll include the
description of steps that got me here, as I believe the context will
make the question clearer.
It all started because I needed to show data (eg. in a list form) from
two related tables (classes). However, SQLAlchemy would emit one SQL
query for getting the objects of the first class, then one query each
for each access to attributes of the other class. It obviously loads
the attributes lazily, which is fine most of the time but grossly
inefficient in this case (as there can be thousands of records in the
first table).
Aha, I'll use eagerload! I thought. Alas, it doesn't seem to work
for inherited classes. A message
[sqlalchemy] api.upgrade autocommit?
Hi, I plan to use the migrate.versioning.api to manage the versions of the tables. But there are different repositories belonging to the modules of my project. Each module has its own repository but will refer to the tables of other modules. Is it possible? I have 2 different repositories: say X and Y The users table is created in repository X. And the customers table in repository Y refers to the users table. When api.upgrade is called for X, the users table is created. But then, when api.upgrade is called soon after for Y, it gives an error saying no users table. 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] getting data from primary keys
Hello SQLAlchemy Group, I've been trying to work out a way I can get the data from a table whose columns are primary keys. The challenge is, I don't know before hand which columns are primary keys since the code is to be run on different tables. I've figured out how to get the primary keys from a table object: cols = table.columns primary_col_names = [] for col in cols: if col.primary_key is True: primary_col_names.append(col.column_name) But how do I go about constructing an sql statement with the primary column names in the list? Or is there an easier way to do this? I've found away to do this using where(), feeding it the column_name, but if there were three names in the primary_col_names list I'd have to append a where for each name, and I'm not entirely sure how that would work. Is there a way to give SQLAlchemy a string sql statement? Cheers, T --~--~-~--~~~---~--~~ 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: How to instantiate objects of a class mapped against multiple tables?
bojanb wrote:
Actually you can't use with_polymorphic() in the query because Meeting
is not an inherited object (one would get an InvalidRequestError if
one tried). But plugging:
with_polymorphic='*'
in the mapper for Person makes the eagerload work in the code above.
However, we're off on a tangent. I still don't know how to instantiate
objects of a class mapped against two tables when they contain both an
autogenerated primary key from the first table and a mandatory foreign
key from the second...
Just to clarify, the mapper on Employee with the with_polymorphic='*'
*is* a mapper that is mapped against two tables, in pretty much the same
way as a map against a plain join is represented. So I'm assuming this is
unsuitable only because it's your observation that the joined tables in
your particular system are more of an implementation detail and you
don't really need to represent inheritance.
So, as far as synchronizing the foreign key with the primary key of the
two tables in a mapper that is mapped to a plain join, you just map two
columns to one attribute. This is also in the docs, at
http://www.sqlalchemy.org/docs/05/mappers.html#mapping-a-class-against-multiple-tables
. The tables are populated in order of foreign key dependency, and after
each primary key generation the value is synchronized to the mapped
attribute, where its then available for the insert into the second table.
Since I didn't read your initial (very long) email carefully enough, here
is your sample program using that style.
from sqlalchemy import create_engine, Table, Column, Integer,
String,MetaData, ForeignKey
from sqlalchemy.orm import mapper, relation, sessionmaker, eagerload
from sqlalchemy.orm.mapper import validates
db_engine=create_engine('sqlite://', echo=True)
metadata = MetaData()
persons = Table('persons',
metadata,
Column('id', Integer, primary_key=True),
Column('name', String(100), nullable=False))
class Employee(object):
def __init__(self, name, position):
self.name = name
self.position = position
employees = Table('employees',
metadata,
Column('id', Integer,
ForeignKey('persons.id'),primary_key=True),
Column('position', String(50), nullable=False))
class Meeting(object):
def __init__(self, date, person_from, person_to):
self.date = date
self.person_from = person_from
self.person_to = person_to
meetings = Table('meetings',
metadata,
Column('id', Integer, primary_key=True),
Column('date', String(8), nullable=False),
Column('person_from_id', Integer,
ForeignKey('persons.id'), nullable=False),
Column('person_to_id', Integer, ForeignKey('persons.id'),
nullable=False))
mapper(Employee, employees.join(persons), properties={
'id':[persons.c.id, employees.c.id]
})
mapper(Meeting, meetings, properties={
'person_from': relation(Employee,
primaryjoin=(meetings.c.person_from_id==persons.c.id)),
'person_to': relation(Employee,
primaryjoin=(meetings.c.person_to_id==persons.c.id)),
})
if __name__ == '__main__':
metadata.create_all(db_engine)
s=sessionmaker(bind=db_engine)()
john = Employee('John', 'person')
peter = Employee('Peter', 'clerk')
jack = Employee('Jack', 'manager')
m1 = Meeting('20090914', peter, john)
m2 = Meeting('20090915', peter, jack)
s.add_all([john, peter, jack, m1, m2])
s.commit()
#We now want to print the names and positions of everyonePeter has
ever met with
peters_meetings =
s.query(Meeting).options(eagerload('person_to')).filter_by(person_from=peter).all()
for meeting in peters_meetings:
print meeting.date, meeting.person_to.name,meeting.person_to.position
On Sep 14, 4:31 pm, Michael Bayer mike...@zzzcomputing.com wrote:
bojanb wrote:
The root of the problem is inheritance. Let's say that I have a Person
class and an Employee class that inherits from it. I also have a
Meeting class that records meetings between two persons.
A query on Meeting will always lazy load Employee's attributes,
regardless of any lazy/eagerload settings. E.g. if I want to print the
list of names of all persons somebody had meetings with and also their
position if they are employees (null if they're not), it will always
be done lazily. This is bad when I have, let's say, 100.000 Meetings.
I guess I can build a custom join and work from that, but if I have
two levels of inheritance on one side and three levels on the other
side, I will have to write a six-way join, and this, I'm sure you'll
agree, sort of defeats the purpose of an object-relational mapper.
Using classes mapped against multiple tables would elegantly solve
this problem, if I could only instantiate them (see my original post).
Here's the code that shows attributes of inherited
[sqlalchemy] Re: getting data from primary keys
If I understand this, you want to construct a query that returns the primary keys in an arbitrary table? Try this: key_cols = [c for c in table.primary_key.columns] session.query(*key_cols).all() --~--~-~--~~~---~--~~ 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: How to instantiate objects of a class mapped against multiple tables?
Actually you can't use with_polymorphic() in the query because Meeting
is not an inherited object (one would get an InvalidRequestError if
one tried). But plugging:
with_polymorphic='*'
in the mapper for Person makes the eagerload work in the code above.
However, we're off on a tangent. I still don't know how to instantiate
objects of a class mapped against two tables when they contain both an
autogenerated primary key from the first table and a mandatory foreign
key from the second...
On Sep 14, 4:31 pm, Michael Bayer mike...@zzzcomputing.com wrote:
bojanb wrote:
The root of the problem is inheritance. Let's say that I have a Person
class and an Employee class that inherits from it. I also have a
Meeting class that records meetings between two persons.
A query on Meeting will always lazy load Employee's attributes,
regardless of any lazy/eagerload settings. E.g. if I want to print the
list of names of all persons somebody had meetings with and also their
position if they are employees (null if they're not), it will always
be done lazily. This is bad when I have, let's say, 100.000 Meetings.
I guess I can build a custom join and work from that, but if I have
two levels of inheritance on one side and three levels on the other
side, I will have to write a six-way join, and this, I'm sure you'll
agree, sort of defeats the purpose of an object-relational mapper.
Using classes mapped against multiple tables would elegantly solve
this problem, if I could only instantiate them (see my original post).
Here's the code that shows attributes of inherited objects are loaded
lazily:
oh. you want with_polymorphic() for this.
http://www.sqlalchemy.org/docs/05/mappers.html#controlling-which-tabl...
code
from sqlalchemy import create_engine, Table, Column, Integer, String,
MetaData, ForeignKey
from sqlalchemy.orm import mapper, relation, sessionmaker, eagerload
from sqlalchemy.orm.mapper import validates
DB_URI='postgres://postg...@localhost/postgres' #Replace this
accordingly
db_engine=create_engine(DB_URI, echo=False)
metadata = MetaData()
class Person(object):
def __init__(self, name):
self.name = name
persons = Table('persons',
metadata,
Column('id', Integer, primary_key=True),
Column('type', String(1), nullable=False),
Column('name', String(100), nullable=False))
class Employee(Person):
def __init__(self, name, position):
Person.__init__(self, name)
self.position = position
employees = Table('employees',
metadata,
Column('id', Integer, ForeignKey('persons.id'),
primary_key=True),
Column('position', String(50), nullable=False))
class Meeting(object):
def __init__(self, date, person_from, person_to):
self.date = date
self.person_from = person_from
self.person_to = person_to
meetings = Table('meetings',
metadata,
Column('id', Integer, primary_key=True),
Column('date', String(8), nullable=False),
Column('person_from_id', Integer, ForeignKey
('persons.id'), nullable=False),
Column('person_to_id', Integer, ForeignKey
('persons.id'), nullable=False))
mapper(Person, persons, polymorphic_on=persons.c.type,
polymorphic_identity='P')
mapper(Employee, employees, inherits=Person, polymorphic_identity='E')
mapper(Meeting, meetings, properties={
'person_from': relation(Person, primaryjoin=
(meetings.c.person_from_id==persons.c.id)),
'person_to': relation(Person, primaryjoin=
(meetings.c.person_to_id==persons.c.id)),
})
if __name__ == '__main__':
metadata.create_all(db_engine)
s=sessionmaker(bind=db_engine)()
try:
john = Person('John')
peter = Employee('Peter', 'clerk')
jack = Employee('Jack', 'manager')
m1 = Meeting('20090914', peter, john)
m2 = Meeting('20090915', peter, jack)
s.add_all([john, peter, jack, m1, m2])
s.commit()
db_engine.echo = True
#We now want to print the names and positions of everyone
Peter has ever met with
peters_meetings = s.query(Meeting).options(eagerload
('person_to')).filter_by(person_from=peter).all()
for meeting in peters_meetings:
if meeting.person_to.type == 'P':
print meeting.date, meeting.person_to.name, None
else:
#Each print statement here will emit an SQL SELECT on
the employees table
print meeting.date, meeting.person_to.name,
meeting.person_to.position
finally:
db_engine.echo = False
s.close()
metadata.drop_all(db_engine)
/code
On Sep 11, 7:52 pm, Michael Bayer mike...@zzzcomputing.com wrote
[sqlalchemy] Re: How to instantiate objects of a class mapped against multiple tables?
bojanb wrote:
The root of the problem is inheritance. Let's say that I have a Person
class and an Employee class that inherits from it. I also have a
Meeting class that records meetings between two persons.
A query on Meeting will always lazy load Employee's attributes,
regardless of any lazy/eagerload settings. E.g. if I want to print the
list of names of all persons somebody had meetings with and also their
position if they are employees (null if they're not), it will always
be done lazily. This is bad when I have, let's say, 100.000 Meetings.
I guess I can build a custom join and work from that, but if I have
two levels of inheritance on one side and three levels on the other
side, I will have to write a six-way join, and this, I'm sure you'll
agree, sort of defeats the purpose of an object-relational mapper.
Using classes mapped against multiple tables would elegantly solve
this problem, if I could only instantiate them (see my original post).
Here's the code that shows attributes of inherited objects are loaded
lazily:
oh. you want with_polymorphic() for this.
http://www.sqlalchemy.org/docs/05/mappers.html#controlling-which-tables-are-queried
code
from sqlalchemy import create_engine, Table, Column, Integer, String,
MetaData, ForeignKey
from sqlalchemy.orm import mapper, relation, sessionmaker, eagerload
from sqlalchemy.orm.mapper import validates
DB_URI='postgres://postg...@localhost/postgres' #Replace this
accordingly
db_engine=create_engine(DB_URI, echo=False)
metadata = MetaData()
class Person(object):
def __init__(self, name):
self.name = name
persons = Table('persons',
metadata,
Column('id', Integer, primary_key=True),
Column('type', String(1), nullable=False),
Column('name', String(100), nullable=False))
class Employee(Person):
def __init__(self, name, position):
Person.__init__(self, name)
self.position = position
employees = Table('employees',
metadata,
Column('id', Integer, ForeignKey('persons.id'),
primary_key=True),
Column('position', String(50), nullable=False))
class Meeting(object):
def __init__(self, date, person_from, person_to):
self.date = date
self.person_from = person_from
self.person_to = person_to
meetings = Table('meetings',
metadata,
Column('id', Integer, primary_key=True),
Column('date', String(8), nullable=False),
Column('person_from_id', Integer, ForeignKey
('persons.id'), nullable=False),
Column('person_to_id', Integer, ForeignKey
('persons.id'), nullable=False))
mapper(Person, persons, polymorphic_on=persons.c.type,
polymorphic_identity='P')
mapper(Employee, employees, inherits=Person, polymorphic_identity='E')
mapper(Meeting, meetings, properties={
'person_from': relation(Person, primaryjoin=
(meetings.c.person_from_id==persons.c.id)),
'person_to': relation(Person, primaryjoin=
(meetings.c.person_to_id==persons.c.id)),
})
if __name__ == '__main__':
metadata.create_all(db_engine)
s=sessionmaker(bind=db_engine)()
try:
john = Person('John')
peter = Employee('Peter', 'clerk')
jack = Employee('Jack', 'manager')
m1 = Meeting('20090914', peter, john)
m2 = Meeting('20090915', peter, jack)
s.add_all([john, peter, jack, m1, m2])
s.commit()
db_engine.echo = True
#We now want to print the names and positions of everyone
Peter has ever met with
peters_meetings = s.query(Meeting).options(eagerload
('person_to')).filter_by(person_from=peter).all()
for meeting in peters_meetings:
if meeting.person_to.type == 'P':
print meeting.date, meeting.person_to.name, None
else:
#Each print statement here will emit an SQL SELECT on
the employees table
print meeting.date, meeting.person_to.name,
meeting.person_to.position
finally:
db_engine.echo = False
s.close()
metadata.drop_all(db_engine)
/code
On Sep 11, 7:52 pm, Michael Bayer mike...@zzzcomputing.com wrote:
have you tried using query + join() + contains_eager() ? any query you
like can be used to build the object graph of your choice along
relations().
bojanbwrote:
Here's something I've been struggling with recently. I'll include the
description of steps that got me here, as I believe the context will
make the question clearer.
It all started because I needed to show data (eg. in a list form) from
two related tables (classes). However, SQLAlchemy would emit one SQL
query for getting the objects of the first class, then one query each
for each access to attributes of the other class. It obviously loads
the attributes
[sqlalchemy] Self Join
I have 2 tables 'Product' and 'ProductPrice'. I want to get all valid products, a product is valid if it has both a valid 'Selling' and 'Buying' ProductPrice type. A ProductPrice is valid if the valid_from date = date.today() and valid_to = date.today(). Product Table: id | sku | principal +---+--- 1 | sku_1 | kraft 2 | sku_2 | kraft 3 | sku_3 | kraft 4 | sku_4 | kraft ProdutPrice Table: id | type| sku| principal | price | valid_from | valid_to +---+-+-++-+ 1 | Buying | sku_1 | kraft | 122 | 2009-05-05 | 2009-12-12 2 | Buying | sku_1 | kraft | 231 | 2010-01-01 | 2010-02-02 3 | Selling | sku_1 | kraft | 125 | 2009-05-05 | 2009-12-12 4 | Buying | sku_2 | kraft | 122 | 2009-05-05 | 2009-12-12 5 | Buying | sku_2 | kraft | 231 | 2010-01-01 | 2010-02-02 6 | Selling | sku_2 | kraft | 125 | 2009-05-05 | 2009-12-12 7 | Buying | sku_3 | kraft | 122 | 2009-05-05 | 2009-12-12 8 | Selling | sku_4 | kraft | 122 | 2009-05-05 | 2009-12-12 Using Raw SQL here is how I did it: 1. SELECT product.id, type, product.sku, product.principal, price, valid_from, valid_to INTO TEMP FROM product INNER JOIN product_price on product.principal = product_price.principal AND product.sku = product_price.sku WHERE valid_from = current_date AND valid_to = current_date ; 2. SELECT DISTINCT * from TEMP a , TEMP b WHERE a.type='Selling' AND b.type='Buying' AND a.principal = b.principal AND a.sku = b.sku; From this two queries I now have distinct products that have a valid pair of both 'Buying' and 'Selling' Price. (Those products with a valid 'Buying' or 'Selling price only and not have them both are dropped) Using SQLAlchemy here is how I did it: 1. valid_price = and_(ProductPrice.sku == Product.sku, ProductPrice.principal==Product.principal, ProductPrice.valid_from = date.today(), ProductPrice.valid_to = date.today()) valid_products = session.query(Product).join(ProductPrice).filter(valid_price) 2. Now I want to self join valid_products, same thing I did in my Raw SQL solution no. 2 I've been trying but getting weird results. If someone can please help me, here's my test case http://pastebin.com/m3f8a95c8 rgds, Paulo --~--~-~--~~~---~--~~ 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 select to Read-only reporting class
I want to abstract some ugly reporting SQL strings into a read-only object model. I have created an empty class, and then I map it to a select object that pulls some statistical information from the DB. The mapper is complaining that it can't assemble a primary key. I am only using this object as a simplified way of querying the database, and will never want to persist the object. The object is read-only. Is there a way to tell sqlalchemy not to worry about persisting this class? --~--~-~--~~~---~--~~ 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: many-to-one question on delete
Steve Zatz wrote: I have a simple foreign key relationship between a Task class and a Context class, where many Tasks can have the same Context. The default value for the Task foreign key context_id is 0. When I delete a Context, the Tasks with that context have their context_id automatically set to None and I would like it to be set to 0. (I believe this is the default cascade behavior as I do not have any cascade set on the mapper.) Right now I then explicitly change each affected Tasks context_id to 0. Do I have to do this explicitly, or is there a way on delete of a Context to have the task updated to a context_id = 0. Thanks for any advice. if you're using foreign keys correctly, that would imply there's an entity with an id of 0, and you'd attach that Context to each Task, replacing the old Context to be deleted. Otherwise if 0 isn't a real ID, and I guess you're using a non-consistent DB like sqlite or MyISAM (and you have some great reason to be doing this in the first place), the rough equivalent would be to detach the to-be-deleted context and then assign 0 to the context_id manually. So yes SQLA's NULL setting behavior is a hardwired thing since we target a certain model of persistence with regards to foreign keys - for other arrangements you'd have to do that yourself. It is easy enough to do within a function that iterates through the list. You can automate the calling of this function by building a SessionExtension, implementing before_flush(), and searching through the session.deleted collection for Context objects to be processed. --~--~-~--~~~---~--~~ 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: 0.55, orm, varying relation join on criteria
me wrote: For certain orm queries with a 1-to-many relation i want to left outer join and then update the on-clause for that relation. Since the criteria changes between queries I cannot fix the join criteria when specifying my object/table mappings. For example: tables: user, email relation: user.emails (1-many) select * from user left outer join email on email.user_id = user.id and email.name like '%hello%' While this is easy to write as a one off query in my case I need to be able to add variable filtering to the join on-clause and in a way that hopefully works for more complex relations. So e.g. if I have a query built like this: query(user).outerjoin((email, emails)) Is there a general way to add to the primary/secondary join criteria that is pulled from this emails relation? Or is there a better way to express this in SA that I've missed? the contract of query.outerjoin(SomeClass.property) is that you're doing a plain join from A to B along pre-established routes. If you'd like the criterion of the ON clause to be customized, the standard route is to spell out the entire thing you want completely. The only potential time saver here would be if you held onto the primaryjoin aspect of the relation and used it in an AND clause, which at first looks like: query(User).outerjoin((Email, and_(email_primary_join, other criterion))) the next level would be that you'd pull email_primary_join from the mapping. You can get at this via User.emails.property.primaryjoin. at the moment that's as automated as it gets as far as what's built in. --~--~-~--~~~---~--~~ 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: Setting language for a db session
gizli wrote: From my understanding, some databases like MySQL does not support setting language for a particular DB session. Some others, such as Oracle, allows this. Is this ever considered in SA? If you are looking for an application wide setting, there is an open-ended API bywhich you can set up any per-connection activities desired within the connection system - it is the PoolListener API which is described in the API documentation. For Oracle, you'd get access to each new connection as its opened and you could establish whatever language settings it allows. If OTOH you are looking for something local to a web request or ORM session, the same API also provides on connect events which you could in theory configure based on a thread-local association with the context you're working in, or you could ensure that a single Connection is acquired for each context and configure it as needed at that level. But this advice all applies to the general question of setting attributes on connections. With regards to locale support for error messages, reimplementing an equivalent feature to Oracle's locale support in pure python to simulate the same feature on other databases which don't offer such a feature is not feasible since every database offers hundreds of error messages which change with each version, and are even reported differently depending on the DBAPI in use to some degree. It really becomes an extension of the general issue of normalizing error messages, something which gets requested occasionally but which again would be an enormously complex and always buggy feature, requiring constant maintenance and releases on the kind of scale and level that only a commercial entity with full time employees could feasibly handle. or is our only option to write SQL code to explicitly pass the language parameter to a session? Do you guys have a better answer? that would solve your problem for Oracle but not a database like MySQL which doesn't offer such a feature. The bigger issue is that IMO its poor form in most cases to expose database errors to end users, unless your application is intended to expose the database as fully as possible in a server admin application such as phpMyAdmin. Other than that case, you usually would express communication with end user via a datamodel which has specific behaviors and constraints, and a controller/business/service layer which allowed only specific actions (and specific error conditions) to occur. Messaging is then handled using a locale-aware view system which allows you to configure translations for all possible messages in as many languages as you need to support. --~--~-~--~~~---~--~~ 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: Self Join
Paulo Aquino wrote: I have 2 tables 'Product' and 'ProductPrice'. I want to get all valid products, a product is valid if it has both a valid 'Selling' and 'Buying' ProductPrice type. A ProductPrice is valid if the valid_from date = date.today() and valid_to = date.today(). Product Table: id | sku | principal +---+--- 1 | sku_1 | kraft 2 | sku_2 | kraft 3 | sku_3 | kraft 4 | sku_4 | kraft ProdutPrice Table: id | type| sku| principal | price | valid_from | valid_to +---+-+-++-+ 1 | Buying | sku_1 | kraft | 122 | 2009-05-05 | 2009-12-12 2 | Buying | sku_1 | kraft | 231 | 2010-01-01 | 2010-02-02 3 | Selling | sku_1 | kraft | 125 | 2009-05-05 | 2009-12-12 4 | Buying | sku_2 | kraft | 122 | 2009-05-05 | 2009-12-12 5 | Buying | sku_2 | kraft | 231 | 2010-01-01 | 2010-02-02 6 | Selling | sku_2 | kraft | 125 | 2009-05-05 | 2009-12-12 7 | Buying | sku_3 | kraft | 122 | 2009-05-05 | 2009-12-12 8 | Selling | sku_4 | kraft | 122 | 2009-05-05 | 2009-12-12 Using Raw SQL here is how I did it: 1. SELECT product.id, type, product.sku, product.principal, price, valid_from, valid_to INTO TEMP FROM product INNER JOIN product_price on product.principal = product_price.principal AND product.sku = product_price.sku WHERE valid_from = current_date AND valid_to = current_date ; 2. SELECT DISTINCT * from TEMP a , TEMP b WHERE a.type='Selling' AND b.type='Buying' AND a.principal = b.principal AND a.sku = b.sku; From this two queries I now have distinct products that have a valid pair of both 'Buying' and 'Selling' Price. (Those products with a valid 'Buying' or 'Selling price only and not have them both are dropped) Using SQLAlchemy here is how I did it: 1. valid_price = and_(ProductPrice.sku == Product.sku, ProductPrice.principal==Product.principal, ProductPrice.valid_from = date.today(), ProductPrice.valid_to = date.today()) valid_products = session.query(Product).join(ProductPrice).filter(valid_price) 2. Now I want to self join valid_products, same thing I did in my Raw SQL solution no. 2 I've been trying but getting weird results. If someone can please help me, here's my test case http://pastebin.com/m3f8a95c8 you can say: valid_products = session.query(Product, ProductPrice.type).join(ProductPrice).filter(valid_price) a = valid_products.subquery() b = valid_products.subquery() PA = aliased(Product, a) PB = aliased(Product, b) q = session.query(PA, PB).\ distinct().\ filter(a.c.type=='Selling').\ filter(b.c.type=='Buying').\ filter(a.c.principal==b.c.principal).\ filter(a.c.sku==b.c.sku) print q.all() if you just want the columns back you can do away with PA and PB and just query(a, b). --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: api.upgrade autocommit?
Suha Onay wrote: Hi, I plan to use the migrate.versioning.api to manage the versions of the tables. But there are different repositories belonging to the modules of my project. Each module has its own repository but will refer to the tables of other modules. Is it possible? I have 2 different repositories: say X and Y The users table is created in repository X. And the customers table in repository Y refers to the users table. When api.upgrade is called for X, the users table is created. But then, when api.upgrade is called soon after for Y, it gives an error saying no users table. this is more a question for the migrate list. It sounds like you may consider your two groups of tables as two different databases - if Migrate offers options to set the name of the migrate table where the version number is stored, or some other way to store multiple version numbers in one database, that would be the way to go - each respository would track the version in a different row and/or table. You'd need to ask on their list if this is possible. --~--~-~--~~~---~--~~ 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: Mapping select to Read-only reporting class
Bryan wrote: I want to abstract some ugly reporting SQL strings into a read-only object model. I have created an empty class, and then I map it to a select object that pulls some statistical information from the DB. The mapper is complaining that it can't assemble a primary key. I am only using this object as a simplified way of querying the database, and will never want to persist the object. The object is read-only. Is there a way to tell sqlalchemy not to worry about persisting this class? the primary key is for more than just persistence. Pick whatever columns on your select object you think are suitable, then configure them on the mapper using the primary_key option: mapper(MyClass, myselect, primary_key=[myselect.c.foo, myselect.c.bar]) --~--~-~--~~~---~--~~ 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: geoalchemy and reflected tables
Michael, Michael Bayer mike...@zzzcomputing.com schrieb: On Sep 13, 2009, at 7:01 AM, Christoph Böhme wrote: Hi all, I wonder if it is possible to reflect tables in a Postgres/PostGIS database with geoalchemy 0.1. The geoalchemy documentation only mentions delarative model definitions and my attempt to simply import * from geoalchemy and then reflect on the tables resulted in a warning when sqlalchemy came across the geometry colunm: /usr/lib/python2.6/site-packages/sqlalchemy/engine/base.py:1265: SAWarning: Did not recognize type 'geometry' of column 'coords' I had a look at the geoalchemy sources to see if I have to register geoalchemy with sqlalchemy to make it aware of the new column types but I could not find anything. currently, the only official hook we have to allow additional types into a reflected table is to do the regular reflection, specifying additional columns manually which you know to have particular types. this of course defeats the purpose of reflection to some degree. thanks for the clarification. Since I need to set the comparator factory for my geometry columns any way I will stick to the official way and define the columns manually. That works fine now. Cheers, Christoph The unofficial way to do it is to stick the descriptor and type into the dialect's ischema_names dictionary.in 0.5 this dictionary is at : from sqlalchemy.databases import postgres postgres.ischema_names['coords'] = Geometry and 0.6: from sqlalchemy.dialects.postgresql import base as pg pg.ischema_names['coords'] = Geometry if the Geometry type has additional arguments, those don't get passed along in this method. There was at some point some proposals to allow the ischema_names dictionary to have an official route to customization but it hasn't moved along. the complexity there is once we make it official, now whatever that system does/does not do is carved in stone for awhile, so we wanted to consider it carefully. --~--~-~--~~~---~--~~ 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: many-to-one question on delete
if you're using foreign keys correctly, that would imply there's an entity with an id of 0, and you'd attach that Context to each Task, replacing the old Context to be deleted. Michael, thanks for the usual thorough response. Yes, there is a Context entity with a unique (non-primary) id of zero, it is No Context' but it is treated exactly the same as any other Context. The reason for this is that the local sqlite database being managed through SQLA is kept in sync with a remote database that (for whatever reason) explicitly sets the 'No Context context_id to zero and not NULL. As you indicate, it is possible to iterate through the list of Tasks and explicitly set the context_id to zero, I just wanted to confirm what you indicated that the setting of the foreign key to NULL on the parent in a delete is hardwired and so I just need to work around that. Again, thanks for the help. Steve --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
