[sqlalchemy] Re: INSERT ... ON DUPLICATE KEY UPDATE
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. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: INSERT ... ON DUPLICATE KEY UPDATE
camlost wrote: 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.) 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. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: INSERT ... ON DUPLICATE KEY UPDATE
camlost wrote: 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? 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. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
