[sqlalchemy] ValueError: second must be in 0..59 error?
During a select() I get the following error:
2007-04-23 08:02:24,447 INFO sqlalchemy.engine.base.Engine.0x..94 {'table':
24892275}
class 'z3c.sqlalchemy.mapper._mapped_arbeitsmittel'
2007-04-23 08:02:24,480 INFO sqlalchemy.engine.base.Engine.0x..94 SELECT
arbeitsmittel.idsachgebiet AS arbeitsmittel_idsachgebiet,
arbeitsmittel.num_hre AS arbeitsmittel_num_hre, arbeitsmittel.bemerkung AS
arbeitsmittel_bemerkung, arbeitsmittel.versionsnr AS
arbeitsmittel_versionsnr, arbeitsmittel.idzielgruppe AS
arbeitsmittel_idzielgruppe, arbeitsmittel.idfassung AS
arbeitsmittel_idfassung, arbeitsmittel.dateiname AS
arbeitsmittel_dateiname, arbeitsmittel.originalname AS
arbeitsmittel_originalname, arbeitsmittel.rechnerstand AS
arbeitsmittel_rechnerstand, arbeitsmittel.id AS arbeitsmittel_id,
arbeitsmittel.hidx_master AS arbeitsmittel_hidx_master,
arbeitsmittel.stammdatenverwaltung_tauglich AS
arbeitsmittel_stammdaten_859d, arbeitsmittel.rechner_stand AS
arbeitsmittel_rechner_stand, arbeitsmittel.rechner_version AS
arbeitsmittel_rechner_version, arbeitsmittel.version AS
arbeitsmittel_version, arbeitsmittel.generator_tauglich AS
arbeitsmittel_generator__147c, arbeitsmittel.aedat AS arbeitsmittel_aedat,
arbeitsmittel.hidx AS arbeitsmittel_hidx, arbeitsmittel.honorarfrei AS
arbeitsmittel_honorarfrei, arbeitsmittel.neudat AS arbeitsmittel_neudat,
arbeitsmittel.rechtefrei AS arbeitsmittel_rechtefrei, arbeitsmittel.status
AS arbeitsmittel_status, arbeitsmittel.signatur AS arbeitsmittel_signatur,
arbeitsmittel.faxabruf AS arbeitsmittel_faxabruf, arbeitsmittel.format AS
arbeitsmittel_format, arbeitsmittel.idgattung AS arbeitsmittel_idgattung,
arbeitsmittel.stellvertreter AS arbeitsmittel_stellvertreter,
arbeitsmittel.seiten AS arbeitsmittel_seiten, arbeitsmittel.zodb_path AS
arbeitsmittel_zodb_path, arbeitsmittel.markierung AS
arbeitsmittel_markierung, arbeitsmittel.umfang AS arbeitsmittel_umfang,
arbeitsmittel.num_pdf AS arbeitsmittel_num_pdf, arbeitsmittel.chgdat AS
arbeitsmittel_chgdat, arbeitsmittel.versionskommentar AS
arbeitsmittel_versionsko_c73d, arbeitsmittel.idautor AS
arbeitsmittel_idautor, arbeitsmittel.versionsize AS
arbeitsmittel_versionsize, arbeitsmittel.sperrgrund AS
arbeitsmittel_sperrgrund, arbeitsmittel.summary AS arbeitsmittel_summary,
arbeitsmittel.gesperrt_bis AS arbeitsmittel_gesperrt_bis,
arbeitsmittel.stand AS arbeitsmittel_stand, arbeitsmittel.bezeichnung AS
arbeitsmittel_bezeichnung, arbeitsmittel.num_rtf AS arbeitsmittel_num_rtf,
arbeitsmittel.formular_version AS arbeitsmittel_formular_v_9df0,
arbeitsmittel.benutzer AS arbeitsmittel_benutzer
FROM arbeitsmittel ORDER BY arbeitsmittel.id
2007-04-23 08:02:24,480 INFO sqlalchemy.engine.base.Engine.0x..94 {}
Traceback (most recent call last):
File test.py, line 10, in ?
for row in S.query(AM).select():
File build/bdist.linux-i686/egg/sqlalchemy/orm/query.py, line 301, in
select
File build/bdist.linux-i686/egg/sqlalchemy/orm/query.py, line 309, in
select_whereclause
File build/bdist.linux-i686/egg/sqlalchemy/orm/query.py, line 798, in
_select_statement
File build/bdist.linux-i686/egg/sqlalchemy/orm/query.py, line 696, in
execute
File build/bdist.linux-i686/egg/sqlalchemy/orm/query.py, line 750, in
instances
File build/bdist.linux-i686/egg/sqlalchemy/engine/base.py, line 971, in
fetchall
ValueError: second must be in 0..59
Any idea where this is coming from?
Andreas
pgpUc8bkg6qON.pgp
Description: PGP signature
[sqlalchemy] Re: ValueError: second must be in 0..59 error?
Additional info: using SA 0.3.6, Postgres 7.4.6, psycopg 2.0.4
Andreas
--On 23. April 2007 08:09:30 +0200 Andreas Jung [EMAIL PROTECTED] wrote:
During a select() I get the following error:
2007-04-23 08:02:24,447 INFO sqlalchemy.engine.base.Engine.0x..94
{'table': 24892275}
class 'z3c.sqlalchemy.mapper._mapped_arbeitsmittel'
2007-04-23 08:02:24,480 INFO sqlalchemy.engine.base.Engine.0x..94 SELECT
arbeitsmittel.idsachgebiet AS arbeitsmittel_idsachgebiet,
arbeitsmittel.num_hre AS arbeitsmittel_num_hre, arbeitsmittel.bemerkung
AS arbeitsmittel_bemerkung, arbeitsmittel.versionsnr AS
arbeitsmittel_versionsnr, arbeitsmittel.idzielgruppe AS
arbeitsmittel_idzielgruppe, arbeitsmittel.idfassung AS
arbeitsmittel_idfassung, arbeitsmittel.dateiname AS
arbeitsmittel_dateiname, arbeitsmittel.originalname AS
arbeitsmittel_originalname, arbeitsmittel.rechnerstand AS
arbeitsmittel_rechnerstand, arbeitsmittel.id AS arbeitsmittel_id,
arbeitsmittel.hidx_master AS arbeitsmittel_hidx_master,
arbeitsmittel.stammdatenverwaltung_tauglich AS
arbeitsmittel_stammdaten_859d, arbeitsmittel.rechner_stand AS
arbeitsmittel_rechner_stand, arbeitsmittel.rechner_version AS
arbeitsmittel_rechner_version, arbeitsmittel.version AS
arbeitsmittel_version, arbeitsmittel.generator_tauglich AS
arbeitsmittel_generator__147c, arbeitsmittel.aedat AS
arbeitsmittel_aedat, arbeitsmittel.hidx AS arbeitsmittel_hidx,
arbeitsmittel.honorarfrei AS arbeitsmittel_honorarfrei,
arbeitsmittel.neudat AS arbeitsmittel_neudat, arbeitsmittel.rechtefrei AS
arbeitsmittel_rechtefrei, arbeitsmittel.status AS arbeitsmittel_status,
arbeitsmittel.signatur AS arbeitsmittel_signatur, arbeitsmittel.faxabruf
AS arbeitsmittel_faxabruf, arbeitsmittel.format AS arbeitsmittel_format,
arbeitsmittel.idgattung AS arbeitsmittel_idgattung,
arbeitsmittel.stellvertreter AS arbeitsmittel_stellvertreter,
arbeitsmittel.seiten AS arbeitsmittel_seiten, arbeitsmittel.zodb_path AS
arbeitsmittel_zodb_path, arbeitsmittel.markierung AS
arbeitsmittel_markierung, arbeitsmittel.umfang AS arbeitsmittel_umfang,
arbeitsmittel.num_pdf AS arbeitsmittel_num_pdf, arbeitsmittel.chgdat AS
arbeitsmittel_chgdat, arbeitsmittel.versionskommentar AS
arbeitsmittel_versionsko_c73d, arbeitsmittel.idautor AS
arbeitsmittel_idautor, arbeitsmittel.versionsize AS
arbeitsmittel_versionsize, arbeitsmittel.sperrgrund AS
arbeitsmittel_sperrgrund, arbeitsmittel.summary AS arbeitsmittel_summary,
arbeitsmittel.gesperrt_bis AS arbeitsmittel_gesperrt_bis,
arbeitsmittel.stand AS arbeitsmittel_stand, arbeitsmittel.bezeichnung AS
arbeitsmittel_bezeichnung, arbeitsmittel.num_rtf AS
arbeitsmittel_num_rtf, arbeitsmittel.formular_version AS
arbeitsmittel_formular_v_9df0, arbeitsmittel.benutzer AS
arbeitsmittel_benutzer
FROM arbeitsmittel ORDER BY arbeitsmittel.id
2007-04-23 08:02:24,480 INFO sqlalchemy.engine.base.Engine.0x..94 {}
Traceback (most recent call last):
File test.py, line 10, in ?
for row in S.query(AM).select():
File build/bdist.linux-i686/egg/sqlalchemy/orm/query.py, line 301, in
select
File build/bdist.linux-i686/egg/sqlalchemy/orm/query.py, line 309, in
select_whereclause
File build/bdist.linux-i686/egg/sqlalchemy/orm/query.py, line 798, in
_select_statement
File build/bdist.linux-i686/egg/sqlalchemy/orm/query.py, line 696, in
execute
File build/bdist.linux-i686/egg/sqlalchemy/orm/query.py, line 750, in
instances
File build/bdist.linux-i686/egg/sqlalchemy/engine/base.py, line 971,
in fetchall
ValueError: second must be in 0..59
Any idea where this is coming from?
Andreas
--
ZOPYX Ltd. Co. KG - Charlottenstr. 37/1 - 72070 Tübingen - Germany
Web: www.zopyx.com - Email: [EMAIL PROTECTED] - Phone +49 - 7071 - 793376
Registergericht: Amtsgericht Stuttgart, Handelsregister A 381535
Geschäftsführer/Gesellschafter: ZOPYX Limited, Birmingham, UK
E-Publishing, Python, Zope Plone development, Consulting
pgppxZ5aisCry.pgp
Description: PGP signature
[sqlalchemy] Re: ValueError: second must be in 0..59 error?
--On 23. April 2007 08:18:54 +0200 Andreas Jung [EMAIL PROTECTED] wrote: Additional info: using SA 0.3.6, Postgres 7.4.6, psycopg 2.0.4 Andreas File build/bdist.linux-i686/egg/sqlalchemy/engine/base.py, line 971, in fetchall ValueError: second must be in 0..59 Any idea where this is coming from? This issue is caused by a TIMESTAMP column within our database with NULL values. psycopg2 tried to convert them to Python datetime instances which fails of course...any idea how to get around this issue on the SA level? Andreas pgpJ1K9lKlC6N.pgp Description: PGP signature
[sqlalchemy] Re: ValueError: second must be in 0..59 error?
* Andreas Jung [EMAIL PROTECTED] [070423 09:49]: --On 23. April 2007 08:18:54 +0200 Andreas Jung [EMAIL PROTECTED] wrote: Additional info: using SA 0.3.6, Postgres 7.4.6, psycopg 2.0.4 Andreas File build/bdist.linux-i686/egg/sqlalchemy/engine/base.py, line 971, in fetchall ValueError: second must be in 0..59 Any idea where this is coming from? This issue is caused by a TIMESTAMP column within our database with NULL values. psycopg2 tried to convert them to Python datetime instances which fails of course...any idea how to get around this issue on the SA level? Well, psycopg2 handles that case well enough: x.fetchall() [(datetime.datetime(2007, 4, 5, 14, 55, 55),)] x.execute(SELECT NULL::timestamp;) x.fetchall() [(None,)] x.execute(SELECT '2007-04-05 14:55:55'::timestamp;) x.fetchall() [(datetime.datetime(2007, 4, 5, 14, 55, 55),)] psycopg2.__version__ '2.0.5.1 (dec dt ext pq3)' What I've found in this case, it makes sense to activate SQL logging, and take a hard look at the data. E.g. I once had somebody enter a date like 04/01/200038. PostgreSQL (and the app that inserted it used manually constructed sql) accepted that timestamp. The sqlalch app trying to read that timestamp crashed, because datetime objects have a smaller dynamic range than timestamp in PostgreSQL. But that wouldn't apply to seconds. Only way to be sure is to capture the SQL statement fetching the data, and fetch it manually with psql to see what is SO special about the data? Andreas --~--~-~--~~~---~--~~ 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: apache, engine and metadata
2) the number of connections to the db growing ... is implemented well the MetaData() class ? I'm using postgresql schema's and I want to mantain a single connection for every db user. Hi! I had the same problem because I was calling create_engine in every page request (apache + mod_python). Then I changed it to something like: g_engine = None def conn(): global g_engine if not g_engine: g_engine = create_engine() return g_engine The module is loaded only once per Apache process. I hope I directed you the right way :-) David --~--~-~--~~~---~--~~ 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] Catching username password errors
I am trying to catch username password errors to show in the
interface. But somehow I cannot catch the exeption. I wrap everything
in a try except, but the exeption is still throwed and my app stops
functioning. What am I doing wrong?
Koen
loginInfo = (
self.username,
self.password,
store.server.address,
store.server.port,
store.tableName.replace(' ', '\ '))
engineURL = 'postgres://%s:[EMAIL PROTECTED]:%s/%s' % loginInfo
try:
engine = create_engine(engineURL)
try:
c = engine.connect()
c.close()
finally:
d.dispose()
except:
self.showError(UserPasswordError)
return False
else:
self.showError(None)
# Make the actual connection here, we should improve this
try:
engine.connect(engineURL)
result = engine.execute(text('SELECT NOW()'))
except:
engine.dispose()
self.showError(UserPasswordError)
return False
--~--~-~--~~~---~--~~
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: Catching username password errors
and the exception traceback is?
also, do provide something functional as tryout-case.
On Monday 23 April 2007 16:54:40 Koen Bok wrote:
I am trying to catch username password errors to show in the
interface. But somehow I cannot catch the exeption. I wrap
everything in a try except, but the exeption is still throwed and
my app stops functioning. What am I doing wrong?
Koen
loginInfo = (
self.username,
self.password,
store.server.address,
store.server.port,
store.tableName.replace(' ', '\ '))
engineURL = 'postgres://%s:[EMAIL PROTECTED]:%s/%s' % loginInfo
try:
engine = create_engine(engineURL)
try:
c = engine.connect()
c.close()
finally:
d.dispose()
except:
self.showError(UserPasswordError)
return False
else:
self.showError(None)
# Make the actual connection here, we should improve this
try:
engine.connect(engineURL)
result = engine.execute(text('SELECT NOW()'))
except:
engine.dispose()
self.showError(UserPasswordError)
return False
--~--~-~--~~~---~--~~
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: Solution!!! ENUM type that works with sqlite too
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/Enum
On Sun, 2007-04-22 at 13:33 +, Ian Charnas wrote:
I'm sure a lot of us have done something like this, I figured I'd post
it so people can find it in a google search and won't have to write it
and debug it themselves... This is a Type that emulates ENUM for
engines (like sqlite) that don't have a native ENUM type. This
*could* be augmented so that it uses the actual ENUM type for engines
that support it. For those interested, you'd add a get_col_spec
method (see other types in sqlalchemy/types.py for details)
class EnumType(Unicode):
Basic type that emulates ENUM sql type.
Useful for database engines that don't support ENUM (such as
sqlite).
required 'names' parameter must be a list of strings for this ENUM
def __init__(self, names, *args, **kw):
self.names = names
super(EnumType, self).__init__(*args, **kw)
def convert_bind_param(self, value, engine):
if value is not None and value not in self.names:
raise EnumError(Value(%s) not in Enum(%s) %
(value, , .join(self.names)))
return super(EnumType, self).convert_bind_param(value, engine)
and this is how you'd use it:
customers_table = Table('customer', metadata,
Column('name', String(50)),
Column('frequency', EnumType(names=['Rare', 'Occasional',
'Regular']), default='Rare'),
)
--~--~-~--~~~---~--~~
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: Proposal: Make pyodbc the preferred DB-API for MSSQL
Hi,
Something like:
from sqlalchemy import *
import pyodbc
db = create_engine('mssql://./test', module=pyodbc)
While we're on the subject, it would be good to be able to specify the
module as a string, e.g. module_name='pyodbc' as then you could specify
the module from a configuration framework. It would be handy to specify
it in the URL as well, e.g. for unit tests.
Paul
[EMAIL PROTECTED] wrote:
How does one explicitly choose one of the connectors?
I would like to specify one explicitly for testing purposes, but I
don't know how. (in the create engine perhaps?)
On Apr 21, 8:16 am, Paul Johnston [EMAIL PROTECTED] wrote:
--~--~-~--~~~---~--~~
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: Proposal: Make pyodbc the preferred DB-API for MSSQL
On Apr 23, 2007, at 1:30 PM, Paul Johnston wrote:
Hi,
Something like:
from sqlalchemy import *
import pyodbc
db = create_engine('mssql://./test', module=pyodbc)
While we're on the subject, it would be good to be able to specify the
module as a string, e.g. module_name='pyodbc' as then you could
specify
the module from a configuration framework. It would be handy to
specify
it in the URL as well, e.g. for unit tests.
we have all that. all create_engines accept a module argument, and
ms-sql specifically adds a module_name argument i believe.
--~--~-~--~~~---~--~~
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: Could not assemble any primary key columns for mapped table error for views
On Apr 23, 2007, at 1:23 PM, Andreas Jung wrote: I have a view arbeitsmittelhierarchy_arbeitsmittle_view which is defined as a left join of two tables. Trying to generate a mapper from the autoload view as Table() fails: File build/bdist.linux-i686/egg/sqlalchemy/orm/session.py, line 277, in query File build/bdist.linux-i686/egg/sqlalchemy/orm/mapper.py, line 1838, in class_mapper File build/bdist.linux-i686/egg/sqlalchemy/orm/mapper.py, line 321, in compile File build/bdist.linux-i686/egg/sqlalchemy/orm/mapper.py, line 336, in _compile_all File build/bdist.linux-i686/egg/sqlalchemy/orm/mapper.py, line 363, in _do_compile File build/bdist.linux-i686/egg/sqlalchemy/orm/mapper.py, line 518, in _compile_tables sqlalchemy.exceptions.ArgumentError: Could not assemble any primary key columns for mapped table 'arbeitsmittelhierarchy_arbeitsmittel_view' What is the workaround to deal with such views? Creating a PrimaryKeyConstraint for the view? Andreas specify primary_key=[cols] to your mapper(). --~--~-~--~~~---~--~~ 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] Selectable/subquery for a (scalar) column?
Hello there,
In a mapped object, is there any way to map a scalar attribute to an
arbitrary selectable/subquery?
Jonathan Ellis demonstrated how to do that for relations on this page:
http://spyced.blogspot.com/2007/01/why-sqlalchemy-impresses-me.html
I'd like to do that for scalars.
I've thought about using a property returning a query, but this still
generates one query per user (my mapped object) and I need to do
everything in one pass.
See attached file for an example of what I'd like to do.
I've the feeling it might already be possible but I don't see how. If
it's not possible yet, do you have any pointer how I could implement
that?
--
Gaëtan de Menten
http://openhex.org
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
from sqlalchemy import *
metadata = MetaData()
users_table = Table('users', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(16)),
)
tags_table = Table('tags', metadata,
Column('id', Integer, primary_key=True),
Column('user_id', Integer, ForeignKey(users.id)),
Column('score1', Float),
Column('score2', Float)
)
metadata.connect('sqlite:///')
metadata.create_all()
class User(object):
def __init__(self, name):
self.name = name
@property
def score_v1(self):
return sum(tag.score for tag in self.tags)
@property
def score_v2(self):
tags = object_session(self).query(Tag).filter_by(user=self)
return tags.sum(tags_table.c.score1 * tags_table.c.score2)
class Tag(object):
def __init__(self, score1, score2):
self.score1 = score1
self.score2 = score2
@property
def score(self):
return self.score1 * self.score2
mapper(Tag, tags_table)
mapper(User, users_table, properties={
'tags': relation(Tag, backref='user'),
})
u1 = User('joe')
t1 = Tag(5.0, 3.0)
t2 = Tag(55.0, 1.0)
u1.tags = [t1, t2]
u2 = User('bar')
t3 = Tag(5.0, 4.0)
t4 = Tag(50.0, 1.0)
t5 = Tag(15.0, 2.0)
u2.tags = [t3, t4, t5]
session = create_session()
session.save(u1)
session.save(u2)
session.flush()
session.clear()
metadata.engine.echo = True
print ---v1---
# generates 1 query per user and loads tag objects
users = session.query(User).select()
for user in users:
print user.name, user.score_v1
session.clear()
print ---v2---
# generates 1 query per user but doesn't load tag objects
users = session.query(User).select()
for user in users:
print user.name, user.score_v2
# I'd like to do the whole thing in 1 query for all users (and without loading
# tag objects)
#mapper(tag, tags_table, properties={
#'score': special_column(
#tags_table.c.score1 * tags_table.c.score2)})
#ideally, reuse subtotal
#score_by_user = select([func.sum(Tag.c.subtotal).label('score')],
#score_by_user = select([func.sum(tags_table.c.score1 *
# tags_table.c.score2).label('score')],
# group_by=[tags_table.c.user_id])
#mapper(User, users_table, properties={
#'tags': relation(tag, backref='user'),
#'score': special_column(tags_value_by_user),
#})
# I also need to be able to defer/undefer those special columns
[sqlalchemy] Re: Selectable/subquery for a (scalar) column?
On Apr 23, 2007, at 3:49 PM, Gaetan de Menten wrote:
Hello there,
In a mapped object, is there any way to map a scalar attribute to an
arbitrary selectable/subquery?
Jonathan Ellis demonstrated how to do that for relations on this page:
http://spyced.blogspot.com/2007/01/why-sqlalchemy-impresses-me.html
I'd like to do that for scalars.
I've thought about using a property returning a query, but this still
generates one query per user (my mapped object) and I need to do
everything in one pass.
See attached file for an example of what I'd like to do.
I've the feeling it might already be possible but I don't see how. If
it's not possible yet, do you have any pointer how I could implement
that?
there are probably three general ways to do what youre doing there.
the oldest way is something i did in the zblog demo before SA 0.1 was
released, which is that you map to the full query you want:
s = select([users_table, func.sum(tags_table.c.score1 *
tags_table.c.score2).label('score')],
users_table.c.user_id==tags_table.c.user_id, group_by=[c for c in
users_table.c])
mapper(User, s)
the effect above is that your func() becomes another ColumnProperty.
the next way is to do it almost the same as Jonathan's blog says to
do it, except youd map the relation to some intermediary class like
Score, and then use AssociationProxy to apply the scalar property
to the class. I might put a built-in feature in sa for scalar
properties that does this, automatically creating an anonymous class
for the intermediaryso that would be the closest to a scalar
relation.
the third way is not as slick but is to use add_column() on query:
session.query(User).join('tags').add_column(func.sum
(tags_table.c.score1 * tags_table.c.score2).label('score'))).group_by
([c for c in users_table.c])
which will give you back tuples of (User, int).
--~--~-~--~~~---~--~~
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: subquery using the ORM
I don't have a very good idea about that. But I do know the query I have used works. Still hoping somebody has an idea of how to achieve that using the ORM. Or maybe I should be using a select and then mapping it. /kk On 4/20/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: sorry for my somewhat stupid question, isn't this possible to be done via joins instead of subselects? On Friday 20 April 2007 03:21:35 Karthik Krishnamurthy wrote: Hi, Given the application in the attached file how do I query for nodes that belong to groupNode id's 1 AND 2. /kk --~--~-~--~~~---~--~~ 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: subquery using the ORM
assuming a reasonable mapping (i.e. the m2m example in the datamapping docs) session.query(Node).select(and_( nodes.c.id.in_(select([group_nodes.c.node_id], group_nodes.c.group_id==1, correlate=False), nodes.c.id.in_(select([group_nodes.c.node_id], group_nodes.c.group_id==2, correlate=False)) )) the query would probably be better expressed as a couple of JOINs to aliases of the group_nodes table instead of using IN On Apr 23, 2007, at 5:23 PM, Karthik Krishnamurthy wrote: I don't have a very good idea about that. But I do know the query I have used works. Still hoping somebody has an idea of how to achieve that using the ORM. Or maybe I should be using a select and then mapping it. /kk On 4/20/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: sorry for my somewhat stupid question, isn't this possible to be done via joins instead of subselects? On Friday 20 April 2007 03:21:35 Karthik Krishnamurthy wrote: Hi, Given the application in the attached file how do I query for nodes that belong to groupNode id's 1 AND 2. /kk --~--~-~--~~~---~--~~ 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] different number range for primary key
Hi everyone, I have a product table which has a product_id field as the primary key. I'd like to have the value of this field be generated from two sequences with different number range depending on the content of the product_type field. E.g., when product_type == 1, product_id will be generated between 1000 and 8999, and when product_type == 2, product_id will be generated between 9000 and . In the database, I can simply define a before insert trigger, and get the next value from the 2 sequences depending on the value of product_type, since the trigger function has access to the whole record to be inserted. In SQLAlchemy however, I can find a way to define such a table. Because product_id is the primary key column, I can't use a database trigger with PassiveDefault since it will be pre-executed anyway. It seems that I shall use a pre-executed default function to fetch the appropriate sequence value, but I couldn't find a way to access the product_type field from within the default function. How do I access other table columns from the default function? If it is not possible, is there a work around for my situation? Best Regards Hong Yuan --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
