[PyQt] Postgresql and QDataWidgetMapper.submit() error on timestamp field

[Dave Kelly]

Hi,

 

I am stuck making a data driven application using PyQt5 and Postgresql.
Background information

 

PyQt version '5.2.1' installed from the repositories on Linux Mint 17.2 64
bit Cinnamon

Postgresql 9.3

 

When I try to submit from a QDataWidgetMapper it doesn't seem to like the
timestamp data type.  The error message I get from model.lasterror().text()
gives me

 

ERROR:  invalid input syntax for type timestamp: 

LINE 1: EXECUTE qpsqlpstmt_1 ('', email at email.com, '350404...

                              ^

(22007) QPSQL: Unable to create query

 

My quesitons are:

 

1. could I have found a PyQt/Qt bug?

2. how can I see the exact SQL statement it is trying to use when I submit?

 

Table structure:

 

booking_id         1              character varying

checked_in         2              timestamp without time zone

checked_out     3              timestamp without time zone

hotel_id               4              character varying

id            5              character varying

label      6              character varying

departure_date               7              date

arrival_date        8              date

num_adults        9              integer

num_children    10           integer

room_id               11           character varying

room_type_id   12           character varying

address                13           character varying

reference            14           character varying

notes    15           text

email     16           character varying

corporation_id  17           bigint

cancelled             18           timestamp without time zone

 

code:

# -*- coding: utf-8 -*-

 

from PyQt5.QtCore import *

from PyQt5.QtGui import *

from PyQt5.QtSql import *

from PyQt5.QtWidgets import *

import sys

from ci_co_table5 import *

from guest_info_form5 import *

 

BOOKING_ID = 0

CHECKED_IN = 1

CHECKED_OUT = 2

HOTEL_ID = 3

ID = 4

LABEL = 5

DEPARTURE_DATE = 6

ARRIVAL_DATE = 7

NUM_ADULTS = 8

NUM_CHILDREN = 9

ROOM_ID = 10

ROOM_TYPE_ID = 11

ADDRESS = 12

REFERENCE = 13

NOTES = 14

EMAIL = 15

CORPORATION_ID = 16

CANCELLED = 17

      

class Guest_form(QDialog, Ui_guest_info_form):

    def __init__(self, res_id, parent=None):

        QDialog.__init__(self)

        self.ui = Ui_guest_info_form()

        self.ui.setupUi(self)

        self.guest_model = QSqlTableModel(self)

        self.guest_model.setTable("bookings")

        self.guest_model.setFilter("id = '{0}'".format(res_id))

        self.guest_model.select()        

        print(self.guest_model.record(0).value("label"))

        print(self.guest_model.record(0).value(0))

        self.mapper = QDataWidgetMapper(self)

        self.mapper.setSubmitPolicy(QDataWidgetMapper.ManualSubmit)

        self.mapper.setModel(self.guest_model)

        self.mapper.addMapping(self.ui.book_label,LABEL)

        self.mapper.addMapping(self.ui.booking_id,BOOKING_ID)

        self.mapper.addMapping(self.ui.ci_co,CHECKED_IN)

        self.mapper.addMapping(self.ui.arrival_date,ARRIVAL_DATE)

        self.mapper.addMapping(self.ui.departure_date,DEPARTURE_DATE)

        self.mapper.addMapping(self.ui.guest_email,EMAIL)

        self.mapper.addMapping(self.ui.adults,NUM_ADULTS)

        self.mapper.addMapping(self.ui.kids,NUM_CHILDREN)

        self.mapper.toFirst()

        

        btn = self.ui.save_record

        btn.clicked.connect(self.update_record)

        

    def update_record(self):

        #QMessageBox.warning(self, "update record", "Update record")

        row  = self.mapper.currentIndex()

        submit = self.mapper.submit()

        print("mapper: ", submit)

        if submit == False:

            print(self.guest_model.lastError().text())

         

if __name__=="__main__":

    app=QApplication(sys.argv)

    #Database stuff

    db = QSqlDatabase.addDatabase("QPSQL");

    db.setHostName("hostname")

    db.setDatabaseName("pms");

    db.setUserName("username");

    db.setPassword("password");

    if (db.open()==False):     

        QMessageBox.critical(None, "Database Error", db.lastError().text())

    res_id = "350404"

    myapp = Guest_form(res_id)

    myapp.show()

    sys.exit(app.exec_())

 

 

Dave Kelly

The Priory Inn <http://theprioryinn.co.uk/> 

01666 502 251

London Road, Tetbury, GL8 8JJ

"Dining in a 30 mile food zone"

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://www.riverbankcomputing.com/pipermail/pyqt/attachments/20151210/9241206f/attachment.html>