469,904 Members | 2,413 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,904 developers. It's quick & easy.

retrieve data from 2 database


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

Jul 28 '05 #1
3 2072
For a start, asking a better question will get better answers:

Googling for python odbc gives this as the first result:

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

Jul 28 '05 #2
On 27 Jul 2005 20:18:55 -0700, "crespoh" <cr*****@gmail.com> declaimed
the following in comp.lang.python:

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

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

-- ================================================== ============ <
wl*****@ix.netcom.com | Wulfraed Dennis Lee Bieber KD6MOG <
wu******@dm.net | Bestiaria Support Staff <
================================================== ============ <
Home Page: <http://www.dm.net/~wulfraed/> <
Overflow Page: <http://wlfraed.home.netcom.com/> <

Jul 28 '05 #3
crespoh wrote:

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.

Steve Holden +44 150 684 7255 +1 800 494 3119
Holden Web LLC http://www.holdenweb.com/

Jul 28 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

16 posts views Thread by Daniel Tonks | last post: by
4 posts views Thread by Claudia Fong | last post: by
1 post views Thread by Mel | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.