[egenix-users] Copy ing rows from tables...

[Steve Holden]

Might I immodestly suggest looking at the recipe I wrote for the Python
cookbook? This demonstrates how the description can be used to access
aspects of the retrieved rows. See

	http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/81189

and possibly

	http://aspn.activestate.com/ASPN/Cookbook/Python?kwd=Databases

for further hopefully useful information.

regards
--
Steve Holden          +1 703 278 8281        http://www.holdenweb.com/
Improve the Internet           http://vancouver-webpages.com/CacheNow/
Python Web Programming                http://pydish.holdenweb.com/pwp/
Interview with GvR August 14, 2003       http://www.onlamp.com/python/


> -----Original Message-----
> From: egenix-users-bounces at lists.egenix.com
> [mailto:egenix-users-bounces at lists.egenix.com]On Behalf Of Jerry
> Westrick
> Sent: Friday, October 10, 2003 6:01 AM
> To: Egenix-Users at Lists.Egenix.Com
> Subject: Re: [egenix-users] Copy ing rows from tables...
>
>
> Okay, thanks...
>
> I see I'll have to do SQL formatting myself,
> and will have to use the column descriptors to
> decide on how to do so...
>
> That answers my questions...
>
> Thanks for you time!
>
>
> Jerry
>
>
> On Fri, 2003-10-10 at 11:38, M.-A. Lemburg wrote:
> > Jerry Westrick wrote:
> > > Mr. Lemburg
> > >
> > > First thansk for the prompt reply...
> > > (it's nice to be supported out of europe every once in a while)!
> > >
> > > Looking in your documentation, is see a routne called "execute"
> > > which I could use to avoid having to do SQL formating.
> > >
> > > So following your suggestion, and documentation I can:
> > > 1- Get list of columns (in correct order from result set)
> > > 2- build insert/update stmt with '?' parameter markers
> > > 3- then for each row returned from original select
> > >     pass the resulting row as 'params' parameter to
> "execute" method
> >
> > Not quite: the '?' marker is usually only allowed in the
> > WHERE clause (plus some other places which vary by ODBC driver).
> > You should get the list of columns and then format them
> > directly into the SQL:
> >
> > "SELECT %s WHERE ... " % (','.join(columnnames),)
> >
> > will get you there.
> >
> > > I wonder if the returned row would be acceptable as input to the
> > > params parameter....
> > >
> > >
> > > Jerry Westrick
> > >
> > >
> > >
> > > On Fri, 2003-10-10 at 10:41, M.-A. Lemburg wrote:
> > >
> > >>Jerry Westrick wrote:
> > >>
> > >>>Dear sirs.
> > >>>
> > >>>I have the situation where I "replicate" a database
> table.  That is I
> > >>>have a complete copy of a (MS SQL) database table stored
> locally (in
> > >>>Postgress).  The system has been setup so that I can identify the
> > >>>modified rows. Theese I need to retrieve and store
> (either insert or
> > >>>update) into my postgress table.
> > >>>
> > >>>I access both databases via your zope mxodbc product.
> > >>>
> > >>>My question is, which is the easiest way to retrive a row from
> > >>>the MS-SQL connection, and inseert/update itno the
> postgress connection.
> > >>>
> > >>>THe standard zope solution, requires the listing of all
> field names in
> > >>>both the update and insert statements.  This is terribly
> maintenance
> > >>>prone.  Since the 2 tables are identical (except my
> version has an
> > >>>additional field which is updated via trigger), I
> suspect that there
> > >>>must be a lower level routines, which i can use which
> takes advantage
> > >>>of the fact that the 2 tables are similarly structured.
> > >>>
> > >>>Thanking you in advance for your support.
> > >>
> > >>It is quite easy to fetch the field names for both tables and
> > >>format an SQL query for the update from that list.
> > >>
> > >>A typical way of doing that is to run the query "SELECT * FROM
> > >>MyTable WHERE 1=0" and then having a look at the result set
> > >>which  is empty but does list the field names.
> > >>
> > >>Another approach is to use the .columns() method on the mxODBC
> > >>Zope DA connection object:
> > >>
> > >>     def columns(self, table_name):
> > >>
> > >>         """ Returns a list of dictionaries with entries 'Name',
> > >>             'Type', 'Precision', 'Scale', 'Nullable'
> ('with Null' or '')
> > >>             for each column in the table table_name.
> > >>
> > >>         """
> > >
> > >
> > >
> > >
> ______________________________________________________________
> _________
> > > eGenix.com User Mailing List
http://www.egenix.com/
> > http://lists.egenix.com/mailman/listinfo/egenix-users


_______________________________________________________________________
eGenix.com User Mailing List                     http://www.egenix.com/
http://lists.egenix.com/mailman/listinfo/egenix-users