PyQt sql data updating error

Question

I'm trying to create GUI for updating data in my database, and I have two ideas. One requires a lot of more work, but works, and the other seems perfect, but gives me error. Here is the working one:

def update_kirurg(self):
              query = QSqlQuery()
              if self.update_attribute_name_lineEdit.text() == "oib_kir":
                     query.prepare("UPDATE kirurg SET oib_kir = :new_oib_kir WHERE oib_kir = :old_oib_kir")
                     query.bindValue(":new_oib_kir", self.update_new_value_lineEdit.text())
                     query.bindValue(":old_oib_kir", self.update_previous_value_lineEdit.text())
                     status = query.exec_()

                     if status is not True:
                            errorText = query.lastError().text()
                            QtGui.QMessageBox.critical(self, 'Query error', errorText)

After that I changed my GUI a bit, that's why lineEdit names are different, and I've tried this code, which would save me a ton of copy and pasting, but for some reason it doesn't work.

def update_asistira(self):
              query = QSqlQuery()
              query.prepare("UPDATE asistira SET :column_name = :new_column_value WHERE :condition_column = :condition_column_value")
              query.bindValue(":column_name", self.update_attribute_name_lineEdit.text())
              query.bindValue(":new_column_value", self.update_new_value_lineEdit.text())
              query.bindValue(":condition_column", self.update_where_column_lineEdit.text())
              query.bindValue(":condition_column_value", self.update_has_value_lineEdit.text())
              status = query.exec_()

              if status is not True:
                     errorText = query.lastError().text()
                     QtGui.QMessageBox.critical(self, 'Query error', errorText)

It gives me this error:

ERROR: Syntax error at or near "(" LINE 1: EXECUTE ('uloga', 'sadasd', 'id_operacije', '1044542ED') ^ (42601) QPSQL: Unable to create query

EDIT: I tried like this:

def update_asistira(self):
              query = QSqlQuery()
              status = query.exec("UPDATE asistira SET uloga = asdasd WHERE oib_kir = 40145478661")


              if status is not True:
                     errorText = query.lastError().text()
                     QtGui.QMessageBox.critical(self, 'Query error', errorText)

Where asistira has this sql code (written directly in postgreSQL):

CREATE TABLE asistira
(
  id_operacije character varying(20) NOT NULL,
  oib_kir character(11) NOT NULL,
  uloga text,
  CONSTRAINT asistira_pkey PRIMARY KEY (id_operacije, oib_kir),
  CONSTRAINT asistira_id_operacije_fkey FOREIGN KEY (id_operacije)
      REFERENCES operacija (id_operacije) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT asistira_oib_kir_fkey FOREIGN KEY (oib_kir)
      REFERENCES kirurg (oib_kir) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

And it gives me this error:

ERROR: column "asdasd" does not exist LINE 1: UPDATE asistira SET uloga = asdasd WHERE oib_kir = 40145478661 (42703) QPSQL: Unable to create query

EDIT2: Apparently you can't use (... :some_value = :another_value...). The value on the left has to be already known, otherwise it just doesn't work. But how do I make UPDATE query than?

EDIT3: I've tried this now and it gives me syntax error

query.exec("""UPDATE "%s" SET "%s" = '%s' WHERE "%s" = '%s'""" % (str(self.lineEdit.text()), str(self.lineEdit_2.text()), str(self.lineEdit_3.text()), str(self.lineEdit_4.text()), str(self.lineEdit_5.text()))

Is it possible to also manually enter table name like I did here?

Please get crate table property to debug it. Have your use PostgreSQL ? Thanks. — Bandhit Suksiri, Aug 25 '14 at 02:51
What do you mean by create table property to debug it? And what's hard code? I'm using postgreSQL — Luka, Aug 25 '14 at 07:10
Sorry, I mistake your question. Hard code this mean, Did your try use string directly without using 'bindValue'? Thanks. — Bandhit Suksiri, Aug 25 '14 at 07:20

score 1 · Accepted Answer · edited May 23 '17 at 11:57

(Sorry of late) OK, I think I understand your problem.

Problem is method QSqlQuery.bindValue (self, QString placeholder, QVariant val, QSql.ParamType type = QSql.In). As I know, If your pass string variable in this method, It will bind single quotes between this string. So if position bind is not attribute of table, It OK; else It can be error. For in this answer, @Bill Karwin says, "The standard SQL language uses double-quotes for delimited identifiers".

To fix it, use method QSqlQuery.bindValue (self, QString placeholder, QVariant val, QSql.ParamType type = QSql.In) only pass string variable not attribute of table. Or use python string.

"""UPDATE asistira SET "%s" = '%s' WHERE "%s" = '%s'""" % (str(self.update_attribute_name_lineEdit.text()), str(self.update_new_value_lineEdit.text()), str(self.update_where_column_lineEdit.text()), str(self.update_has_value_lineEdit.text()))

or

"""UPDATE asistira SET %s = '%s' WHERE %s = '%s'""" % (str(self.update_attribute_name_lineEdit.text()), str(self.update_new_value_lineEdit.text()), str(self.update_where_column_lineEdit.text()), str(self.update_has_value_lineEdit.text()))

And in problem 2, your forget single quotes between string variable.

"""UPDATE asistira SET uloga = 'asdasd' WHERE oib_kir = '40145478661'"""

Hope is helps,

EDIT 3 : Your forget ')' at last line. And your can avoid double-quotes in this case. (If your have complex or use same variable, please use it). I think in this case we should manually enter table. It's static. — Bandhit Suksiri, Aug 26 '14 at 16:43
It works! Thank you so so much, this has helped me immensely :) — Luka, Aug 26 '14 at 16:50

PyQt sql data updating error

1 Answers1