[sqlalchemy] How to search a particular hour?
Hi all,
Anyone have any ideal how to search a particular hour.
usually we can use datepart function in sql.
for example
datepart(hour, datetime) in (7,8)
But it is not really working in sqlalchemy
I tried
func.datepart(func.hour, xx.c.datetime)._in((7,8))
but i got
NotSupportedError: (NotSupportedError) ('Python type Function not
supported.)
can any one help me on this?
Ning
--
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: self referential with 2 forign key constraints
thanks, that was it I looking for -- 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.
Re: [sqlalchemy] inner join and ambiguous columns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: On Feb 17, 2010, at 9:56 AM, Manlio Perillo wrote: Michael Bayer ha scritto: [...] By the way, I have found an incorrect behaviour in SQLAlchemy, when the select column list is empty. query = sql.select( None, contents.c.slug == 'python', from_obj=[join]) SQLAlchemy generates an incorrect SQL query. It should, instead, automatically add the columns from the `from_obj` list, skipping duplicate columns involved in a join. sounds more like an assumption to me. select(None) is specifically a select with no columns, which can be added later using column() (that might be the intent). Of course. The columns should be added when the SQL query is generated. yeah that's not how it works. the columns are added to the structure. statement compilation doesn't make huge guesses like that. Ok. My idea was to implement the equivalent of SQL '*' column. I can't use the literal '*' in the select column list, since it will disable (?) SQLAlchemy type system. And I don't want to manually add the columns, since I will end up with duplicate columns. Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkt9Hi0ACgkQscQJ24LbaURw+ACcDmBfcJTZyJJtn3w7Iw02tUY6 bZAAnAr+m0GNB2pwn/uQFxjHibvaTGpB =9462 -END PGP SIGNATURE- -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to 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.
Re: [sqlalchemy] How to search a particular hour?
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Ning ha scritto:
Hi all,
Anyone have any ideal how to search a particular hour.
usually we can use datepart function in sql.
for example
datepart(hour, datetime) in (7,8)
But it is not really working in sqlalchemy
I tried
func.datepart(func.hour, xx.c.datetime)._in((7,8))
This is not the correct syntax.
http://www.postgresql.org/docs/8.3/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
func.datepart('hour', xxx.c.datetime)
Manlio
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAkt9PWsACgkQscQJ24LbaUSZDACfVErbMbOGAxdles+PSfyt1MFi
cnIAoJT5gz3aM6/HGATkakEczmgQ3B+L
=lWFS
-END PGP SIGNATURE-
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to 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.
Re: [sqlalchemy] How to return object with maximum column via a mapper?
Am Donnerstag 18 Februar 2010 02:07:15 schrieb Michael Bayer:
On Feb 17, 2010, at 12:27 PM, Hermann Himmelbauer wrote:
Hi,
I have the following many to one relation:
- A bank account table (acc)
- An Interest rate table, which relates to the account table. Colums are
an ID, a rate (decimal), a date and a flag outlining if the interest rate
is credit or debit ('H' / 'S')
- One account may have multiple interest rates (one to many)
What I now want is to retrieve the most recent (the current valid)
interest rate for a specific account. For now, I did this like this:
mapper_acc = mapper(Acc, table_acc, properties = {
# Current debit irate
'current_debit_irate': relation(
IRate, order_by = table_irate.c.date.desc(),
uselist = False, primaryjoin = and_(
table_acc.c.accid == table_irate.c.accid,
table_irate.c.type == S),
cascade=all)
})
This works but is very inefficient, as this mapper seems to read in all
interest rate objects despite I use uselist=False, which is slow. So I
wonder if it's possible to optimize this in some way so that SQLAlchemy
constructs some specific SQL, something like:
select * from irates where irateid = (select irateid, max(date) from
irates where accid = 123 and type = 'S' group by date)
Here, you'd build the query representing the max() for your related item,
then create a non-primary mapper which maps IRate to it. Build your
relation then using that nonprimary mapper as the target. I think i just
showed this to someone on this list about a week ago.
iratealias = irate.alias()
i.e. irate_select = select(irate).where(irate.c.id=select([iratealias.c.id,
max(date)]).where(...)).alias()
irate_mapper = mapper(IRate, irate_select, non_primary=True)
mapper(Acc, acc, properties={current_irate, relation(irate_mapper)})
Wow, that was complicated. I managed to simplify (and probably speed up) the
query by using order_by with limit. To sum up, I tried the following:
1) Your idea with a slightly modified mapper:
irate_select =
select([table_irate]).alias().order_by(table_irate.c.date.desc()).limit(1).alias()
irate_mapper = mapper(IRate, irate_select, non_primary = True)
mapper(Acc, acc, properties={current_debit_irate, relation(irate_mapper)})
--- Did not work as my database (MaxDB) unfortunately does not support order
by in subqueries
2) Hint from stepz via #freenode:
# Current debit irate
'current_debit_irate_new2': relation(
IRate, uselist=False,
primaryjoin=table_irate.c.irateid == select(
[table_irate.c.irateid], table_irate.c.accid == table_acc.c.accid
).correlate(table_acc).order_by(
table_irate.c.date.desc()).limit(1)),
Does also not work for the same reason.
3) Hint in another list reply: Use the original with lazy='dynamic'
# Dynamic loading
'current_debit_irate_dynamic': relation(
IRate, order_by = table_irate.c.date.desc(),
uselist = False, lazy = 'dynamic', primaryjoin = and_(
table_acc.c.accid == table_irate.c.accid,
table_irate.c.type == S),
cascade=all),
And then in my class, I have something like this:
@property
def current_debit_irate(self):
return self.current_debit_irate_query[0]
This adds a limit 1 at the end of the query and acutally works! Great!
-- Conclusion ---
To my mind, an excellent thing would be to add something like limit similar
to order_by to the relation() specifier, as this would then save all of the
above.
Best Regards,
Hermann
--
herm...@qwer.tk
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9 4902 64B4 D16B 2998 93C7
--
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.
Re: [sqlalchemy] inner join and ambiguous columns
On Feb 18, 2010, at 6:02 AM, Manlio Perillo wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: On Feb 17, 2010, at 9:56 AM, Manlio Perillo wrote: Michael Bayer ha scritto: [...] By the way, I have found an incorrect behaviour in SQLAlchemy, when the select column list is empty. query = sql.select( None, contents.c.slug == 'python', from_obj=[join]) SQLAlchemy generates an incorrect SQL query. It should, instead, automatically add the columns from the `from_obj` list, skipping duplicate columns involved in a join. sounds more like an assumption to me. select(None) is specifically a select with no columns, which can be added later using column() (that might be the intent). Of course. The columns should be added when the SQL query is generated. yeah that's not how it works. the columns are added to the structure. statement compilation doesn't make huge guesses like that. Ok. My idea was to implement the equivalent of SQL '*' column. I can't use the literal '*' in the select column list, since it will disable (?) SQLAlchemy type system. And I don't want to manually add the columns, since I will end up with duplicate columns. that's a better idea. how about sqlalchemy.EXPAND_STAR ? Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkt9Hi0ACgkQscQJ24LbaURw+ACcDmBfcJTZyJJtn3w7Iw02tUY6 bZAAnAr+m0GNB2pwn/uQFxjHibvaTGpB =9462 -END PGP SIGNATURE- -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to 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. -- 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.
Re: [sqlalchemy] inner join and ambiguous columns
On Feb 18, 2010, at 10:18 AM, Michael Bayer wrote: Ok. My idea was to implement the equivalent of SQL '*' column. I can't use the literal '*' in the select column list, since it will disable (?) SQLAlchemy type system. And I don't want to manually add the columns, since I will end up with duplicate columns. that's a better idea. how about sqlalchemy.EXPAND_STAR ? although, still not a compiler level thing. you still want to be able to say select.c.colname. So it would occur at the point of objects being added to the FROM list. Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkt9Hi0ACgkQscQJ24LbaURw+ACcDmBfcJTZyJJtn3w7Iw02tUY6 bZAAnAr+m0GNB2pwn/uQFxjHibvaTGpB =9462 -END PGP SIGNATURE- -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to 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. -- 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. -- 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.
Re: [sqlalchemy] inner join and ambiguous columns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: On Feb 18, 2010, at 10:18 AM, Michael Bayer wrote: Ok. My idea was to implement the equivalent of SQL '*' column. I can't use the literal '*' in the select column list, since it will disable (?) SQLAlchemy type system. And I don't want to manually add the columns, since I will end up with duplicate columns. that's a better idea. how about sqlalchemy.EXPAND_STAR ? although, still not a compiler level thing. you still want to be able to say select.c.colname. So it would occur at the point of objects being added to the FROM list. Right. A direct support for the special '*' column is better, and in SQL you can still specify additional columns in addition to '*'. As for the name to use, EXPAND_STAR is ok. The only alternative I can think about is something like sqlalchemy.schema.COLUMN_ALL. Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkt9anIACgkQscQJ24LbaUTn0gCff3M4sFUoRz2xV//qYeKjTlLw fJkAn1AK19mS5B4/4ZLk8mjDSRTyDRc4 =wu+0 -END PGP SIGNATURE- -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to 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.
Re: [sqlalchemy] another problem with complex join
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: [...] so what I had in mind is that, if its given a join as the left side, it just does the natural thing, i.e. joins to the right. If the natural join isn't available, then it does its usual search through the whole thing. What do you mean by natural join isn't available? There is no direct foreign key relationship between the left and the right side of the join? I think _match_primaries could, right before it raises its message, Since we are speaking about _match_primaries, I'm curious to know why the implementation is: def _match_primaries(self, primary, secondary): global sql_util if not sql_util: from sqlalchemy.sql import util as sql_util return sql_util.join_condition(primary, secondary) What is the need for a sql_util to be global? just ask well is this particular foreign key the rightmost join on the left side and then its good. Non sure to understand what you have in mind, here. Do you mean that the checks: `if len(crit) == 0` and `len(constraints) 1` should not be done by the util.join_condition, and instead: 1) `_match_primaries` method will call `util.join_condition`, using the rightmost join on the left side (as in my patch) 2) if len(crit) == 0, then it will call `util.join_condition` again, but this time using the left side of the join, as is ? to get non-default behavior, as always you'd specify the on clause. which you'd have to do anyway even without the natural feature if you wanted to joinunnaturally. Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkt9cOYACgkQscQJ24LbaUSS7QCeMchE6p2t3WaHDJzH+dTAu2Xk BBUAmQHpDq8Naq9f4cWsolK9BRnjTBcf =UAU4 -END PGP SIGNATURE- -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to 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: Getting useful error messages
I ran into this also and it was caused by forgetting to map the class to the table. Make sure you have a line like: orm.mapper(TaskAction, taskaction_table) Mike: should there be a more specific error message for a missing mapping? -aw On Feb 3, 11:04 am, Michael Bayer mike...@zzzcomputing.com wrote: King Simon-NFHD78 wrote: The line below the one you're complaining about is telling you what the column in question is: Invalid column expression 'class '__main__.TaskAction'' So somehow, you've passed your TaskAction class in a place where SA is expecting a column expression. I think we'd need to see the command that you actually typed in to work out what the problem is. Also, again, please upgrade to 0.5.8. Hundreds of bugs and sub-optimal behaviors have been fixed since 0.5.4p2 so you may get better results. Simon -- 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. -- 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.
Re: [sqlalchemy] Re: Getting useful error messages
On Feb 18, 2010, at 1:16 PM, Hollister wrote: I ran into this also and it was caused by forgetting to map the class to the table. Make sure you have a line like: orm.mapper(TaskAction, taskaction_table) Mike: should there be a more specific error message for a missing mapping? there's a very specific error for that its sqlalchemy.orm.exc.UnmappedError. -aw On Feb 3, 11:04 am, Michael Bayer mike...@zzzcomputing.com wrote: King Simon-NFHD78 wrote: The line below the one you're complaining about is telling you what the column in question is: Invalid column expression 'class '__main__.TaskAction'' So somehow, you've passed your TaskAction class in a place where SA is expecting a column expression. I think we'd need to see the command that you actually typed in to work out what the problem is. Also, again, please upgrade to 0.5.8. Hundreds of bugs and sub-optimal behaviors have been fixed since 0.5.4p2 so you may get better results. Simon -- 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. -- 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. -- 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] cursor description
hello, I want to know how to find the cursor description i.e equivalent to cursor.description in dbapi.I also want to know whether it is supported in all types of databases? -- Njoy the share of Freedom :) Anusha Kadambala -- 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.
