better way?

[Pet]

On Aug 12, 4:29 pm, Scott David Daniels <Scott.Dani... at Acm.Org> wrote:
> 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.

Yes, I think so

> 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

Sometimes, column list could be long, besides I keep column list in
sync for both update and insert query

> 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.

yes, but I'm building both UPDATE in INSERT from same list of columns,
so I didn't found better way as this one


> 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)])

I'm going to rebuild my queries like that.

Thank you very much!


> 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.Dani... at Acm.Org