[sqlalchemy] Changing the order_by with Result-Set mapping
I'm finally getting a bit more at ease with the more complex aspects of SQLAlchemy. But yet again, I still need some help. Now I'm trying to change the ordering from my mapper's default. My Signal mapper was defined to order by the signal_name (using an AssociationProxy). That has been working. With the below new query, I'd like to order using an additional table's column: signal_type_size_order. u1_join = sql.join(db.signals, db.module_inputs, sql.and_(db.module_inputs.c.module_id == m.module_id, db.module_inputs.c.signal_id == db.signals.c.signal_id)) u2_join = sql.join(db.signals, db.module_outputs, sql.and_(db.module_outputs.c.module_id == m.module_id, db.module_outputs.c.signal_id == db.signals.c.signal_id)) sig_id_sel = sql.select([db.signals.c.signal_id], from_obj=[u1_join]). \ union(sql.select([db.signals.c.signal_id], from_obj=[u2_join])) # Construct a query to gather the signals from the above UNION sel = sql.select([db.signals, db.signal_types], db.signals.c.signal_id.in_(sig_id_sel), from_obj=[sql.join(db.signals, db.signal_types)], use_labels=True, order_by=[db.signal_types.c.signal_type_size_order, db.signals.c.signal_name]) res = model.session.query(domain_model.Signal).instances(sel.execute(), sql.class_mapper(domain_model.SignalType)) Everything is perfect up through the sel assignment. sel evaluates to the below query which returns a correct order from the sqlite3 CLI. Something is wrong with my res assignment line. (I am using 0.3.5 at least until my unit tests pass again.) SELECT signals.signal_id AS signals_signal_id, signals.signal_name AS signals_signal_name, signals.signal_description AS signals_signal_description, signals.signal_units_id AS signals_signal_units_id, signals.signal_type_id AS signals_signal_type_id, signals.signal_range AS signals_signal_range, signal_types.signal_type_id AS signal_types_signal_type_id, signal_types.signal_type_name AS signal_types_signal_type_name, signal_types.signal_type_ctype AS signal_types_signal_type_44f9, signal_types.signal_type_size_order AS signal_types_signal_type_e087, signal_types.signal_type_description AS signal_types_signal_type_f7d7 FROM signals JOIN signal_types ON signal_types.signal_type_id = signals.signal_type_id WHERE signals.signal_id IN (SELECT signals.signal_id AS signal_id FROM signals JOIN module_inputs ON module_inputs.module_id = ? AND module_inputs.signal_id = signals.signal_id UNION SELECT signals.signal_id AS signal_id FROM signals JOIN module_outputs ON module_outputs.module_id = ? AND module_outputs.signal_id = signals.signal_id) ORDER BY signal_types.signal_type_size_order, signals.signal_name --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] remove duplicates - DISTINCT constraint
I need to populate a table with author names for example the table name is author and it has a field authorname of type text. authorname has the following values: W. Shakespear J. Smith W. Shakespear R. Williams K. Winslet ... and so on. Then I want SQLAlchemy to remove the duplicates, so that W. Shakespear will be saved only once. How can I achieve this using SQLAlchemy? 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: remove duplicates - DISTINCT constraint
Disrupt07 ha scritto: I need to populate a table with author names for example the table name is author and it has a field authorname of type text. authorname has the following values: W. Shakespear J. Smith W. Shakespear R. Williams K. Winslet ... and so on. Then I want SQLAlchemy to remove the duplicates, so that W. Shakespear will be saved only once. How can I achieve this using SQLAlchemy? Thanks Primary Key or unique constraint do this... anyway if these names are stored in a list or something similar a sequence... firt of all put it in a type 'set' ...so automatically your problem is gone.. In [21]: set( [ 'W. Shakespear', 'J. Smith', 'R. William', 'W. Shakespear', 'J. Smith']) Out[21]: set(['W. Shakespear', 'R. William', 'J. Smith']) Glauco -- ++ Glauco Uri - Programmatore glauco(at)allevatori.com Sfera Carta Software® [EMAIL PROTECTED] Via Bazzanese,69 Casalecchio di Reno(BO) - Tel. 051591054 ++ --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: remove duplicates - DISTINCT constraint
Disrupt07 ha scritto: Thanks. But what do you mean by a type 'set' ? Is this a functionality of SQLAlchemy or of a list or some programming language? What's your data sources ? a python list? a text file? another DB? You can do a sequence of insert into with a unique contraint over that field so duplicate records will not be stored... the set otherwise is only usable if you have a list (from a turbo Gear Form form for example ) a = Authors() my_authors= [ 'W. Shakespear', 'J. Smith', 'R. William', 'W. Shakespear', 'J. Smith'] for x in set( my_authors ) a.new( x ) Please explain how to do it in SQLAlchemy because I want to populate the table initially with all of the data, then I want SQLAlchemy to save only unique distinct values. Glauco -- ++ Glauco Uri - Programmatore glauco(at)allevatori.com Sfera Carta Software® [EMAIL PROTECTED] Via Bazzanese,69 Casalecchio di Reno(BO) - Tel. 051591054 ++ --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] parameter binding in connection.execute()
I know I can have bound parameters in engine.text(): t = engine.text(select foo from mytable where lala=:hoho) r = t.execute(hoho=7) ...but I need the same for connection.execute(), since I am using temporary tables and they are not accessible via engine.text Should I resort to manual quoting? tnx --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: multiple table inheritance problem
Julien Cigar wrote: Another quick question, relative to multiple inheritance. Is it common to keep a back reference to the parent class within the child class ? u mean theclass.__bases__? or what? The idea behind this is that I want to be able to retrieve the child from the parent. why u need it? also, I know that it's not possible under pure OO programming, but I wondered if it's dirty to do it like that ? it is possible, depends on the language and framework. e.g. in smalltalk u can ask which classes are my subclasses. But this is talking about classes, not instances. what u mean child and parent here? if this is about tables, u _can_ have pure child, but that is only incomplete/partial data; full child is all parent attributes plus all child attributes (parent.join(child), that is). And for each full child (complete object), u have one parent-only data and one child-only data... and they are 1:1, so u can get one from another and vice-versa. i think u either have wrong model or something i don't get? if u have different attributes for different Content subtypes, then that means subclasses and eventualy polymorphism. Or u go the single_typee (singletable) way, and put all stuff in Content, and just check for presence/absence of some attributes. For example I have something like: assign_mapper(session.context, content.Content, t_contents, properties=dict(state = relation(state.State))) assign_mapper(session.context, folder.Folder, t_folders, inherits=content.Content.mapper, properties = dict(data = relation(content.Content, secondary=t_nodes, lazy=False))) assign_mapper(session.context, page.Page, t_pages, inherits=content.Content.mapper, properties=dict(content=relation(content.Content, backref='page'))) container = Folder.get(folder_id) # I get a Folder object for data in container.data: # Here I have Content objects # and I want to be able to see if the Content is a Page or a News or ... if data.page: ... elif data.news: ... ... the other idea is to add a string like page or news in the Content table (or another table) .. what do you think is the best solution ? Thanks, Julien Julien wrote: OK it was that ! Thanks On Mon, 2007-04-02 at 12:19 -0400, Michael Bayer wrote: defintiely do not call flush() on root_folder, call it for the session overall. thats probably the issue, since its failing to get the full set of child objects properly in the flush. On Apr 2, 2007, at 10:44 AM, Julien Cigar wrote: Hello, I'm playing a bit with Multiple Table Inheritance (the Non-polymorphic way) and I got a problem which I have copy/paste on http://rafb.net/p/HAhx8p22.html Here is the basic idea of what I want to do (it's a kind of CMS): - Content is the base class for all contents (Pages, News, Events, Folder, ...) - Folder is a Content which can contain one or more Content (Node-like structure) I suspect that the problem is that I don't have a sequence for my primary keys in my subclasses .. ? Thanks, Julien --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Concurrency problem
On Apr 2, 2007, at 10:35 PM, Barry Warsaw wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Our populate_instance() checks the isnew flag and if this is true, it does not call our initialization stuff. Now, I don't really see the isnew flag documented, so I assumed it was only called when the object was initially loaded from the database, but apparently it's true /every/ time the object is loaded. So when I session.expire() it and then subsequently load it, populate_instance() gets called with isnew=True. The causes our application-specific initialization stuff to get called on an existing MailList object, and this is what blows away our lock attribute. I guess that if the documentation for isnew in append_result() is the same as isnew in populate_instance() then indeed we'll need that extra check. Is that correct? OK, yes, im sorry about the lack of docs for isnew...sometimes i subconsciously want to see how long it takes for someone to ask me about something (bad habit) although in this case that flag is over a year old and i think youre the first :) isnew is used to indicate that we are going to populate the instance with data from the database, *and* that this particular row is the first row in the result which has indicated the presence of this entity (i.e. the primary key points to it). this implies that populate_instance() can be called *multiple times* for the instance, if multiple successive rows all contain its particular primary key. this is how SA deals with the products of eager loads and other joins, where many rows contain the same entity identity in a number of successive rows corresponding to the total product of eagerly loaded child items - the normal populate_instance() just delegates the populate this attribute task onto MapperProperty objects that represent each attribute. for the MapperProperty objects that represent eager loaders, they unconditionally send the row off to their child mappers to look at the row, where it then goes into its own populate_instance() process with a possibly True isnew flag. so basically, isnew means populate attributes and if its false it means we already did this entity. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: new features in 0.3.6
On Apr 3, 2007, at 5:58 AM, Glauco wrote:
In [1]: t = TipoFigura()
In [1]: t.mapper.mapped_table
Table('tipo_figura',DynamicMetaData(),
Column('codice',PGChar(length=1),primary_key=True,nullable=False),
Column('descrizione',PGText
(length=None),nullable=False),schema=None)
In [2]: s = select( [t.c.codice], from_obj=[t.mapper.mapped_table],
use_labels=True)
In [3]: ret = session.query( t.mapper )
In [4]: ret.instances( s.execute() )
2007-04-03 11:27:26,575 sqlalchemy.engine.base.Engine.0x..74 INFO
SELECT tipo_figura.codice AS tipo_figura_codice
FROM tipo_figura
/home//build/bdist.linux-i686/egg/sqlalchemy/engine/base.py in
_convert_key(self, key)
NoSuchColumnError: Could not locate column in row for column
'tipo_figura.descrizione'
1) Why this error?? the query is perfect and there is n reason
for check presence of the descrizione column. (the problem is
the same i found in previous mail)
the descrizone column is part of your mapping so the mapper will be
searching for it. understand that session.query() is *always* going
to load instances of the primary class - your codice column is in
*addition* to the main mapped class (but in this case, its already
part of it).
if you just want to select codice alone with no mapped objects,
execute the select() statement by itself without using the ORM (i.e.
session.query).
2) and why if i put all columns i lost use_label features ?
s = select( [t.c.codice, t.c.descrizione], from_obj=
[t.mapper.mapped_table], use_labels=True)
ret.instances( s.execute() )[0].c.keys()
2007-04-03 11:39:56,921 sqlalchemy.engine.base.Engine.0x..74 INFO
SELECT tipo_figura.codice AS tipo_figura_codice,
tipo_figura.descrizione AS tipo_figura_descrizione
FROM tipo_figura
2007-04-03 11:39:56,921 sqlalchemy.engine.base.Engine.0x..74 INFO {}
Out[15]: ['codice', 'descrizione']
the instances method does not return result rows - it returns
object instances. the c attribute there is off of one of your
mapped classes and the two column names are the columns off of your
original Table object.
to get the columns you want
s = select( [t.c.codice, t.c.descrizione], from_obj=
[t.mapper.mapped_table], use_labels=True).execute()[0].keys()
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: new features in 0.3.6
Michael Bayer ha scritto:
On Apr 3, 2007, at 5:58 AM, Glauco wrote:
In [1]: t = TipoFigura()
In [1]: t.mapper.mapped_table
Table('tipo_figura',DynamicMetaData(),
Column('codice',PGChar(length=1),primary_key=True,nullable=False),
Column('descrizione',PGText
(length=None),nullable=False),schema=None)
In [2]: s = select( [t.c.codice], from_obj=[t.mapper.mapped_table],
use_labels=True)
In [3]: ret = session.query( t.mapper )
In [4]: ret.instances( s.execute() )
2007-04-03 11:27:26,575 sqlalchemy.engine.base.Engine.0x..74 INFO
SELECT tipo_figura.codice AS tipo_figura_codice
FROM tipo_figura
/home//build/bdist.linux-i686/egg/sqlalchemy/engine/base.py in
_convert_key(self, key)
NoSuchColumnError: Could not locate column in row for column
'tipo_figura.descrizione'
1) Why this error?? the query is perfect and there is n reason
for check presence of the descrizione column. (the problem is
the same i found in previous mail)
the descrizone column is part of your mapping so the mapper will be
searching for it. understand that session.query() is *always* going
to load instances of the primary class - your codice column is in
*addition* to the main mapped class (but in this case, its already
part of it).
if you just want to select codice alone with no mapped objects,
execute the select() statement by itself without using the ORM (i.e.
session.query).
Yes, but i lost generative methods filter_by
2) and why if i put all columns i lost use_label features ?
s = select( [t.c.codice, t.c.descrizione], from_obj=
[t.mapper.mapped_table], use_labels=True)
ret.instances( s.execute() )[0].c.keys()
2007-04-03 11:39:56,921 sqlalchemy.engine.base.Engine.0x..74 INFO
SELECT tipo_figura.codice AS tipo_figura_codice,
tipo_figura.descrizione AS tipo_figura_descrizione
FROM tipo_figura
2007-04-03 11:39:56,921 sqlalchemy.engine.base.Engine.0x..74 INFO {}
Out[15]: ['codice', 'descrizione']
the instances method does not return result rows - it returns
object instances. the c attribute there is off of one of your
mapped classes and the two column names are the columns off of your
original Table object.
to get the columns you want
s = select( [t.c.codice, t.c.descrizione], from_obj=
[t.mapper.mapped_table], use_labels=True).execute()[0].keys()
Yes in fact this is only an example.. my purpose is to implement this
system for do generic search function over complex mapper , for example
my first work is a mapper over 19 tables with 1 relation n:m and 2 1:n..
i need some features from select function and other from generative qry
over mapper... but it's difficult to concentrate all cases in one select
function.
Glauco
--
++
Glauco Uri - Programmatore
glauco(at)allevatori.com
Sfera Carta Software® [EMAIL PROTECTED]
Via Bazzanese,69 Casalecchio di Reno(BO) - Tel. 051591054
++
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: new features in 0.3.6
On Apr 3, 2007, at 11:26 AM, Glauco wrote:
Yes, but i lost generative methods filter_by
First of all, filter_by() has no interaction with instances(). with
instances(), no SQL is constructed by the Query object - it takes the
result set of whatever exact select() statement youve constructed and
thats it. its roughly equivalent functionality to hibernate's
session.createSQLQuery(). its used when you know the exact SQL you
want to issue and youd rather not have Query build the SQL for you.
so as far as getting columns in conjunction with filter_by(), we are
only talking about add_column() and add_entity().
so basically youre saying that this:
session.query(MyClass).filter_by(foo='bar').list()
will return instances of MyClass, while this:
session.query(MyClass).filter_by(foo='bar').add_column
('somecolumn').list()
will return a result set of somecolumn. but *not* MyClass. thats
probably not what people expect.
the only way I can see this happening would be some new method:
session.query(MyClass).filter_by(foo='bar').remove_entity
(MyClass).add_column('somecolumn').list()
or perhaps:
session.query(MyClass).filter_by(foo='bar').set_column
('somecolumn').list()
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Database Disconnection Detection
check out the very latest trunk and apply this patch: Index: lib/sqlalchemy/databases/oracle.py === --- lib/sqlalchemy/databases/oracle.py (revision 2487) +++ lib/sqlalchemy/databases/oracle.py (working copy) @@ -409,6 +409,8 @@ if context is not None: context._rowcount = rowcount +def is_disconnect(self, e): +return isinstance(e, self.dbapi.DatabaseError) and ORA-03114 in str(e) OracleDialect.logger = logging.class_logger(OracleDialect) see if that throws sqlalchemy.exceptions.SQLError for all disconnect cases. On Apr 3, 2007, at 12:28 PM, Greg Copeland wrote: I've done a little bit more testing and have found some interesting results. If a connection has previously been established and it then fails, I get a cx_Oracle exception. If a connection has yet to be established, I get a SQLAlchemy exception. These are the guys I'm seeing. If it would be helpful, I can provide a stack trace too. Nonetheless, I assume these cases are covered by the current, ongoing effort. cx_Oracle.DatabaseError instance at 0x1917bb6c sqlalchemy.exceptions.DBAPIError instance at 0x1917bb4c I was catching these with a generic, catchall, Exception handler but decided I couldn't live with that, out of fear I'd wind up in an infinite loop. I am now using, except (sa.exceptions.DBAPIError, cx_Oracle.DatabaseError), e:. While I don't like having the cx_Oracle exception in there, at least I have explicit handling with the unknown/unexpected case broken out now. Greg P.S. Keep up the good work guys! On Apr 2, 5:06 pm, Michael Bayer [EMAIL PROTECTED] wrote: our current work with auto-reconnect is checked in to the trunk, including exception catches around rollbacks and commits. so far it does seem to work with postgres, in a rudimental test. the oracle routine is still not checked 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: simple many-to-many select
On Apr 3, 12:39 pm, Alexandre CONRAD [EMAIL PROTECTED] wrote: And I'd like to find all attachments from one client. But as there's a weak (secondary) table in between, I can no longer have something like: model.Attachment.id_client==c.client.id How can I achieve this ? using a criterion like and_(Attachment.c.attachment_id==attachment_has_clients.c.id_attachment, attachment_has_clients.c.id_client=Client.c.cllient_id) or you could use the built in stuff: session.query(Attachment).filter_by(clients=someclient).list() ps: I'm using assign_mapper. Regards, -- Alexandre CONRAD --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Concurrency problem
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Apr 3, 2007, at 9:35 AM, Michael Bayer wrote: OK, yes, im sorry about the lack of docs for isnew...sometimes i subconsciously want to see how long it takes for someone to ask me about something (bad habit) although in this case that flag is over a year old and i think youre the first :) Heh, um glad to help? :) so basically, isnew means populate attributes and if its false it means we already did this entity. Cool, thanks. That tells me what I need to know! - -Barry -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (Darwin) iQCVAwUBRhKhIXEjvBPtnXfVAQIJAAP/XRTViMrga4tOECEJD0OkacjfcUCVQOV3 lDUTPtu09se0uiPlZvfW03w7BBniguupLFSUOPBQHlf/Zh3SWG585lF9HeNP0ocC viBepxS+2rm1Y3CZVnXvfNwZYUfpn1vwns2+cOFoHnwR8scdexK9UTAG7yIH/ICs IzNwbKB/g7U= =bDwL -END PGP SIGNATURE- --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
