# SQLite ON CONFLICT query

**URL:** <https://forum.inductiveautomation.com/t/sqlite-on-conflict-query/97013>\
**Category:** Ignition\
**Tags:** ignition81, perspective, scripting, database\
**Created:** [October 9, 2024, 11:07am UTC](https://forum.inductiveautomation.com/t/sqlite-on-conflict-query/97013 "2024-10-09T11:07:57Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![illank86](https://avatars.discourse-cdn.com/v4/letter/i/c77e96/32.png) [@illank86](https://forum.inductiveautomation.com/u/illank86)\
**Post date:** [October 9, 2024, 11:07am UTC](https://forum.inductiveautomation.com/t/sqlite-on-conflict-query/97013/1 "2024-10-09T11:07:57Z")

</div>

Hi all,

for customer trial purpose, i used SQLite for Database

I have problem with INSERT ON CONFLICT DO UPDATE with SQLite, when there is no conflict ,then the query works normally, but when conflict happen because of unique constraint, the ON CONFLICT does'nt work and throw an error..

```python
    import java.lang.Exception
	
	txId = system.db.beginTransaction(timeout=5000)
	
	try:
		excelFile = event.file.getBytes()
		res = system.dataset.fromExcel(excelFile, headerRow=0, sheetNumber=0)
		data = Convert.toJson(res)
		
		query = """INSERT INTO myTable(orders, earl_start_date) 
		VALUES(?, ?)
		ON CONFLICT (orders)
		DO UPDATE SET 
		orders=?, earl_start_date=?"""
		
		for item in data:
			args = [item["Order"], system.date.toMillis(item["Earl.start date"])]*2
		    system.db.runPrepUpdate(query, args, tx=txId)
			
		system.db.commitTransaction(txId)	
	except java.lang.Exception, err:
		system.perspective.sendMessage(
		    'toastMessage', 
		    payload={"text": "There is problem with database query \n\n" + str(err), "level": "error"}, 
		    scope='session'
		)
		system.db.rollbackTransaction(txId)
	
	else:
		self.getSibling("Table").props.data = data
	finally:
		system.db.closeTransaction(txId)

```

any idea?

Regards

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [October 9, 2024, 12:06pm UTC](https://forum.inductiveautomation.com/t/sqlite-on-conflict-query/97013/2 "2024-10-09T12:06:09Z")

</div>

Why not `try` your insert, `catch` the error if there is one since you have a unique constraint and do your update in there? You can follow this logic of this post

> [@Insert into database only if the entry doesn't exist](https://forum.inductiveautomation.com/t/insert-into-database-only-if-the-entry-doesnt-exist/35122/2):
>
> There are two ways to do this - check for a duplicate manually with python before inserting, and if there’s a problem show the screen Make UNIQUE INDEX definitions on your table in the database, and then just try/except inserting into it. Personally I like to leave data integrity to the database where appropriate, and ensuring UNIQUE-ness is one of those times in my opinion. I use this method for a MySQL DB and it looks like this in practice. My personal solution would be to make your n…

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [October 9, 2024, 12:23pm UTC](https://forum.inductiveautomation.com/t/sqlite-on-conflict-query/97013/3 "2024-10-09T12:23:40Z")

</div>

> [@illank86](#):
>
> any idea?

Don't use SQLite.

- It is not suitable for production use, due to its single-threaded implementation, so any "trial" isn't valid.
- It doesn't support SQL and various SQL datatypes as well as any of the other common choices.
- It is not the only free DB you can use. PostgreSQL and MariaDB are much better choices for any trial.

(PostgreSQL, in particular, has the best ON CONFLICT implementation on the market. You should use it.)

---

<div class="post-metadata">

**Author:** ![illank86](https://avatars.discourse-cdn.com/v4/letter/i/c77e96/32.png) [@illank86](https://forum.inductiveautomation.com/u/illank86)\
**Post date:** [October 9, 2024, 12:41pm UTC](https://forum.inductiveautomation.com/t/sqlite-on-conflict-query/97013/4 "2024-10-09T12:41:05Z")

</div>

for SQLite, there is no "Duplicate" word in the error message..

---

<div class="post-metadata">

**Author:** ![illank86](https://avatars.discourse-cdn.com/v4/letter/i/c77e96/32.png) [@illank86](https://forum.inductiveautomation.com/u/illank86)\
**Post date:** [October 9, 2024, 12:44pm UTC](https://forum.inductiveautomation.com/t/sqlite-on-conflict-query/97013/5 "2024-10-09T12:44:31Z")

</div>

using PostgreSQL is to overkill for PoC, its just small application, for production i will use PostgreSQL or MS SQL for sure

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [October 9, 2024, 12:46pm UTC](https://forum.inductiveautomation.com/t/sqlite-on-conflict-query/97013/6 "2024-10-09T12:46:43Z")

</div>

What error message do you get from trying to insert a duplicate?

I would tend to agree with @pturmel here - setting up a real database doesn't take more than 15 minutes of time and you will get a lot more for it. If you continue with SQLLite syntax like INSERT ... ON CONFLICT UPDATE which SQL Server does not have, you will end up needing to rewrite your queries too - probably more work than just making an instance of SQL Server Express.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [October 9, 2024, 12:49pm UTC](https://forum.inductiveautomation.com/t/sqlite-on-conflict-query/97013/7 "2024-10-09T12:49:21Z")

</div>

> [@bkarabinchak.psi](#):
>
> which SQL Server does not have

Well, that is one of the many reasons to not use SQL Server. (Particularly since it has no deterministic alternative.)

> [@illank86](#):
>
> PostgreSQL is to overkill

No excuse. You can spin up a docker instance of Postgres in seconds.

---

<div class="post-metadata">

**Author:** ![illank86](https://avatars.discourse-cdn.com/v4/letter/i/c77e96/32.png) [@illank86](https://forum.inductiveautomation.com/u/illank86)\
**Post date:** [October 9, 2024, 12:59pm UTC](https://forum.inductiveautomation.com/t/sqlite-on-conflict-query/97013/8 "2024-10-09T12:59:52Z")

</div>

for sure i will use Postgesql, but i got it working, dont know why this is working compared to the first code

```python
    import java.lang.Exception
	
	txId = system.db.beginTransaction(timeout=5000)
	
	excelFile = event.file.getBytes()
	res = system.dataset.fromExcel(excelFile, headerRow=0, sheetNumber=0)
	data = Convert.toJson(res)
	
	query = """INSERT INTO myTable(orders, earl_start_date) 
	VALUES(?, ?)
	ON CONFLICT (orders)
	DO UPDATE SET 
	orders=?, earl_start_date=?"""
	
	try:
		for item in data:
			args = [item["Order"], system.date.toMillis(item["Earl.start date"])] * 2
			tester = system.db.runPrepUpdate(query, args, tx=txId)

		system.db.commitTransaction(txId)	
	except java.lang.Exception, err:
		system.perspective.sendMessage(
		    'toastMessage', 
		    payload={"text": "There is problem with database query \n\n" + str(err), "level": "error"}, 
		    scope='session'
		)
		system.db.rollbackTransaction(txId)
	else:
		self.getSibling("Table").props.data = data
	finally:
		system.db.closeTransaction(txId)

```

move most of the code out of the try catch block solved the issue..