D'Arcy
Thanks for the quick reply. I'll look into that. I don't think I had that
interface compiled when I installed PostgreSQL so I'll check.
Thanks again.
At 08:29 AM 5/29/99 -0400, you wrote:
>Thus spake John J. Boris, Sr.
>> I was wondering if PostgreSQL had a function that allowed you to update a
>> table from a delimited file that would update matching records and insert
>> new records. Under Unify you could do an update with a switch (I think it
>> was -n) that would take input from a pipe delimited file (in the order the
>> fields of the table) and since the table had which field was unique it
>> would key on that and if it didn't find the key it would add the record.
>
>How about a python solution? (untested)
>
>#! /usr/bin/env python
>from pg import DB # http://www.druid.net/pygresql/
>db = DB() # or specify other than the default database
>
>for l in open('path_to_input_filename').readlines():
> dict = convert_line_to_dictionary(l) # this could be automated somewhat
> try: db.insert(dict) # fails if duplicate
> except(db.update(dict) # do this if insert fails
>
>The insert and update can be tried in the reverse order if you expect
>more lines in the input to be updating existing records than new records.
>
>The convert_line_to_dictionary() function could be automated somewhat but
>I really hate depending on the order of fields. However, if I know the
>order of fields in the input file (easier to guarantee) then I woould
>do something like this.
>
> l = string.split(l, '|')
> dict = {
> 'field0': l[0],
> 'field1': l[1],
> 'field2': l[2]
> }
>
>There is also a module to handle comma delimited, quoted strings files.
>
>--
>D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
>http://www.druid.net/darcy/ | and a sheep voting on
>+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
John J. Boris, Sr. Boris Computer Services/ONLine Services
email:john.boris@onlinesvc.com.com
bbs: The Bleeding Edge 609-858-9221