PyQt: Extracting data from database and comparing it to QLineEdit

Question

I'd like to make an if statement which compares a value inserted in QLineEdit, with a value (primary key) from a table, and if it already exists, it shows message "Identification number you've entered is already in use".

This is the most important part of the code:

class kirurg_GUI(QtGui.QWidget, Ui_kirurg_Widget):
        def __init__(self):
              QtGui.QWidget.__init__(self)
              self.setupUi(self)
              self.kirurg_Add_button.clicked.connect(self.insert_kirurg)

        def insert_kirurg(self):
                     query = QSqlQuery()
                     query.exec("INSERT INTO kirurg (oib_kir, prezime, ime, adresa, broj_telefona)"
                                "VALUES ('%s', '%s', '%s', '%s', '%s')" % (''.join(self.kirurg_oib_kir_lineEdit.text()),
                                                                           ''.join(self.kirurg_prezime_lineEdit.text()),
                                                                           ''.join(self.kirurg_ime_lineEdit.text()),
                                                                           ''.join(self.kirurg_adresa_lineEdit.text()),
                                                                           ''.join(self.kirurg_broj_telefona_lineEdit.text())))

So basically, when kirurg_Add_button is clicked it should add new row in database (consisting of these five values), but only if the first value kirurg_oib_kir doesn't already exist as a primary key in my database.

Did your try this ? Before insert value "SELECT oib_kir FROM kirurg WHERE oib_kir = YOURINPUTVALUE". If result it are empty, you can show dialog or message your want. else, your can insert this data. — Kitsune Meyoko, Aug 20 '14 at 14:16
What is your RDBMS? Some allows extra clauses for that specific case, like MySQL "INSERT IGNORE .." — Sylvain Leroux, Aug 20 '14 at 14:17
@KitsuneMeyoko I won't go for a `SELECT` then `INSERT` as this could lead to race conditions — Sylvain Leroux, Aug 20 '14 at 14:19
Just add a `where not exists` clause to your query. See this question http://stackoverflow.com/questions/3164505/mysql-insert-record-if-not-exists-in-table — Luke, Aug 20 '14 at 14:20
@ Sylvain Leroux, I'm using PostgreSQL, version 9.3.5 I believe — Luka, Aug 20 '14 at 14:43
@Luke, I'm trying that but don't know how to put it exactly. I suppose it's something like this: `"WHERE NOT EXISTS (SELECT oib_kir FROM kirurg WHERE oib_kir = self.kirurg_oib_kir_lineEdit.text()"`. Also, how would I write a message after that stating that person with that oib_kir value is already in the database? — Luka, Aug 20 '14 at 15:12
@ Kitsune Meyoko, I tried that but I don't know how to put my input value. `"SELECT oib_kir FROM kirurg WHERE oib_kir = self.kirurg_oib_kir_lineEdit.text()` is the best I've got, but it doesn't work — Luka, Aug 20 '14 at 15:34
On the other-hand, Your 'oib_kir' in your table is set primary key. If your execute your old code, return value of 'query.exec()' it can tell executes a previously prepared SQL query. Returns true if the query executed successfully; otherwise returns false. So, I think it can be know in Qt the query is successful (That mean it can insert or not). — Kitsune Meyoko, Aug 20 '14 at 15:38
Useful reference for implement : http://pyqt.sourceforge.net/Docs/PyQt4/qsqlquery.html (suggest read example) — Kitsune Meyoko, Aug 20 '14 at 15:49
Sorry friend, but I didn't quite understand what you're trying to say. — Luka, Aug 20 '14 at 15:49
I have to says, If anything error in 'bool QSqlQuery.exec_ (self)' (or your query.exec()), It should be return status False; else it query success and should be return True. So in SQL, IF your have insert duplicated primary key data, that query should be error. — Kitsune Meyoko, Aug 20 '14 at 15:59

score 1 · Accepted Answer · edited Aug 23 '14 at 21:52

1

Actually, this method bool QSqlQuery.exec_ (self, QString query) you implemented can tell if the query is successful or has failed. If it failed, you can get error code by using QSqlError QSqlQuery.lastError (self). So, your 'oib_kir' in your table is set to the primary key. If you have inserted duplicated primary key data, that query should be erroneous. Also, this error should be send status to pyqt.

This code has not been tested yet. Please use to guild-line only;

class kirurg_GUI(QtGui.QWidget, Ui_kirurg_Widget):
        def __init__(self):
              QtGui.QWidget.__init__(self)
              self.setupUi(self)
              self.kirurg_Add_button.clicked.connect(self.insert_kirurg)

        def insert_kirurg(self):
                     oib_kir       = self.kirurg_oib_kir_lineEdit.text() # <- return it should be QString
                     prezime       = self.kirurg_prezime_lineEdit.text()
                     ime           = self.kirurg_ime_lineEdit.text()
                     adresa        = self.kirurg_adresa_lineEdit.text()
                     broj_telefona = self.kirurg_broj_telefona_lineEdit.text()
                     if not all([not oib_kir.isEmpty(), not prezime.isEmpty(), not ime.isEmpty(), not adresa.isEmpty(), not broj_telefona.isEmpty()]): # <- If python string use bool(DATA)
                         QMessageBox.critical(self, 'Query error', 'Data must not empty')
                     else:
                         query = QSqlQuery()
                         status = query.exec("INSERT INTO kirurg (oib_kir, prezime, ime, adresa, broj_telefona)"
                                "VALUES ('%s', '%s', '%s', '%s', '%s')" % (''.join(oib_kir),
                                                                           ''.join(prezime),
                                                                           ''.join(ime),
                                                                           ''.join(adresa),
                                                                           ''.join(broj_telefona)))
                         if status is not True:
                             errorText = query.lastError().text()
                             QMessageBox.critical(self, 'Query error', errorText)

Note: It's not directly to check "Identification number you've entered is already in use", but you have all error codes for SQL!

Useful reference for QSqlQuery

Useful reference for QString

edited Aug 23 '14 at 21:52

Jamal

747
7
22
31

answered Aug 20 '14 at 16:26

Kitsune Meyoko

3,002
1
13
15

Thanks, I did that and it worked, except i had to put `QtGui.QMessageBox`. But I can still leave oib_kir blank and it doesn't give me any errors. Why is that if it's primary key in my database and it says NOT NULL in database? – Luka Aug 20 '14 at 21:27
I don't know whet is your table optional creation. Such as "oib_kir TEXT NOT NULL PRIMARY KEY," . – Kitsune Meyoko Aug 21 '14 at 02:30
This is my database sql: `oib_kir character(11) NOT NULL`... `CONSTRAINT kirurg_pkey PRIMARY KEY (oib_kir)`. Thank you once again for this function, I can use it in all my sql queries :) But it's weird why is letting me leave the oib_kir field empty. – Luka Aug 21 '14 at 08:56
Also, I tried entering new row in my database manually and it doesn't let me leave oib_kir empty, so I guess it has something to do with lastError function. – Luka Aug 21 '14 at 09:02
Insert empty data, It should be query failed, isn't it? If failed it should be have error. What is return status of 'bool QSqlQuery.exec_ (self, QString query)' ? And what is your SQL query command ? – Kitsune Meyoko Aug 21 '14 at 09:06
That's the thing, it doesn't give me that error message when I enter empty oib_kir. But when I try to enter oib_kir that's already in db it says: ERROR: duplicate key value violates unique constraint "kirurg_pkey". My command is exactly like you posted, except with QtGui.QMessageBox instead of QMessageBox. Here is my complete SQL code for that table which I use in my postgres database: `CREATE TABLE kirurg ( oib_kir character(11) NOT NULL, prezime text, ime text, adresa text, broj_telefona character varying(12), CONSTRAINT kirurg_pkey PRIMARY KEY (oib_kir) )` – Luka Aug 21 '14 at 09:16
On the other-hand, return data of 'self.kirurg_oib_kir_lineEdit.text()' is empty string ('', Not none). Then, it can be insert record. If it true, add case before query have string. See my answer. – Kitsune Meyoko Aug 21 '14 at 09:59
I've only put this, since others can be empty: `oib_kir = self.kirurg_oib_kir_lineEdit.text() if not oib_kir.isEmpty(): QtGui.QMessageBox.critical(self, 'Query error', 'Data must not empty')`, and it's giving me this error: AttributeError: 'str' object has no attribute 'isEmpty' – Luka Aug 21 '14 at 10:21
Huuummm ???? 'self.kirurg_oib_kir_lineEdit.text()' isn't return 'QString' data-type ? Why it python string data-type, It should not be that. OK, replace 'DATA.isEmpty()' to 'bool(DATA)'. – Kitsune Meyoko Aug 21 '14 at 10:29
Ok, I've put this now:`if not oib_kir: QtGui.QMessageBox.critical(self, 'Query error', 'oib_kir must not be empty')`, and it shows me the error "oib_kir must not be empty", which is great, but it still add's that row in my database, eventhough it has empty oib_kir. – Luka Aug 21 '14 at 10:43
Sorry, I forget if-else statement something, Please read my answer again, hope is helps and last port. (Toooo long post.) – Kitsune Meyoko Aug 21 '14 at 10:54
1

It finally works! Thanks for everything man, you're the best :)! – Luka Aug 21 '14 at 11:09
1

Did you know there is a [whole revision history](http://stackoverflow.com/posts/25409862/revisions) that supports edit summaries? That way you don't need all the "last updated" bits. – Seth Aug 23 '14 at 21:52

PyQt: Extracting data from database and comparing it to QLineEdit

1 Answers1