petshmidt at googlemail.com
Wed Aug 12 17:09:02 CEST 2009
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.?
> 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
More information about the Python-list