[sqlalchemy] Re: SqlAlchemy limiting returned rows prematurely
I think the problem might be not the query itself, but the date ranges that get converted using wrong date format. Do you get the same results if you use real datetime objects instead of strings there? --~--~-~--~~~---~--~~ 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: SqlAlchemy limiting returned rows prematurely
Hi, On Jun 30, 8:10 am, Maciej Szumocki mszumo...@contman.pl wrote: I think the problem might be not the query itself, but the date ranges that get converted using wrong date format. Do you get the same results if you use real datetime objects instead of strings there? I was beginning to think along the same lines, but I tried both datetime.datetime and datetime.date and got the same results. SqlAlchemy never does show what rows it's fetching either, as Bayer said it should. I even tried using SA 0.5.0rc4 just to see if it was some kind of weird regression, but that didn't help either. - Mike --~--~-~--~~~---~--~~ 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: SqlAlchemy limiting returned rows prematurely
Mike Driscoll wrote:
Hi,
On Jun 30, 8:10 am, Maciej Szumocki mszumo...@contman.pl wrote:
I think the problem might be not the query itself, but the date ranges
that get converted using wrong date format. Do you get the same
results if you use real datetime objects instead of strings there?
I was beginning to think along the same lines, but I tried both
datetime.datetime and datetime.date and got the same results.
SqlAlchemy never does show what rows it's fetching either, as Bayer
said it should. I even tried using SA 0.5.0rc4 just to see if it was
some kind of weird regression, but that didn't help either.
build a standalone test script that is entirely outside of the domain of
your application:
engine = create_engine('url', echo='debug')
engine.execute(your query).fetchall()
what do you get ?
--~--~-~--~~~---~--~~
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: SqlAlchemy limiting returned rows prematurely
Hi,
On Jun 30, 10:42 am, Michael Bayer mike...@zzzcomputing.com wrote:
Mike Driscoll wrote:
Hi,
On Jun 30, 8:10 am, Maciej Szumocki mszumo...@contman.pl wrote:
I think the problem might be not the query itself, but the date ranges
that get converted using wrong date format. Do you get the same
results if you use real datetime objects instead of strings there?
I was beginning to think along the same lines, but I tried both
datetime.datetime and datetime.date and got the same results.
SqlAlchemy never does show what rows it's fetching either, as Bayer
said it should. I even tried using SA 0.5.0rc4 just to see if it was
some kind of weird regression, but that didn't help either.
build a standalone test script that is entirely outside of the domain of
your application:
engine = create_engine('url', echo='debug')
engine.execute(your query).fetchall()
what do you get ?
That seems to give me all the information including the rows from
November and December that were missing before. That's interesting. I
was trying to remember how to do it using just straight SQL, but I
couldn't find it on the website for some reason. I wonder why my
session doesn't work then...
Here's the SQL it prints out:
2009-06-30 14:13:37,467 INFO sqlalchemy.engine.base.Engine.0x...e5f0
SELECT
Checks.CHK_DOC_NO,
Checks.EMP_ID,
Checks.CHECK_DATE,
Checks.CHECK_NO,
Checks.CHECK_AMT,
Checks.STATUS,
Checks.PAY_PERIOD,
Checks.DIRECT_DEPOSIT
FROM
MyDB.dbo.CheckHistory Checks
WHERE CHECK_DATE BETWEEN '1/1/2007' and '12/31/2007'
ORDER BY Checks.CHECK_DATE DESC
- Mike
--~--~-~--~~~---~--~~
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: SqlAlchemy limiting returned rows prematurely
Hi all,
Mike Driscoll wrote:
Hi,
On Jun 30, 8:10 am, Maciej Szumocki mszumo...@contman.pl wrote:
I think the problem might be not the query itself, but the date ranges
that get converted using wrong date format. Do you get the same
results if you use real datetime objects instead of strings there?
I was beginning to think along the same lines, but I tried both
datetime.datetime and datetime.date and got the same results.
SqlAlchemy never does show what rows it's fetching either, as Bayer
said it should. I even tried using SA 0.5.0rc4 just to see if it was
some kind of weird regression, but that didn't help either.
build a standalone test script that is entirely outside of the domain of
your application:
engine = create_engine('url', echo='debug')
engine.execute(your query).fetchall()
what do you get ?
That seems to give me all the information including the rows from
November and December that were missing before. That's interesting. I
was trying to remember how to do it using just straight SQL, but I
couldn't find it on the website for some reason. I wonder why my
session doesn't work then...
- Mike
Well, I finally found out why it works in SQL Server and not in SA. I
was supposed to be querying against a test db that was a replica of
our online system. Alas, it was not. Lots of data is missing from the
database I was using. If I change the connection string to run the
query against our live database, it works fine.
I apologize for all the noise. Sheesh!
Mike
--~--~-~--~~~---~--~~
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: SqlAlchemy limiting returned rows prematurely
Hi, On Jun 26, 5:40 pm, Michael Bayer mike...@zzzcomputing.com wrote: Mike Driscoll wrote: Hi, I am having an issue with what is getting returned from my SA query. Here's my code: code beginDate = 1/1/2007 endDate = 12/31/2007 qry = session.query(CheckHistory.CHK_DOC_NO, CheckHistory.EMP_ID, CheckHistory.CHECK_DATE, CheckHistory.CHECK_NO, CheckHistory.CHECK_AMT, CheckHistory.STATUS, CheckHistory.PAY_PERIOD, CheckHistory.DIRECT_DEPOSIT ) qry = qry.filter(CheckHistory.CHECK_DATE.between(beginDate, endDate) ).order_by(CheckHistory.CHECK_DATE.desc()) result = qry.all() /code When I run this, it only shows results between 1/1 and 1/10. If I run the equivalent SQL query directly in MS Sql Server 2000's Enterprise Manager, I get everything between 1/1 and 1/12 as I should. Does SqlAlchemy have some kind of result set limiter? There are lots of rows returned... I am using SA 0.5.4p2 and Python 2.5. Thanks! use echo=debug on your engine to see what rows are being fetched. the query you have above is very straightforward. I thought it was pretty simple too. I turned debug mode on and it looks like SA spits out a ton of SQL queries before it ever gets to the one I sent it. When it does mine, it shows this: SELECT [CHECKHISTORY].[CHK_DOC_NO] AS [CHECKHISTORY_CHK_DOC_NO], [CHECKHISTORY].[EMP_ID] AS [CHECKHISTORY_EMP_ID], [CHECKHISTORY]. [CHECK_DATE] AS [CHECKHISTORY_CHECK_DATE], [CHECKHISTORY].[CHECK_NO] AS [CHECKHISTORY_CHECK_NO], [CHECKHISTORY].[CHECK_AMT] AS [CHECKHISTORY_CHECK_AMT], [CHECKHISTORY].[STATUS] AS [CHECKHISTORY_STATUS], [CHECKHISTORY].[PAY_PERIOD] AS [CHECKHISTORY_PAY_PERIOD], [CHECKHISTORY].[DIRECT_DEPOSIT] AS [CHECKHISTORY_DIRECT_DEPOSIT] FROM [CHECKHISTORY] WHERE [CHECKHISTORY].[CHECK_DATE] BETWEEN ? AND ? ORDER BY [CHECKHISTORY].[CHECK_DATE] DESC 2009-06-29 09:27:46,703 INFO sqlalchemy.engine.base.Engine.0x...5eb0 ['1/1/2007', '12/31/2007'] This looks right to me, but when I print out the data using a simple for loop, the latest date is 10/31/2007., not 12/31/2007. I must be missing something really simple. - Mike --~--~-~--~~~---~--~~ 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: SqlAlchemy limiting returned rows prematurely
Mike Driscoll wrote: Hi, On Jun 26, 5:40 pm, Michael Bayer mike...@zzzcomputing.com wrote: Mike Driscoll wrote: Hi, I am having an issue with what is getting returned from my SA query. Here's my code: code beginDate = 1/1/2007 endDate = 12/31/2007 qry = session.query(CheckHistory.CHK_DOC_NO, CheckHistory.EMP_ID, CheckHistory.CHECK_DATE, CheckHistory.CHECK_NO, CheckHistory.CHECK_AMT, CheckHistory.STATUS, CheckHistory.PAY_PERIOD, CheckHistory.DIRECT_DEPOSIT ) qry = qry.filter(CheckHistory.CHECK_DATE.between(beginDate, endDate) ).order_by(CheckHistory.CHECK_DATE.desc()) result = qry.all() /code When I run this, it only shows results between 1/1 and 1/10. If I run the equivalent SQL query directly in MS Sql Server 2000's Enterprise Manager, I get everything between 1/1 and 1/12 as I should. Does SqlAlchemy have some kind of result set limiter? There are lots of rows returned... I am using SA 0.5.4p2 and Python 2.5. Thanks! use echo=debug on your engine to see what rows are being fetched. the query you have above is very straightforward. I thought it was pretty simple too. I turned debug mode on and it looks like SA spits out a ton of SQL queries before it ever gets to the one I sent it. When it does mine, it shows this: SELECT [CHECKHISTORY].[CHK_DOC_NO] AS [CHECKHISTORY_CHK_DOC_NO], [CHECKHISTORY].[EMP_ID] AS [CHECKHISTORY_EMP_ID], [CHECKHISTORY]. [CHECK_DATE] AS [CHECKHISTORY_CHECK_DATE], [CHECKHISTORY].[CHECK_NO] AS [CHECKHISTORY_CHECK_NO], [CHECKHISTORY].[CHECK_AMT] AS [CHECKHISTORY_CHECK_AMT], [CHECKHISTORY].[STATUS] AS [CHECKHISTORY_STATUS], [CHECKHISTORY].[PAY_PERIOD] AS [CHECKHISTORY_PAY_PERIOD], [CHECKHISTORY].[DIRECT_DEPOSIT] AS [CHECKHISTORY_DIRECT_DEPOSIT] FROM [CHECKHISTORY] WHERE [CHECKHISTORY].[CHECK_DATE] BETWEEN ? AND ? ORDER BY [CHECKHISTORY].[CHECK_DATE] DESC 2009-06-29 09:27:46,703 INFO sqlalchemy.engine.base.Engine.0x...5eb0 ['1/1/2007', '12/31/2007'] This looks right to me, but when I print out the data using a simple for loop, the latest date is 10/31/2007., not 12/31/2007. I must be missing something really simple. the debug setting should log the actual result rows too as they are fetched. you might want to flush() your session before you emit that query, drop into a pdb session, and poke around to see what the state of the transaction is before the query is issued. --~--~-~--~~~---~--~~ 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: SqlAlchemy limiting returned rows prematurely
Hi, On Jun 29, 9:51 am, Michael Bayer mike...@zzzcomputing.com wrote: Mike Driscoll wrote: Hi, On Jun 26, 5:40 pm, Michael Bayer mike...@zzzcomputing.com wrote: Mike Driscoll wrote: Hi, I am having an issue with what is getting returned from my SA query. Here's my code: code beginDate = 1/1/2007 endDate = 12/31/2007 qry = session.query(CheckHistory.CHK_DOC_NO, CheckHistory.EMP_ID, CheckHistory.CHECK_DATE, CheckHistory.CHECK_NO, CheckHistory.CHECK_AMT, CheckHistory.STATUS, CheckHistory.PAY_PERIOD, CheckHistory.DIRECT_DEPOSIT ) qry = qry.filter(CheckHistory.CHECK_DATE.between(beginDate, endDate) ).order_by(CheckHistory.CHECK_DATE.desc()) result = qry.all() /code When I run this, it only shows results between 1/1 and 1/10. If I run the equivalent SQL query directly in MS Sql Server 2000's Enterprise Manager, I get everything between 1/1 and 1/12 as I should. Does SqlAlchemy have some kind of result set limiter? There are lots of rows returned... I am using SA 0.5.4p2 and Python 2.5. Thanks! use echo=debug on your engine to see what rows are being fetched. the query you have above is very straightforward. I thought it was pretty simple too. I turned debug mode on and it looks like SA spits out a ton of SQL queries before it ever gets to the one I sent it. When it does mine, it shows this: SELECT [CHECKHISTORY].[CHK_DOC_NO] AS [CHECKHISTORY_CHK_DOC_NO], [CHECKHISTORY].[EMP_ID] AS [CHECKHISTORY_EMP_ID], [CHECKHISTORY]. [CHECK_DATE] AS [CHECKHISTORY_CHECK_DATE], [CHECKHISTORY].[CHECK_NO] AS [CHECKHISTORY_CHECK_NO], [CHECKHISTORY].[CHECK_AMT] AS [CHECKHISTORY_CHECK_AMT], [CHECKHISTORY].[STATUS] AS [CHECKHISTORY_STATUS], [CHECKHISTORY].[PAY_PERIOD] AS [CHECKHISTORY_PAY_PERIOD], [CHECKHISTORY].[DIRECT_DEPOSIT] AS [CHECKHISTORY_DIRECT_DEPOSIT] FROM [CHECKHISTORY] WHERE [CHECKHISTORY].[CHECK_DATE] BETWEEN ? AND ? ORDER BY [CHECKHISTORY].[CHECK_DATE] DESC 2009-06-29 09:27:46,703 INFO sqlalchemy.engine.base.Engine.0x...5eb0 ['1/1/2007', '12/31/2007'] This looks right to me, but when I print out the data using a simple for loop, the latest date is 10/31/2007., not 12/31/2007. I must be missing something really simple. the debug setting should log the actual result rows too as they are fetched. you might want to flush() your session before you emit that query, drop into a pdb session, and poke around to see what the state of the transaction is before the query is issued. Well, that's weird. I'm not doing anything to my session before I run the query since this is a debugging script. I usually write all my new queries in a self-contained file to make it easier to tell what's going on. Anyway, I'll do a flush and paste all its output here: http://paste.pocoo.org/show/125810/ I'll get my superior to look at it tomorrow with some kind of debugger as he has more experience with those. Thanks, Mike --~--~-~--~~~---~--~~ 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: SqlAlchemy limiting returned rows prematurely
Mike Driscoll wrote: Hi, On Jun 29, 9:51 am, Michael Bayer mike...@zzzcomputing.com wrote: Mike Driscoll wrote: Hi, On Jun 26, 5:40 pm, Michael Bayer mike...@zzzcomputing.com wrote: Mike Driscoll wrote: Hi, I am having an issue with what is getting returned from my SA query. Here's my code: code beginDate = 1/1/2007 endDate = 12/31/2007 qry = session.query(CheckHistory.CHK_DOC_NO, CheckHistory.EMP_ID, CheckHistory.CHECK_DATE, CheckHistory.CHECK_NO, CheckHistory.CHECK_AMT, CheckHistory.STATUS, CheckHistory.PAY_PERIOD, CheckHistory.DIRECT_DEPOSIT ) qry = qry.filter(CheckHistory.CHECK_DATE.between(beginDate, endDate) ).order_by(CheckHistory.CHECK_DATE.desc()) result = qry.all() /code When I run this, it only shows results between 1/1 and 1/10. If I run the equivalent SQL query directly in MS Sql Server 2000's Enterprise Manager, I get everything between 1/1 and 1/12 as I should. Does SqlAlchemy have some kind of result set limiter? There are lots of rows returned... I am using SA 0.5.4p2 and Python 2.5. Thanks! use echo=debug on your engine to see what rows are being fetched. the query you have above is very straightforward. I thought it was pretty simple too. I turned debug mode on and it looks like SA spits out a ton of SQL queries before it ever gets to the one I sent it. When it does mine, it shows this: SELECT [CHECKHISTORY].[CHK_DOC_NO] AS [CHECKHISTORY_CHK_DOC_NO], [CHECKHISTORY].[EMP_ID] AS [CHECKHISTORY_EMP_ID], [CHECKHISTORY]. [CHECK_DATE] AS [CHECKHISTORY_CHECK_DATE], [CHECKHISTORY].[CHECK_NO] AS [CHECKHISTORY_CHECK_NO], [CHECKHISTORY].[CHECK_AMT] AS [CHECKHISTORY_CHECK_AMT], [CHECKHISTORY].[STATUS] AS [CHECKHISTORY_STATUS], [CHECKHISTORY].[PAY_PERIOD] AS [CHECKHISTORY_PAY_PERIOD], [CHECKHISTORY].[DIRECT_DEPOSIT] AS [CHECKHISTORY_DIRECT_DEPOSIT] FROM [CHECKHISTORY] WHERE [CHECKHISTORY].[CHECK_DATE] BETWEEN ? AND ? ORDER BY [CHECKHISTORY].[CHECK_DATE] DESC 2009-06-29 09:27:46,703 INFO sqlalchemy.engine.base.Engine.0x...5eb0 ['1/1/2007', '12/31/2007'] This looks right to me, but when I print out the data using a simple for loop, the latest date is 10/31/2007., not 12/31/2007. I must be missing something really simple. the debug setting should log the actual result rows too as they are fetched. you might want to flush() your session before you emit that query, drop into a pdb session, and poke around to see what the state of the transaction is before the query is issued. Well, that's weird. I'm not doing anything to my session before I run the query since this is a debugging script. I usually write all my new queries in a self-contained file to make it easier to tell what's going on. Anyway, I'll do a flush and paste all its output here: http://paste.pocoo.org/show/125810/ I'll get my superior to look at it tomorrow with some kind of debugger as he has more experience with those. all those other queries are tables being reflected, i.e. with autoload=True. that's not apparent in your paste, though perhaps they are invoked in your application from an import somewhere. --~--~-~--~~~---~--~~ 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: SqlAlchemy limiting returned rows prematurely
Mike Driscoll wrote: Hi, I am having an issue with what is getting returned from my SA query. Here's my code: code beginDate = 1/1/2007 endDate = 12/31/2007 qry = session.query(CheckHistory.CHK_DOC_NO, CheckHistory.EMP_ID, CheckHistory.CHECK_DATE, CheckHistory.CHECK_NO, CheckHistory.CHECK_AMT, CheckHistory.STATUS, CheckHistory.PAY_PERIOD, CheckHistory.DIRECT_DEPOSIT ) qry = qry.filter(CheckHistory.CHECK_DATE.between(beginDate, endDate) ).order_by(CheckHistory.CHECK_DATE.desc()) result = qry.all() /code When I run this, it only shows results between 1/1 and 1/10. If I run the equivalent SQL query directly in MS Sql Server 2000's Enterprise Manager, I get everything between 1/1 and 1/12 as I should. Does SqlAlchemy have some kind of result set limiter? There are lots of rows returned... I am using SA 0.5.4p2 and Python 2.5. Thanks! use echo=debug on your engine to see what rows are being fetched. the query you have above is very straightforward. Mike --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
