INSERT ... ON DUPLICATE KEY UPDATE

[camlost]

hello,
i'm trying to use sqlalchemy 0.5.1 with python 2.5.4 on windows. and
mysql 5.1.

the task is simple: to keep fresh information about our servers.

i can get a list of server names from AD, get some info about them and
insert them into DB using Session.add().

if i run the script for the first time, it works fine.
however, the next run fails: sqlalchemy.exc.IntegrityError:
(IntegrityError) (1062, "Duplicate entry...

the table looks like this:
'id', 'int(11)', 'PRIMARY KEY', 'auto_increment'
'name', 'varchar(16)', 'UNIQUE'
'ip_address', 'varchar(16)'
... (some other columns which are not important)

if i want to keep the data up to date, i need to update them if they
already exist in DB.
if i would use plain (literal) sql, i could execute insert in $SUBJ.
but i would like to use sqlalchemy's native solution if it's possible.
however, i don't know how to do this.

can anyone help me?
thanks

c.

[Werner F. Bruhin]

What about something like this:

# get by primary key, you could also use a where clause
item = session.query(db.YourClass).get(131312)

if item:
# do whatever to update
print item
else:
# create a new one
newItem = db.YourClass()
print newItem
session.add(newItem)

# commit
session.commit()

> thanks
>
> c.
>
> >
>
>
>

[camlost]

Thank you for the reply.

However, this solution (though I'm ready to use it) would create a lot
of SQL queries comparing it with simple INSERT ... ON DUPLICATE KEY
UPDATE.
On the other hand, I admit the INSERT ... IN DUPLICATE KEY UPDATE
might not be available in other DBs. I would like the application
would be independent of the database engine bellow.

So... is there some way how to achieve this while keeping number of
SQL queries low? :-)
(The number of objects handled this way is about 20 000.)

Thanks

c.

[jason kirtland]

Sure, if your process will be the only one inserting and changing these
rows. Working through your 20k python objects in batches of 1000 or
whatever size you like, collect the key values from the python objects.
Run a database select to see which of those keys are present in the
database, and then divide your batch into two parts: data needing insert
and data needing update.

If you've got write contention for this data you'd need to work more
granularly (likely row by row) instead, keeping in mind the database
engine's transaction model and ideally taking advantage of any tools the
db engine provides (like ON DUPLICATE or sql's MERGE) . Performance and
engine agnosticism may be mutually exclusive here.