[sqlalchemy] Re: Automatically filtering all queries
On 26 май, 20:50, Michael Bayer mike...@zzzcomputing.com wrote: However, its quite easy to achieve. Just use this. class LimitingQuery(Query): def get(self, ident): return Query.get(self.populate_existing(), ident) def __iter__(self): return Query.__iter__(self.private()) @_generative() def private(self): crit = (self._entities[0].mapper.class_.public == True) if self._criterion: self._criterion = crit else: self._criterion = crit full test case attached. Thanks a lot! But the test doesn't cover all use-cases. Here is one that fails: count1 = sess.query(Address).count() count2 = len(sess.query(Address).all()) assert count1==count2, '%d!=%d' % (count1, count2) --~--~-~--~~~---~--~~ 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: Changes in exc.py causing problems.
Hooray. \o/ I'll leave the code commented until I pull the next release. Cheers, On May 26, 6:03 pm, Michael Bayer mike...@zzzcomputing.com wrote: this is all fixed in the current trunk. release probably today as the issue you have below is more severe than the one I had noticed. Bob Farrell wrote: Hi hi. £ diff SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/exc.py SQLAlchemy-0.5.4p1- py2.5.egg/sqlalchemy/exc.py 134a135,139 if len(self.params) 10: return ' '.join((SQLAlchemyError.__str__(self), repr(self.statement), repr(self.params[:2]), '... and a total of %i bound parameters' % len(self.params))) This change is resulting in this problem: File /home/bob/src/prospectspace/branches/sqlalchemy-eon-merge/ prospectspace/commands/pspatch.py, line 1473, in create_user print e File /home/bob/src/prospectspace/branches/sqlalchemy-eon-merge/lib/ python2.5/site-packages/SQLAlchemy-0.5.4p1-py2.5.egg/sqlalchemy/ exc.py, line 138, in __str__ repr(self.params[:2]), TypeError: unhashable type Which is a little confusing, not sure why any hashing attempt is happening there - I'll investigate it further and send a patch tomorrow, unless something blaringly obvious stands out to you as to what's causing this. Cheers, --~--~-~--~~~---~--~~ 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: Questions on SQLA Queries
Q1. Good question %) I didn't find anything about it in docs (but i didn't search a lot), so i use map function to convert it to a list you want. And I think it is the right solution. Because if you query for more then one column (session.query(User.is, User.name).all()) a list of tuples is what you want to get as a result. So i think it is good, that it works the same way for one or more then one query params. On May 26, 9:10 pm, Harish Vishwanath harish.shas...@gmail.com wrote: Hello, Question 1: When there is a query like below : q = session.query(User.name) #(User is a class) and when I do q.all(), a list of tuples (User.name,) is returned though a single column is asked for. Is there a way to get a list directly from q.all() when a single column is required? Question 2: I need to delete a bulky table and I want to print diagnostics after n number of deletes. Is there a way to use Query object so that a SQL statement like below can be generated? delete from movie where year in (select top 30 year from movie where year 50); , so that a message can be logged after every 30 deletes. I am using Sqlite DB. Regards, Harish --~--~-~--~~~---~--~~ 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: Questions on SQLA Queries
Thanks! Could you elaborate on how you use the map function? I couldn't find it myself in the docs. Regards, Harish On Wed, May 27, 2009 at 3:07 PM, Timothy N. Tsvetkov timothy.tsvet...@gmail.com wrote: Q1. Good question %) I didn't find anything about it in docs (but i didn't search a lot), so i use map function to convert it to a list you want. And I think it is the right solution. Because if you query for more then one column (session.query(User.is, User.name).all()) a list of tuples is what you want to get as a result. So i think it is good, that it works the same way for one or more then one query params. On May 26, 9:10 pm, Harish Vishwanath harish.shas...@gmail.com wrote: Hello, Question 1: When there is a query like below : q = session.query(User.name) #(User is a class) and when I do q.all(), a list of tuples (User.name,) is returned though a single column is asked for. Is there a way to get a list directly from q.all() when a single column is required? Question 2: I need to delete a bulky table and I want to print diagnostics after n number of deletes. Is there a way to use Query object so that a SQL statement like below can be generated? delete from movie where year in (select top 30 year from movie where year 50); , so that a message can be logged after every 30 deletes. I am using Sqlite DB. Regards, Harish --~--~-~--~~~---~--~~ 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: Questions on SQLA Queries
I believe by map function, Timothy may be implying that you should use any of the python idioms for converting iterables of tuples to a straight tuple. The one I like best from itertools import chain q = session.query(User.name) #(User is a class) names = itertools.chain(*q.all() ) But you could use generator comprehensions ( names = (x[0] for x in q.all()), operator.itemgetter, or map instead. Correct me, Timothy, if necessary. Gregg On Wed, May 27, 2009 at 6:25 AM, Harish Vishwanath harish.shas...@gmail.com wrote: Thanks! Could you elaborate on how you use the map function? I couldn't find it myself in the docs. Regards, Harish On Wed, May 27, 2009 at 3:07 PM, Timothy N. Tsvetkov timothy.tsvet...@gmail.com wrote: Q1. Good question %) I didn't find anything about it in docs (but i didn't search a lot), so i use map function to convert it to a list you want. And I think it is the right solution. Because if you query for more then one column (session.query(User.is, User.name).all()) a list of tuples is what you want to get as a result. So i think it is good, that it works the same way for one or more then one query params. On May 26, 9:10 pm, Harish Vishwanath harish.shas...@gmail.com wrote: Hello, Question 1: When there is a query like below : q = session.query(User.name) #(User is a class) and when I do q.all(), a list of tuples (User.name,) is returned though a single column is asked for. Is there a way to get a list directly from q.all() when a single column is required? Question 2: I need to delete a bulky table and I want to print diagnostics after n number of deletes. Is there a way to use Query object so that a SQL statement like below can be generated? delete from movie where year in (select top 30 year from movie where year 50); , so that a message can be logged after every 30 deletes. I am using Sqlite DB. Regards, Harish --~--~-~--~~~---~--~~ 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: Questions on SQLA Queries
Q1. How about simple list comprehension? names = [row[0] for row in q.all()] Q2. This is an interesting question. The delete() method of query didn't give the result you are looking for: session.query(Movie).filter(year50).limit(30).delete() generates SQL delete from movie where year 50 so the select with limit is not built into the delete I didn't test if, but like this should work. It would be nice to make it a little more elegant so we didn't need to have separate query and delete statements. while True: ids = [row[0] for row in session.query(Movie.id).limit(30)] if len(ids) == 0: break session.query(Movie).in_(ids).delete() session.commit() You might need to do something with synchronize_session on the delete(). If the total number of rows to delete is very large and/or the table is very large, you will almost certainly need to spend some time optimizing this delete process. -- Mike Conley On Wed, May 27, 2009 at 7:25 AM, Harish Vishwanath harish.shas...@gmail.com wrote: Thanks! Could you elaborate on how you use the map function? I couldn't find it myself in the docs. Regards, Harish On Wed, May 27, 2009 at 3:07 PM, Timothy N. Tsvetkov timothy.tsvet...@gmail.com wrote: Q1. Good question %) I didn't find anything about it in docs (but i didn't search a lot), so i use map function to convert it to a list you want. And I think it is the right solution. Because if you query for more then one column (session.query(User.is, User.name).all()) a list of tuples is what you want to get as a result. So i think it is good, that it works the same way for one or more then one query params. On May 26, 9:10 pm, Harish Vishwanath harish.shas...@gmail.com wrote: Hello, Question 1: When there is a query like below : q = session.query(User.name) #(User is a class) and when I do q.all(), a list of tuples (User.name,) is returned though a single column is asked for. Is there a way to get a list directly from q.all() when a single column is required? Question 2: I need to delete a bulky table and I want to print diagnostics after n number of deletes. Is there a way to use Query object so that a SQL statement like below can be generated? delete from movie where year in (select top 30 year from movie where year 50); , so that a message can be logged after every 30 deletes. I am using Sqlite DB. Regards, Harish --~--~-~--~~~---~--~~ 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: Questions on SQLA Queries
And here is the tested version of the code I published earlier while True: ids = [row[0] for row in session.query(Movie.id).filter(Movie.year50).limit(30)] if len(ids) == 0: break session.query(Movie).filter(Movie.id.in_(ids)).delete(synchronize_session=False) session.flush() session.commit() -- Mike Conley On Wed, May 27, 2009 at 9:13 AM, Gregg Lind gregg.l...@gmail.com wrote: I believe by map function, Timothy may be implying that you should use any of the python idioms for converting iterables of tuples to a straight tuple. The one I like best from itertools import chain q = session.query(User.name) #(User is a class) names = itertools.chain(*q.all() ) But you could use generator comprehensions ( names = (x[0] for x in q.all()), operator.itemgetter, or map instead. Correct me, Timothy, if necessary. Gregg On Wed, May 27, 2009 at 6:25 AM, Harish Vishwanath harish.shas...@gmail.com wrote: Thanks! Could you elaborate on how you use the map function? I couldn't find it myself in the docs. Regards, Harish On Wed, May 27, 2009 at 3:07 PM, Timothy N. Tsvetkov timothy.tsvet...@gmail.com wrote: Q1. Good question %) I didn't find anything about it in docs (but i didn't search a lot), so i use map function to convert it to a list you want. And I think it is the right solution. Because if you query for more then one column (session.query(User.is, User.name).all()) a list of tuples is what you want to get as a result. So i think it is good, that it works the same way for one or more then one query params. On May 26, 9:10 pm, Harish Vishwanath harish.shas...@gmail.com wrote: Hello, Question 1: When there is a query like below : q = session.query(User.name) #(User is a class) and when I do q.all(), a list of tuples (User.name,) is returned though a single column is asked for. Is there a way to get a list directly from q.all() when a single column is required? Question 2: I need to delete a bulky table and I want to print diagnostics after n number of deletes. Is there a way to use Query object so that a SQL statement like below can be generated? delete from movie where year in (select top 30 year from movie where year 50); , so that a message can be logged after every 30 deletes. I am using Sqlite DB. Regards, Harish --~--~-~--~~~---~--~~ 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] Performance, cd
Hello everyone,
I nailed the problem with performance, it wasn't the 'big query' not
loading collections, but this one:
rsvs =
session.query(Reservation).filter(Reservation.project_id.in_(projids)).filter(Reservation.status
== 'pending').filter(Reservation.end_date todaydt).all()
The problem with this is that it generates lots of single queries,
selecting Reservations one by one according to id (debug log below).
I have absolutely no idea why this happens instead of INNER JOIN on
NewHosts.
I have relations defined as follows:
newhosts_table = Table('newhosts',md,
Column('id',Integer,primary_key=True),
Column('ip',String),
Column('hostname',String),
Column('location',String),
Column('architecture_id',Integer,ForeignKey('architecture.id')),
Column('os_kind_id',Integer,ForeignKey('os_kind.id')),
Column('os_version_id',Integer,ForeignKey('os_version.id')),
Column('virtualization_id',Integer,ForeignKey('virtualization.id')),
Column('shareable',SLBoolean),
Column('shareable_between_projects',SLBoolean),
Column('cpu',String),
Column('ram',String),
Column('notes',String),
Column('physical_box',SLBoolean),
Column('project_id',Integer,ForeignKey('project.id')))
reservation_table = Table('reservation', md,
Column('id',Integer,primary_key=True),
Column('start_date',SLDate),
Column('end_date',SLDate),
Column('status', String),
Column('businessneed', String),
Column('notetohwrep',String),
Column('email_id',Integer,ForeignKey('email.id')),
Column('project_id',Integer,ForeignKey('project.id'))
)
reservation_newhosts_assoc_table = Table('reservation_newhosts', md,
Column('reservation_id',Integer,ForeignKey('reservation.id')),
Column('host_id',Integer,ForeignKey('newhosts.id'))
)
mapper(Reservation, reservation_table,
properties={'email':relation(Email,order_by=Email.id),
'project':relation(Project, order_by=Project.id),
'hosts':relation(Host,
secondary=reservation_hosts_assoc_table,backref='reservation'),
'newhosts':relation(NewHost,
secondary=reservation_newhosts_assoc_table,backref='reservationnh')}
)
mapper(NewHost, newhosts_table,
properties={
'architecture':relation(Architecture,order_by=Architecture.id,backref='newhosts'),
'os_kind':relation(OS_Kind,order_by=OS_Kind.id,backref='newhosts'),
'os_version':relation(OS_version,
order_by=OS_version.id,backref='newhosts'),
'virtualization':relation(Virtualization,order_by=Virtualization.id,
backref='newhosts'),
'project':relation(Project,order_by=Project.id, backref='newhosts'),
'reservations':relation(Reservation,secondary=reservation_newhosts_assoc_table,
backref='newhost_reservations')}
)
SQL:
INFO:sqlalchemy.engine.base.Engine.0x...584c:SELECT newhosts.id AS
newhosts_id, newhosts.ip AS newhosts_ip, newhosts.hostname AS
newhosts_hostname, newhosts.location AS newhosts_location,
newhosts.architectu
re_id AS newhosts_architecture_id, newhosts.os_kind_id AS
newhosts_os_kind_id, newhosts.os_version_id AS newhosts_os_version_id,
newhosts.virtualization_id AS newhosts_virtualization_id,
newhosts.shareable AS newhosts_shareable,
newhosts.shareable_between_projects AS
newhosts_shareable_between_projects, newhosts.cpu AS newhosts_cpu,
newhosts.ram AS newhosts_ram, newhosts.notes AS newhosts_notes,
newhosts.physic
al_box AS newhosts_physical_box, newhosts.project_id AS newhosts_project_id
FROM newhosts, reservation_newhosts
WHERE %(param_1)s = reservation_newhosts.reservation_id AND newhosts.id
= reservation_newhosts.host_id
INFO:sqlalchemy.engine.base.Engine.0x...584c:{'param_1': 902}
DEBUG:sqlalchemy.engine.base.Engine.0x...584c:Col ('newhosts_id',
'newhosts_ip', 'newhosts_hostname', 'newhosts_location',
'newhosts_architecture_id', 'newhosts_os_kind_id',
'newhosts_os_version_id', 'newhos
ts_virtualization_id', 'newhosts_shareable',
'newhosts_shareable_between_projects', 'newhosts_cpu', 'newhosts_ram',
'newhosts_notes', 'newhosts_physical_box', 'newhosts_project_id')
INFO:sqlalchemy.engine.base.Engine.0x...584c:SELECT reservation.id AS
reservation_id, reservation.start_date AS reservation_start_date,
reservation.end_date AS reservation_end_date, reservation.status AS res
ervation_status, reservation.businessneed AS reservation_businessneed,
reservation.notetohwrep AS reservation_notetohwrep, reservation.email_id
AS reservation_email_id, reservation.project_id AS reservation_
project_id
FROM reservation
WHERE reservation.id = %(id_1)s
LIMIT 1 OFFSET 0
INFO:sqlalchemy.engine.base.Engine.0x...584c:{'id_1': 903}
DEBUG:sqlalchemy.engine.base.Engine.0x...584c:Col ('reservation_id',
'reservation_start_date', 'reservation_end_date', 'reservation_status',
'reservation_businessneed', 'reservation_notetohwrep', 'reservatio
n_email_id', 'reservation_project_id')
DEBUG:sqlalchemy.engine.base.Engine.0x...584c:Row (903,
datetime.date(2009, 10, 28), datetime.date(2009, 11, 1), 'pending',
'#1', '1', 1, 13)
INFO:sqlalchemy.engine.base.Engine.0x...584c:SELECT newhosts.id AS
newhosts_id, newhosts.ip AS newhosts_ip, newhosts.hostname
[sqlalchemy] Re: 0.5.4p2 Tests failed
the changes between 0.5.4p1 and 0.5.4p2 are extremely minimal and I've pasted them at http://paste.pocoo.org/show/119367/ .Additionally here they are passing for py2.4 and py2.5 at http://groovie.org:8012/ which is an intel linux platform. I run the tests with 2.6 here on an intel mac. In particular the PPC tests seem to be running with pre-existing tables from a previous run - run the tests with --dropfirst to clear out all pre-existing rables. For the intel, my guess would be the wrong version of SQLAlchemy is being tested (like an early 0.5 version perhaps). On May 26, 2009, at 11:34 PM, Melton Low wrote: Hi, I just installed the just released version 0.5.4p2. 3 of the tests failed on my Intel Mac and 97 failures on my PPC Mac. With the previous version 0.5.4p1, all tests ran successfully on both systems. The successfull tests were ran with the stock version of psqlite bundled with Python. Pysqlite 2.5.5 was installed over the standard version bundled with Python. I did not re-run the SqlAlchemy test suites after installing pysqlite 2.5.5. Earlier today I installed the latest release of Sqlite 3.6.14.2. I have attached my installation log and the test result logs for both systems. Since I am just learning SQLAlchemy, the failed tests probably will not affect me. In any case, I will be reverting back to 0.5.4p1. Hopefull, the log entries can help you isolate the problems. My environment: Mac OS X 10.5.7 Intel MacBook Python 2.6.2 pysqlite 2.5.5 psycopg2 2.0.11 Mac OS X 10.4.11 PowerPC Python 2.6.2 pysqlite 2.5.5 psycopg2 2.0.11 Regards, Mel Intel SQLAlchemy-0.5.4p2 TESTs.txtPPC SQLAlchemy-0.5.4p2 installlog.txtPPC SQLAlchemy-0.5.4p2 TESTs.txtIntel SQLAlchemy-0.5.4p2 installlog.txt --~--~-~--~~~---~--~~ 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: Automatically filtering all queries
On May 27, 2009, at 4:25 AM, Denis S. Otkidach wrote: On 26 май, 20:50, Michael Bayer mike...@zzzcomputing.com wrote: However, its quite easy to achieve. Just use this. class LimitingQuery(Query): def get(self, ident): return Query.get(self.populate_existing(), ident) def __iter__(self): return Query.__iter__(self.private()) @_generative() def private(self): crit = (self._entities[0].mapper.class_.public == True) if self._criterion: self._criterion = crit else: self._criterion = crit full test case attached. Thanks a lot! But the test doesn't cover all use-cases. Here is one that fails: count1 = sess.query(Address).count() count2 = len(sess.query(Address).all()) assert count1==count2, '%d!=%d' % (count1, count2) you'd have to hack .count() as well in a similar fashion. --~--~-~--~~~---~--~~ 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: Performance, cd
the Query.all() call only generates a single SQL statement at all
times. Its only when you access attributes on individual rows that a
second statement would occur. If the multiple queries truly occur
within the scope of the all() call, I'd check to see if you have a
@reconstructor or __new__ of some kind that may be causing this.
On May 27, 2009, at 10:19 AM, Marcin Krol wrote:
Hello everyone,
I nailed the problem with performance, it wasn't the 'big query' not
loading collections, but this one:
rsvs =
session
.query
(Reservation
).filter
(Reservation.project_id.in_(projids)).filter(Reservation.status
== 'pending').filter(Reservation.end_date todaydt).all()
The problem with this is that it generates lots of single queries,
selecting Reservations one by one according to id (debug log below).
I have absolutely no idea why this happens instead of INNER JOIN on
NewHosts.
I have relations defined as follows:
newhosts_table = Table('newhosts',md,
Column('id',Integer,primary_key=True),
Column('ip',String),
Column('hostname',String),
Column('location',String),
Column('architecture_id',Integer,ForeignKey('architecture.id')),
Column('os_kind_id',Integer,ForeignKey('os_kind.id')),
Column('os_version_id',Integer,ForeignKey('os_version.id')),
Column('virtualization_id',Integer,ForeignKey('virtualization.id')),
Column('shareable',SLBoolean),
Column('shareable_between_projects',SLBoolean),
Column('cpu',String),
Column('ram',String),
Column('notes',String),
Column('physical_box',SLBoolean),
Column('project_id',Integer,ForeignKey('project.id')))
reservation_table = Table('reservation', md,
Column('id',Integer,primary_key=True),
Column('start_date',SLDate),
Column('end_date',SLDate),
Column('status', String),
Column('businessneed', String),
Column('notetohwrep',String),
Column('email_id',Integer,ForeignKey('email.id')),
Column('project_id',Integer,ForeignKey('project.id'))
)
reservation_newhosts_assoc_table = Table('reservation_newhosts', md,
Column('reservation_id',Integer,ForeignKey('reservation.id')),
Column('host_id',Integer,ForeignKey('newhosts.id'))
)
mapper(Reservation, reservation_table,
properties={'email':relation(Email,order_by=Email.id),
'project':relation(Project, order_by=Project.id),
'hosts':relation(Host,
secondary=reservation_hosts_assoc_table,backref='reservation'),
'newhosts':relation(NewHost,
secondary=reservation_newhosts_assoc_table,backref='reservationnh')}
)
mapper(NewHost, newhosts_table,
properties={
'architecture
':relation(Architecture,order_by=Architecture.id,backref='newhosts'),
'os_kind':relation(OS_Kind,order_by=OS_Kind.id,backref='newhosts'),
'os_version':relation(OS_version,
order_by=OS_version.id,backref='newhosts'),
'virtualization':relation(Virtualization,order_by=Virtualization.id,
backref='newhosts'),
'project':relation(Project,order_by=Project.id, backref='newhosts'),
'reservations
':relation(Reservation,secondary=reservation_newhosts_assoc_table,
backref='newhost_reservations')}
)
SQL:
INFO:sqlalchemy.engine.base.Engine.0x...584c:SELECT newhosts.id AS
newhosts_id, newhosts.ip AS newhosts_ip, newhosts.hostname AS
newhosts_hostname, newhosts.location AS newhosts_location,
newhosts.architectu
re_id AS newhosts_architecture_id, newhosts.os_kind_id AS
newhosts_os_kind_id, newhosts.os_version_id AS newhosts_os_version_id,
newhosts.virtualization_id AS newhosts_virtualization_id,
newhosts.shareable AS newhosts_shareable,
newhosts.shareable_between_projects AS
newhosts_shareable_between_projects, newhosts.cpu AS newhosts_cpu,
newhosts.ram AS newhosts_ram, newhosts.notes AS newhosts_notes,
newhosts.physic
al_box AS newhosts_physical_box, newhosts.project_id AS
newhosts_project_id
FROM newhosts, reservation_newhosts
WHERE %(param_1)s = reservation_newhosts.reservation_id AND
newhosts.id
= reservation_newhosts.host_id
INFO:sqlalchemy.engine.base.Engine.0x...584c:{'param_1': 902}
DEBUG:sqlalchemy.engine.base.Engine.0x...584c:Col ('newhosts_id',
'newhosts_ip', 'newhosts_hostname', 'newhosts_location',
'newhosts_architecture_id', 'newhosts_os_kind_id',
'newhosts_os_version_id', 'newhos
ts_virtualization_id', 'newhosts_shareable',
'newhosts_shareable_between_projects', 'newhosts_cpu', 'newhosts_ram',
'newhosts_notes', 'newhosts_physical_box', 'newhosts_project_id')
INFO:sqlalchemy.engine.base.Engine.0x...584c:SELECT reservation.id AS
reservation_id, reservation.start_date AS reservation_start_date,
reservation.end_date AS reservation_end_date, reservation.status AS
res
ervation_status, reservation.businessneed AS reservation_businessneed,
reservation.notetohwrep AS reservation_notetohwrep,
reservation.email_id
AS reservation_email_id, reservation.project_id AS reservation_
project_id
FROM reservation
WHERE reservation.id = %(id_1)s
LIMIT 1 OFFSET 0
INFO:sqlalchemy.engine.base.Engine.0x...584c:{'id_1': 903}
[sqlalchemy] Re: Performance, cd
Hello Mike,
Nailed it! Thanks a million, Mike!
Michael Bayer wrote:
the Query.all() call only generates a single SQL statement at all
times. Its only when you access attributes on individual rows that a
second statement would occur. If the multiple queries truly occur
within the scope of the all() call, I'd check to see if you have a
@reconstructor or __new__ of some kind that may be causing this.
On May 27, 2009, at 10:19 AM, Marcin Krol wrote:
Hello everyone,
I nailed the problem with performance, it wasn't the 'big query' not
loading collections, but this one:
rsvs =
session
.query
(Reservation
).filter
(Reservation.project_id.in_(projids)).filter(Reservation.status
== 'pending').filter(Reservation.end_date todaydt).all()
The problem with this is that it generates lots of single queries,
selecting Reservations one by one according to id (debug log below).
I have absolutely no idea why this happens instead of INNER JOIN on
NewHosts.
I have relations defined as follows:
newhosts_table = Table('newhosts',md,
Column('id',Integer,primary_key=True),
Column('ip',String),
Column('hostname',String),
Column('location',String),
Column('architecture_id',Integer,ForeignKey('architecture.id')),
Column('os_kind_id',Integer,ForeignKey('os_kind.id')),
Column('os_version_id',Integer,ForeignKey('os_version.id')),
Column('virtualization_id',Integer,ForeignKey('virtualization.id')),
Column('shareable',SLBoolean),
Column('shareable_between_projects',SLBoolean),
Column('cpu',String),
Column('ram',String),
Column('notes',String),
Column('physical_box',SLBoolean),
Column('project_id',Integer,ForeignKey('project.id')))
reservation_table = Table('reservation', md,
Column('id',Integer,primary_key=True),
Column('start_date',SLDate),
Column('end_date',SLDate),
Column('status', String),
Column('businessneed', String),
Column('notetohwrep',String),
Column('email_id',Integer,ForeignKey('email.id')),
Column('project_id',Integer,ForeignKey('project.id'))
)
reservation_newhosts_assoc_table = Table('reservation_newhosts', md,
Column('reservation_id',Integer,ForeignKey('reservation.id')),
Column('host_id',Integer,ForeignKey('newhosts.id'))
)
mapper(Reservation, reservation_table,
properties={'email':relation(Email,order_by=Email.id),
'project':relation(Project, order_by=Project.id),
'hosts':relation(Host,
secondary=reservation_hosts_assoc_table,backref='reservation'),
'newhosts':relation(NewHost,
secondary=reservation_newhosts_assoc_table,backref='reservationnh')}
)
mapper(NewHost, newhosts_table,
properties={
'architecture
':relation(Architecture,order_by=Architecture.id,backref='newhosts'),
'os_kind':relation(OS_Kind,order_by=OS_Kind.id,backref='newhosts'),
'os_version':relation(OS_version,
order_by=OS_version.id,backref='newhosts'),
'virtualization':relation(Virtualization,order_by=Virtualization.id,
backref='newhosts'),
'project':relation(Project,order_by=Project.id, backref='newhosts'),
'reservations
':relation(Reservation,secondary=reservation_newhosts_assoc_table,
backref='newhost_reservations')}
)
SQL:
INFO:sqlalchemy.engine.base.Engine.0x...584c:SELECT newhosts.id AS
newhosts_id, newhosts.ip AS newhosts_ip, newhosts.hostname AS
newhosts_hostname, newhosts.location AS newhosts_location,
newhosts.architectu
re_id AS newhosts_architecture_id, newhosts.os_kind_id AS
newhosts_os_kind_id, newhosts.os_version_id AS newhosts_os_version_id,
newhosts.virtualization_id AS newhosts_virtualization_id,
newhosts.shareable AS newhosts_shareable,
newhosts.shareable_between_projects AS
newhosts_shareable_between_projects, newhosts.cpu AS newhosts_cpu,
newhosts.ram AS newhosts_ram, newhosts.notes AS newhosts_notes,
newhosts.physic
al_box AS newhosts_physical_box, newhosts.project_id AS
newhosts_project_id
FROM newhosts, reservation_newhosts
WHERE %(param_1)s = reservation_newhosts.reservation_id AND
newhosts.id
= reservation_newhosts.host_id
INFO:sqlalchemy.engine.base.Engine.0x...584c:{'param_1': 902}
DEBUG:sqlalchemy.engine.base.Engine.0x...584c:Col ('newhosts_id',
'newhosts_ip', 'newhosts_hostname', 'newhosts_location',
'newhosts_architecture_id', 'newhosts_os_kind_id',
'newhosts_os_version_id', 'newhos
ts_virtualization_id', 'newhosts_shareable',
'newhosts_shareable_between_projects', 'newhosts_cpu', 'newhosts_ram',
'newhosts_notes', 'newhosts_physical_box', 'newhosts_project_id')
INFO:sqlalchemy.engine.base.Engine.0x...584c:SELECT reservation.id AS
reservation_id, reservation.start_date AS reservation_start_date,
reservation.end_date AS reservation_end_date, reservation.status AS
res
ervation_status, reservation.businessneed AS reservation_businessneed,
reservation.notetohwrep AS reservation_notetohwrep,
reservation.email_id
AS reservation_email_id, reservation.project_id AS reservation_
project_id
FROM reservation
WHERE reservation.id = %(id_1)s
LIMIT 1 OFFSET 0
[sqlalchemy] Re: 0.5.4p2 Tests failed
Hi Michael, I re-build my systems last night - zapped Python, Mercurial, Sqlite, pysqlite, psycopg, and SQLAlchemy. You 0.5.4p1 test suite ran perfectly as long as I only have the version of pysqlite included with Python. As I am going out of town, I decided to leave 0.5.4p2 alone for the time being. As soon as I can I will try installing it and will let you know. Mel On Wed, May 27, 2009 at 8:21 AM, Michael Bayer mike...@zzzcomputing.comwrote: the changes between 0.5.4p1 and 0.5.4p2 are extremely minimal and I've pasted them at http://paste.pocoo.org/show/119367/ .Additionally here they are passing for py2.4 and py2.5 at http://groovie.org:8012/ which is an intel linux platform. I run the tests with 2.6 here on an intel mac. In particular the PPC tests seem to be running with pre-existing tables from a previous run - run the tests with --dropfirst to clear out all pre-existing rables. For the intel, my guess would be the wrong version of SQLAlchemy is being tested (like an early 0.5 version perhaps). On May 26, 2009, at 11:34 PM, Melton Low wrote: Hi, I just installed the just released version 0.5.4p2. 3 of the tests failed on my Intel Mac and 97 failures on my PPC Mac. With the previous version 0.5.4p1, all tests ran successfully on both systems. The successfull tests were ran with the stock version of psqlite bundled with Python. Pysqlite 2.5.5 was installed over the standard version bundled with Python. I did not re-run the SqlAlchemy test suites after installing pysqlite 2.5.5. Earlier today I installed the latest release of Sqlite 3.6.14.2. I have attached my installation log and the test result logs for both systems. Since I am just learning SQLAlchemy, the failed tests probably will not affect me. In any case, I will be reverting back to 0.5.4p1. Hopefull, the log entries can help you isolate the problems. My environment: Mac OS X 10.5.7 Intel MacBook Python 2.6.2 pysqlite 2.5.5 psycopg2 2.0.11 Mac OS X 10.4.11 PowerPC Python 2.6.2 pysqlite 2.5.5 psycopg2 2.0.11 Regards, Mel Intel SQLAlchemy-0.5.4p2 TESTs.txtPPC SQLAlchemy-0.5.4p2 installlog.txtPPC SQLAlchemy-0.5.4p2 TESTs.txtIntel SQLAlchemy-0.5.4p2 installlog.txt --~--~-~--~~~---~--~~ 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.5.4p2 Tests failed
with the latest pysqlite (2.5.5) I also get a huge (192) amount of failures with many versions of SQLalchemy, including p1 and p2. I had noticed that pysqlite's development seemed to be going further with changes that make the library harder to develop against, but particularly disturbing is that one of our memory leak tests seems to reveal a memory leak within pysqlite.So overall this is a very disturbing result and I would recommend against using the non-python included pysqlite. On May 27, 2009, at 10:51 AM, Melton Low wrote: Hi Michael, I re-build my systems last night - zapped Python, Mercurial, Sqlite, pysqlite, psycopg, and SQLAlchemy. You 0.5.4p1 test suite ran perfectly as long as I only have the version of pysqlite included with Python. As I am going out of town, I decided to leave 0.5.4p2 alone for the time being. As soon as I can I will try installing it and will let you know. Mel On Wed, May 27, 2009 at 8:21 AM, Michael Bayer mike...@zzzcomputing.com wrote: the changes between 0.5.4p1 and 0.5.4p2 are extremely minimal and I've pasted them at http://paste.pocoo.org/show/119367/ .Additionally here they are passing for py2.4 and py2.5 at http://groovie.org:8012/ which is an intel linux platform. I run the tests with 2.6 here on an intel mac. In particular the PPC tests seem to be running with pre-existing tables from a previous run - run the tests with --dropfirst to clear out all pre-existing rables. For the intel, my guess would be the wrong version of SQLAlchemy is being tested (like an early 0.5 version perhaps). On May 26, 2009, at 11:34 PM, Melton Low wrote: Hi, I just installed the just released version 0.5.4p2. 3 of the tests failed on my Intel Mac and 97 failures on my PPC Mac. With the previous version 0.5.4p1, all tests ran successfully on both systems. The successfull tests were ran with the stock version of psqlite bundled with Python. Pysqlite 2.5.5 was installed over the standard version bundled with Python. I did not re-run the SqlAlchemy test suites after installing pysqlite 2.5.5. Earlier today I installed the latest release of Sqlite 3.6.14.2. I have attached my installation log and the test result logs for both systems. Since I am just learning SQLAlchemy, the failed tests probably will not affect me. In any case, I will be reverting back to 0.5.4p1. Hopefull, the log entries can help you isolate the problems. My environment: Mac OS X 10.5.7 Intel MacBook Python 2.6.2 pysqlite 2.5.5 psycopg2 2.0.11 Mac OS X 10.4.11 PowerPC Python 2.6.2 pysqlite 2.5.5 psycopg2 2.0.11 Regards, Mel Intel SQLAlchemy-0.5.4p2 TESTs.txtPPC SQLAlchemy-0.5.4p2 installlog.txtPPC SQLAlchemy-0.5.4p2 TESTs.txtIntel SQLAlchemy-0.5.4p2 installlog.txt --~--~-~--~~~---~--~~ 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.5.4p2 Tests failed
The first time I installed pysqlite 2.5.5, my Mac 10.4 build fine but not on the 10.5. The solution from the pysqlite people was to build_static on 10.5. It seem to work the first time around. Unfortunately I was not able to get a clean install last night on either systems. So for the time begin I will stick with the Python included version. Regards, Mel On Wed, May 27, 2009 at 9:05 AM, Michael Bayer mike...@zzzcomputing.comwrote: with the latest pysqlite (2.5.5) I also get a huge (192) amount of failures with many versions of SQLalchemy, including p1 and p2. I had noticed that pysqlite's development seemed to be going further with changes that make the library harder to develop against, but particularly disturbing is that one of our memory leak tests seems to reveal a memory leak within pysqlite.So overall this is a very disturbing result and I would recommend against using the non-python included pysqlite. On May 27, 2009, at 10:51 AM, Melton Low wrote: Hi Michael, I re-build my systems last night - zapped Python, Mercurial, Sqlite, pysqlite, psycopg, and SQLAlchemy. You 0.5.4p1 test suite ran perfectly as long as I only have the version of pysqlite included with Python. As I am going out of town, I decided to leave 0.5.4p2 alone for the time being. As soon as I can I will try installing it and will let you know. Mel On Wed, May 27, 2009 at 8:21 AM, Michael Bayer mike...@zzzcomputing.comwrote: the changes between 0.5.4p1 and 0.5.4p2 are extremely minimal and I've pasted them at http://paste.pocoo.org/show/119367/ .Additionally here they are passing for py2.4 and py2.5 at http://groovie.org:8012/ which is an intel linux platform. I run the tests with 2.6 here on an intel mac. In particular the PPC tests seem to be running with pre-existing tables from a previous run - run the tests with --dropfirst to clear out all pre-existing rables. For the intel, my guess would be the wrong version of SQLAlchemy is being tested (like an early 0.5 version perhaps). On May 26, 2009, at 11:34 PM, Melton Low wrote: Hi, I just installed the just released version 0.5.4p2. 3 of the tests failed on my Intel Mac and 97 failures on my PPC Mac. With the previous version 0.5.4p1, all tests ran successfully on both systems. The successfull tests were ran with the stock version of psqlite bundled with Python. Pysqlite 2.5.5 was installed over the standard version bundled with Python. I did not re-run the SqlAlchemy test suites after installing pysqlite 2.5.5. Earlier today I installed the latest release of Sqlite 3.6.14.2. I have attached my installation log and the test result logs for both systems. Since I am just learning SQLAlchemy, the failed tests probably will not affect me. In any case, I will be reverting back to 0.5.4p1. Hopefull, the log entries can help you isolate the problems. My environment: Mac OS X 10.5.7 Intel MacBook Python 2.6.2 pysqlite 2.5.5 psycopg2 2.0.11 Mac OS X 10.4.11 PowerPC Python 2.6.2 pysqlite 2.5.5 psycopg2 2.0.11 Regards, Mel Intel SQLAlchemy-0.5.4p2 TESTs.txtPPC SQLAlchemy-0.5.4p2 installlog.txtPPC SQLAlchemy-0.5.4p2 TESTs.txtIntel SQLAlchemy-0.5.4p2 installlog.txt --~--~-~--~~~---~--~~ 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.5.4p2 Tests failed
DOH, oh right, I made the same mistake and just built pysqlite against an arbitrary SQLite version. WHEW my day just got shorter again :) . On May 27, 2009, at 11:21 AM, Melton Low wrote: The first time I installed pysqlite 2.5.5, my Mac 10.4 build fine but not on the 10.5. The solution from the pysqlite people was to build_static on 10.5. It seem to work the first time around. Unfortunately I was not able to get a clean install last night on either systems. So for the time begin I will stick with the Python included version. Regards, Mel On Wed, May 27, 2009 at 9:05 AM, Michael Bayer mike...@zzzcomputing.com wrote: with the latest pysqlite (2.5.5) I also get a huge (192) amount of failures with many versions of SQLalchemy, including p1 and p2. I had noticed that pysqlite's development seemed to be going further with changes that make the library harder to develop against, but particularly disturbing is that one of our memory leak tests seems to reveal a memory leak within pysqlite.So overall this is a very disturbing result and I would recommend against using the non- python included pysqlite. On May 27, 2009, at 10:51 AM, Melton Low wrote: Hi Michael, I re-build my systems last night - zapped Python, Mercurial, Sqlite, pysqlite, psycopg, and SQLAlchemy. You 0.5.4p1 test suite ran perfectly as long as I only have the version of pysqlite included with Python. As I am going out of town, I decided to leave 0.5.4p2 alone for the time being. As soon as I can I will try installing it and will let you know. Mel On Wed, May 27, 2009 at 8:21 AM, Michael Bayer mike...@zzzcomputing.com wrote: the changes between 0.5.4p1 and 0.5.4p2 are extremely minimal and I've pasted them at http://paste.pocoo.org/show/119367/ .Additionally here they are passing for py2.4 and py2.5 at http://groovie.org:8012/ which is an intel linux platform. I run the tests with 2.6 here on an intel mac. In particular the PPC tests seem to be running with pre-existing tables from a previous run - run the tests with --dropfirst to clear out all pre-existing rables. For the intel, my guess would be the wrong version of SQLAlchemy is being tested (like an early 0.5 version perhaps). On May 26, 2009, at 11:34 PM, Melton Low wrote: Hi, I just installed the just released version 0.5.4p2. 3 of the tests failed on my Intel Mac and 97 failures on my PPC Mac. With the previous version 0.5.4p1, all tests ran successfully on both systems. The successfull tests were ran with the stock version of psqlite bundled with Python. Pysqlite 2.5.5 was installed over the standard version bundled with Python. I did not re-run the SqlAlchemy test suites after installing pysqlite 2.5.5. Earlier today I installed the latest release of Sqlite 3.6.14.2. I have attached my installation log and the test result logs for both systems. Since I am just learning SQLAlchemy, the failed tests probably will not affect me. In any case, I will be reverting back to 0.5.4p1. Hopefull, the log entries can help you isolate the problems. My environment: Mac OS X 10.5.7 Intel MacBook Python 2.6.2 pysqlite 2.5.5 psycopg2 2.0.11 Mac OS X 10.4.11 PowerPC Python 2.6.2 pysqlite 2.5.5 psycopg2 2.0.11 Regards, Mel Intel SQLAlchemy-0.5.4p2 TESTs.txtPPC SQLAlchemy-0.5.4p2 installlog.txtPPC SQLAlchemy-0.5.4p2 TESTs.txtIntel SQLAlchemy-0.5.4p2 installlog.txt --~--~-~--~~~---~--~~ 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] postgres and/or generic enum support in sqlalchemy
Hi, http://www.sqlalchemy.org/trac/ticket/1109 Is this still being worked on? In particular it would be interesting to get this running for postgres for us. If not, I would like to have a go at it in the near future, or am happy to help test if someone else is working on this. Cheers, Damian --~--~-~--~~~---~--~~ 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: postgres and/or generic enum support in sqlalchemy
from my perspective my comments on the ticket still stand - the MySQL specificness has to be removed and the interface regarding encoding and such should be simplified.The MySQL import and __supported business should be removed as well - I know the Interval type is doing the same thing with a dialect-specific import, but that one is wrong too - there is a better approach in the 0.6 branch. MySQL's MSEnum would be present in the dialect's colspecs dictionary as Enum:MSEnum. For Postgresql, we'd also add PGEnum and place that in PG's colspecs dict.I would prefer this to be an 0.6 thing but it can likely be backported to 0.5 without much difficulty. On May 27, 2009, at 11:07 AM, Damian wrote: Hi, http://www.sqlalchemy.org/trac/ticket/1109 Is this still being worked on? In particular it would be interesting to get this running for postgres for us. If not, I would like to have a go at it in the near future, or am happy to help test if someone else is working on this. Cheers, Damian --~--~-~--~~~---~--~~ 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] onclause in join in 5.3?
Hello everyone,
Is there such a thing?
I have to specify onclause since Host - Reservation is many-to-many,
and if I don't specify onclause, I get exception Can't find any foreign
key relationships between 'reservation' and 'hosts'.
Docs:
The onclause may be a string name of a relation(), or a class-bound
descriptor representing a relation.
What does class-bound descriptor representing a relation mean anyway??
This results in exception:
rsvs = session.query(Reservation,
Host).filter(Reservation.project_id.in_(projids)).filter(Reservation.status
== 'pending').join(Host, onclause='hosts').all()
Traceback (most recent call last):
File stdin, line 1, in module
File string, line 1, in lambda
File
/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.5.3-py2.6.egg/sqlalchemy/util.py,
line 212, in go
return fn(*args, **kw)
File
/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.5.3-py2.6.egg/sqlalchemy/orm/query.py,
line 869, in join
raise TypeError(unknown arguments: %s % ','.join(kwargs.iterkeys()))
TypeError: unknown arguments: onclause
I do in fact have 'hosts' relation in Reservation:
mapper(Reservation, reservation_table,
properties={'email':relation(Email,order_by=Email.id),
'project':relation(Project, order_by=Project.id),
'hosts':relation(Host,
secondary=reservation_hosts_assoc_table,backref='reservation'),
'newhosts':relation(NewHost,
secondary=reservation_newhosts_assoc_table,backref='reservationnh')}
)
So it's either onclause that is truly unknown to join() or smth else
throws it off.
I even tried this, don't know if it makes sense:
rsvs = session.query(Reservation, Host).join(Host,
onclause='hosts').filter(Reservation.project_id.in_(projids)).filter(Reservation.status
== 'pending').all()
Traceback (most recent call last):
File stdin, line 1, in module
File string, line 1, in lambda
File
/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.5.3-py2.6.egg/sqlalchemy/util.py,
line 212, in go
return fn(*args, **kw)
File
/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.5.3-py2.6.egg/sqlalchemy/orm/query.py,
line 869, in join
raise TypeError(unknown arguments: %s % ','.join(kwargs.iterkeys()))
TypeError: unknown arguments: onclause
rsvs = session.query(Reservation).join(Host, onclause='hosts').all()
Traceback (most recent call last):
File stdin, line 1, in module
File string, line 1, in lambda
File
/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.5.3-py2.6.egg/sqlalchemy/util.py,
line 212, in go
return fn(*args, **kw)
File
/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.5.3-py2.6.egg/sqlalchemy/orm/query.py,
line 869, in join
raise TypeError(unknown arguments: %s % ','.join(kwargs.iterkeys()))
TypeError: unknown arguments: onclause
Regards,
mk
--~--~-~--~~~---~--~~
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: postgres and/or generic enum support in sqlalchemy
Agreed - it makes more sense to have a generic enum type which may fall back to software should the db not actually support it. Having said that, in terms of using (selecting/updating) an enum field the syntax should be quite similar across implementations. While I'm not familiar with databases other than mysql/pg it would seem that aside from the syntax for creating an enum and getting the values that compose an enum there wouldn't have to be anything dialect specific for it. Would it, based on the above assumption, not be possible to have a generic enum type that figures out what to do based on what type of database we are connected to without needing to specify MSEnum or the currently ficticious PGEnum? Or is this what you are suggesting and I have misunderstood it? If it comes in 0.6, thats fine by me - we can work around it for now. As it stands SQLAlchemy is probably one of the most awesome libraries i've ever worked with :). Damian On Wed, May 27, 2009 at 4:49 PM, Michael Bayer mike...@zzzcomputing.comwrote: from my perspective my comments on the ticket still stand - the MySQL specificness has to be removed and the interface regarding encoding and such should be simplified.The MySQL import and __supported business should be removed as well - I know the Interval type is doing the same thing with a dialect-specific import, but that one is wrong too - there is a better approach in the 0.6 branch. MySQL's MSEnum would be present in the dialect's colspecs dictionary as Enum:MSEnum. For Postgresql, we'd also add PGEnum and place that in PG's colspecs dict.I would prefer this to be an 0.6 thing but it can likely be backported to 0.5 without much difficulty. On May 27, 2009, at 11:07 AM, Damian wrote: Hi, http://www.sqlalchemy.org/trac/ticket/1109 Is this still being worked on? In particular it would be interesting to get this running for postgres for us. If not, I would like to have a go at it in the near future, or am happy to help test if someone else is working on this. Cheers, Damian --~--~-~--~~~---~--~~ 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: onclause in join in 5.3?
query.join() takes an onclause as in the following:
query.join((target, onclause), (target2, onclause2), ...)
the onclause can be an expression or a named relation.
see the examples in
http://www.sqlalchemy.org/docs/05/ormtutorial.html#querying-with-joins
.
On May 27, 2009, at 11:54 AM, Marcin Krol wrote:
Hello everyone,
Is there such a thing?
I have to specify onclause since Host - Reservation is many-to-many,
and if I don't specify onclause, I get exception Can't find any
foreign
key relationships between 'reservation' and 'hosts'.
Docs:
The onclause may be a string name of a relation(), or a class-bound
descriptor representing a relation.
What does class-bound descriptor representing a relation mean
anyway??
This results in exception:
rsvs = session.query(Reservation,
Host
).filter
(Reservation.project_id.in_(projids)).filter(Reservation.status
== 'pending').join(Host, onclause='hosts').all()
Traceback (most recent call last):
File stdin, line 1, in module
File string, line 1, in lambda
File
/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.5.3-py2.6.egg/
sqlalchemy/util.py,
line 212, in go
return fn(*args, **kw)
File
/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.5.3-py2.6.egg/
sqlalchemy/orm/query.py,
line 869, in join
raise TypeError(unknown arguments: %s %
','.join(kwargs.iterkeys()))
TypeError: unknown arguments: onclause
I do in fact have 'hosts' relation in Reservation:
mapper(Reservation, reservation_table,
properties={'email':relation(Email,order_by=Email.id),
'project':relation(Project, order_by=Project.id),
'hosts':relation(Host,
secondary=reservation_hosts_assoc_table,backref='reservation'),
'newhosts':relation(NewHost,
secondary=reservation_newhosts_assoc_table,backref='reservationnh')}
)
So it's either onclause that is truly unknown to join() or smth else
throws it off.
I even tried this, don't know if it makes sense:
rsvs = session.query(Reservation, Host).join(Host,
onclause
=
'hosts
').filter
(Reservation.project_id.in_(projids)).filter(Reservation.status
== 'pending').all()
Traceback (most recent call last):
File stdin, line 1, in module
File string, line 1, in lambda
File
/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.5.3-py2.6.egg/
sqlalchemy/util.py,
line 212, in go
return fn(*args, **kw)
File
/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.5.3-py2.6.egg/
sqlalchemy/orm/query.py,
line 869, in join
raise TypeError(unknown arguments: %s %
','.join(kwargs.iterkeys()))
TypeError: unknown arguments: onclause
rsvs = session.query(Reservation).join(Host,
onclause='hosts').all()
Traceback (most recent call last):
File stdin, line 1, in module
File string, line 1, in lambda
File
/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.5.3-py2.6.egg/
sqlalchemy/util.py,
line 212, in go
return fn(*args, **kw)
File
/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.5.3-py2.6.egg/
sqlalchemy/orm/query.py,
line 869, in join
raise TypeError(unknown arguments: %s %
','.join(kwargs.iterkeys()))
TypeError: unknown arguments: onclause
Regards,
mk
--~--~-~--~~~---~--~~
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.5.4p2 Tests failed
I did a static build of pysqlite 2.5.5 against sqlite 3.6.14. All tests pass except the single memory leak test. I'll see if i can isolate this for them. On May 27, 2009, at 11:21 AM, Melton Low wrote: The first time I installed pysqlite 2.5.5, my Mac 10.4 build fine but not on the 10.5. The solution from the pysqlite people was to build_static on 10.5. It seem to work the first time around. Unfortunately I was not able to get a clean install last night on either systems. So for the time begin I will stick with the Python included version. Regards, Mel On Wed, May 27, 2009 at 9:05 AM, Michael Bayer mike...@zzzcomputing.com wrote: with the latest pysqlite (2.5.5) I also get a huge (192) amount of failures with many versions of SQLalchemy, including p1 and p2. I had noticed that pysqlite's development seemed to be going further with changes that make the library harder to develop against, but particularly disturbing is that one of our memory leak tests seems to reveal a memory leak within pysqlite.So overall this is a very disturbing result and I would recommend against using the non- python included pysqlite. On May 27, 2009, at 10:51 AM, Melton Low wrote: Hi Michael, I re-build my systems last night - zapped Python, Mercurial, Sqlite, pysqlite, psycopg, and SQLAlchemy. You 0.5.4p1 test suite ran perfectly as long as I only have the version of pysqlite included with Python. As I am going out of town, I decided to leave 0.5.4p2 alone for the time being. As soon as I can I will try installing it and will let you know. Mel On Wed, May 27, 2009 at 8:21 AM, Michael Bayer mike...@zzzcomputing.com wrote: the changes between 0.5.4p1 and 0.5.4p2 are extremely minimal and I've pasted them at http://paste.pocoo.org/show/119367/ .Additionally here they are passing for py2.4 and py2.5 at http://groovie.org:8012/ which is an intel linux platform. I run the tests with 2.6 here on an intel mac. In particular the PPC tests seem to be running with pre-existing tables from a previous run - run the tests with --dropfirst to clear out all pre-existing rables. For the intel, my guess would be the wrong version of SQLAlchemy is being tested (like an early 0.5 version perhaps). On May 26, 2009, at 11:34 PM, Melton Low wrote: Hi, I just installed the just released version 0.5.4p2. 3 of the tests failed on my Intel Mac and 97 failures on my PPC Mac. With the previous version 0.5.4p1, all tests ran successfully on both systems. The successfull tests were ran with the stock version of psqlite bundled with Python. Pysqlite 2.5.5 was installed over the standard version bundled with Python. I did not re-run the SqlAlchemy test suites after installing pysqlite 2.5.5. Earlier today I installed the latest release of Sqlite 3.6.14.2. I have attached my installation log and the test result logs for both systems. Since I am just learning SQLAlchemy, the failed tests probably will not affect me. In any case, I will be reverting back to 0.5.4p1. Hopefull, the log entries can help you isolate the problems. My environment: Mac OS X 10.5.7 Intel MacBook Python 2.6.2 pysqlite 2.5.5 psycopg2 2.0.11 Mac OS X 10.4.11 PowerPC Python 2.6.2 pysqlite 2.5.5 psycopg2 2.0.11 Regards, Mel Intel SQLAlchemy-0.5.4p2 TESTs.txtPPC SQLAlchemy-0.5.4p2 installlog.txtPPC SQLAlchemy-0.5.4p2 TESTs.txtIntel SQLAlchemy-0.5.4p2 installlog.txt --~--~-~--~~~---~--~~ 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: onclause in join in 5.3?
query.join() takes an onclause as in the following:
query.join((target, onclause), (target2, onclause2), ...)
the onclause can be an expression or a named relation.
see the examples in
http://www.sqlalchemy.org/docs/05/ormtutorial.html#querying-with-joins
.
On May 27, 2009, at 11:54 AM, Marcin Krol wrote:
Hello everyone,
Is there such a thing?
I have to specify onclause since Host - Reservation is many-to-many,
and if I don't specify onclause, I get exception Can't find any
foreign
key relationships between 'reservation' and 'hosts'.
Docs:
The onclause may be a string name of a relation(), or a class-bound
descriptor representing a relation.
What does class-bound descriptor representing a relation mean
anyway??
This results in exception:
rsvs = session.query(Reservation,
Host
).filter
(Reservation.project_id.in_(projids)).filter(Reservation.status
== 'pending').join(Host, onclause='hosts').all()
Traceback (most recent call last):
File stdin, line 1, in module
File string, line 1, in lambda
File
/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.5.3-py2.6.egg/
sqlalchemy/util.py,
line 212, in go
return fn(*args, **kw)
File
/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.5.3-py2.6.egg/
sqlalchemy/orm/query.py,
line 869, in join
raise TypeError(unknown arguments: %s %
','.join(kwargs.iterkeys()))
TypeError: unknown arguments: onclause
I do in fact have 'hosts' relation in Reservation:
mapper(Reservation, reservation_table,
properties={'email':relation(Email,order_by=Email.id),
'project':relation(Project, order_by=Project.id),
'hosts':relation(Host,
secondary=reservation_hosts_assoc_table,backref='reservation'),
'newhosts':relation(NewHost,
secondary=reservation_newhosts_assoc_table,backref='reservationnh')}
)
So it's either onclause that is truly unknown to join() or smth else
throws it off.
I even tried this, don't know if it makes sense:
rsvs = session.query(Reservation, Host).join(Host,
onclause
=
'hosts
').filter
(Reservation.project_id.in_(projids)).filter(Reservation.status
== 'pending').all()
Traceback (most recent call last):
File stdin, line 1, in module
File string, line 1, in lambda
File
/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.5.3-py2.6.egg/
sqlalchemy/util.py,
line 212, in go
return fn(*args, **kw)
File
/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.5.3-py2.6.egg/
sqlalchemy/orm/query.py,
line 869, in join
raise TypeError(unknown arguments: %s %
','.join(kwargs.iterkeys()))
TypeError: unknown arguments: onclause
rsvs = session.query(Reservation).join(Host,
onclause='hosts').all()
Traceback (most recent call last):
File stdin, line 1, in module
File string, line 1, in lambda
File
/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.5.3-py2.6.egg/
sqlalchemy/util.py,
line 212, in go
return fn(*args, **kw)
File
/usr/local/lib/python2.6/site-packages/SQLAlchemy-0.5.3-py2.6.egg/
sqlalchemy/orm/query.py,
line 869, in join
raise TypeError(unknown arguments: %s %
','.join(kwargs.iterkeys()))
TypeError: unknown arguments: onclause
Regards,
mk
--~--~-~--~~~---~--~~
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: Automatically filtering all queries
On 27 май, 18:22, Michael Bayer mike...@zzzcomputing.com wrote: On May 27, 2009, at 4:25 AM, Denis S. Otkidach wrote: class LimitingQuery(Query): def get(self, ident): return Query.get(self.populate_existing(), ident) def __iter__(self): return Query.__iter__(self.private()) @_generative() def private(self): crit = (self._entities[0].mapper.class_.public == True) if self._criterion: self._criterion = crit else: self._criterion = crit full test case attached. Thanks a lot! But the test doesn't cover all use-cases. Here is one that fails: count1 = sess.query(Address).count() count2 = len(sess.query(Address).all()) assert count1==count2, '%d!=%d' % (count1, count2) you'd have to hack .count() as well in a similar fashion. Probably I have to hack something to insure proper subqueries construction. I believe this is wrong way. Hacked .get() and .from_statement() method guarantee that if missed something I'll get an exception, while in this case I have to come over hard to detect bug first (and even seeing the bug doesn't point me to yet another method I have to fix). I'll try to use my query with get() and from_statement() hacked and fallback to filtering everything manually if it won't work. --~--~-~--~~~---~--~~ 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: postgres and/or generic enum support in sqlalchemy
On May 27, 2009, at 12:09 PM, Dimmich Damian wrote: Would it, based on the above assumption, not be possible to have a generic enum type that figures out what to do based on what type of database we are connected to without needing to specify MSEnum or the currently ficticious PGEnum? Or is this what you are suggesting and I have misunderstood it? that's what im suggesting, since that's how all of our regular types work anyway. You specify String, when it comes time to do something database specific it imports PGString or similar. 0.6 has scaled down the need for dialect-specific types but the concept is the same. --~--~-~--~~~---~--~~ 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] Accessing classes a table away in a mapper - why does this work?
Hi,
I have been struggling with trying to create relations which reference
objects a couple of tables away.
e.g
Sentence has many entities
Entity has many NormalisedVersion
NormalisedVersion has one Gene
kind of thing
and was trying to link from Sentence to genes directly.
secondary = entities_table.join(entities_genes_normalised_table,
onclause=and_(entities_table.c.entity_id==entities_genes_normalised_table.c.entity_id,
entities_table.c.deleted == 0 )).alias(fubar)
mapper(Sentence, sentences_table, properties={ genes:relation(Gene,
primaryjoin=sentences_table.c.sentence_id ==
secondary.c.entities_sentence_id,
secondaryjoin=secondary.c.entities_genes_normalised_entrez_gene_id ==
genes_table.c.entrez_gene_id,
viewonly = True,
secondary=secondary)
I spent ages on this hacking/reading/getting frustrated and finally
tried putting the alias call on the end of the secondary join, which
makes it work perfectly.
Without it calling sentence.genes gives me all of the genes found in
all of the sentences. So my question is really ... why does this work?
What effect does the alias have?
Many thanks in advance,
Nathan
--~--~-~--~~~---~--~~
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: Automatically filtering all queries
On May 27, 2009, at 12:20 PM, Denis S. Otkidach wrote: Probably I have to hack something to insure proper subqueries construction. uh yeah if subqueries are happening, that makes things more complicated too. But the other DB tools you're comparing us against probably have a lot less subquery capability than we do. --~--~-~--~~~---~--~~ 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: Accessing classes a table away in a mapper - why does this work?
On May 27, 2009, at 12:23 PM, Nathan Harmston wrote:
Hi,
I have been struggling with trying to create relations which reference
objects a couple of tables away.
e.g
Sentence has many entities
Entity has many NormalisedVersion
NormalisedVersion has one Gene
kind of thing
and was trying to link from Sentence to genes directly.
secondary = entities_table.join(entities_genes_normalised_table,
onclause
=
and_
(entities_table
.c.entity_id==entities_genes_normalised_table.c.entity_id,
entities_table.c.deleted == 0 )).alias(fubar)
mapper(Sentence, sentences_table, properties={ genes:relation(Gene,
primaryjoin=sentences_table.c.sentence_id ==
secondary.c.entities_sentence_id,
secondaryjoin=secondary.c.entities_genes_normalised_entrez_gene_id ==
genes_table.c.entrez_gene_id,
viewonly = True,
secondary=secondary)
I spent ages on this hacking/reading/getting frustrated and finally
tried putting the alias call on the end of the secondary join, which
makes it work perfectly.
Without it calling sentence.genes gives me all of the genes found in
all of the sentences. So my question is really ... why does this work?
What effect does the alias have?
the alias has the effect of converting the join expression to a fully
parenthesized subquery with a name, thus encapsulating its data as a
relation (a relation in the Codd sense of the word). its the
difference between:
select * from table, someothertable join somethirdtable on
someothertable.id=somethirdtable.foo where table.id=somethirdtable.bar
and
select * from table, (select * from someothertable join somethirdtable
on someothertable.id=somethirdtable.foo) as anon_1 where table.id =
anon_1.bar
in the first case, we have a FROM clause that isn't even legal on many
databases. in the second we have a FROM clause that selects from two
distinct selectables.
--~--~-~--~~~---~--~~
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: postgres and/or generic enum support in sqlalchemy
Great :). So, what needs doing to get Enum support? The ticket, which is part of the 0.5.5 roadmap mentions adding tests, would having tests be sufficient for getting it into 0.5.5? Is it still worth writing a posgres/enum PGEnum type for 0.5.x and integrating it with the patch that is in the ticket above? I have no idea how hard it would be to modify 0.5 to support an enum type in a databases colspecs.. It would seem that the approach in the ticket would be able to expose the same interface in 0.5/0.6 even if the internals changed. Cheers, Damian On Wed, May 27, 2009 at 5:22 PM, Michael Bayer mike...@zzzcomputing.comwrote: On May 27, 2009, at 12:09 PM, Dimmich Damian wrote: Would it, based on the above assumption, not be possible to have a generic enum type that figures out what to do based on what type of database we are connected to without needing to specify MSEnum or the currently ficticious PGEnum? Or is this what you are suggesting and I have misunderstood it? that's what im suggesting, since that's how all of our regular types work anyway. You specify String, when it comes time to do something database specific it imports PGString or similar. 0.6 has scaled down the need for dialect-specific types but the concept is the same. --~--~-~--~~~---~--~~ 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: postgres and/or generic enum support in sqlalchemy
On May 27, 2009, at 12:39 PM, Dimmich Damian wrote: Great :). So, what needs doing to get Enum support? The ticket, which is part of the 0.5.5 roadmap mentions adding tests, would having tests be sufficient for getting it into 0.5.5? tests plus the cleanup discussed. Is it still worth writing a posgres/enum PGEnum type for 0.5.x and integrating it with the patch that is in the ticket above? I have no idea how hard it would be to modify 0.5 to support an enum type in a databases colspecs.. It would seem that the approach in the ticket would be able to expose the same interface in 0.5/0.6 even if the internals changed. the approach is more or less the same for 0.5 and 0.6. Yes, there would be a PGEnum in the postgres.py module. Cheers, Damian On Wed, May 27, 2009 at 5:22 PM, Michael Bayer mike...@zzzcomputing.com wrote: On May 27, 2009, at 12:09 PM, Dimmich Damian wrote: Would it, based on the above assumption, not be possible to have a generic enum type that figures out what to do based on what type of database we are connected to without needing to specify MSEnum or the currently ficticious PGEnum? Or is this what you are suggesting and I have misunderstood it? that's what im suggesting, since that's how all of our regular types work anyway. You specify String, when it comes time to do something database specific it imports PGString or similar. 0.6 has scaled down the need for dialect-specific types but the concept is the same. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
