Thread: Re: [SQLObject] MS SQL pain for a newb
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Mike D. <mdr...@co...> - 2008-02-12 14:18:27
|
Christopher, > > 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. Well, that sucks. I really like the way SQLObject works, but SQLAlchemy is a good alternative too. Unfortunately, our organization has a lot of databases from various vendors that we can't change willy-nilly, so the primary keys can be whatever they want them to be. Of course, there are quite a few tables that don't even have primary keys. So I guess this won't work... > > 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 > I have been playing in my own sandbox using examples from a couple of books that use SQLObject in TurboGears. I think I'll have to change to SQLAlchemy though. I played with it a couple of weeks ago and it doesn't have this primary key requirement. Thanks for the help! Mike |
From: Mike D. <mdr...@co...> - 2008-02-12 14:20:02
|
Hi Oleg, > -----Original Message----- > From: sql...@li... > [mailto:sql...@li...] On Behalf Of > Oleg Broytmann > Sent: Friday, February 08, 2008 3:47 PM > To: sql...@li... > Subject: Re: [SQLObject] MS SQL pain for a newb > > On Fri, Feb 08, 2008 at 03:07:33PM -0600, Mike Driscoll wrote: > > I am a newb when it comes to SQLObject. > > Welcome! > > > 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 > > This could be achieved by > connectionForURI('mssql://user:pa...@nt...:port/d > b?debug=1') That's good to know...Thanks for the tip! > > > prefs = tblAcctPrefs.select() > > list(prefs) > > > > 1) Why does it change my column name from empID to emp_id? > > This how "styles" in SQLObject works. Default style changes > uppercase to lowercase and adds underscores. > > > 2) Why does it insist on querying against an id column that > I do not > > specify? > > Every SQLObject's table must have a Primary Key id (not necessary > named "id"). > > > 3) How do I do this correctly? > > ALTER TABLE to add the id column. Or, if empID is the Primary Key, > use it as the id: > > class tblAcctPrefs(SQLObject): > class sqlmeta: > idName = "emp_id" > idType = str > pref_name = StringCol() > pref_value = StringCol() > > Oleg. As I mentioned to Chris, since the tables I will be using have been created by vendors and are outside of my control, I cannot alter the tables as that would break their use for other programs that access them. Thanks for the advice though. At least I know what to do if I create my own databases. Mike |
From: Oleg B. <ph...@ph...> - 2008-02-12 14:37:02
|
On Tue, Feb 12, 2008 at 08:22:17AM -0600, Mike Driscoll wrote: > > Or, if empID is the Primary Key, > > use it as the id: > > > > class tblAcctPrefs(SQLObject): > > class sqlmeta: > > idName = "emp_id" > > idType = str > > pref_name = StringCol() > > pref_value = StringCol() > > > since the tables I will be using have been > created by vendors and are outside of my control, I cannot alter the > tables as that would break their use for other programs that access them. My last advise was not about altering the tables but about naming of the primary key. SQLObject requires a single-column primary key of type INT or string, not necessary named 'id'. Unfortunately if there are tables with multiple columns primary key you are out of luck - SQLObject cannot handle them. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Mike D. <mdr...@co...> - 2008-02-12 14:39:32
|
Oleg, > -----Original Message----- > From: sql...@li... > [mailto:sql...@li...] On > Behalf Of Oleg Broytmann > Sent: Tuesday, February 12, 2008 8:37 AM > To: sql...@li... > Subject: Re: [SQLObject] MS SQL pain for a newb > > On Tue, Feb 12, 2008 at 08:22:17AM -0600, Mike Driscoll wrote: > > > Or, if empID is the Primary Key, use it as the id: > > > > > > class tblAcctPrefs(SQLObject): > > > class sqlmeta: > > > idName = "emp_id" > > > idType = str > > > pref_name = StringCol() > > > pref_value = StringCol() > > > > > since the tables I will be using have been created by > vendors and are > > outside of my control, I cannot alter the tables as that > would break > > their use for other programs that access them. > > My last advise was not about altering the tables but about > naming of the primary key. SQLObject requires a single-column > primary key of type INT or string, not necessary named 'id'. > Unfortunately if there are tables with multiple columns > primary key you are out of luck - SQLObject cannot handle them. > > Oleg. That's what I figured from the docs. Thanks for confirming that and for being so kind of helpful. Mike > -- > Oleg Broytmann http://phd.pp.ru/ > ph...@ph... > Programmers don't die, they just GOSUB without RETURN. > > -------------------------------------------------------------- > ----------- > 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 > s > |