[sqlalchemy] Re: Another tutorial!
On Feb 4, 4:24 am, King Simon-NFHD78 simon.k...@motorola.com wrote: -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Mike Driscoll Sent: 04 February 2010 03:34 To: sqlalchemy Subject: [sqlalchemy] Another tutorial! Hi, I just finished up a tutorial series on SqlAlchemy that I thought I'd share: http://www.blog.pythonlibrary.org/2010/02/03/another-step-by-s tep-sqlalchemy-tutorial-part-1-of-2/ http://www.blog.pythonlibrary.org/2010/02/03/another-step-by-s tep-sqlalchemy-tutorial-part-2-of-2/ Hopefully it's made well enough that people can follow the tutorial easily. Let me know if I made any serious blunders. Thanks, Mike Hi Mike, Not a serious blunder, but I think there may be a small mistake in part 2, where you describe updating an email address: # change the first address prof.addresses[0] = Address(pr...@marvel.com) I don't think this is going to update the 'pr...@dc.com' row in the database to say 'pr...@marvel.com'. Instead, it is going to disconnect that row from the user by setting the user_id to NULL, and add a new row with the new address. (This may be what you intended, but I don't think it's clear from the description). I would have thought that you'd actually want to write this: # change the first address prof.addresses[0].email_address = pr...@marvel.com Hope that helps, Simon I was testing this in IDLE and it seemed to work when I did prof.addresses to check it. If the user gets set to NULL, wouldn't prof.addresses only show one entry? I'll check it out and make sure. If I messed it up, I'll get it fixed. Thanks for the bug report! - Mike -- 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: Another tutorial!
On Feb 4, 9:26 am, King Simon-NFHD78 simon.k...@motorola.com
wrote:
Mike Driscoll wrote:
On Feb 4, 8:30 am, Mike Driscoll kyoso...@gmail.com wrote:
On Feb 4, 4:24 am, King Simon-NFHD78 simon.k...@motorola.com
wrote:
[SNIP]
Not a serious blunder, but I think there may be a small
mistake in part
2, where you describe updating an email address:
# change the first address
prof.addresses[0] = Address(pr...@marvel.com)
I don't think this is going to update the 'pr...@dc.com'
row in the
database to say 'pr...@marvel.com'. Instead, it is going
to disconnect
that row from the user by setting the user_id to NULL,
and add a new row
with the new address. (This may be what you intended, but
I don't think
it's clear from the description).
I would have thought that you'd actually want to write this:
# change the first address
prof.addresses[0].email_address = pr...@marvel.com
Hope that helps,
Simon
I was testing this in IDLE and it seemed to work when I did
prof.addresses to check it. If the user gets set to NULL, wouldn't
prof.addresses only show one entry? I'll check it out and make sure.
If I messed it up, I'll get it fixed. Thanks for the bug report!
- Mike
I just ran through that section again using the Python interpreter and
after changing the address like this:
prof.addresses[0] = Address(pr...@marvel.com)
I then used the following (per the official tutorial):
prof.addresses[0].user
User('Prof','Prof. Xavier', 'fudge')
So my method appears to work. I tried your method too:
prof.addresses[0].email_address = prof...@image.com
prof.addresses[0].user
User('Prof','Prof. Xavier', 'fudge')
That appears to give the same result. Let me know if I am
misunderstanding something basic here.
The difference is that in your case, there is now a row in the Address table
without an associated User. Try running the following:
for address in session.query(Address):
print Address %r belongs to User %r % (address, address.user)
I think you will see addresses that don't belong to any users.
Simon
Thanks Simon! That made sense. I've fixed my example to match what you
said. Sorry about that.
- Mike
--
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: Another tutorial!
Simon, On Feb 4, 10:15 am, King Simon-NFHD78 simon.k...@motorola.com wrote: Mike Driscoll wrote: Thanks Simon! That made sense. I've fixed my example to match what you said. Sorry about that. - Mike No problem. I'm afraid you still have a typo though. You have: addresses[0].email_address = Address(pr...@marvel.com) Whereas you want: addresses[0].email_address = pr...@marvel.com :-) Simon Oops...you're right. I must be blind today. Fixed (again)! --- Mike Driscoll Blog: http://blog.pythonlibrary.org PyCon 2010 Atlanta Feb 19-21 http://us.pycon.org/ -- 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: Another tutorial!
On Feb 4, 10:36 am, John Trammell jo...@holmescorp.com wrote: You made some serious blunders. Check your comments on the blog post. It looks like Werner found the same issue that Simon already told me about. This has been fixed per Simon's notes. I also found that I forgot to import ForeignKey in the first part of the series. This was also fixed. Thanks for being so nice. --- Mike Driscoll Blog: http://blog.pythonlibrary.org PyCon 2010 Atlanta Feb 19-21 http://us.pycon.org/ -- 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] Another tutorial!
Hi, I just finished up a tutorial series on SqlAlchemy that I thought I'd share: http://www.blog.pythonlibrary.org/2010/02/03/another-step-by-step-sqlalchemy-tutorial-part-1-of-2/ http://www.blog.pythonlibrary.org/2010/02/03/another-step-by-step-sqlalchemy-tutorial-part-2-of-2/ Hopefully it's made well enough that people can follow the tutorial easily. Let me know if I made any serious blunders. Thanks, Mike -- 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] SqlAlchemy results differ from its generated SQL
Hi, When I run a simple query like this: qry = session.query(CheckHistory) qry = qry.filter(and_(CheckHistory.CHECK_DATE = '1/1/2007', CheckHistory.CHECK_DATE = '1/1/2008')) res = qry.all() I get one CheckHistory row and a None. If I run the echoed SQL code in my database's query analyzer, I get almost 5000 results. Last Friday, this worked just fine. However, over the weekend, we upgraded from MS SQL Server 2000 to 2005. I don't see how this could have messed up SqlAlchemy, but something weird is going on here. Does anyone have any hints for troubleshooting this? I am using SqlAlchemy 0.5.6 on Windows XP with Python 2.5. 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
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
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: Problems with sub-queries
On Jun 26, 5:49 pm, Michael Bayer mike...@zzzcomputing.com wrote: Mike wrote: TypeError: 'Alias' object is unindexable that error means you're trying to say x['foo'] on something that doesn't have a __getitem__(). I don't see that here, can you send a stack trace which would reveal if this is being produced from within SQLAlchemy internals ? Oops...you're exactly right. I didn't copy and paste everything and missed that piece. It appears to return some correct results, but not all of them. When I run the straight SQL in MS SQL, I get additional results. Do you have any insights as to why that might happen? 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
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
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] SqlAlchemy limiting returned rows prematurely
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! 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 reflection error in TurboGears 2
Well, I did the fake id column because SA was throwing an error on a
the reflected table since it didn't have a primary key defined. I
think I may have found a workaround though.
Thanks for the help.
Mike
On Jun 3, 5:05 pm, Michael Bayer mike...@zzzcomputing.com wrote:
i don't see what the purpose of a fake id column serves here. you can
make a Table object and place within it as many actual columns as you
want, including primary key columns. if the table name is truly all
uppercase, then you need to name it that way in the Table object.
Mike Driscoll wrote:
Hi,
Actually, that was just a placeholder name since I'm not sure that my
employer would like me sticking my tables online. Anyway, the
tablename in Microsoft's Enterprise Manager shows it as being all
lowercase although when I run queries against it in MS Query Analyzer,
it uses all uppercase.
All the column names in the table are in uppercase and there are no
foreign keys. The vendor also didn't bother setting a primary key.
So, I decided to try accessing the table without autoload. Since there
are quite a few columns, I thought I'd also try grabbing only the
columns I wanted. For a simple test, I did this:
test_tbl = Table(tablename, metadata,
Column('id', Integer, primary_key=True),
Column(CYCLE_CODE, String))
Unfortunately, MSSQL is reporting that my fake primary key column is
invalid. Is there a workaround? Do I need to recreate all the Columns
in a Table object and also in my class definition?
Sorry for all the trouble.
Mike
On Jun 3, 3:48 pm, Michael Bayer mike...@zzzcomputing.com wrote:
tableTwo is a case sensitive name so must be spelled out with that exact
case. in particular if your database is returning foreign key names
without proper case sensitivity then issues will occur with this. turn
echo='debug' to see all SQL emitted and result sets returned.
Mike Driscoll wrote:
Hi,
That fixed that issue. However, now I'm getting an error that my 2nd
table doesn't exist:
sqlalchemy.exc.NoSuchTableError: tableTwo
This is weird since it's been around since 2007 and has lots of data
in it. I tried changing the name to all lowercase and all uppercase,
but the error remains the same. I also tried removing the schema line
to no avail.
Here's the complete traceback:
File D:\pyRetention\Scripts\paster, line 5, in module
pkg_resources.run_script('pastescript==1.7.3', 'paster')
File c:\Python25\Lib\site-packages\pkg_resources.py, line 448, in
run_script
self.require(requires)[0].run_script(script_name, ns)
File c:\Python25\Lib\site-packages\pkg_resources.py, line 1166, in
run_script
execfile(script_filename, namespace, namespace)
File c:\Python25\Lib\site-packages\pastescript-1.7.3-py2.5.egg\EGG-
INFO\scripts\paster, line 18, in module
command.run()
File c:\Python25\Lib\site-packages\pastescript-1.7.3-py2.5.egg\paste
\script\command.py, line 84, in run
invoke(command, command_name, options, args[1:])
File c:\Python25\Lib\site-packages\pastescript-1.7.3-py2.5.egg\paste
\script\command.py, line 123, in invoke
exit_code = runner.run(args)
File c:\Python25\Lib\site-packages\pastescript-1.7.3-py2.5.egg\paste
\script\command.py, line 218, in run
result = self.command()
File c:\Python25\Lib\site-packages\pastescript-1.7.3-py2.5.egg\paste
\script\serve.py, line 276, in command
relative_to=base, global_conf=vars)
File c:\Python25\Lib\site-packages\pastescript-1.7.3-py2.5.egg\paste
\script\serve.py, line 313, in loadapp
**kw)
File c:\Python25\Lib\site-packages\pastedeploy-1.3.3-py2.5.egg\paste
\deploy\loadwsgi.py, line 204, in loadapp
return loadobj(APP, uri, name=name, **kw)
File c:\Python25\Lib\site-packages\pastedeploy-1.3.3-py2.5.egg\paste
\deploy\loadwsgi.py, line 225, in loadobj
return context.create()
File c:\Python25\Lib\site-packages\pastedeploy-1.3.3-py2.5.egg\paste
\deploy\loadwsgi.py, line 625, in create
return self.object_type.invoke(self)
File c:\Python25\Lib\site-packages\pastedeploy-1.3.3-py2.5.egg\paste
\deploy\loadwsgi.py, line 110, in invoke
return fix_call(context.object, context.global_conf,
**context.local_conf)
File c:\Python25\Lib\site-packages\pastedeploy-1.3.3-py2.5.egg\paste
\deploy\util\fixtypeerror.py, line 57, in fix_call
val = callable(*args, **kw)
File D:\pyRetention\pyRetention\pyretention\config\middleware.py,
line 35, in make_app
app = make_base_app(global_conf, full_stack=True, **app_conf)
File c:\Python25\Lib\site-packages\turbogears2-2.0-py2.5.egg\tg
\configuration.py, line 588, in make_base_app
load_environment(global_conf, app_conf)
File c:\Python25\Lib\site-packages\turbogears2-2.0-py2.5.egg\tg
\configuration.py, line 438, in load_environment
self.setup_sqlalchemy()
File c:\Python25\Lib\site
[sqlalchemy] Re: SqlAlchemy reflection error in TurboGears 2
Hi Simon and Michael, On Jun 4, 8:03 am, King Simon-NFHD78 simon.k...@motorola.com wrote: Mike Driscoll wrote: Well, I did the fake id column because SA was throwing an error on a the reflected table since it didn't have a primary key defined. I think I may have found a workaround though. From SA's point of view, the primary key just has to be a set of columns that uniquely identify a row in the database. It doesn't explicitly have to be defined as a primary key in the database. The reason for this is that if you retrieve an object (a row) from the database and modify it, there is no way to save those changes back to the database unless you can uniquely identify that row, so you know what to put in the WHERE clause of the UPDATE statement. If there really isn't a set of columns that uniquely identifies a row in the database, and you only want read-only support, you might be able to tell SA that the primary key is made up of all the columns in the table. However, if there are any duplicate rows in the table, SA will only return a single object for those rows because it has no way of knowing that they are different. If you do have duplicate rows, you might be better working with the SQL-only layer of SA, rather than the ORM. Simon It turns out that my ultimate issue was myself. I had our db admin (i.e. my boss) look at it and he quickly found that while my username had access to the database, it did not have access to that particular table. This caused autoload to report that the table did not exist AND if I set a primary key and disabled autoload, it made SA spit out a SELECT permission error. I apologize for the noise. So much to learn...so little time. - 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 reflection error in TurboGears 2
Hi,
Actually, that was just a placeholder name since I'm not sure that my
employer would like me sticking my tables online. Anyway, the
tablename in Microsoft's Enterprise Manager shows it as being all
lowercase although when I run queries against it in MS Query Analyzer,
it uses all uppercase.
All the column names in the table are in uppercase and there are no
foreign keys. The vendor also didn't bother setting a primary key.
So, I decided to try accessing the table without autoload. Since there
are quite a few columns, I thought I'd also try grabbing only the
columns I wanted. For a simple test, I did this:
test_tbl = Table(tablename, metadata,
Column('id', Integer, primary_key=True),
Column(CYCLE_CODE, String))
Unfortunately, MSSQL is reporting that my fake primary key column is
invalid. Is there a workaround? Do I need to recreate all the Columns
in a Table object and also in my class definition?
Sorry for all the trouble.
Mike
On Jun 3, 3:48 pm, Michael Bayer mike...@zzzcomputing.com wrote:
tableTwo is a case sensitive name so must be spelled out with that exact
case. in particular if your database is returning foreign key names
without proper case sensitivity then issues will occur with this. turn
echo='debug' to see all SQL emitted and result sets returned.
Mike Driscoll wrote:
Hi,
That fixed that issue. However, now I'm getting an error that my 2nd
table doesn't exist:
sqlalchemy.exc.NoSuchTableError: tableTwo
This is weird since it's been around since 2007 and has lots of data
in it. I tried changing the name to all lowercase and all uppercase,
but the error remains the same. I also tried removing the schema line
to no avail.
Here's the complete traceback:
File D:\pyRetention\Scripts\paster, line 5, in module
pkg_resources.run_script('pastescript==1.7.3', 'paster')
File c:\Python25\Lib\site-packages\pkg_resources.py, line 448, in
run_script
self.require(requires)[0].run_script(script_name, ns)
File c:\Python25\Lib\site-packages\pkg_resources.py, line 1166, in
run_script
execfile(script_filename, namespace, namespace)
File c:\Python25\Lib\site-packages\pastescript-1.7.3-py2.5.egg\EGG-
INFO\scripts\paster, line 18, in module
command.run()
File c:\Python25\Lib\site-packages\pastescript-1.7.3-py2.5.egg\paste
\script\command.py, line 84, in run
invoke(command, command_name, options, args[1:])
File c:\Python25\Lib\site-packages\pastescript-1.7.3-py2.5.egg\paste
\script\command.py, line 123, in invoke
exit_code = runner.run(args)
File c:\Python25\Lib\site-packages\pastescript-1.7.3-py2.5.egg\paste
\script\command.py, line 218, in run
result = self.command()
File c:\Python25\Lib\site-packages\pastescript-1.7.3-py2.5.egg\paste
\script\serve.py, line 276, in command
relative_to=base, global_conf=vars)
File c:\Python25\Lib\site-packages\pastescript-1.7.3-py2.5.egg\paste
\script\serve.py, line 313, in loadapp
**kw)
File c:\Python25\Lib\site-packages\pastedeploy-1.3.3-py2.5.egg\paste
\deploy\loadwsgi.py, line 204, in loadapp
return loadobj(APP, uri, name=name, **kw)
File c:\Python25\Lib\site-packages\pastedeploy-1.3.3-py2.5.egg\paste
\deploy\loadwsgi.py, line 225, in loadobj
return context.create()
File c:\Python25\Lib\site-packages\pastedeploy-1.3.3-py2.5.egg\paste
\deploy\loadwsgi.py, line 625, in create
return self.object_type.invoke(self)
File c:\Python25\Lib\site-packages\pastedeploy-1.3.3-py2.5.egg\paste
\deploy\loadwsgi.py, line 110, in invoke
return fix_call(context.object, context.global_conf,
**context.local_conf)
File c:\Python25\Lib\site-packages\pastedeploy-1.3.3-py2.5.egg\paste
\deploy\util\fixtypeerror.py, line 57, in fix_call
val = callable(*args, **kw)
File D:\pyRetention\pyRetention\pyretention\config\middleware.py,
line 35, in make_app
app = make_base_app(global_conf, full_stack=True, **app_conf)
File c:\Python25\Lib\site-packages\turbogears2-2.0-py2.5.egg\tg
\configuration.py, line 588, in make_base_app
load_environment(global_conf, app_conf)
File c:\Python25\Lib\site-packages\turbogears2-2.0-py2.5.egg\tg
\configuration.py, line 438, in load_environment
self.setup_sqlalchemy()
File c:\Python25\Lib\site-packages\turbogears2-2.0-py2.5.egg\tg
\configuration.py, line 393, in setup_sqlalchemy
self.package.model.init_model(engine)
File d:\pyRetention\pyRetention\pyretention\model\__init__.py, line
70, in init_model
autoload=True, autoload_with=engine)
File c:\Python25\Lib\site-packages\sqlalchemy-0.5.4p2-py2.5.egg
\sqlalchemy\schema.py, line 113, in __call__
return type.__call__(self, name, metadata, *args, **kwargs)
File c:\Python25\Lib\site-packages\sqlalchemy-0.5.4p2-py2.5.egg
\sqlalchemy\schema.py, line 239, in __init__
autoload_with.reflecttable(self, include_columns=include_columns)
File c:\Python25\Lib\site-packages\sqlalchemy-0.5.4p2-py2.5.egg
[sqlalchemy] Re: Weird error on upgrade to 0.5.2
Oops...I thought I had copied the traceback in, but I forgot it...my
bad. Here's the error:
Traceback (most recent call last):
File main.py, line 1461, in module
app = Main(flag)
File main.py, line 81, in __init__
self.createCtrls()
File main.py, line 431, in createCtrls
self.zsession)
File /home/jcbailey/Desktop/Timesheet/ts_info.py, line 366, in
getUserInfo
userInfo = query.first()
File /usr/lib/python2.5/site-packages/sqlalchemy/orm/query.py,
line 1219, in first
ret = list(self[0:1])
File /usr/lib/python2.5/site-packages/sqlalchemy/orm/query.py,
line 1140, in __getitem__
return list(res)
File /usr/lib/python2.5/site-packages/sqlalchemy/orm/query.py,
line 1344, in instances
for row in fetch]
File /usr/lib/python2.5/site-packages/sqlalchemy/orm/query.py,
line 1343, in genexpr
rows = [rowtuple(proc(context, row) for proc in process)
File /usr/lib/python2.5/site-packages/sqlalchemy/orm/query.py,
line 2062, in proc
return row[column]
File /usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py,
line 1348, in __getitem__
return self.__parent._get_col(self.__row, key)
File /usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py,
line 1609, in _get_col
type_, processor, index = self._props[key]
File /usr/lib/python2.5/site-packages/sqlalchemy/util.py, line 71,
in __missing__
self[key] = val = self.creator(key)
File /usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py,
line 1507, in fallback
raise exc.NoSuchColumnError(Could not locate column in row for
column '%s' % (str(key)))
sqlalchemy.exc.NoSuchColumnError: Could not locate column in row for
column 'GEOverview.SALVAR'
I'll try to get pyodbc installed too. Thanks!
Mike
On Mar 23, 1:06 pm, Michael Bayer mike...@zzzcomputing.com wrote:
we'd need to know what stopped working means, and also note that our
MSSQL support is strongest with pyodbc and not so much with adodbapi.
Mike wrote:
Hi,
I was using SqlAlchemy 0.5.0rc4 (and Python 2.5) on Ubuntu 8.10 and it
was working great. Then one of my users upgraded to SA 0.5.2 and now
one of my queries stopped working. Here's what my query looks like:
code
query = session.query(GE.ID, GE.FNAME, GE.LNAME,
GE.DATE, GE.SALVAR).filter_by
(NETNAME=str(username))
userInfo = query.first()
/code
I have the adodb module installed and I think that is what SA is
using. The database is on Microsoft SQL Server 2000. I am using the
autoload functionality to create the Table object:
overview_tbl = Table('GEOverview', metadata,
Column('id', Integer, primary_key=True),
autoload=True)
Any ideas on what's different between the two versions that would
cause this? I have a feeling this will probably effect my Windows
users too if I upgrade them.
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: Weird error on upgrade to 0.5.2
Just an FYI: I put pyodbc 2.1.4 (and its dependencies) onto my Ubuntu
test bed and SA is giving me the same error.
Mike
On Mar 23, 1:06 pm, Michael Bayer mike...@zzzcomputing.com wrote:
we'd need to know what stopped working means, and also note that our
MSSQL support is strongest with pyodbc and not so much with adodbapi.
Mike wrote:
Hi,
I was using SqlAlchemy 0.5.0rc4 (and Python 2.5) on Ubuntu 8.10 and it
was working great. Then one of my users upgraded to SA 0.5.2 and now
one of my queries stopped working. Here's what my query looks like:
code
query = session.query(GE.ID, GE.FNAME, GE.LNAME,
GE.DATE, GE.SALVAR).filter_by
(NETNAME=str(username))
userInfo = query.first()
/code
I have the adodb module installed and I think that is what SA is
using. The database is on Microsoft SQL Server 2000. I am using the
autoload functionality to create the Table object:
overview_tbl = Table('GEOverview', metadata,
Column('id', Integer, primary_key=True),
autoload=True)
Any ideas on what's different between the two versions that would
cause this? I have a feeling this will probably effect my Windows
users too if I upgrade them.
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
-~--~~~~--~~--~--~---
