better way?

[Scott David Daniels]

Pet wrote:
> On 11 Aug., 22:19, "Rami Chowdhury" <rami.chowdh... at gmail.com> wrote:
>> Ah, my apologies, I must have been getting it confused with ON UPDATE  
>> [things]. Thanks for correcting me.
>>
>> On Tue, 11 Aug 2009 13:10:03 -0700, Matthew Woodcraft  
>>
>> <matt... at woodcraft.me.uk> wrote:
>>> "Rami Chowdhury" <rami.chowdh... at gmail.com> writes:
>>>> IIRC Postgres has had ON DUPLICATE KEY UPDATE functionality longer than
>>>> MySQL...
>>> PostgreSQL does not have ON DUPLICATE KEY UPDATE.
>>> The SQL standard way to do what the OP wants is MERGE. PostgreSQL
>>> doesn't have that either.
> 
> So, I'm doing it in right way?
> What about building columns? map(lambda s: s + ' = %s', fields)
> Is that o.k.?
Isn't
     t = [field + ' = %s' for field in fields]
clearer than
     t = map(lambda s: s + ' = %s', fields)
? your call of course.

I don't quite understand why you are building the SQL from data
but constructing the arguments in source.  I'd actually set the
SQL up directly as a string, making both the SQL and Python more
readable. To the original question, you could unconditionally
perform a queries vaguely like:

UPDATE_SQL = '''UPDATE table ...
      WHERE id = %s AND location = %s;'''
INSERT_SQL = '''INSERT INTO table(...
      WHERE NOT EXISTS(SELECT * FROM table
                       WHERE id = %s AND location = %s;);'''
I'd put the NOW() and constant args (like the 1) in the SQL itself.
then your code might become:
     row = (self.wl, name, location, id)
     self._execQuery(db, UPDATE_SQL, [row])
     self._execQuery(db, INSERT_SQL, [row + (location, id)])
if _execQuery is like the standard Python DB interfaces.  Having
the SQL do the checking means you allows the DB to check its
index and use that result to control the operation, simplifying
the Python code without significantly affecting the the DB work
needed.  The "SELECT *" form in the EXIST test is something DB
optimizers look for, so don't fret about wasted data movement.



--Scott David Daniels
Scott.Daniels at Acm.Org