John wrote: > On Saturday 12 September 2009 09:26:34 am Ed Leafe wrote: >> On Sep 11, 2009, at 9:03 PM, Jeff Johnson wrote: >>> I am using Dabo to access a MSSQL Server and a PostgreSQL Server. It >>> was pretty easy to get two connections going. PostgreSQL is my app >>> connection. Is it possible to do a select statement on a table from a >>> database in one connection and a table from a database in another >>> connection? >>> >>> Here is what I want to do: >>> >>> SELECT * from mstable ; >>> where not exists(select * from pgtable ; >>> where mstable.pk = pgtable.pk) >>> >>> The purpose is to find records in the MS table that don't exist in the >>> PG table so I can add them. >> As others have pointed out, you can't query across connections. >> >> How big are the tables? If they aren't huge, you might try something >> like this: use the PostgreSQL cursor to grab all the PKs from the >> pgtable into a tuple, and then grab the mscursor's records. Something >> like this: >> >> sql = "select pk from pgtable" >> pgcursor.execute(sql) >> pgpks = tuple([rec["pk"] for rec in pgcursor.getDataSet()]) >> >> sql = """select * from mstable >> where mstable.pk not in %s; >> """ >> mscursor.execute(sql, pgpks) >> >> >> -- Ed Leafe > > Which brings a question to mind about the size limits of lists. Let's say > the > pgpks was over 100,000. Would it still work? Where would it stop working? > > To get around the size issue would you use a loop using a fetchone routine? > I > was working with only a gig or two and did not run into issues transfering > data. But I wondered what would cause the system to break. > > I'm just wondering what happens when dealing with large data sources. > > Johnf > Thanks Ed & John! The tables are large but all have datetimes in them so I can select the most recent month, etc. The mssql tables don't have primary keys but do have a unique field. Ed's suggestions sounds very workable.
Thanks, -- Jeff Jeff Johnson j...@dcsoftware.com Phoenix Python User Group - sunpigg...@googlegroups.com _______________________________________________ Post Messages to: Dabo-users@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users Searchable Archives: http://leafe.com/archives/search/dabo-users This message: http://leafe.com/archives/byMID/4aabdbe6.9050...@dcsoftware.com
