0

Assuming I have a database that can add values as follow:

def information(cursor):
    a = input("Please define a 'a':")
    b = input("Please define a b:")
    c = input("Please define a c:")
    d = input("Please define a d:")
   
    statement=f'INSERT INTO data VALUES ({a}, "{b}", "{c}", "{d}");'
    cursor.execute(statement)
    cursor.close()

How can I get a nice message if the Value already is in table.

John Doe
  • 37
  • 8
  • Something like `IF(EXISTS... RETURN ...` Which DBMS is this? – Charlieface Jan 26 '21 at 00:05
  • see this answer: [psycopg2 unique key violation error](https://stackoverflow.com/questions/58740043/how-do-i-catch-a-psycopg2-errors-uniqueviolation-error-in-a-python-flask-app) – AnkurSaxena Jan 26 '21 at 00:06

1 Answers1

0

I must say, I'm not entirely familiar with Python, especially with how it's SQLite driver works.

But the gist of the solution is to conditionally insert, then get how many rows were inserted. In many implementations of SQL we can do something like SELECT @@ROWCOUNT but I don't think that works in SQLite.

But I think this works, it depends on the insert failing a unique key:

INSERT OR IGNORE INTO data VALUES ({a}, "{b}", "{c}", "{d}");

Then get the rowcount with:

cursor.rowcount

So we can use that result to check what happened. If it is zero, then the insert failed.

Incidentally, I believe your current implementation is open to SQL injection attacks. You should instead use parameters with a prepared query.

Charlieface
  • 52,284
  • 6
  • 19
  • 43
  • https://stackoverflow.com/questions/45343175/python-3-sqlite-parameterized-sql-query – Charlieface Jan 26 '21 at 01:00
  • I'm not good on Python so you'll have to work that out. You want to check the rowcount and based on that being `0` give back a message. – Charlieface Jan 26 '21 at 01:07
  • If you have a unique constraint then the insert will fail, so you can just check the rowcount. Apologies, you want `IGNORE` instead of `ABORT` – Charlieface Jan 26 '21 at 01:10
  • Ok, thanks for feedback. Please write here if you have any suggestions on how to implement. – John Doe Jan 26 '21 at 01:14