Re: [sqlalchemy] sql custom function
Il 10/02/2015 09:33, jo ha scritto:
Hi all,
I can't realize how to create a sql custom function. I need to
transform a string column value into a number as in:
def mese(par):
mm = dict(A=1,B=2,C=3,D=4,E=5,H=6,L=7,M=8,P=9,R=10,S=11,T=12)
return mm[par.upper()]
I tried to use it in a query as this one below, but it doesn't work:
session.query(Ana).filter(Ana.c.mese==mese(sa.func.substr(Ana.c.cfiscale,8,9))
Please, could someone help me to write a such function?
thanks
j
Hi Jo,
probably you forgot to register function?
Sure you can optimize this code, but this work.
from sqlalchemy import *
from sqlalchemy.orm import sessionmaker
e = create_engine('sqlite://')
metadata = MetaData()
t = Table('t', metadata, Column('v', String(1)))
metadata.create_all(e)
def mese(par):
mm = dict(A=1,B=2,C=3,D=4,E=5,H=6,L=7,M=8,P=9,R=10,S=11,T=12)
return mm[par.upper()]
conn = sessionmaker(e)().connection()
conn.connection.connection.create_function(mese,1,mese)
conn.execute( t.insert().values(v='P'))
conn.execute( t.insert().values(v='D'))
conn.execute(select v, mese(v) from t).fetchall()
[(u'P', 9), (u'D', 4)]
Glauco
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] complex in clause
Michael Bayer ha scritto: search the docs for tuple_. wonderful! thank you Gla -- Glauco Uri Prometeia SpA Via G. Marconi, 43 - 40122 Bologna Via Gonzaga, 7 - 20123 Milano Via Tirso, 26 - 00198 Roma Italia e-mail : glauco@prometeia.it phone : +39 051 6480911 --- Il contenuto e gli allegati di questo messaggio sono strettamente confidenziali, e ne sono vietati la diffusione, la riproduzione e l'uso non autorizzato. Il suo contenuto non costituisce impegno da parte della Società salvo accordo scritto tra quest'ultima ed il destinatario. Qualora il presente messaggio Le fosse pervenuto per errore, La preghiamo di comunicare immediatamente al mittente l'errata ricezione e di distruggere quanto ricevuto (compresi i file allegati) senza farne copia. Qualsivoglia utilizzo non autorizzato del contenuto di questo messaggio costituisce violazione dell'obbligo di non rivelare il contenuto della corrispondenza tra altri soggetti, salvo più grave illecito, ed espone il responsabile alle relative conseguenze. This e-mail (and any attachment(s)) is strictly confidential and for use only by intended recipient(s). Any use, distribution, reproduction or disclosure by any other person is strictly prohibited. The content of this e-mail does not constitute a commitment by the Company except where provided for in a written agreement between this e-mail addressee and the Company. If you are not an intended recipient(s), please notify the sender promptly and destroy this message and its attachments without reading or saving it in any manner. Any non authorized use of the content of this message constitutes a violation of the obligation to abstain from learning of the correspondence among other subjects, except for more serious offence, and exposes the person responsible to the relevant consequences. --- -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] complex in clause
What is the best practice for this case? select * from my_tbl where (a,b) in ((1,1),(2,2)) It seems to me that in clause is a column attribute so i don't figure how to sqlalchemyfy this where condition Thank you in advance Gla -- Glauco Uri Prometeia SpA Via G. Marconi, 43 - 40122 Bologna Via Gonzaga, 7 - 20123 Milano Via Tirso, 26 - 00198 Roma Italia e-mail : glauco@prometeia.it phone : +39 051 6480911 --- Il contenuto e gli allegati di questo messaggio sono strettamente confidenziali, e ne sono vietati la diffusione, la riproduzione e l'uso non autorizzato. Il suo contenuto non costituisce impegno da parte della Società salvo accordo scritto tra quest'ultima ed il destinatario. Qualora il presente messaggio Le fosse pervenuto per errore, La preghiamo di comunicare immediatamente al mittente l'errata ricezione e di distruggere quanto ricevuto (compresi i file allegati) senza farne copia. Qualsivoglia utilizzo non autorizzato del contenuto di questo messaggio costituisce violazione dell'obbligo di non rivelare il contenuto della corrispondenza tra altri soggetti, salvo più grave illecito, ed espone il responsabile alle relative conseguenze. This e-mail (and any attachment(s)) is strictly confidential and for use only by intended recipient(s). Any use, distribution, reproduction or disclosure by any other person is strictly prohibited. The content of this e-mail does not constitute a commitment by the Company except where provided for in a written agreement between this e-mail addressee and the Company. If you are not an intended recipient(s), please notify the sender promptly and destroy this message and its attachments without reading or saving it in any manner. Any non authorized use of the content of this message constitutes a violation of the obligation to abstain from learning of the correspondence among other subjects, except for more serious offence, and exposes the person responsible to the relevant consequences. --- -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: Formatting return of 'query'
Harish Vishwanath ha scritto: cut How can I modify this query to return something like : [(fname~lname~22~pwd)...] with '~' being preferred delimiter. SA return a list or record, what exactly you are searching for? a string or something else I would like to know if I can return something like above directly from the query itself. something like ? [ '~'.join(x) for x in qry.fetchall() ] Glauco --~--~-~--~~~---~--~~ 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: changing instance identity
jo ha scritto: Michael Bayer ha scritto: that is your own application changing the primary key of a loaded instance, which is a feature very old versions of SQLAlchemy did not support. Upgrade to any recent version of 0.4 or 0.5 and you wont have that issue anymore. I think I don't want to change this behavior , Michael, Simply I would like to realize that changes was done at current loaded instance to avoid flush it. cut Yes you have it, cls._state['original'].data but probably you must work over object stored in session Gla --~--~-~--~~~---~--~~ 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: SELECT statement inside in_
Sanjay ha scritto: I got that the obvious way is just writing the string query, i.e. query.filter(col IN SELECT col FROM some_table) something like this: query.filter( col.in_( select([col],...).correlate(None) )) Glauco --~--~-~--~~~---~--~~ 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: case_sensitive
jo ha scritto: Hi all, Trying to migrate from 0.3.10 to 0.5 I have this error: sqlalchemy.exc.ArgumentError: Unknown UniqueConstraint argument(s): 'case_sensitive' how can I define the case_sensitive=True for a unique constraint? thank you, j http://www.sqlalchemy.org/trac/browser/sqlalchemy/tags/rel_0_4_8/CHANGES case_sensitive=(True|False) setting removed from schema items, since checking this state added a lot of method call overhead and there was no decent reason to ever set it to False. Table and column names which are all lower case will be treated as case-insenstive (yes we adjust for Oracle's UPPERCASE style too). Glauco -- ++ Glauco Uri glauco(at)sferacarta.com Sfera Carta Software® info(at)sferacarta.com 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: query doctest
sandro dentella ha scritto: Hi, in a doctest I have:: str(q) == str(query.filter(User.first_name == 'ed')) True that works but the following fails, (both where built starting from the same session.query(User) q == query.filter(User.first_name == 'ed') True in another place comparing str fails just because a join has ON join condition inverted (but semantically equivalent). What's the correct way to test if two queries are semantically equivalent? Thanks sandro *:-) Hi sandro, did you want to compare compiled qry or its result? Gla -- ++ Glauco Uri glauco(at)sferacarta.com Sfera Carta Software® info(at)sferacarta.com 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] use func.* as adapter only
I don't know if is better to use the psycopg adapter. Can i use the funct.* function only as adapter? example: In [1]: aa = [1,2,3,4,] In [2]: print sa.func.in_( *aa ) in(:in, :in_1, :in_2, :in_3) how to obtain this? in ( 1,2,3,4 ) thank you Glauco -- ++ Glauco Uri glauco(at)sferacarta.com Sfera Carta Software® info(at)sferacarta.com 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: use func.* as adapter only
Michael Bayer ha scritto:
Glauco wrote:
I don't know if is better to use the psycopg adapter.
Can i use the funct.* function only as adapter?
example:
In [1]: aa = [1,2,3,4,]
In [2]: print sa.func.in_( *aa )
in(:in, :in_1, :in_2, :in_3)
how to obtain this?
in ( 1,2,3,4 )
perhaps you're looking for sa.func.in_(*[literal_column(x) for x in aa]) ?
not exactly.
In [13]: print sa.func.in_( *[sa.func.literal_column(x) for x in
[1,2,3]] ).compile()
in(literal_column(:literal_column), literal_column(:literal_column_1),
literal_column(:literal_column_2))
What i mean is similar to psycopg adapter:
class SQL_IN(object):
Adapt a tuple to an SQL quotable object.
def __init__(self, seq):
self._seq = seq
def getquoted(self):
# this is the important line: note how every object in the
# list is adapted and then how getquoted() is called on it
qobjs = [str(psycoadapt(o).getquoted()) for o in self._seq]
return '(' + ', '.join(qobjs) + ')'
__str__ = getquoted
I'm not sure what this accomplishes for you overall, however.
I know this is not a good practise of SA, but this is a small isolated
case. Here i don't have access to my mappers, so i create manually the
SQL and after all i use the engine to execute.
so i find useful to use adapter.
Thank you
Glauco
--
++
Glauco Uri
glauco(at)sferacarta.com
Sfera Carta Software® info(at)sferacarta.com
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: use func.* as adapter only
Michael Bayer ha scritto: from sqlalchemy import * aa = [1,2,3,4] print func.in_(*[literal_column(str(x)) for x in aa]) in(1, 2, 3, 4) this is what you asked for ? Yess! well done ! I'm using the sa.func.literal_column instead of sa.literal_column... Thank you one more time Michael Gla -- ++ Glauco Uri glauco(at)sferacarta.com Sfera Carta Software® info(at)sferacarta.com 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: problem with SHOW command
Manlio Perillo ha scritto:
Hi.
I'm having strange problems when I execute a SHOW command with PostgreSQL.
from sqlalchemy import create_engine, sql, __version__
print __version__
URL = 'postgres://xxx:[EMAIL PROTECTED]/xxx'
db = create_engine(URL, echo=True)
conn = db.connect()
query = sql.text('SHOW CLIENT_ENCODING')
r = conn.execute(query)
print r.scalar()
I've no problem:
0.3.10
2008-07-07 15:22:29,161 INFO sqlalchemy.engine.base.Engine.0x..d4 SHOW
CLIENT_ENCODING
2008-07-07 15:22:29,161 INFO sqlalchemy.engine.base.Engine.0x..d4 {}
UTF8
do you have the same problem with the fetchall()?
r = conn.execute( 'SHOW CLIENT_ENCODING' )
print r.fetchall()
Glauco
--
++
Glauco Uri
glauco(at)sferacarta.com
Sfera Carta Software® info(at)sferacarta.com
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: select in
Michael Bayer ha scritto: On Feb 11, 2008, at 6:36 AM, Glauco wrote: Hi all, What's the simplest way for do a simple: select * from myTable where id in (1,2,3); I've solved this by using Subquery but final qry isn't pretty as this one. mytable.select(mytable.c.id.in_([1,2,3])) Thank you michael, i've lost the in_ operator. Glauco -- ++ Glauco Uri glauco(at)sferacarta.com Sfera Carta Software® info(at)sferacarta.com 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] select in
Hi all, What's the simplest way for do a simple: select * from myTable where id in (1,2,3); I've solved this by using Subquery but final qry isn't pretty as this one. Thank you Glauco -- ++ Glauco Uri glauco(at)sferacarta.com Sfera Carta Software® info(at)sferacarta.com 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] SelectResults and group_by
Hi all for some reason i cannot work over the code before the result. The result was a SelectResults qry. Now i must do something like: MySelectResults.group_by( table.c.colname ).having( func.count( table.c.colname ) x ) Does anyone have any suggestion? Thank you Glauco -- ++ Glauco Uri glauco(at)sferacarta.com Sfera Carta Software® info(at)sferacarta.com 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] problem with cast in postgres
Hi all, i'm using the funct.cast in postgreSQL, but i thing the correct
dialect is not used
This is an example:
valore is a text field.
Scheda.select( tbl['scheda'].c.valore == '753' )
SELECT scheda.id_precedente AS scheda_id_precedente, scheda.stato_record
AS scheda_stato_record, scheda.ts_ultima_modifica AS
scheda_ts_ultima_modifica, scheda.cod_unita_di_misura AS
scheda_cod_unita_di_misura, scheda.id_figura_aziendale AS
scheda_id_figura_aziendale, scheda.id AS scheda_id, scheda.id_azienda AS
scheda_id_azienda, scheda.partite AS scheda_partite,
scheda.cod_caratteristica_rischio AS scheda_cod_caratteristica_rischio,
scheda.cod_attivita AS scheda_cod_attivita, scheda.id_produzione AS
scheda_id_produzione, scheda.data_rilevamento AS
scheda_data_rilevamento, scheda.id_unita_aziendale AS
scheda_id_unita_aziendale, scheda.id_operatore AS scheda_id_operatore,
scheda.valore AS scheda_valore
FROM scheda
WHERE scheda.valore = %(scheda_valore)s ORDER BY scheda.id
{'scheda_valore': '753'}
Scheda.select( func.cast( tbl['scheda'].c.valore, 'int') == '753' )
SQLError: (ProgrammingError) syntax error at or near , at character 751
'SELECT scheda.id_precedente AS scheda_id_precedente,
scheda.stato_record AS scheda_stato_record, scheda.ts_ultima_modifica AS
scheda_ts_ultima_modifica, scheda.cod_unita_di_misura AS
scheda_cod_unita_di_misura, scheda.id_figura_aziendale AS
scheda_id_figura_aziendale, scheda.id AS scheda_id, scheda.id_azienda AS
scheda_id_azienda, scheda.partite AS scheda_partite,
scheda.cod_caratteristica_rischio AS scheda_cod_caratteristica_rischio,
scheda.cod_attivita AS scheda_cod_attivita, scheda.id_produzione AS
scheda_id_produzione, scheda.data_rilevamento AS
scheda_data_rilevamento, scheda.id_unita_aziendale AS
scheda_id_unita_aziendale, scheda.id_operatore AS scheda_id_operatore,
scheda.valore AS scheda_valore \nFROM scheda \nWHERE
*cast(scheda.valore, %(cast)s)* = %(cast_1)s ORDER BY scheda.id'
{'cast': 'int', 'cast_1': '753'}
casting in Postgres use another syntax:
*cast(sche3086.valore as int)*
is this a bug or i'm missing to tell dialect?
Glauco
--
++
Glauco Uri
glauco(at)sferacarta.com
Sfera Carta Software® info(at)sferacarta.com
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: problem with cast in postgres
Michael Bayer ha scritto: CAST is its own contstruct: Scheda.select( cast( tbl['scheda'].c.valore, 'int') == '753' ) what doses it means? ahhh ops ! i've get cast from sqlalchemy.funct instead of sqlalchemy.sql anyway 'int' cannot work this is the proper use. Scheda.select( sqlalchemy.sql.cast( tbl['scheda'].c.valore, sqlalchemy.types.INT) == '753' ) Thank you Michael -- ++ Glauco Uri glauco(at)sferacarta.com Sfera Carta Software® info(at)sferacarta.com 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: deleting
imgrey ha scritto: Good Day sqlalchemy. I was searching, but didn't found a way to delete records from db not executing selection first. You must istantiate an istance of the obj to be deleted. This is an Object Manager, so all operation is available on object. or you can execute a plain string-sql direclty from engine connection = engine.connect() connection.execute(DELETE FROM a WHERE b = c) So, how to represent this SQL statement in slqalchemy ORM : DELETE FROM a WHERE b = c ? not it look like this : stuff = session.query(A).select_from(a_table).filter(A.c.b==c).first() session.delete(stuff) P.S. How to understand the following : Traceback (most recent call last): File threading.py, line 442, in __bootstrap self.run() File ./camper.py, line 173, in run session.delete(stuff) File /usr/lib/python2.4/site-packages/sqlalchemy/orm/scoping.py, line 74, in do return getattr(self.registry(), name)(*args, **kwargs) File /usr/lib/python2.4/site-packages/sqlalchemy/orm/session.py, line 849, i n delete self._delete_impl(object) File /usr/lib/python2.4/site-packages/sqlalchemy/orm/session.py, line 1007, in _delete_impl raise exceptions.InvalidRequestError(Instance '%s' is not persisted % mapp erutil.instance_str(obj)) InvalidRequestError: Instance '[EMAIL PROTECTED]' is not persisted seems your object is not attached to session. It occurs sometimes in threaded application in this code : from sqlalchemy.orm import mapper, relation, backref, create_session, scoped_session session = scoped_session(create_session) stuff = session.query(Path).select_from(f_table.join(u_table)).filter(User.c.id==theone.id).first() session.delete(stuff) i don't use scoped_session. but try instead stuff.delete() Glauco -- ++ Glauco Uri glauco(at)sferacarta.com Sfera Carta Software® info(at)sferacarta.com 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: Generative queries in SA0.3 ?
exhuma.twn ha scritto: I have seen that in SA 0.4 on can do something like: q = table.select() q = q.where(x=1) q = q.where(z=2) ... Is this also possible in SA 0.3? I want to build a web-page where a user can refine filters on the go, to perform a drill-down in a data set. Doing this with plain-text SQL is fairly easy, but doing it with SA is not as straight-forward. Considering this scenario, would it be possible to *remove* one such filter from a query? Say, remove the x=1 from the above query. You must post-pone rendering of qry where clause... where_clause = [] where_clause.append( cond1 ) where_clause.append( cond2 ) where_clause.append( cond3 ) you can at this point of programm remove some condition positinally or by content. where_clause.pop( cond2) or del where_clause[1] finally you can rendere your qry: your_base_qry = session.query( bla bla ) if where_clause: your_removed_qry = your_base_qry.filter( and_( *where_clause ) ) 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: aliased tables
Roger Demetrescu ha scritto:
On 9/27/07, Glauco [EMAIL PROTECTED] wrote:
Glauco, can you send a small test script reproducing the error ?
I'll gladly take a look at that...
Cheers,
Roger
Yes...
this is the example
session.query( UnitaAziendale ).order_by(Anagrafica.c.nome)
SNIP
Thanks Glauco...
But you forgot your Tables and mappers definition... ;)
isn't forgotten...
i dont' have table definition in SA but use reverse engeenering over an
existing DB,
and this mapper are huge...
anyway i try to give an idea with only some example of base table
implied in this example.
Table(u'unita_aziendale',
DynamicMetaData(),
Column(u'id',PGInteger(),primary_key=True,nullable=False,default=PassiveDefault(sqlalchemy.sql._TextClause
object at 0xb6fdf06c)),
Column(u'id_azienda',PGInteger(),ForeignKey(u'azienda.id'),nullable=False),
Column(u'id_anagrafica',PGInteger(),ForeignKey(u'anagrafica.id')),
Column(u'codice_struttura',PGText(length=None,convert_unicode=False)),
Column(u'bdn_id',PGInteger()),
Column(u'bdn_data_ultima_modifica',PG2Date()),
Column(u'bdn_specie',PGChar(length=4,convert_unicode=False)),
Column(u'bdn_flag_carne_latte',PGChar(length=1,convert_unicode=False)),
Column(u'data_inizio',PG2Date()),Column(u'data_fine',PG2Date()),
Column(u'id_precedente',PGInteger(),ForeignKey(u'unita_aziendale.id')),
Column(u'ts_ultima_modifica',PG2DateTime(timezone=False),default=PassiveDefault(sqlalchemy.sql._TextClause
object at 0xb6fdf98c)),
Column(u'id_operatore',PGInteger(),ForeignKey(u'operatore.id')),
Column(u'stato_record',PGChar(length=1,convert_unicode=False),default=PassiveDefault(sqlalchemy.sql._TextClause
object at 0xb6fdfacc)),schema=None)
Table(u'anagrafica',
DynamicMetaData(),
Column(u'id',PGInteger(),primary_key=True,nullable=False,default=PassiveDefault(sqlalchemy.sql._TextClause
object at 0xb702390c)),
Column(u'nome',PGText(length=None,convert_unicode=False)),
Column(u'indirizzo',PGText(length=None,convert_unicode=False)),
Column(u'telefono',PGText(length=None,convert_unicode=False)),
Column(u'email',PGText(length=None,convert_unicode=False)),
Column(u'id_comune',PGInteger(),ForeignKey(u'comune.id')),
Column(u'localita',PGText(length=None,convert_unicode=False)),
Column(u'cap',PGText(length=None,convert_unicode=False)),
Column(u'dato_fiscale',PGText(length=None,convert_unicode=False)),
Column(u'cod_nazionalita',PGChar(length=3,convert_unicode=False),ForeignKey(u'nazione.codice')),
Column(u'data_nascita',PG2Date()),Column(u'id_comune_nascita',PGInteger(),ForeignKey(u'comune.id')),
Column(u'cod_nazione_nascita',PGChar(length=3,convert_unicode=False),ForeignKey(u'nazione.codice')),
Column(u'id_precedente',PGInteger(),ForeignKey(u'anagrafica.id')),
Column(u'ts_ultima_modifica',PG2DateTime(timezone=False),default=PassiveDefault(sqlalchemy.sql._TextClause
object at 0xb70291cc)),
Column(u'id_operatore',PGInteger(),ForeignKey(u'operatore.id')),
Column(u'stato_record',PGChar(length=1,convert_unicode=False),default=PassiveDefault(sqlalchemy.sql._TextClause
object at 0xb702930c)),
Column(u'tipo',PGChar(length=1,convert_unicode=False),nullable=False,default=PassiveDefault(sqlalchemy.sql._TextClause
object at 0xb70293cc)),
Column(u'cod_professione',PGChar(length=3,convert_unicode=False),ForeignKey(u'professione.codice')),
Column(u'cod_titolo_studio',PGChar(length=3,convert_unicode=False),ForeignKey(u'titolo_studio.codice')),schema=None)
assign_mapper(context,
UnitaAziendale,
tbl['unita_aziendale'],
column_prefix=unita_aziendale_,
extension = SelectResultsExt(),
properties = { 'anagrafica' :
relation(Anagrafica, lazy=False),
'figure' :
relation(FiguraAziendale, backref='unita_aziendale'),
'produzioni' :
relation(Produzione, backref='unita_aziendale'),
'azienda_veterinario' :
relation(AziendaVeterinario,
primaryjoin=tbl['azienda_veterinario'].c.id_unita_aziendale ==
tbl['unita_aziendale'].c.id),
'peso' :
relation(SchedaTotali),
})
assign_mapper(context,
Anagrafica,
tbl['anagrafica'],
column_prefix = 'anagrafica_',
extension = SelectResultsExt(),
properties = {
'comune' : relation( Comune,
primaryjoin = tbl['anagrafica'].c.id_comune ==
tbl['comune'].c.id ),
'nazione': relation( Nazione,
primaryjoin = tbl['anagrafica'].c.cod_nazionalita ==
tbl['nazione'].c.codice ),
'comune_nascita' : relation( Comune,
primaryjoin = tbl['anagrafica'].c.id_comune_nascita
== tbl['comune'].c.id),
'nazione_nascita' : relation(Nazione,
primaryjoin = tbl['anagrafica
[sqlalchemy] aliased tables
Hi all, how can i know the correct name of a table compiled into qry that SA has automatically aliased? for example: a join b join c join d.. sqlalchemy has created something like select blabla from a AS anon_b406 join. when i try to put into an order by a obviously the qry goes wrong. invalid reference to FROM-clause entry for table a Thank's Glauco -- ++ Glauco Uri - Programmatore glauco(at)allevatori.com Sfera Carta Software(R) [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: aliased tables
Roger Demetrescu ha scritto:
Hi Glauco
On 9/27/07, Glauco [EMAIL PROTECTED] wrote:
Hi all,
how can i know the correct name of a table compiled into qry that SA
has automatically aliased?
for example:
a join b join c join d..
sqlalchemy has created something like
select blabla
from a AS anon_b406
join.
when i try to put into an order by a
obviously the qry goes wrong.
invalid reference to FROM-clause entry for table a
Does your question has any relation with the patch that allows
ordering or related objects's attributes ? ;)
no no Roger, i will install next days...
If so, I think I know what you are doing wrong... you must use Query's
.join() or .outerjoin() method. Like that:
session.query(ObjectA).join('property_b').order_by(ObjectB.c.foobar)
Where 'foobar' maps to the column from B table which you want to use
to sort your data...
This is correct Roger, but my problem is when in this example SA compile
the qry using alias
select ... form table_a as alias1. table_b as alias2... order by
table_a.column1
in this case the qry is wrong because the order by is incorrect.
take a look
*sqlalchemy.exceptions.SQLError: ('(ProgrammingError) missing
FROM-clause entry for table azienda\n',
SELECT unita_aziendale.id_anagrafica AS unita_aziendale_id_anagrafica,
unita_aziendale.bdn_data_ultima_modifica AS
unita_aziendale_bdn_data_ultima_modifica,
unita_aziendale.bdn_specie AS unita_aziendale_bdn_specie,
unita_aziendale.ts_ultima_modifica AS
unita_aziendale_ts_ultima_modifica,
****unita_aziendale.id_precedente AS unita_aziendale_id_precedente,
****unita_aziendale.id AS unita_aziendale_id,
CUT
****anon_b406.data_aggiornamento AS anon_b406_data_aggiornamento,
unita_aziendale.bdn_flag_carne_latte AS
unita_aziendale_bdn_flag_carne_latte
FROM produzione, unita_aziendale
LEFT OUTER JOIN anagrafica AS anon_b3be ON anon_b3be.id =
unita_aziendale.id_anagrafica
LEFT OUTER JOIN scheda_totali AS anon_b406 ON unita_aziendale.id =
anon_b406.id_unita_aziendale
WHERE SOME CONDITION
ORDER BY azienda.id_asl ASC, anon_b3be.id, anon_b406.id_unita_aziendale'
Glauco
*
--
++
Glauco Uri - Programmatore
glauco(at)allevatori.com
Sfera Carta Software(R) [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: aliased tables
Glauco, can you send a small test script reproducing the error ?
I'll gladly take a look at that...
Cheers,
Roger
Yes...
this is the example
session.query( UnitaAziendale ).order_by(Anagrafica.c.nome)
(ProgrammingError) invalid reference to FROM-clause entry for table
anagrafica
*HINT: Perhaps you meant to reference the table alias anon_b3be.*
'SELECT unita_aziendale.ts_ultima_modifica AS
unita_aziendale_ts_ultima_modifica, unita_aziendale.id AS
unita_aziendale_id, unita_aziendale.id_anagrafica AS
unita_aziendale_id_anagrafica, unita_aziendale.id_operatore AS
unita_aziendale_id_operatore, unita_aziendale.data_inizio AS
unita_aziendale_data_inizio, unita_aziendale.bdn_data_ultima_modifica AS
unita_aziendale_bdn_data_ultima_modifica, unita_aziendale.bdn_id AS
unita_aziendale_bdn_id, unita_aziendale.codice_struttura AS
unita_aziendale_codice_struttura, unita_aziendale.id_azienda AS
unita_aziendale_id_azienda, anon_b406.peso AS anon_b406_peso,
anon_b406.tempo AS anon_b406_tempo, anon_b406.id_unita_aziendale AS
anon_b406_id_unita_aziendale, anon_b406.data_aggiornamento AS
anon_b406_data_aggiornamento, unita_aziendale.bdn_specie AS
unita_aziendale_bdn_specie, unita_aziendale.id_precedente AS
unita_aziendale_id_precedente, unita_aziendale.bdn_flag_carne_latte AS
unita_aziendale_bdn_flag_carne_latte, unita_aziendale.data_fine AS
unita_aziendale_data_fine, unita_aziendale.stato_record AS
unita_aziendale_stato_record, anon_b3be.cod_nazionalita AS
anon_b3be_cod_nazionalita, anon_b3be.id AS anon_b3be_id,
anon_b3be.id_comune AS anon_b3be_id_comune, anon_b3be.cod_professione AS
anon_b3be_cod_professione, anon_b3be.dato_fiscale AS
anon_b3be_dato_fiscale, anon_b3be.localita AS anon_b3be_localita,
anon_b3be.telefono AS anon_b3be_telefono, anon_b3be.email AS
anon_b3be_email, anon_b3be.nome AS anon_b3be_nome, anon_b3be.tipo AS
anon_b3be_tipo, anon_b3be.cod_titolo_studio AS
anon_b3be_cod_titolo_studio, anon_b3be.indirizzo AS anon_b3be_indirizzo,
anon_b3be.id_precedente AS anon_b3be_id_precedente,
anon_b3be.cod_nazione_nascita AS anon_b3be_cod_nazione_nascita,
anon_b3be.id_comune_nascita AS anon_b3be_id_comune_nascita,
anon_b3be.stato_record AS anon_b3be_stato_record, anon_b3be.id_operatore
AS anon_b3be_id_operatore, anon_b3be.cap AS anon_b3be_cap,
anon_b3be.ts_ultima_modifica AS anon_b3be_ts_ultima_modifica,
anon_b3be.data_nascita AS anon_b3be_data_nascita \nFROM unita_aziendale
LEFT OUTER JOIN scheda_totali AS anon_b406 ON unita_aziendale.id =
anon_b406.id_unita_aziendale LEFT OUTER JOIN anagrafica AS anon_b3be ON
anon_b3be.id = unita_aziendale.id_anagrafica ORDER BY anagrafica.nome,
anon_b406.id_unita_aziendale, anon_b3be.id' {}
Anagrafica is a table implicated in the mapper UnitaAziendale, so i
expect a correct ordering, but seems SA don't consider to have aliased
during the select anagrafica as anon_b3be. When compiler add order_by
condition he use ORDER BY anagrafica.nome as indicated in the function.
But i'm confused now... SA tell me that he have renamed this table but
create a wrong qry...why this appens?
Thank's
Glauco
--
++
Glauco Uri - Programmatore
glauco(at)allevatori.com
Sfera Carta Software(R) [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: Insert through ORM performance (was: Performance question)
CUT
thats because SA still does a lot of work on each batch of {params} to
check for defaults and also to process bind parameters. We might
look into optimizing some of the redundant work which occurs within
this process in 0.4, however as long as people still want their
unicodes converted to utf-8, their datetimes converted to strings on
sqlite, their binaries correctly massaged, their Python side defaults
to fire off, this overhead will still be present for those types.
So, if you truly want DBAPI-speed inserts, use the raw connection:
engine.connect().connection.executemany(your statement, [{params1},
{params2}, {params3}, ...])
This only for 0.4 version.. i think
is this the correct methods for the 0.3 ?
engine.connect()._executemany()
Thank's
Glauco
--
++
Glauco Uri - Programmatore
glauco(at)allevatori.com
Sfera Carta Software(r) [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: Insert through ORM performance (was: Performance question)
So, if you truly want DBAPI-speed inserts, use the raw connection:
engine.connect().connection.executemany(your statement, [{params1},
{params2}, {params3}, ...])
Wow.. after some try i found this is enought fast for me..
engine.connect().execute(sql,list_qry_params )
direct execution of a precompiled sql throw a list of 3000 dict is
faster about 12 times!!
Main interesting feature is occupation of CPU and memory .. it is lesser
than an half of previous method ( n x single row insertion )
thank's Michael for your help
Glauco
--
++
Glauco Uri - Programmatore
glauco(at)allevatori.com
Sfera Carta Software(r) [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: sql executemany and postgresql - probably bug
seems there is some other prob too:
- in 0.3 it is issued 2 statements:
select nextval('Manager_id_seq')
and then:
INSERT INTO Manager (duties, name, id) VALUES (%(duties)s, %(name)s,
%(id)s)
with the ids got from the db and the other parameters.
- in 0.4 it is issued only the last insert with all ids None which
leads to the error
If you have to insert a lot of this... (and id is a serial) let postgres
do his work.
sql=INSERT INTO Manager (duties, name) VALUES (%(duties)s,
%(name)s)
list_qry_params = [('dut1','name1'),('dut2','name2'),('dut_n','name_n')]
engine.connect().execute(sql, list_qry_params )
Glauco
--
++
Glauco Uri - Programmatore
glauco(at)allevatori.com
Sfera Carta Software(r) [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: large amount of insert
Michael Bayer ha scritto: well at the very least you want to ensure that executemany() is being used for the inserts (i.e. with sqlalchemy, dont use ORM, use connection.execute(statement, [param, param, param...]). If you use SQLAlchemy's executemany() facilities, we've just increased their efficiency by about 60% in the 0.4.0beta3 release, we had some huge latency issues with this particular operation previously. faster still is to use raw DBAPI: conn = engine.connect() conn.connection.executemany(statement, [params...]) If this don't cause thread or other problem with SA, i think this is the best solution, i can split my n insert query in segments of 100 qry a time. using copy from..file is probably very fast, but its a little weird to use that in the context of a web request. I think too... Thank you -- ++ Glauco Uri - Programmatore glauco(at)allevatori.com Sfera Carta Software(r) [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] strange serial problem in postgres
Hi all, i've added a new table recentli in main DB, but at first
insertion have this problem:
any time i try an insertion the sqlalchemy engine search nextval of a
partial primary key.
SQLError: (ProgrammingError) relation scadenziario_id_piano_seq does
not exist
'select nextval(\'scadenziario_id_piano_seq\')' {}
but
the table is:
CREATE TABLE scadenziario (
id_pianoINTEGER NOT NULL references piano(id),
data_creazione DATE,
data_programmataDATE NOT NULL,
cod_verificaTEXT NOT NULL references
verifica(codice) ON UPDATE CASCADE,
);
How and why SQLAlchemy read a serial for the id_piano column ?
Thank you
Glauco
--
++
Glauco Uri - Programmatore
glauco(at)allevatori.com
Sfera Carta Software(r) [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: strange serial problem in postgres
justanyone ha scritto: In Postgres at least, serial columns are typically defined as: CREATE TABLE FOO ( id_piano serial primary key, ... ) This automatically creates a sequence foo_id_piano_seq. Of course, you can also create a sequence separate from a table with CREATE SEQUENCE, but this is (IMHO) wordy. You can select from it to find the current value with CURRVAL(), or get the value and increment it in one operation as: SELECT NEXTVAL ( sequencename ); -- Kevin Thank you kevin , i've worked a lot over PG and this tecnique is consolidated in our model.. but here the problem is that the column is a simplycolumn_name INTEGER NOT NULL references other_table(id) and i cannot understand why sqlalchemy whant to use it as a column_name SERIAL Thank you Glauco -- ++ Glauco Uri - Programmatore glauco(at)allevatori.com Sfera Carta Software(r) [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] cannot read msg...
can someone read me? Glauco -- ++ Glauco Uri - Programmatore glauco(at)allevatori.com Sfera Carta Software(r) [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: Field with constant
Expo ha scritto: If I would make a select with a field set to a constant like this: SELECT 1 AS field FROM foo how is the select()'s field definition ? I'v tried with: select([1], from_obj=[foo]) but field has no name. select([1 as bar], from_obj=[foo]) Glauco -- ++ Glauco Uri - Programmatore glauco(at)allevatori.com Sfera Carta Software(r) [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] multiple mappers on une table problem.
Hi Folk, I'm finding difficult implement this features of SA .
The example is: a simple table with a column type. I want to do a
secondary mapper based on the primary mapper with the only different in
the column type='something'
create table person(
name text,
type text);
class Person ( Obj ):
pass
assign_mapper(context,
Person,
tbl['person'],
column_prefix = 'person_',
extension = SelectResultsExt(),
properties = {
bla bla
})
class Blonde( Person ):
pass
assign_mapper(context,
Blonde,
select( [tbl['anagrafica']], tbl['anagrafica'].c.tipo ==
'P').alias('blonde'),
non_primary = True,
)
This cause a lot of inspiegable problem to property that are perfecly
functional in the primary
sqlalchemy.exceptions.ArgumentError: Can't determine relation direction
for relationship 'Blabla.comune_nascita (Comune)' - foreign key columns
are present in neither the parent nor the child's mapped tables
Some ideas?
Glauco
--
++
Glauco Uri - Programmatore
glauco(at)allevatori.com
Sfera Carta Software(r) [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] SQLError.... label Error
I use hardly the column prefix, so my query are always aliased. this obviously cause a lot of problem whith the order_by clause (that NOT use the same aliases) Is there some solution or mus wait for the new relase? This is a tipical Example ... the anagrafica.nome ORDER_BY column don't work bu SA know how to use correclty the alias... beacause the ProgrammingError is correct. SQLError: (ProgrammingError) invalid reference to FROM-clause entry for table anagrafica HINT: Perhaps you meant to reference the table alias anon_f3fb. SELECT operatore.data_inizio_attivita AS operatore_data_inizio_attivita, operatore.password AS operatore_password, operatore.logname AS operatore_logname, operatore.id_anagrafica AS operatore_id_anagrafica, operatore.data_fine_attivita AS operatore_data_fine_attivita, operatore.zoperoles AS operatore_zoperoles, anon_4877.ts_ultima_modifica AS anon_4877_ts_ultima_modifica, anon_4877.id AS anon_4877_id, anon_4877.id_anagrafica AS anon_4877_id_anagrafica, anon_4877.id_operatore AS anon_4877_id_operatore, anon_4877.data_inizio AS anon_4877_data_inizio, anon_4877.bdn_data_ultima_modifica AS anon_4877_bdn_data_ultima_modifica, anon_4877.bdn_id AS anon_4877_bdn_id, anon_4877.codice_struttura AS anon_4877_codice_struttura, anon_4877.id_azienda AS anon_4877_id_azienda, anon_4877.stato_record AS anon_4877_stato_record, anon_4877.bdn_specie AS anon_4877_bdn_specie, anon_4877.id_precedente AS anon_4877_id_precedente, anon_4877.bdn_flag_carne_latte AS anon_4877_bdn_flag_carne_latte, anon_4877.data_fine AS anon_4877_data_fine, operatore.id AS operatore_id, anon_a2d6.codice AS anon_a2d6_codice, anon_a2d6.descrizione AS anon_a2d6_descrizione, anon_a2d6.group_id AS anon_a2d6_group_id, anon_f3fb.cod_nazionalita AS anon_f3fb_cod_nazionalita, anon_f3fb.id AS anon_f3fb_id, anon_f3fb.id_comune AS anon_f3fb_id_comune, anon_f3fb.cod_professione AS anon_f3fb_cod_professione, anon_f3fb.dato_fiscale AS anon_f3fb_dato_fiscale, anon_f3fb.localita AS anon_f3fb_localita, anon_f3fb.telefono AS anon_f3fb_telefono, anon_f3fb.email AS anon_f3fb_email, anon_f3fb.nome AS anon_f3fb_nome, anon_f3fb.tipo AS anon_f3fb_tipo, anon_f3fb.cod_titolo_studio AS anon_f3fb_cod_titolo_studio, anon_f3fb.indirizzo AS anon_f3fb_indirizzo, anon_f3fb.id_precedente AS anon_f3fb_id_precedente, anon_f3fb.cod_nazione_nascita AS anon_f3fb_cod_nazione_nascita, anon_f3fb.id_comune_nascita AS anon_f3fb_id_comune_nascita, anon_f3fb.stato_record AS anon_f3fb_stato_record, anon_f3fb.id_operatore AS anon_f3fb_id_operatore, anon_f3fb.cap AS anon_f3fb_cap, anon_f3fb.ts_ultima_modifica AS anon_f3fb_ts_ultima_modifica, anon_f3fb.data_nascita AS anon_f3fb_data_nascita FROM operatore LEFT OUTER JOIN azienda_veterinario AS anon_28bf ON anon_28bf.id_veterinario = operatore.id LEFT OUTER JOIN unita_aziendale AS anon_4877 ON anon_4877.id = anon_28bf.id_unita_aziendale LEFT OUTER JOIN acl AS anon_e91a ON operatore.id = anon_e91a.id_operatore LEFT OUTER JOIN ruolo AS anon_a2d6 ON anon_a2d6.codice = anon_e91a.cod_ruolo LEFT OUTER JOIN anagrafica AS anon_f3fb ON anon_f3fb.id = operatore.id_anagrafica ORDER BY *_anagrafica.nome_*, anon_28bf.id, anon_e91a.id, anon_f3fb.id' -- ++ Glauco Uri - Programmatore glauco(at)allevatori.com Sfera Carta Software(r) [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: SQLError.... label Error
Michael Bayer ha scritto:
On Jun 11, 2007, at 11:19 AM, Glauco wrote:
I use hardly the column prefix, so my query are always aliased.
this obviously cause a lot of problem whith the order_by clause
(that NOT use the same aliases)
the approach here depends on how this query is being constructed. if
you are using your own alias objects, insert the columns as
referenced from the alias object itself into the order_by clause.
alias = mytable.alias('foo')
alias.select(order_by=[alias.c.bar])
my problem is exactly this... i don't use aliases, and the example is a
qru constructed by SA.
*Operatore.select_by().order_by( Anagrafica.c.nome).list()*
SQLError: (ProgrammingError) invalid reference to FROM-clause entry for
table anagrafica
HINT: Perhaps you meant to reference the table alias anon_f3fb.
'SELECT operatore.data_inizio_attivita AS
operatore_data_inizio_attivita, operatore.password AS
operatore_password, operatore.logname AS operatore_logname,
operatore.id_anagrafica AS operatore_id_anagrafica,
anon_4877.ts_ultima_modifica AS anon_4877_ts_ultima_modifica,
anon_4877.id AS anon_4877_id, anon_4877.id_anagrafica AS
anon_4877_id_anagrafica, anon_4877.id_operatore AS
anon_4877_id_operatore, anon_4877.data_inizio AS anon_4877_data_inizio,
anon_4877.bdn_data_ultima_modifica AS
anon_4877_bdn_data_ultima_modifica, anon_4877.bdn_id AS
anon_4877_bdn_id, anon_4877.codice_struttura AS
anon_4877_codice_struttura, anon_4877.id_azienda AS
anon_4877_id_azienda, anon_4877.stato_record AS anon_4877_stato_record,
anon_4877.bdn_specie AS anon_4877_bdn_specie, anon_4877.id_precedente AS
anon_4877_id_precedente, anon_4877.bdn_flag_carne_latte AS
anon_4877_bdn_flag_carne_latte, anon_4877.data_fine AS
anon_4877_data_fine, operatore.id AS operatore_id, anon_a2d6.codice AS
anon_a2d6_codice, anon_a2d6.descrizione AS anon_a2d6_descrizione,
anon_a2d6.group_id AS anon_a2d6_group_id, operatore.data_fine_attivita
AS operatore_data_fine_attivita, operatore.zoperoles AS
operatore_zoperoles, anon_f3fb.cod_nazionalita AS
anon_f3fb_cod_nazionalita, anon_f3fb.id AS anon_f3fb_id,
anon_f3fb.id_comune AS anon_f3fb_id_comune, anon_f3fb.cod_professione AS
anon_f3fb_cod_professione, anon_f3fb.dato_fiscale AS
anon_f3fb_dato_fiscale, anon_f3fb.localita AS anon_f3fb_localita,
anon_f3fb.telefono AS anon_f3fb_telefono, anon_f3fb.email AS
anon_f3fb_email, anon_f3fb.nome AS anon_f3fb_nome, anon_f3fb.tipo AS
anon_f3fb_tipo, anon_f3fb.cod_titolo_studio AS
anon_f3fb_cod_titolo_studio, anon_f3fb.indirizzo AS anon_f3fb_indirizzo,
anon_f3fb.id_precedente AS anon_f3fb_id_precedente,
anon_f3fb.cod_nazione_nascita AS anon_f3fb_cod_nazione_nascita,
anon_f3fb.id_comune_nascita AS anon_f3fb_id_comune_nascita,
anon_f3fb.stato_record AS anon_f3fb_stato_record, anon_f3fb.id_operatore
AS anon_f3fb_id_operatore, anon_f3fb.cap AS anon_f3fb_cap,
anon_f3fb.ts_ultima_modifica AS anon_f3fb_ts_ultima_modifica,
anon_f3fb.data_nascita AS anon_f3fb_data_nascita \nFROM operatore LEFT
OUTER JOIN azienda_veterinario AS anon_28bf ON anon_28bf.id_veterinario
= operatore.id LEFT OUTER JOIN unita_aziendale AS anon_4877 ON
anon_4877.id = anon_28bf.id_unita_aziendale LEFT OUTER JOIN acl AS
anon_e91a ON operatore.id = anon_e91a.id_operatore LEFT OUTER JOIN ruolo
AS anon_a2d6 ON anon_a2d6.codice = anon_e91a.cod_ruolo LEFT OUTER JOIN
anagrafica AS anon_f3fb ON anon_f3fb.id = operatore.id_anagrafica ORDER
BY anagrafica.nome, anon_28bf.id, anon_e91a.id, anon_f3fb.id' {}
some ideas?
Glauco
--
++
Glauco Uri - Programmatore
glauco(at)allevatori.com
Sfera Carta Software(r) [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: multiple mappers on une table problem.
Michael Bayer ha scritto:
On Jun 11, 2007, at 11:12 AM, Glauco wrote:
This cause a lot of inspiegable problem to property that are perfecly
functional in the primary
sqlalchemy.exceptions.ArgumentError: Can't determine relation
direction
for relationship 'Blabla.comune_nascita (Comune)' - foreign key
columns
are present in neither the parent nor the child's mapped tables
you need to explicitly specify conditions like primaryjoin,
foreign_keys, etc.
http://www.sqlalchemy.org/docs/
adv_datamapping.html#advdatamapping_properties_customjoin
For this reason i've inserted explicit primaryjoin condition, and these
condition work perfectly in the primary mapper.
But in the secondary wont work...
class Anagrafica:
bla bla
# my primary mapper
assign_mapper(context,
Anagrafica,
tbl['anagrafica'],
column_prefix = 'anagrafica_',
extension = SelectResultsExt(),
properties = {
'comune' : relation( Comune,
primaryjoin = tbl['anagrafica'].c.id_comune ==
tbl['comune'].c.id ),
'nazione': relation( Nazione,
primaryjoin = tbl['anagrafica'].c.cod_nazionalita ==
tbl['nazione'].c.codice ),?
'comune_nascita' : relation( Comune,
primaryjoin = tbl['anagrafica'].c.id_comune_nascita
== tbl['comune'].c.id),
'nazione_nascita' : relation(Nazione,
primaryjoin = tbl['anagrafica'].c.cod_nazione_nascita
== tbl['nazione'].c.codice ),
'professione': relation( Professione ),
'titolo_studio' : relation( TitoloStudio ),
})
# my secondary mapper
class Person( Anagrafica ):
pass
assign_mapper(context,
Person,
select([tbl['anagrafica']], tbl['anagrafica'].c.tipo ==
'P').alias('person'),
column_prefix = 'anagrafica_',
extension = SelectResultsExt(),
non_primary = True,
properties = {
'comune' : relation( Comune,
primaryjoin = tbl['anagrafica'].c.id_comune ==
tbl['comune'].c.id ),
'nazione': relation( Nazione,
primaryjoin = tbl['anagrafica'].c.cod_nazionalita ==
tbl['nazione'].c.codice ),?
'comune_nascita' : relation( Comune,
primaryjoin = tbl['anagrafica'].c.id_comune_nascita
== tbl['comune'].c.id),
'nazione_nascita' : relation(Nazione,
primaryjoin = tbl['anagrafica'].c.cod_nazione_nascita
== tbl['nazione'].c.codice ),
'professione': relation( Professione ),
'titolo_studio' : relation( TitoloStudio ),
})
In [8]: Anagrafica.search()
Out[8]: sqlalchemy.ext.selectresults.SelectResults object at 0xb6dfa92c
In [9]: Person.search()
ArgumentError: Can't determine relation direction for relationship
'Person.comune_nascita (Comune)' - foreign key columns are present in
neither the parent nor the child's mapped tables
Some ideas?
Thank you
Glauco
--
++
Glauco Uri - Programmatore
glauco(at)allevatori.com
Sfera Carta Software(r) [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: InstrumentedList in a boolean context
Julien Cigar ha scritto: Hi list, With the following : a = Invasive.get(67).habitats b = Invasive.get(57).habitats type(a), type(b) (class 'sqlalchemy.orm.attributes.InstrumentedList', class 'sqlalchemy.orm.attributes.InstrumentedList') a == b True a [tgias.models.habitat.Habitat object at 0x8d635ec, tgias.models.habitat.Habitat object at 0x8d5d8ec] b [tgias.models.habitat.Habitat object at 0x8d635ec, tgias.models.habitat.Habitat object at 0x8d5d8ec] a.reverse() a [tgias.models.habitat.Habitat object at 0x8d5d8ec, tgias.models.habitat.Habitat object at 0x8d635ec] b [tgias.models.habitat.Habitat object at 0x8d635ec, tgias.models.habitat.Habitat object at 0x8d5d8ec] a == b False Would it not be logical to have True instead of False for the last statement ? I don't think so, because the default order is an information ! a == b is a comparo of content so [1,2,3] is different than [3,2,1] sometimes ordering in presentation of data is a useful information. Do you think the order of an InstrumentedList is important for collections (in a boolean context) ? Yes it is. You can use set for compare w/out order or a is b for compare identical items too. 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] parent dont get _state modified from child states
i all my mapper i use this property frequently:
@property
def changed(self):
return self._state.get('modified')
but in some cases ( 1:n relation ) mapper this don't work:
In [1]: o =Operatore.select()[4]
In [2]: o._state.get('modified')
Out[2]: False
In [3]: o.groups[0].acl_cod_ruolo
Out[3]: u'VET'
In [4]: o.groups[0].acl_cod_ruolo = '123'
In [5]: o.groups[0].acl_cod_ruolo
Out[5]: '123'
In [6]: o.groups[0]._state.get('modified')
Out[6]: True
In [7]: o._state.get('modified')
Out[7]: False
Why the parent self._state['modified'] don't get the changes from childs
mappers sometimes?
i'm doing something wrong?
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: cascade='all, delete-orphan' causing error about unsaved, pending instances
http://www.sqlalchemy.org/trac/wiki/
FAQ#FlushError:instancesomeinstanceisanunsavedpendinginstanceandisanorph
an
I think this is not Andreas an I too are searching to explain.
The documentation is correct, and infact in tg-admin all go
perfeclty.. but when the same example is gone from TG this error
occurs.
I'm speaking of:
mapper(Parent, sometable, properties={
relation(Child, cascade=all, delete-orphan)
})
p = Parent.select()[0]
c = Child()
p.child.append ( c )
session.save(c)
session.flush()
Glauco
--~--~-~--~~~---~--~~
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: retrieving columns from secondary in n:m relation
I think is not possible... :-( The association object pattern describe because But for this and other cases i've a proposal: The add_column function must add only column in the Result, and oly add_entity must add tables to from clause, in this manner people who use this feature can do anyway all they want. Otherwise in cases where generated select contain already the table of my add_column dont add it again retrievind erroneus records. Thank you Glauco --~--~-~--~~~---~--~~ 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] unsave instance on a cascade='all, delete-orphans'
in a mapper where i've a 1:n relation i've some problem with Tubo Gears in delete, after the cascade=delete-orphan, all options, all times i add a new instance of child this error occours: instance Acl None is an unsaved, pending instance and is an orphan (is not attached to any parent 'Operatore' instance via that classes' 'groups' attribute) If i remove the cascade=delete-orphan, all option, the insert run perfeclty. Thank you Glauco --~--~-~--~~~---~--~~ 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: SQL newbie question: How to select the last message for each user
are you searhing for ? select max( changeTime ) from OrderDiscount group by orderID ; ? i don't understand well what you are searching for Glauco --~--~-~--~~~---~--~~ 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] retrieving columns from secondary in n:m relation
Hi all, i want to retrieve columns from table used as secondary in a n:m
join.
For Example, in this case how to retrieda data_fine, data_inizio from
secondary tables ?
my mapper respect correclty this property:
'associated_unita_aziendale' : relation( UnitaAziendale,
secondary=tbl['azienda_veterinario'],
primaryjoin=tbl['azienda_veterinario'].c.id_veterinario ==
tbl['operatore'].c.id,
lazy = False,
backref=veterinari_associati,
i've already tried with add_column and add_entity, but SA do always the
wrond cartesian product.
only solution i found is to add a .filter at the end with the explicit
join relation.
operatore:
{ u'id':
Column(u'id',PGInteger(),primary_key=True,nullable=False,default=PassiveDefault(sqlalchemy.sql._TextClause
object at 0xb714da2c)),
u'logname': Column(u'logname',PGText(length=None,convert_unicode=False)),
u'password': Column(u'password',PGText(length=None,convert_unicode=False)),
u'zoperoles':
Column(u'zoperoles',PGText(length=None,convert_unicode=False))}
secondary table:
{u'data_fine': Column(u'data_fine',PG2Date()),
u'data_inizio': Column(u'data_inizio',PG2Date()),
u'id':
Column(u'id',PGInteger(),primary_key=True,nullable=False,default=PassiveDefault(sqlalchemy.sql._TextClause
object at 0xb715e0ec)),
u'id_veterinario':
Column(u'id_veterinario',PGInteger(),ForeignKey(u'operatore.id')),
u'id_unita_aziendale':
Column(u'id_unita_aziendale',PGInteger(),ForeignKey(u'unita_aziendale.id')),
}
unita_aziendale
{
u'id':
Column(u'id',PGInteger(),primary_key=True,nullable=False,default=PassiveDefault(sqlalchemy.sql._TextClause
object at 0xb717842c)),
bla..bla...
}
Thank You
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: select() vs. self.session.query(): no group_by, different params?
Michael Bayer ha scritto: On May 2, 2007, at 11:23 AM, Glauco wrote: Example: create table people ( name text, surname text, type CASE 'A','B','C' ) There is no solution to do for example the simple query based over the mapper People: select count(type) from people group by type; first of all, i dont understand why youd want to GROUP BY a column used in an aggregate. but anyway: select([func.count(People.c.type)], group_by=[People.c.type]) this is exactly what i intend for select function ... where is the uncoompresion? this is the response to the simple question how many A,,B,C-person do i have in my DB ? only solution is to use the select functioon. i fail to see why thats a problem. if you want a SELECT to return tuple results, it follows that you should use select(). is not a problem if you can use the select funcion , otherway getting this information from mapper is impossible. 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] Correct use of PropertyLoader
I want to create a correct mapper with property from a dict. ;-O
The dict information can come to me via a Form or a web Services or a
file, so i must create from nothing a mapper with his property too for
the insert into
Example:
assign_mapper(context,
MainTable,
tbl['main_table'],
properties = { 'detail_table' : relation(DeatailTable),
})
dic = {'name': 'foo',
'surname': 'bar',
'detail_table': {'mansion': 1 }
}
I've build a generic script that do a correct instance of the main
mapper but i cannot create detail_table instance when necessary.
for_insert = MainTable()
i found that
for_insert.mapper.properties has
{'detail_table': sqlalchemy.orm.properties.PropertyLoader object at
0xb70577ec}
but how can use this for instantiate
for_insert.detail_table =
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: select() vs. self.session.query(): no group_by, different params?
Michael Bayer ha scritto:
On Apr 24, 2007, at 4:05 PM, Chris Shenton wrote:
Am I being stupid about not seeing the difference -- what keywords and
arguments I can use -- between:
self.session.query(MyClass).select(...)
and
select(...)
these two methods are fundamentally different. first of all,
session.query(MyClass) deals with object relational mapping, and
select() deals with result sets...i think you got that part.
so the big thing with query(MyClass).select() is that its designed to
return object instances. in order to return object instances, the
mapper already knows what columns it wants to select...so in the vast
majority of usages with query(), you dont specify any columns
clause, and the columns argument, which you know from
sqlalchemy.sql.select(), is not part of the select() method on
sqlalchemy.orm.query.Query.
so the basic answer to your question is, sqlalchemy.sql.select()
takes the list of columns as its first argument, query(MyClass).select
() does not. in the latter case, the columns clause is
automatically all of the columns from MyClass' table. there is a
group_by keyword argument available on query.select(), as well as a
generative method group_by() which is used like query.group_by(list
of columns).select()
however, Query() has a whole boatload of new features that came out
in 0.3.6. the best way to get a sense for everything that query can
do is to look at the generatted docs, which i now have linked from
the main docs in two places (ive also recently rewritten the whole
documentation on Query, as of last weekend, worth checking out):
http://www.sqlalchemy.org/docs/
sqlalchemy_orm_query.html#docstrings_sqlalchemy.orm.query_Query
as it turns out, theres a neat way to do exactly what you want with
this most recent Query object (the goal being, return object
instances plus an aggregate value in one shot). do it like:
session.query(Fault).add_column(func.count(Fault.c.severity).label
('count')).group_by([c for c in Fault.c]).filter(Fault.c.ts_created
= self.this_week).list()
The add_columns is a good option , but using the aggregate function
func.count is useless because aggregating columns from a Mapper must
contain every column all time.
Example:
create table people (
name text,
surname text,
type CASE 'A','B','C'
)
There is no solution to do for example the simple query based over the
mapper People:
select count(type) from people group by type;
only solution is to use the select functioon.
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: simple many-to-many select
Ram Yalamanchili ha scritto: was filter_by added recently? I have a assign_mapped class User from TG, and doing a session.query(User).filter_by doesn't work (no such method). yes, it's in the latest 0.3.6 version... this sometime it's hopeful :-) - orm: - the full featureset of the SelectResults extension has been merged into a new set of methods available off of Query. These methods all provide generative behavior, whereby the Query is copied and a new one returned with additional criterion added. The new methods include: filter() - applies select criterion to the query filter_by() - applies by-style criterion to the query avg() - return the avg() function on the given column join() - join to a property (or across a list of properties) outerjoin() - like join() but uses LEFT OUTER JOIN limit()/offset() - apply LIMIT/OFFSET range-based access which applies limit/offset: session.query(Foo)[3:5] distinct() - apply DISTINCT list() - evaluate the criterion and return results no incompatible changes have been made to Query's API and no methods have been deprecated. Existing methods like select(), select_by(), get(), get_by() all execute the query at once and return results like they always did. join_to()/join_via() are still there although the generative join()/outerjoin() methods are easier to use. -- ++ 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: How would you build a mapper with a calculated field based on a join from two tables
vinjvinj ha scritto:
Say I have a python class defined:
def class User(object):
get_calculated_field(self):
return self.a_column_in_user_table_1 +
self.a_column_in_additional_info
user_table = Table('user_table', metadata, autoload=True)
additional_info = Table('additional_info', metadata, autoload=True)
Now I need to do the following:
1. Add an additional read only attribute to the User object which is
calculated from columns on the user_table and additional_info table
2. Be able to generate a join between a user_table and additional_info
table and then for each row that is returned by the db, have the User
object add calculated_field as a read only attribute (by calling the
function get_calculated_field)
Why additional_info isn't a property of user_table ?
properties = { 'additional_info' : relation(additional_info),
})
so you have:
a = user_table()
a.a_column_in_user_table_1 + a.additional_info.your_column
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: simple many-to-many select
Glauco ha scritto: Ram Yalamanchili ha scritto: was filter_by added recently? I have a assign_mapped class User from TG, and doing a session.query(User).filter_by doesn't work (no such method). yes, it's in the latest 0.3.6 version... this sometime it's hopeful :-) ps... i forgot link: http://www.sqlalchemy.org/CHANGES - orm: - the full featureset of the SelectResults extension has been merged into a new set of methods available off of Query. These methods all provide generative behavior, whereby the Query is copied and a new one returned with additional criterion added. The new methods include: filter() - applies select criterion to the query filter_by() - applies by-style criterion to the query avg() - return the avg() function on the given column join() - join to a property (or across a list of properties) outerjoin() - like join() but uses LEFT OUTER JOIN limit()/offset() - apply LIMIT/OFFSET range-based access which applies limit/offset: session.query(Foo)[3:5] distinct() - apply DISTINCT list() - evaluate the criterion and return results no incompatible changes have been made to Query's API and no methods have been deprecated. Existing methods like select(), select_by(), get(), get_by() all execute the query at once and return results like they always did. join_to()/join_via() are still there although the generative join()/outerjoin() methods are easier to use. -- ++ 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
Michael Bayer ha scritto:
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()
i think set_column is a good choice, because in big mapper ( or big
select over a lot of tables ) the sa and DB must manipulate a lot of
unused fields.
another example is a mapper i've done overa a join (i call it a mapper
ove a view) in this case i must do it for retrieve the cartesian product
of the 3 tables, but all time the result object is bigger than what i'm
searching for. For this reason i want to optimize memory occupation of
result object.
Thank you
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: simple many-to-many select
Alexandre CONRAD ha scritto: Okay, thanks. Any idea if .filter_by() and other new generative method will be available in an assign_mapper object? Yes..it's available, the final object has identical property as a mapper 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: 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] 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] new features in 0.3.6
= ret.select(select(columns)) return ret i've done all for returnig a class 'sqlalchemy.orm.query.Query' but with the last .select(select(columns)) it return a list and this is different. how can i use this features and returning the same orm.query obj? thank all 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] explicit primary key in many-to-many relation
in my many to many relation, i've an abiguous primary key so Sa tell me: sqlalchemy.exceptions.ArgumentError: Error determining primary and/or secondary join for relationship 'xxx' between mappers 'Mapper|Yyy|yy' and 'Mapper|Xxx|xxx'. If the underlying error cannot be corrected, you should specify the 'primaryjoin' (and 'secondaryjoin', if there is an association table present) keyword arguments to the relation() function (or for backrefs, by specifying the backref using the backref() function with keyword arguments) to explicitly specify the join conditions. Nested error is Cant determine join between 'xxx' and 'yyy'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly. onclause is not within relation optin, so what's the correct manner to explicit it? Thank you 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: explicit primary key in many-to-many relation
Glauco ha scritto: in my many to many relation, i've an abiguous primary key so Sa tell me: sqlalchemy.exceptions.ArgumentError: Error determining primary and/or secondary join for relationship 'xxx' between mappers 'Mapper|Yyy|yy' and 'Mapper|Xxx|xxx'. If the underlying error cannot be corrected, you should specify the 'primaryjoin' (and 'secondaryjoin', if there is an association table present) keyword arguments to the relation() function (or for backrefs, by specifying the backref using the backref() function with keyword arguments) to explicitly specify the join conditions. Nested error is Cant determine join between 'xxx' and 'yyy'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly. onclause is not within relation optin, so what's the correct manner to explicit it? Thank you Glauco Found.. primaryjoin Sorry for noise. 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: What should the future of SQLAlchemy Query look like ?
Michael Bayer ha scritto: hey list - I continue to be troubled by the slightly fragmented nature of SA's Query object (and the cousin SelectResults). When I work with Hibernate, I can see that their querying interface is a little more consistent than ours. We have flags that are used for some things, generative methods for others. so id like to look into defining the next generation of query. Id like it to have a quasi-generative approach, like Hibernates. this means you can say: q = q.where(something).order_by(somethingelse) but also, its the same as: q.where(something) q.order_by(somethingelse) so its really the same instance (this is not how SelectResults works at the moment). the whole business of using SelectResults, using SelectResultsExt, all that crap just to get a different API, id like to get rid of (i mean, itll stay there but you wont need it). im sorry ive made you all type that much. This would be a rewrite of `Query`, and we'd leave the old one around in its usual place. Im thinking we could put this newer `Query` on the session under the method name `select()`. Anyway, I put a wiki page over at http://www.sqlalchemy.org/trac/wiki/QueryV4 , with like 2 lines of code what it might look like. I would like folks to comment on it, and add use cases, sample code, things youd like to see. note that Im looking mostly for the Python API, and maybe a little bit of the method of specifying criterion, but not really a whole new object-query layer (like building a new HQL, or using AST-parsing, etc. i still think thats something else entirely). Please think of something to add, particularly if you are working with polymorphic mappings, or youve had a lot to say in past iterations (i.e. like dmiller, dennis, etc). I dont want to make a move on this until something definitely cool and widely useful has been worked out. if we just have a vague notion of something, theres no point...while we can prototype it, if its a side thing then not enough people are going to use it (and therefore valid complaints wont be heard) unless we parade this thing down the main aisle. this query would hopefully be the last one we write for the SA core (since we are running out of reasonable method names on session ;) ). SA is a great Work, power and useful. Only think , probably is too much finalised to oneTabel - OneMapper prototyping For example, my purpose now is to revisiting a lot of mapper created from different programmers over a huge DB so it's very important for maintain mappers clear, univocity in these mappers. I found different but not equal possibility in some operation for example: - It's not too clear because not all the features of the Table object is not manteined in the Mapper. I've 3 mapper Amapper - Bmapper - Cmapper - Why, if i prefer to use Mapper instead of the Tbl direct qry, i must anyway always explicity the join to other mapper, for retrieve all selected records, Amapper.select_by( BmapperColumnCondition ) retrieve always select * from A where clause so if i'm searching something from B i must redesign selection qry.. - Why ( aa = Amapper, is a mapper bb = Bmapper, is a mapper; aa.Bmapper, is a Unit of Work) this let me use Amapper.c.field == x but i cannot Use Amapper.Bmapper.c.field = y take in mind my work of maintain this huge library so if i must upgrade Cmapper i don't want to manipulate ALL mapper referring to it I hope my explanation is clear, :-) Sorry for my poor English 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: Explicit column in a SelectResults qry.
Michael Bayer ha scritto:
SelectResults is used for mapped classes, the columns that are to be
selected for a particular mapping are fixed. theres no reason youd
want to be able to change the column clause.
I try to do an example:
create table macrocategory ( code char(3),
description text );
create table subcategory ( code char(3),
description text,
cod_macrocategory char(3) reference
macrocategory(code) );
create table category ( code char(3),
description text,
cod_subcategory char(3) reference
subcategory(code) );
This is my example_mapper.py
class _Subcategory(DomainObject, SferaDomainObject):
def __str__(self):
return self.code
assign_mapper(context,
_Subcategory,
tbl['subcategory'],
column_prefix = 'subcategory_'
extension = SelectResultsExt()
properties = {'category' : relation( _category ),
)
class _Category(DomainObject, SferaDomainObject):
def __str__(self):
return self.code
assign_mapper(context,
_Category,
tbl['category '],
extension = SelectResultsExt()
column_prefix = 'category_',
)
class Macrocategory(DomainObject, SferaDomainObject):
def __str__(self):
return self.code
def search( self, **kw ):
Generic Search function
by_where_clause = {}
where_clause = []
for k,v in kw.items():
if k in ('macrocategory_',\
'subcategory_code',\
'subcategory_description',\
'subcategory_cod_macrocategory',\
'category_code',\
'category_description',\
'category_cod_subcategory'):
by_where_clause[ k ] = v
elif k == 'macrocategory_description':
where_clause.append(
self.c.macrocategory_description.op('ilike')('%'+v+'%')
else:
raise ValueError, Unknow search parameter
if where_clause:
return self.select_by( **by_where_clause ).select( and_(
*where_clause )
else:
return self.select_by( **by_where_clause )
assign_mapper(context,
Macrocategory,
tbl['macrocategory '],
extension = SelectResultsExt()
column_prefix = 'macrocategory_',
)
Why i do that?
My primary purpose is to get from mappers the correct reference
from-to table and have the simplest select qry using select_by function.
Obviously the search function was the unique select function over these
3 tables and i must pass some parameters to it for retrieving some field
or other fields.
If i pass a first level table field the select_by do correclty a
select from macrocategory
If i pass a second level table field the select_by do correclty a
select from macrocategory join subcategory
and so on for select from macrocategory join subcategory join category
The where_clause was compiled correclty, *BUT* in this structure the SA
get always all field only from first mapper and sometimes i want field
from all three mapper so all programmes mus do search traversing this 3
mappers mus everytime build this own search function and finally i have
a lot of redundat code like this.
select([filed, field, field], from_obj=[
Macrocategory.join(_Subcategory,
Macrocategory.c.code == _Subcategory.c.cod_macrocategory
).join(_Category, bla bla bla
Try to imagine my case have over 10 tables whith a lot of condition for
primary key and condition...
my work for maintain this library in a pythonic form is enormous
This problem is olny mine? i'm doing an incorrect use of SA ?
Thank's
Glauco
Hibernate does have the ability to stick arbitrary columns into
mapping queries which are returned as scalars. im not sure if SA
needs this so much as we have the entire SQL construction facility
that can be used on its own (hibernate doesnt).
On Mar 7, 2007, at 4:02 AM, Glauco wrote:
Glauco ha scritto:
CUT
The simplest example is to specify columns to select on a generated
qry
(not all field of all tables involved in the generated qry).
does the sqlalchemy.ext.selectresults.SelectResults object have
something like column clause parameter of select function?
Thank's
Glauco
--
++
Glauco Uri - Programmatore
glauco(at)allevatori.com
Sfera Carta Software® [EMAIL PROTECTED]
Via Bazzanese,69 Casalecchio di Reno(BO) - Tel. 051591054
[sqlalchemy] Re: Explicit column in a SelectResults qry.
Glauco ha scritto: CUT The simplest example is to specify columns to select on a generated qry (not all field of all tables involved in the generated qry). does the sqlalchemy.ext.selectresults.SelectResults object have something like column clause parameter of select function? Thank's 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] Explicit column in a SelectResults qry.
Hi all, i've builded a mapper based on over 10 tables and it's
optimized well for use of *_by function.
Obviously some clausoleWhere must be formatted for select so my mapper
has this profile:
class MyClass(DomainObject, SferaDomainObject):
def search( self, **kw ):
by_where_clause = {}
where_clause = []
for k,v in kw.items():
# Generic _by clausole want use SA engine for dinamic
join_to tbl
if k in ('filt1', 'filt2', .):
by_where_clause[ k ] = v
# Generic explicit clausole for the where_clause feature
as operators
elif k == 'filt5':
where_clause.append( self.c.filt5 = v)
elif k == 'filt6':
where_clause.append( self.c.filt6 = v )
else:
raise ValueError, XXX
if where_clause:
return
self.select_by( **by_where_clause ).select( and_( *where_clause )
else:
return self.select_by( **by_where_clause )
this have extension = SelectResultsExt().
Now...
how can i specify field name in select function ?
something like
select ([tbl1.c.field1, tbl3.c.field2, tbl2.c.field3,
tbl5.c.field1], ??? )
Thank's all
Glauco
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
