better way?

S

someone

Hello,

I'd like to make insert into db if record not exist otherwise update.
to save typing list of columns in both statements I do following

query = "SELECT location FROM table WHERE location = %s AND id = %s;"
result = self._getResult(db, query, [location,id])

fields = ['id', 'location', 'wl', 'modified', 'counter', 'name']
if result:
t = map(lambda s: s + ' = %s', fields)
temp = ", ".join(t)
query = "UPDATE table SET " + temp + " WHERE location = %s AND id
= %s;"
self._execQuery(db, query, [id, location, self.wl, 'NOW()', 1,
name, location, id])
else:
f = ", ".join(fields)
query = """
INSERT INTO table
(""" + f + """)
VALUES
(%s,%s,%s,NOW()- interval '1 day', 1, %s)
"""
self._execQuery(db, query, [id, location, self.wl, name])



is there better or more readable way to do it?

Pet
 
K

Kushal Kumaran

Hello,

I'd like to make insert into db if record not exist otherwise update.
to save typing list of columns in both statements I do following

<snip>


is there better or more readable way to do it?

Well, mysql, in particular, offers an "on duplicate key update" clause
that you can take a look at. Don't know about about other databases.
 
P

Pet

Well, mysql, in particular, offers an "on duplicate key update" clause
that you can take a look at.  Don't know about about other databases.

Oh, forgotten to mention. It's PostGres
 
R

Rami Chowdhury

IIRC Postgres has had ON DUPLICATE KEY UPDATE functionality longer than
MySQL...
 
M

Matthew Woodcraft

Rami Chowdhury said:
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.

-M-
 
R

Rami Chowdhury

Ah, my apologies, I must have been getting it confused with ON UPDATE
[things]. Thanks for correcting me.
 
P

Pet

Ah, my apologies, I must have been getting it confused with ON UPDATE  
[things]. Thanks for correcting me.

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.

--
Rami Chowdhury
"Never attribute to malice that which can be attributed to stupidity" --  
Hanlon's Razor
408-597-7068 (US) / 07875-841-046 (UK) / 0189-245544 (BD)

So, I'm doing it in right way?
What about building columns? map(lambda s: s + ' = %s', fields)
Is that o.k.?

thanks
 
D

Dennis Lee Bieber

Well, mysql, in particular, offers an "on duplicate key update" clause
that you can take a look at. Don't know about about other databases.
SQLite offers "insert or replace"
 
D

Dennis Lee Bieber

Oh, forgotten to mention. It's PostGres

Really? There are still installations of an RDBMS that predates the
commonalization of SQL?

I suspect you mean PostgreSQL -- it IS a different beast from the
older Postgres.

In either event -- my old books don't show an "all in one" solution.

Best answer is probably to create some stored procedures which you
call instead of plain INSERT; the stored procedure could then do
whatever is needed to check for a duplicate (actually the easiest, I'd
think, would be: if primary key is supplied, it is an UPDATE; if primary
key is NULL or not supplied, it is an insert and the primary key will be
auto-generated).
 
D

Dennis Lee Bieber

The SQL standard way to do what the OP wants is MERGE. PostgreSQL
doesn't have that either.
Odd... I don't recall any of my various SQL books ever mentioning a
MERGE statement...

Ah... SQL 2003 standard. Though reading wikipedia, it is designed to
work merging two similar /tables/ -- meaning the original poster would
basically first have to insert their data into a new/blank table, and at
the end, perform the MERGE to do the insert/update of the old table
using the records in the new table.
http://en.wikipedia.org/wiki/Merge_(SQL)
 
P

Pet

        Really? There are still installations of an RDBMS that predates the
commonalization of SQL?

        I suspect you mean PostgreSQL -- it IS a different beast from the
older Postgres.

O, yeah. Of course it is PostgreSQL
        In either event -- my old books don't show an "all in one" solution.

        Best answer is probably to create some stored procedures which you
call instead of plain INSERT; the stored procedure could then do
whatever is needed to check for a duplicate (actually the easiest, I'd
think, would be: if primary key is supplied, it is an UPDATE; if primary
key is NULL or not supplied, it is an insert and the primary key will be
auto-generated).

I don't really like the idea of stored procedure, because query would
depend on existence of it then. On the other side, it looks like best
option.
 
P

Pet

Pet said:
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  
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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,743
Messages
2,569,478
Members
44,898
Latest member
BlairH7607

Latest Threads

Top