[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: Problems Inserting into Oracle using Bind Variables from Turbo Gears Controller
I'm getting this error even though I have convert_unicode = True and
the proper NLS_LANG setting:
SQLError: (NotSupportedError) Variable_TypeByValue(): unhandled data
type unicode 'DELETE FROM model_acc_protocol WHERE
model_acc_protocol.model_id = :model_id AND
model_acc_protocol.acc_protocol_id = :acc_protocol_id AND
model_acc_protocol.color = :color' {'model_id': 60, 'color': u'RED',
'acc_protocol_id': u'2001-99'}
I know my convert_unicode = True is doing something because it solved
others problems before. Somehow it isn't working here. One thing that
is different here is that the values u'RED' and u'2001-99' were pulled
from the database during the same flush(). In the select statement
just before this one these value were bound as 'RED' and '2001-99'
instead of unicode.
Any ideas?
Steve
On Apr 25, 1:08 pm, Michael Bayer [EMAIL PROTECTED] wrote:
On Apr 25, 2007, at 12:26 PM, kap_ravi wrote:
Apparently there is some problem with theUNICODEdatathat sqlAlchemy
sends to oracle from the turbo gears framework. But I have tried the
same from the python command line with named parameters and its works
without any issues.
feeding inunicodestrings requires that they be encoded into
bytestrings first. this can be accomplished by either the usage of
theUnicodetypeon your Column definitions, or by sending the flag
convert_unicode=True to create_engine(). the default encoding for
this flag is utf-8 which can be configured by the 'encoding' flag
sent to create_engine().
additionally, when using Oracle, you have to ensure that the
environment variable NLS_LANG is set appropriately, such as AMERICAN-
AMERICA.UTF8.
--~--~-~--~~~---~--~~
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: Problems Inserting into Oracle using Bind Variables from Turbo Gears Controller
On May 3, 2007, at 11:25 AM, shday wrote:
I'm getting this error even though I have convert_unicode = True and
the proper NLS_LANG setting:
SQLError: (NotSupportedError) Variable_TypeByValue(): unhandled data
type unicode 'DELETE FROM model_acc_protocol WHERE
model_acc_protocol.model_id = :model_id AND
model_acc_protocol.acc_protocol_id = :acc_protocol_id AND
model_acc_protocol.color = :color' {'model_id': 60, 'color': u'RED',
'acc_protocol_id': u'2001-99'}
I know my convert_unicode = True is doing something because it solved
others problems before. Somehow it isn't working here. One thing that
is different here is that the values u'RED' and u'2001-99' were pulled
from the database during the same flush(). In the select statement
just before this one these value were bound as 'RED' and '2001-99'
instead of unicode.
those two values should not be u'' strings. therefore it would
appear that those bindparams are untyped. if you are using bindparam
() directly, add type=Unicode to it. similarly for whatever other
constructs youre making which arent derived from a Column, make sure
types are sent in as appropriate. ( a construct derived from a
Column would look like mytable.c.somecolumn == u'foo', in that case
the Unicode type should be generated automatically).
--~--~-~--~~~---~--~~
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: Problems Inserting into Oracle using Bind Variables from Turbo Gears Controller
The problem seems to be related to a backref I had. I found a way
without using backref and now things work.
With this I get a unicode error:
acc_mapper =
mapper(ACCProtocolSpecies,acc_protocol_species_table,properties={'models':relation(Model,secondary=model_acc_protocol_table,lazy=False,backref='acc_protocols'),
'study_requests':relation(StudyRequest,secondary=model_acc_protocol_table,lazy=False)})
With this I don't:
acc_mapper =
mapper(ACCProtocolSpecies,acc_protocol_species_table,properties={'models':relation(Model,secondary=model_acc_protocol_table,lazy=False),
'study_requests':relation(StudyRequest,secondary=model_acc_protocol_table,lazy=False)})
Here is the related tg controller code:
@expose()
def delete(self,model_id):
model = dbmodel.session.query(dbmodel.Model).get([model_id])
acc_protocol_id = model.acc_protocols[0].acc_protocol_id
species = model.acc_protocols[0].species
record =
dbmodel.session.query(dbmodel.ModelACCProtocol).get([model_id,
acc_protocol_id, species])
dbmodel.session.delete(record)
dbmodel.session.delete(model)
dbmodel.session.flush()
return Deleted
On May 3, 12:04 pm, Michael Bayer [EMAIL PROTECTED] wrote:
On May 3, 2007, at 11:25 AM, shday wrote:
I'm getting this error even though I have convert_unicode = True and
the proper NLS_LANG setting:
SQLError: (NotSupportedError) Variable_TypeByValue(): unhandled data
type unicode 'DELETE FROM model_acc_protocol WHERE
model_acc_protocol.model_id = :model_id AND
model_acc_protocol.acc_protocol_id = :acc_protocol_id AND
model_acc_protocol.color = :color' {'model_id': 60, 'color': u'RED',
'acc_protocol_id': u'2001-99'}
I know my convert_unicode = True is doing something because it solved
others problems before. Somehow it isn't working here. One thing that
is different here is that the values u'RED' and u'2001-99' were pulled
from the database during the same flush(). In the select statement
just before this one these value were bound as 'RED' and '2001-99'
instead of unicode.
those two values should not be u'' strings. therefore it would
appear that those bindparams are untyped. if you are using bindparam
() directly, add type=Unicode to it. similarly for whatever other
constructs youre making which arent derived from a Column, make sure
types are sent in as appropriate. ( a construct derived from a
Column would look like mytable.c.somecolumn == u'foo', in that case
the Unicode type should be generated automatically).- Hide quoted text -
- Show quoted text -
--~--~-~--~~~---~--~~
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: Problems Inserting into Oracle using Bind Variables from Turbo Gears Controller
On May 3, 2007, at 2:41 PM, shday wrote:
The problem seems to be related to a backref I had. I found a way
without using backref and now things work.
With this I get a unicode error:
acc_mapper =
mapper(ACCProtocolSpecies,acc_protocol_species_table,properties=
{'models':relation
(Model,secondary=model_acc_protocol_table,lazy=False,backref='acc_prot
ocols'),
'study_requests':relation
(StudyRequest,secondary=model_acc_protocol_table,lazy=False)})
With this I don't:
acc_mapper =
mapper(ACCProtocolSpecies,acc_protocol_species_table,properties=
{'models':relation
(Model,secondary=model_acc_protocol_table,lazy=False),
'study_requests':relation
(StudyRequest,secondary=model_acc_protocol_table,lazy=False)})
Ok well thats a bug in SA. what is the specific join condition to
the model_acc_protocol_table / primary keys in the related tables ?
'color' and 'acc_protocol_id' are foreign key columns in the
association table (and are part of how the relationship joins ?) a
stack trace here would tell me the bug is where i think it is (my
guess is, dependency.py line 383).
I also notice you have the same table used as a secondary table in
two different relationships which also might create confusion but not
related to unicode.
--~--~-~--~~~---~--~~
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: Queries on computed properties
On May 2, 2007, at 8:32 PM, Michael Bayer wrote:
oh duh, i forgot about the new thing Gaetan came up with, try this
too:
mapper(Invoice, invoice_table, properties={
'customer':column_property(func.substr(invoice_table.c.invnum, 1,
4).label('customer'))
})
That's so very, *very* close. It works perfectly for that particular
column, but a slightly more complex function causes breakage. The
only problem is that it generates SQL that PostgreSQL 8.2 isn't quite
happy with. Given the following code:
rdy2bill_table = Table('rdy2bill', metadata,
Column('invid', Integer, primary_key=True),
Column('invnum', String),
Column('pprresp', String, key='responsible'),
Column('xmlvars', String),
)
mapper(ReadyToBill, rdy2bill_table, properties={
'customer' : column_property(func.substr
(rdy2bill_table.c.invnum, 1,
4).label('customer')),
'groupcode': column_property(func.substring
(rdy2bill_table.c.xmlvars, M.XRSGRPCD(.*)/M.XRSGRPCD).label
('groupcode')),
})
inv = session.query(ReadyToBill).get_by(invid=1000346504,
groupcode='BILLGSCB')
I get a query like:
'SELECT substr(rdy2bill.invnum, %(substr)s, %(substr_1)s) AS
customer, rdy2bill.xmlvars AS rdy2bill_xmlvars, rdy2bill.pprresp AS
rdy2bill_pprresp, rdy2bill.invnum AS rdy2bill_invnum, rdy2bill.invid
AS rdy2bill_invid, substring(rdy2bill.xmlvars, %(substring)s) AS
groupcode \nFROM rdy2bill \nWHERE (rdy2bill.invid = %(rdy2bill_invid)
s) AND (substring(rdy2bill.xmlvars, %(substring)s) AS groupcode = %
(literal)s) ORDER BY rdy2bill.invid \n LIMIT 1' {'substring':
'M.XRSGRPCD(.*)/M.XRSGRPCD', 'substr': 1, 'literal': 'BILLGSCB',
'rdy2bill_invid': 1000346504, 'substr_1': 4}
The killer part is the (substring(rdy2bill.xmlvars, %(substring)s)
AS groupcode = in the WHERE clause. PostgreSQL apparently doesn't
want that predicate to be named. Can that be disabled?
--
Kirk Strauser
--~--~-~--~~~---~--~~
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: Queries on computed properties
On May 3, 2007, at 5:29 PM, Kirk Strauser wrote: The killer part is the (substring(rdy2bill.xmlvars, %(substring)s) AS groupcode = in the WHERE clause. PostgreSQL apparently doesn't want that predicate to be named. Can that be disabled? -- not really (well yes, you can take the label off, but then you dont get it in your columns clause, so that will break). mapping to the select statement that includes the column is the more general solution here (also allows the function to be called once instead of twice). --~--~-~--~~~---~--~~ 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: Queries on computed properties
no wait, scratch my last email for a bit. try rev 2601.
On May 3, 2007, at 5:29 PM, Kirk Strauser wrote:
On May 2, 2007, at 8:32 PM, Michael Bayer wrote:
oh duh, i forgot about the new thing Gaetan came up with, try this
too:
mapper(Invoice, invoice_table, properties={
'customer':column_property(func.substr(invoice_table.c.invnum, 1,
4).label('customer'))
})
That's so very, *very* close. It works perfectly for that
particular column, but a slightly more complex function causes
breakage. The only problem is that it generates SQL that
PostgreSQL 8.2 isn't quite happy with. Given the following code:
rdy2bill_table = Table('rdy2bill', metadata,
Column('invid', Integer, primary_key=True),
Column('invnum', String),
Column('pprresp', String, key='responsible'),
Column('xmlvars', String),
)
mapper(ReadyToBill, rdy2bill_table, properties={
'customer' : column_property(func.substr
(rdy2bill_table.c.invnum, 1,
4).label('customer')),
'groupcode': column_property(func.substring
(rdy2bill_table.c.xmlvars, M.XRSGRPCD(.*)/M.XRSGRPCD).label
('groupcode')),
})
inv = session.query(ReadyToBill).get_by(invid=1000346504,
groupcode='BILLGSCB')
I get a query like:
'SELECT substr(rdy2bill.invnum, %(substr)s, %(substr_1)s) AS
customer, rdy2bill.xmlvars AS rdy2bill_xmlvars, rdy2bill.pprresp AS
rdy2bill_pprresp, rdy2bill.invnum AS rdy2bill_invnum,
rdy2bill.invid AS rdy2bill_invid, substring(rdy2bill.xmlvars, %
(substring)s) AS groupcode \nFROM rdy2bill \nWHERE (rdy2bill.invid
= %(rdy2bill_invid)s) AND (substring(rdy2bill.xmlvars, %(substring)
s) AS groupcode = %(literal)s) ORDER BY rdy2bill.invid \n LIMIT
1' {'substring': 'M.XRSGRPCD(.*)/M.XRSGRPCD', 'substr': 1,
'literal': 'BILLGSCB', 'rdy2bill_invid': 1000346504, 'substr_1': 4}
The killer part is the (substring(rdy2bill.xmlvars, %(substring)s)
AS groupcode = in the WHERE clause. PostgreSQL apparently
doesn't want that predicate to be named. Can that be disabled?
--
Kirk Strauser
--~--~-~--~~~---~--~~
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: Queries on computed properties
On May 3, 2007, at 5:18 PM, Michael Bayer wrote: not really (well yes, you can take the label off, but then you dont get it in your columns clause, so that will break). mapping to the select statement that includes the column is the more general solution here (also allows the function to be called once instead of twice). Fair enough. But then, is there something else I can do to get it to emit PostgreSQL-compatible SQL? -- Kirk Strauser --~--~-~--~~~---~--~~ 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: Queries on computed properties
On May 3, 2007, at 5:31 PM, Michael Bayer wrote: no wait, scratch my last email for a bit. try rev 2601. Perfect! That was exactly what it needed. I have to say that after using SQLAlchemy for about a week, I'm really excited about this. -- Kirk Strauser --~--~-~--~~~---~--~~ 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: sqlalchemy introspection How?
On Apr 28, 2007, at 12:00 PM, johnny wrote:
What I was trying to get was, there isn't a python command line
option, that will create Object Mapper Classes for the tables, so I
can import them within my applications. I guess, I have to code these
every time I need to use a certain table like this:
metadata = BoundMetaData('dburi')
user_table = table('user', metadata, autoload=True)
class User(object):
pass
mapper(User, user_table)
Thank you.
theres another extension called SqlSoup that does what I think youre
asking for. creates the classes on the fly for each table which it
also reflects on the fly.
http://www.sqlalchemy.org/trac/wiki/SqlSoup
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
