Multiple Databases in SA

[carb...@gmail.com]

I have a situation where I need to access two databases inside the
same PostgreSQL instance. I tried to create a new engine and bind the
specific tables to it, but this only works in the shell. (I think
maybe one of the TG/CherryPy filters is overriding me.)

Any ideas on how to do this?

Thanks!

A.

[Jorge Godoy]

"ad...@sherman.ca" <carb...@gmail.com> writes:

Show us the errors you get. If you have multiple databases you'll have to
pick the correct connection, etc.

--
Jorge Godoy <jgo...@gmail.com>

[carb...@gmail.com]

On Jan 30, 5:25 am, Jorge Godoy <jgo...@gmail.com> wrote:

> "a...@sherman.ca" <carbo...@gmail.com> writes:
> > I have a situation where I need to access two databases inside the
> > same PostgreSQL instance. I tried to create a new engine and bind the
> > specific tables to it, but this only works in the shell. (I think
> > maybe one of the TG/CherryPy filters is overriding me.)
>

> > Any ideas on how to do this?Show us the errors you get. If you have multiple databases you'll have to

> pick the correct connection, etc.

Well, first I added this to my model:

tracker_engine = create_engine('postgres://vrst_tracker:{Q]&L]$;!
@tranquility.techsupport.local:5432/vrst_tracker')
tracker_metadata = DynamicMetaData(name="Tracker Metadata")
tracker_metadata.connect(tracker_engine)
trackerissue_table = Table('_issue', tracker_metadata, autoload=True)
trackerclient_table = Table('_client', tracker_metadata,
autoload=True)
session.bind_table(trackerissue_table, tracker_engine)
session.bind_table(trackerclient_table, tracker_engine)
assign_mapper(session.context, TrackerIssue, trackerissue_table)
assign_mapper(session.context, TrackerClient, trackerclient_table)

>From the "tg-admin" shell, doing this works:

issues = TrackerIssue.select()

So I can access both databases. When I do the same method inside of a
controller method, it throws the query against the wrong database and
I get a ProgrammingError.

Thanks,

A.

--
Adam Sherman
Technologist
+1 (613) 797-6819 | http://www.sherman.ca/ | sip:ad...@sherman.ca

[carb...@gmail.com]

On Jan 30, 6:47 am, "a...@sherman.ca" <carbo...@gmail.com> wrote:
> On Jan 30, 5:25 am, Jorge Godoy <jgo...@gmail.com> wrote:
> > "a...@sherman.ca" <carbo...@gmail.com> writes:
> > > I have a situation where I need to access two databases inside the
> > > same PostgreSQL instance. I tried to create a new engine and bind the
> > > specific tables to it, but this only works in the shell. (I think
> > > maybe one of the TG/CherryPy filters is overriding me.)
>
> > > Any ideas on how to do this?Show us the errors you get. If you have multiple databases you'll have to

> > pick the correct connection, etc.Well, first I added this to my model:

>
> tracker_engine = create_engine('postgres://vrst_tracker:{Q]&L]$;!
> @tranquility.techsupport.local:5432/vrst_tracker')
> tracker_metadata = DynamicMetaData(name="Tracker Metadata")
> tracker_metadata.connect(tracker_engine)
> trackerissue_table = Table('_issue', tracker_metadata, autoload=True)
> trackerclient_table = Table('_client', tracker_metadata,
> autoload=True)
> session.bind_table(trackerissue_table, tracker_engine)
> session.bind_table(trackerclient_table, tracker_engine)
> assign_mapper(session.context, TrackerIssue, trackerissue_table)
> assign_mapper(session.context, TrackerClient, trackerclient_table)
>
> >From the "tg-admin" shell, doing this works:issues = TrackerIssue.select()
>
> So I can access both databases. When I do the same method inside of a
> controller method, it throws the query against the wrong database and
> I get a ProgrammingError.

I added session.bind_table(trackerissue_table, tracker_engine) to the
controller method, which seems to work. Is this smart?

A.

[Jorge Godoy]

"ad...@sherman.ca" <carb...@gmail.com> writes:

> Well, first I added this to my model:
>
> tracker_engine = create_engine('postgres://vrst_tracker:{Q]&L]$;!
> @tranquility.techsupport.local:5432/vrst_tracker')
> tracker_metadata = DynamicMetaData(name="Tracker Metadata")
> tracker_metadata.connect(tracker_engine)
> trackerissue_table = Table('_issue', tracker_metadata, autoload=True)
> trackerclient_table = Table('_client', tracker_metadata,
> autoload=True)
> session.bind_table(trackerissue_table, tracker_engine)
> session.bind_table(trackerclient_table, tracker_engine)
> assign_mapper(session.context, TrackerIssue, trackerissue_table)
> assign_mapper(session.context, TrackerClient, trackerclient_table)
>
>>From the "tg-admin" shell, doing this works:
>
> issues = TrackerIssue.select()
>
> So I can access both databases. When I do the same method inside of a
> controller method, it throws the query against the wrong database and
> I get a ProgrammingError.

I must be blind, but I'm only seeing one database there: vrst_tracker.

Where's the other one?

And a ProgrammingError is an exception raised from PostgreSQL's psycopg
driver.

--
Jorge Godoy <jgo...@gmail.com>

[carb...@gmail.com]

On Jan 30, 9:40 am, Jorge Godoy <jgo...@gmail.com> wrote:

> "a...@sherman.ca" <carbo...@gmail.com> writes:
> > Well, first I added this to my model:
>
> > tracker_engine = create_engine('postgres://vrst_tracker:{Q]&L]$;!
> > @tranquility.techsupport.local:5432/vrst_tracker')
> > tracker_metadata = DynamicMetaData(name="Tracker Metadata")
> > tracker_metadata.connect(tracker_engine)
> > trackerissue_table = Table('_issue', tracker_metadata, autoload=True)
> > trackerclient_table = Table('_client', tracker_metadata,
> > autoload=True)
> > session.bind_table(trackerissue_table, tracker_engine)
> > session.bind_table(trackerclient_table, tracker_engine)
> > assign_mapper(session.context, TrackerIssue, trackerissue_table)
> > assign_mapper(session.context, TrackerClient, trackerclient_table)
>
> >>From the "tg-admin" shell, doing this works:
>
> > issues = TrackerIssue.select()
>
> > So I can access both databases. When I do the same method inside of a
> > controller method, it throws the query against the wrong database and

> > I get a ProgrammingError.I must be blind, but I'm only seeing one database there: vrst_tracker.

>
> Where's the other one?
>
> And a ProgrammingError is an exception raised from PostgreSQL's psycopg
> driver.

Sorry, I should have been more clear. The other database is accessed
using the normal TG way, configuration is in dev.cfg. The
ProgrammingError is because the query is trying to fetch a table that
doesn't exist in that context. (Wrong DB)

A.

[Jorge Godoy]

"ad...@sherman.ca" <carb...@gmail.com> writes:

> Sorry, I should have been more clear. The other database is accessed
> using the normal TG way, configuration is in dev.cfg. The
> ProgrammingError is because the query is trying to fetch a table that
> doesn't exist in that context. (Wrong DB)

I can't help with a complete example to try seeing what happens... Sorry.
Maybe someone else might be able to help.

--
Jorge Godoy <jgo...@gmail.com>

[Toshio Kuratomi]

On Tue, 2007-01-30 at 10:38 -0800, ad...@sherman.ca wrote:
> On Jan 30, 9:40 am, Jorge Godoy <jgo...@gmail.com> wrote:
> > "a...@sherman.ca" <carbo...@gmail.com> writes:
> > > Well, first I added this to my model:
> >
> > > tracker_engine = create_engine('postgres://vrst_tracker:{Q]&L]$;!
> > > @tranquility.techsupport.local:5432/vrst_tracker')
> > > tracker_metadata = DynamicMetaData(name="Tracker Metadata")
> > > tracker_metadata.connect(tracker_engine)
> > > trackerissue_table = Table('_issue', tracker_metadata, autoload=True)
> > > trackerclient_table = Table('_client', tracker_metadata,
> > > autoload=True)
> > > session.bind_table(trackerissue_table, tracker_engine)
> > > session.bind_table(trackerclient_table, tracker_engine)
> > > assign_mapper(session.context, TrackerIssue, trackerissue_table)
> > > assign_mapper(session.context, TrackerClient, trackerclient_table)
> >

Try creating your own session and associating the mapper/table with
that. If it works, ask if that's expected behaviour on the sqlalchemy
list/IRC channel and report back :-)

-Toshio