Re: [SQLObject] MS SQL pain for a newb

[Christopher S. <csi...@gm...>]

Try reading this whole section part of the SQLObject documentation:
http://sqlobject.org/SQLObject.html#legacy-database-schemas
"""
All tables that you want to turn into a class need to have an integer primary 
key.
[...]
By default names in SQLObject are expected to be mixed case in Python (like 
mixedCase), and underscore-separated in SQL (like mixed_case). This applies 
to table and column names. The primary key is assumed to be simply id.
[...]
If you use non-integer keys, all primary key management is up to you.
"""
This seems to be the stuff you're running into.

By default, name mangling is used; case transitions in Python (like 'empID') 
are converted to lower case with underscores in SQL (like 'emp_id')... sounds 
like fromDatabase=True is helping you get around this though.

SQLObject expects tbl_acct_prefs to have a column named 'id' that's an integer 
primary key - sounds like you don't have that.  It's easy if you have an 
integer primary key named something else (just set sqlmeta.idName), but if 
you don't have that it's kind of a dealbreaker.

If you're just learning how to use SQLObject, I'd recommend that you start 
with an empty database created & managed entirely by a SQLObject app, if 
possible.

Good luck,
cs

On Friday 08 February 2008 15:07:33 Mike Driscoll wrote:
> Hi,
>
> I am a newb when it comes to SQLObject. I have been working through
> TurboGears tutorials and books and decided to try to make my own app.
> Unfortunately, I seem to be doing something wrong. All I'm doing is trying
> to do a "select *". Here's what I've got so far:
>
> <code>
>
> from sqlobject import *
>
> # mssql://user:pass@host:port/db
> connection =
> connectionForURI('mssql://user:pa...@nt...:port/db')
> sqlhub.processConnection = connection
>
> class tblAcctPrefs(SQLObject):
>     # these are the actual column names
>     # is that a no-no?
>     empID = StringCol()
>     pref_name = StringCol()
>     pref_value = StringCol()
>
> tblAcctPrefs._connection.debug = True
> prefs = tblAcctPrefs.select()
> list(prefs)
>
> </code>
>
> This gives me a goofy traceback like this:
>
> DatabaseError: internal error: SQL Server message 207, severity 16, state
> 3, line 1:
> Invalid column name 'id'.
> SQL Server message 207, severity 16, state 3, line 1:
> Invalid column name 'emp_id'.
> DB-Lib error message 10007, severity 5:
> General SQL Server error: Check messages from the SQL Server.
>
> The debug spits this out:
>
> 1/Select  :  SELECT tbl_acct_prefs.id, tbl_acct_prefs.emp_id,
> tbl_acct_prefs.pref_name, tbl_acct_prefs.pref_value FROM tbl_acct_prefs
> WHERE 1 = 1
> 1/QueryR  :  SELECT tbl_acct_prefs.id, tbl_acct_prefs.emp_id,
> tbl_acct_prefs.pref_name, tbl_acct_prefs.pref_value FROM tbl_acct_prefs
> WHERE 1 = 1
>
> I changed the class to this:
>
> <code>
>
> class tblAcctPrefs(SQLObject):
>     class sqlmeta:
>         fromDatabase = True
>
> </code>
>
> and now I get just one invalid table error:
>
> DatabaseError: internal error: SQL Server message 207, severity 16, state
> 3, line 1:
> Invalid column name 'id'.
> DB-Lib error message 10007, severity 5:
> General SQL Server error: Check messages from the SQL Server.
>
>
> My questions are:
>
> 1) Why does it change my column name from empID to emp_id?
> 2) Why does it insist on querying against an id column that I do not
> specify?
> 3) How do I do this correctly?
>
> I'm sure I'll be asking other simple questions as well. I'm currently
> using the latest SQLObject (I think) with Python 2.4 on Windows XP. Thanks
> a lot!
>
> Mike Driscoll
> Applications Specialist
> MCIS - Technology Center
>
>
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Microsoft
> Defy all challenges. Microsoft(R) Visual Studio 2008.
> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss



-- 
Christopher Singley, CFA
President, Singley Capital Management, Inc.
tel (713) 459-0881