[sqlalchemy] 0.7.4 and multiple schemas

[Julien Cigar]

Hello,

I upgraded to 0.7.4 together with PostgreSQL 9.0.5.

I have a database with several schemas, and it looks like the handle of 
schemas changed in 0.7.4


I altered the default database search_path with:

{{{
xxx=# alter DATABASE xxx SET search_path TO public,gis,cr2010;
}}}

and I used to do something like:

{{{
meta = MetaData()
engine = engine_from_config(myconfig)
meta.reflect()
}}}

but in this case nothing is reflected .. (meta.tables is empty), is it 
normal? Also is it intended that SQLAlchemy doesn't care about the 
default search_path ..?


I wondered if I should use the new Inspector 
(http://docs.sqlalchemy.org/en/latest/core/schema.html#fine-grained-reflection-with-inspector) 
for such case ?


Thank you!,
Julien


--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.

--
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.

attachment: jcigar.vcf

Re: [sqlalchemy] 0.7.4 and multiple schemas

[A.M.]

On Jan 24, 2012, at 11:08 AM, Julien Cigar wrote:

 Hello,
 
 I upgraded to 0.7.4 together with PostgreSQL 9.0.5.
 
 I have a database with several schemas, and it looks like the handle of 
 schemas changed in 0.7.4
 
 I altered the default database search_path with:
 
 {{{
 xxx=# alter DATABASE xxx SET search_path TO public,gis,cr2010;
 }}}
 
 and I used to do something like:
 
 {{{
 meta = MetaData()
 engine = engine_from_config(myconfig)
 meta.reflect()
 }}}
 
 but in this case nothing is reflected .. (meta.tables is empty), is it 
 normal? Also is it intended that SQLAlchemy doesn't care about the default 
 search_path ..?
 
 I wondered if I should use the new Inspector 
 (http://docs.sqlalchemy.org/en/latest/core/schema.html#fine-grained-reflection-with-inspector)
  for such case ?

The search_path works fine for me with reflection. If you login as the intended 
user using psql and enter \d, do you see any tables? Your results suggest no. 
Perhaps you need ALTER USER X SET search_path TO ….

Cheers,
M

-- 
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.

Re: [sqlalchemy] 0.7.4 and multiple schemas

[Michael Bayer]

On Jan 24, 2012, at 11:08 AM, Julien Cigar wrote:

 Hello,
 
 I upgraded to 0.7.4 together with PostgreSQL 9.0.5.
 
 I have a database with several schemas, and it looks like the handle of 
 schemas changed in 0.7.4
 
 I altered the default database search_path with:
 
 {{{
 xxx=# alter DATABASE xxx SET search_path TO public,gis,cr2010;
 }}}
 
 and I used to do something like:
 
 {{{
 meta = MetaData()
 engine = engine_from_config(myconfig)
 meta.reflect()
 }}}
 
 but in this case nothing is reflected .. (meta.tables is empty), is it 
 normal? Also is it intended that SQLAlchemy doesn't care about the default 
 search_path ..?

The search_path greatly complicates the MetaData object as you can have the 
same table name in multiple schemas.It's best to keep the default search 
path of public and qualify alternate schemas explicitly, that is:

meta.reflect()
meta.reflect(schema='gis')
meta.reflect(schema='cr2010')


The code for get_table_names() is the same in 0.6 as it is in 0.7, it checks 
for tables in the schema returned by select current_schema().   There's been 
some fixes regarding tables reflected as a result of being referenced by 
foreign key constraints, though.




-- 
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.