Thread: [SQLObject] fromDatabase with Schemas under Postgres not working
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Ben B. <be...@gr...> - 2005-08-23 22:42:21
|
I'm having difficulty getting SQLObject to properly pull the column info from the database using fromDatabase = True. This only seems to occur if the table is under a schema in Postgres. If the table is in the "public" schema for the database, it has no problem. Is there some extra configuration I need to add to indicate that its in a schema other than the sqlmeta->table option? Here's the error it gives me (I DO have a Primary Key, so I'm guessing its not looking in the right place): Traceback (most recent call last): File "<stdin>", line 1, in ? File "/usr/local/www/gamma.parachute.com/lib/College.py", line 10, in ? class Signup(SQLObject): File "/usr/local/lib/python2.3/site-packages/SQLObject-0.7dev_r935- py2.3.egg/sqlobject/declarative.py", line 94, in __new__ cls.__classinit__(cls, new_attrs) File "/usr/local/lib/python2.3/site-packages/SQLObject-0.7dev_r935- py2.3.egg/sqlobject/main.py", line 765, in __classinit__ cls.sqlmeta.addColumnsFromDatabase() File "/usr/local/lib/python2.3/site-packages/SQLObject-0.7dev_r935- py2.3.egg/sqlobject/main.py", line 417, in addColumnsFromDatabase for columnDef in conn.columnsFromSchema(sqlmeta.table, soClass): File "/usr/local/lib/python2.3/site-packages/SQLObject-0.7dev_r935- py2.3.egg/sqlobject/postgres/pgconnection.py", line 197, in columnsFromSchema assert primaryKey, "No primary key found in table %r" % tableName AssertionError: No primary key found in table 'college.signups' From the database for the table: id | integer | not null default nextval ('college.signups_id_seq'::text) Indexes: "signups_pkey" primary key, btree (id) All standard stuff.If anyone has some advice on how to make this work with postgres schema's, I'd hate to have to write out all the column info. :) Thanks, Ben |
From: Oleg B. <ph...@ma...> - 2005-08-24 08:04:14
|
On Tue, Aug 23, 2005 at 03:42:10PM -0700, Ben Bangert wrote: > AssertionError: No primary key found in table 'college.signups' The error message says it pretty clear - make your id column a PRIMARY KEY! Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Ben B. <be...@gr...> - 2005-08-25 21:00:47
|
On Aug 24, 2005, at 1:03 AM, Oleg Broytmann wrote: > On Tue, Aug 23, 2005 at 03:42:10PM -0700, Ben Bangert wrote: > >> AssertionError: No primary key found in table 'college.signups' >> > > The error message says it pretty clear - make your id column a > PRIMARY > KEY! I thought I was clear, its the same EXACT table def, only under a postgresql database schema, vs under the "public" schema. It works under the "public" schema but not a user-defined schema. There is a primary key present, but SQLObject is not capable of figuring out it should be looking under a schema and table for the primary key, vs just the table. I'm sorry the term schema is getting so confused here, when I say Postgresql Schema, I'm referring to this ability of Postgres for organizing multiple sets of tables under "Schemas" under the same database: http://www.postgresql.org/docs/8.0/static/ddl-schemas.html I in fact just tested this with actual stuff to be sure. I'm reasonably positive its a SQLObject bug at this point regarding locating a primary key ID under Postgres schemas. Here are two schemas, the sole difference being that one of them uses a Postgresql schema organization. I will assume someone testing these can setup their own postgres database. SQLObject files: #author schema WITHOUT postgres schema class Author(SQLObject): _fromDatabase = True #author schema WITH postgres schema class Author(SQLObject): _table = 'boo.author' _fromDatabase = True Now here are the two different SQL statements needed to create each: #SQL WITHOUT postgres schema CREATE TABLE author ( id serial PRIMARY KEY, login varchar(8) NOT NULL, password varchar(40) NOT NULL, name varchar(30) NOT NULL ); #SQL WITH postgres schema CREATE SCHEMA boo; CREATE TABLE boo.author ( id serial PRIMARY KEY, login varchar(8) NOT NULL, password varchar(40) NOT NULL, name varchar(30) NOT NULL ); Please note, they have primary keys, regardless to what SQLObject knows or doesn't know. Technically, both of these should be able to be run on a database, without any problems, because the first one puts the tables under the default, aka "public" schema in Postgresql, whereas the second one puts the table under the boo schema. Now, if you run the 1st SQL into a db, and load the first SQLObject schema, there's no issues, it looks like this: >>> from odb import * >>> Looks good, no problems. Now I clear out the database, and load the second SQL set, and load up the second SQLObject schema, to get this: >>> from schemadb import * schemadb.py:5: DeprecationWarning: '_table' is deprecated; please set the 'table' attribute in sqlmeta instead class Author(SQLObject): Traceback (most recent call last): File "<stdin>", line 1, in ? File "schemadb.py", line 5, in ? class Author(SQLObject): File "/usr/local/lib/python2.3/site-packages/sqlobject/ declarative.py", line 94, in __new__ cls.__classinit__(cls, new_attrs) File "/usr/local/lib/python2.3/site-packages/sqlobject/main.py", line 468, in __classinit__ cls.addColumnsFromDatabase() File "/usr/local/lib/python2.3/site-packages/sqlobject/main.py", line 674, in addColumnsFromDatabase for columnDef in conn.columnsFromSchema(cls.sqlmeta.table, cls): File "/usr/local/lib/python2.3/site-packages/sqlobject/postgres/ pgconnection.py", line 196, in columnsFromSchema assert primaryKey, "No primary key found in table %r" % tableName AssertionError: No primary key found in table 'boo.author' So it's obviously not looking for boo.id, its looking for id which of course doesn't exist. I am 100% positive there is a primary key, as this is what Postgresql prompt tells me: pyblog=> \d boo. Table "boo.author" Column | Type | Modifiers ----------+----------------------- +----------------------------------------------------- id | integer | not null default nextval ('boo.author_id_seq'::text) login | character varying(8) | not null password | character varying(40) | not null name | character varying(30) | not null Indexes: "author_pkey" PRIMARY KEY, btree (id) Sequence "boo.author_id_seq" Column | Type ---------------+--------- sequence_name | name last_value | bigint increment_by | bigint max_value | bigint min_value | bigint cache_value | bigint log_cnt | bigint is_cycled | boolean is_called | boolean Index "boo.author_pkey" Column | Type --------+--------- id | integer primary key, btree, for table "boo.author" Now, on top of this all, SQLObject also throws up if you should load both of these. If you load the first SQL, and the second, importing the first SQLObject schema gets you to this odd message: >>> from odb import * Traceback (most recent call last): File "<stdin>", line 1, in ? File "odb.py", line 5, in ? class Author(SQLObject): File "/usr/local/lib/python2.3/site-packages/sqlobject/ declarative.py", line 94, in __new__ cls.__classinit__(cls, new_attrs) File "/usr/local/lib/python2.3/site-packages/sqlobject/main.py", line 468, in __classinit__ cls.addColumnsFromDatabase() File "/usr/local/lib/python2.3/site-packages/sqlobject/main.py", line 674, in addColumnsFromDatabase for columnDef in conn.columnsFromSchema(cls.sqlmeta.table, cls): File "/usr/local/lib/python2.3/site-packages/sqlobject/postgres/ pgconnection.py", line 194, in columnsFromSchema assert primaryKey is None, "Already found primary key (%r), then found: %r" % (primaryKey, indexDef) AssertionError: Already found primary key ('id'), then found: 'CREATE UNIQUE INDEX author_pkey ON boo.author USING btree (id)' I'll be submitting some patches containing unit tests exposing this bug tonight hopefully if I have some time. - Ben |
From: Ahmed M. A. <ahm...@wa...> - 2005-08-24 10:15:12
|
Hi, Ben Bangert wrote : > I'm having difficulty getting SQLObject to properly pull the column > info from the database using fromDatabase = True. > This only seems to occur if the table is under a schema in Postgres. if you are using a postgres schema, there are two ways which work for me - Use the same name of user as the schema ( eg: myUserName = mySchemaName) or - Prefix the table name with the schema name example : class myClass(SQLObject): _table='mySchemaName.myTableName' ... Ahmed -----Message d'origine----- De : sql...@li... [mailto:sql...@li...]De la part de Ben Bangert Envoye : mercredi 24 aout 2005 00:42 A : sql...@li... Objet : [SQLObject] fromDatabase with Schemas under Postgres not working I'm having difficulty getting SQLObject to properly pull the column info from the database using fromDatabase = True. This only seems to occur if the table is under a schema in Postgres. If the table is in the "public" schema for the database, it has no problem. Is there some extra configuration I need to add to indicate that its in a schema other than the sqlmeta->table option? Here's the error it gives me (I DO have a Primary Key, so I'm guessing its not looking in the right place): Traceback (most recent call last): File "<stdin>", line 1, in ? File "/usr/local/www/gamma.parachute.com/lib/College.py", line 10, in ? class Signup(SQLObject): File "/usr/local/lib/python2.3/site-packages/SQLObject-0.7dev_r935- py2.3.egg/sqlobject/declarative.py", line 94, in __new__ cls.__classinit__(cls, new_attrs) File "/usr/local/lib/python2.3/site-packages/SQLObject-0.7dev_r935- py2.3.egg/sqlobject/main.py", line 765, in __classinit__ cls.sqlmeta.addColumnsFromDatabase() File "/usr/local/lib/python2.3/site-packages/SQLObject-0.7dev_r935- py2.3.egg/sqlobject/main.py", line 417, in addColumnsFromDatabase for columnDef in conn.columnsFromSchema(sqlmeta.table, soClass): File "/usr/local/lib/python2.3/site-packages/SQLObject-0.7dev_r935- py2.3.egg/sqlobject/postgres/pgconnection.py", line 197, in columnsFromSchema assert primaryKey, "No primary key found in table %r" % tableName AssertionError: No primary key found in table 'college.signups' From the database for the table: id | integer | not null default nextval ('college.signups_id_seq'::text) Indexes: "signups_pkey" primary key, btree (id) All standard stuff.If anyone has some advice on how to make this work with postgres schema's, I'd hate to have to write out all the column info. :) Thanks, Ben ------------------------------------------------------- SF.Net email is Sponsored by the Better Software Conference & EXPO September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf _______________________________________________ sqlobject-discuss mailing list sql...@li... https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss ---------------------------------------------------------------------------- ----------- Wanadoo vous informe que cet e-mail a ete controle par l'anti-virus mail. Aucun virus connu a ce jour par nos services n'a ete detecte. |