retrieve data from 2 database

[crespoh]

Hi,

How do I select data from two tables from two different databases and
compare them? using dbi,odbc

 

[Jason Drew]

For a start, asking a better question will get better answers:
http://www.catb.org/~esr/faqs/smart-questions.html

Googling for python odbc gives this as the first result:
http://www.python.org/windows/win32/odbc.html

In general, how you compare database tables will depend a lot on the
nature of the tables: e.g. are you comparing names to names, rows to
rows, apples to oranges, etc.?

 

[Dennis Lee Bieber]

Hi,

How do I select data from two tables from two different databases and
compare them? using dbi,odbc

1) Does "two different databases" mean two different DBMSs, or just
two logically distinct databases withing one DBMS?

Some DBMS allow for accessing across "database" boundaries (as
long as both are maintained within the same DBMS) without using a second
connection. (MySQL, for example, looks to allow "select ... from
db_name.table_name..." to cross boundaries.)

2) Do you mean two tables in /each/ "database" (four tables total)
or one in each "database"?

Offhand, if you can do cross-bounds queries, you can probably
use one query/cursor/connection.

select d1.t.f1 as d1f1, d1.t.f2 as d1f2, ... d2.t.f1 as d2f1,
d2.t.f2 as d2f2... from d1.t, d2.t where...

and then run a test comparing all the d1f* to d2f*

Otherwise, you may need two cursors to allow for independent
retrieves...

But maybe you want something like a DIFF report, and the tables
won't match row for row. For that, you will need to write a DIFF style
compare loop, and have cursors that can be rewound (or fetchall into
local data types that you can index).

--

 

[Steve Holden]

Hi,

How do I select data from two tables from two different databases and
compare them? using dbi,odbc

Well, a lot depends on the database. If it's two Access .mdb files, for
example, or two different databases in a single SQL Server instance,
sometimes you can use SQL statements that cross-refer. Not possible with
ODBC, though, I fear.

In that case, we have to hope that the datasets are small enough to hold
in memory, as Python data structures, and then operate on them as Python
sets or some such. The DB API delivers the results of queries as lists
of tuples, each tuple representing a row, but of course they can easily
be transformed into other Python objects with more convenient properties
to join the two sets together.

Much then depends on how you want to join the two sets.

regards
Steve