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

Jerry Westrick Jerry at Westrick.Com
Fri Oct 10 13:01:04 CEST 2003


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



More information about the egenix-users mailing list