Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Option to ignore inserts if primary key exists already #21

Closed
simonw opened this issue May 25, 2019 · 3 comments
Closed

Option to ignore inserts if primary key exists already #21

simonw opened this issue May 25, 2019 · 3 comments

Comments

@simonw
Copy link
Owner

simonw commented May 25, 2019

I've just noticed that SQLite lets you IGNORE inserts that collide with a pre-existing key. This can be quite handy if you have a dataset that keeps changing in part, and you don't want to upsert and replace pre-existing PK rows but you do want to ignore collisions to existing PK rows.

Do sqlite_utils support such (cavalier!) behaviour?

Originally posted by @psychemedia in #18 (comment)

@simonw simonw changed the title I've just noticed that SQLite lets you IGNORE inserts that collide with a pre-existing key. This can be quite handy if you have a dataset that keeps changing in part, and you don't want to upsert and replace pre-existing PK rows but you do want to ignore collisions to existing PK rows. Option to ignore inserts if primary key exists already May 25, 2019
@simonw
Copy link
Owner Author

simonw commented May 25, 2019

I think this can be implemented as an --ignore option to sqlite-utils insert

@simonw simonw modified the milestone: 1.0 May 25, 2019
@simonw
Copy link
Owner Author

simonw commented May 28, 2019

https://www.sqlite.org/lang_insert.html

Looks like it's as simple as INSERT OR IGNORE - and we already have code related to that here:

sql = """
INSERT {upsert} INTO [{table}] ({columns}) VALUES {rows};
""".format(
upsert="OR REPLACE" if upsert else "",

@simonw simonw closed this as completed in 00c5a49 May 29, 2019
@simonw
Copy link
Owner Author

simonw commented May 29, 2019

Shipped this feature in sqlite-utils 1.1: https://sqlite-utils.readthedocs.io/en/latest/changelog.html#v1-1

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant