Ah, no idea, not too familiar with different users mixing like that
in postgres.
try out these two queries with your PG database and see what you get,
this is how it reflects the tables currently. the primary key
columns are determined by the second query. if we can figure out
what else the query needs to adjust for the user scenario we can
update that.
query # 1:
SELECT columns.table_schema, columns.table_name, columns.column_name,
columns.is_nullable, columns.data_type, columns.ordinal_position,
columns.character_maximum_length, columns.numeric_precision,
columns.numeric_scale, columns.column_default
FROM information_schema.columns
WHERE columns.table_name = %(columns_table_name)s AND
columns.table_schema = %(columns_table_schema)s ORDER BY
columns.ordinal_position
bindparams: {'columns_table_name': 'engine_users',
'columns_table_schema': 'public'}
query # 2:
SELECT table_constraints.constraint_name AS
table_constraints_constraint_name, table_constraints.constraint_type
AS table_constraints_constraint_type, table_constraints.table_name AS
table_constraints_table_name, key_column_usage.table_schema AS
key_column_usage_table_schema, key_column_usage.table_name AS
key_column_usage_table_name, key_column_usage.column_name AS
key_column_usage_column_name, key_column_usage.constraint_name AS
key_column_usage_constraint_name,
constraint_column_usage.table_schema AS
constraint_column_usage_table_schema,
constraint_column_usage.table_name AS
constraint_column_usage_table_name,
constraint_column_usage.column_name AS
constraint_column_usage_column_name,
constraint_column_usage.constraint_name AS
constraint_column_usage_constraint_name
FROM information_schema.table_constraints JOIN
information_schema.constraint_column_usage ON
constraint_column_usage.constraint_name =
table_constraints.constraint_name JOIN
information_schema.key_column_usage ON
key_column_usage.constraint_name =
constraint_column_usage.constraint_name
WHERE table_constraints.table_name = %(table_constraints_table_name)s
AND table_constraints.table_schema = %
(table_constraints_table_schema)s
bindparams: {'table_constraints_table_schema': 'public',
'table_constraints_table_name': 'engine_users'}
On Feb 21, 2006, at 5:44 AM, Sandro Dentella wrote:
hi all,
Sqlalchemy fails to find primary keys in certain situations (if my
test/comprehension is correct... ;-). It seems that it fails when
looking
for a table that has been built by another user. Create 2
identical tables
as follows and make them property of 2 different users:
CREATE USER user1;
CREATE USER user2;
SET SESSION AUTHORIZATION 'user1';
DROP TABLE table1;
CREATE TABLE table1 (
user_key varchar(3) NOT NULL PRIMARY KEY
);
GRANT ALL on table1 to PUBLIC;
SET SESSION AUTHORIZATION 'user2';
DROP TABLE table2;
CREATE TABLE table2 (
user_key varchar(3) NOT NULL PRIMARY KEY
);
GRANT ALL on table2 to PUBLIC;
now connect as user1 and execute:
tables = [ 'table1', 'table2' ]
for table_name in tables:
table = Table(table_name, engine, autoload=True)
print table_name
print [c for c in table.columns]
print table.primary_key
you get something like this:
table1
[Column('user_key',<sqlalchemy.databases.postgres.PGString object
at
0xb6cabf2c>,key='user_key',primary_key=True,nullable=False,hidden=Fa
ls
e,default=None)]
[Column('user_key',<sqlalchemy.databases.postgres.PGString object
at
0xb6cabf2c>,key='user_key',primary_key=True,nullable=False,hidden=Fa
ls
e,default=None)]
table2
[Column('user_key',<sqlalchemy.databases.postgres.PGString object
at
0xb6cb086c>,key='user_key',primary_key=False,nullable=False,hidden=F
al
se,default=None)]
[]
Where primary_key is True for the first and False for the second.
Why that? is this a bug or I'm missing something?
TIA
sandro
*:-)
--
Sandro Dentella *:-)
e-mail: [EMAIL PROTECTED]
http://www.tksql.org TkSQL Home page - My GPL
work
-------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc. Do you grep through
log files
for problems? Stop! Download the new AJAX search engine that makes
searching your log files as easy as surfing the web. DOWNLOAD
SPLUNK!
http://sel.as-us.falkag.net/sel?
cmd=lnk&kid=103432&bid=230486&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
