On Aug 12, 4:29 pm, Scott David Daniels <scott.dani...@acm.org> wrote: > Pet wrote: > > On 11 Aug., 22:19, "Rami Chowdhury" <rami.chowdh...@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...@woodcraft.me.uk> wrote: > >>> "Rami Chowdhury" <rami.chowdh...@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...@acm.org -- http://mail.python.org/mailman/listinfo/python-list
