How to store date in Sqlite and view it in column in QTableView?

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

How to store date in Sqlite and view it in column in QTableView?

nenad
With PostgreSQL everything is ok.

But I have a problem how to store date in Sqlite and view it in column in
QTableView (with Sqlite database as a model for QTableView). Adding new rows
with date type seems like it adds some other type, not date. And date column
sort in QTableView doesn't work as it is supposed to.

Has anybody an example how to properly do that?



--
Sent from: http://python.6.x6.nabble.com/PyQt-f1792048.html
_______________________________________________
PyQt mailing list    [hidden email]
https://www.riverbankcomputing.com/mailman/listinfo/pyqt
Reply | Threaded
Open this post in threaded view
|

Re: How to store date in Sqlite and view it in column in QTableView?

Sibylle Koczian-3
Am 15.06.2018 um 15:06 schrieb nenad:
> With PostgreSQL everything is ok.
>
> But I have a problem how to store date in Sqlite and view it in column in
> QTableView (with Sqlite database as a model for QTableView). Adding new rows
> with date type seems like it adds some other type, not date. And date column
> sort in QTableView doesn't work as it is supposed to.
>

Sqlite hasn't got a "date type" like PostgreSQL and others. I suspect
it's that fact that's causing you problems. Documentation:

https://sqlite.org/datatype3.html

especially 2.2 and

https://sqlite.org/lang_datefunc.html

If you define your date column as "text" in the database and store your
date values in ISO format they should "just work" in the QTableView,
including sorting.

HTH
Sibylle





_______________________________________________
PyQt mailing list    [hidden email]
https://www.riverbankcomputing.com/mailman/listinfo/pyqt
Reply | Threaded
Open this post in threaded view
|

Re: How to store date in Sqlite and view it in column in QTableView?

nenad
Thank you, Sibylle.

I already have my date column as "text" in the database in ISO format
("yyyy-MM-dd"). But the problem is, it doesn't "just work" in QTableView,
including sorting, because I need EU date format "dd.MM.yyyy" in view and
edit (delegate), but still retain ISO format in database.

I found the solution (for anybody that has similar problem with Sqlite in
PyQt):





--
Sent from: http://python.6.x6.nabble.com/PyQt-f1792048.html
_______________________________________________
PyQt mailing list    [hidden email]
https://www.riverbankcomputing.com/mailman/listinfo/pyqt
Reply | Threaded
Open this post in threaded view
|

Re: How to store date in Sqlite and view it in column in QTableView?

nenad
Code:

import sys
from PyQt4.QtGui import *
from PyQt4.QtCore import *
from PyQt4.QtSql import *

class DateColumnDelegate(QStyledItemDelegate):
    def __init__(self, parent=None):
        super(DateColumnDelegate, self).__init__(parent)
        # 14th March 2016, American: 03/14/2016, British: 14/03/2016,
German(EU): 14.03.2016
        self.format = "dd.MM.yyyy"

    def displayText(self, value, locale):
        return QDate.fromString(value, "yyyy-MM-dd").toString(self.format)

    def createEditor(self, parent, option, index):
        dateedit = QDateEdit(parent)
        dateedit.setDisplayFormat(self.format)
        dateedit.setCalendarPopup(True)
        return dateedit

    def setEditorData(self, editor, index):
        value = index.model().data(index, Qt.DisplayRole)
        editor.setDate(QDate.fromString(value, "yyyy-MM-dd"))

    def setModelData(self, editor, model, index):
        date = editor.date()
        model.setData(index, date)

class Window(QWidget):
    def __init__(self, parent=None):
        super(Window, self).__init__(parent)
        self.model = QSqlTableModel(self)
        self.model.setTable("test")
        self.model.setEditStrategy(QSqlTableModel.OnFieldChange) #
OnManualSubmit, OnRowChange
        self.model.select()

        self.view = QTableView()
        self.view.setModel(self.model)
        self.view.setSelectionMode(QAbstractItemView.SingleSelection)
        self.view.setSelectionBehavior(QAbstractItemView.SelectRows)
        self.view.resizeColumnsToContents()
        self.view.horizontalHeader().setStretchLastSection(True)
        self.view.setSortingEnabled(True)
        self.view.horizontalHeader().setSortIndicator(1, Qt.AscendingOrder)
        self.view.setItemDelegateForColumn(1, DateColumnDelegate())

        vbox = QVBoxLayout(self)
        vbox.addWidget(self.view)
        self.setWindowTitle("Date delegate test")

app = QApplication(sys.argv)
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName(":memory:")
if not db.open():
    print(db.lastError().text())
    sys.exit()
#createFakeData()
query = QSqlQuery()
query.exec_( "DROP TABLE IF EXISTS test" )
#query.exec_( "CREATE TABLE IF NOT EXISTS test (name TEXT, dateofbirth1
DATE, dateofbirth2 TEXT, dateofbirth3 INTEGER)" )
query.exec_( "CREATE TABLE IF NOT EXISTS test (name TEXT, dateofbirth TEXT)"
)
query.prepare("INSERT INTO test (name, dateofbirth) VALUES (:name,
:dateofbirth)")
dateFormat = "yyyy-MM-dd"
names = []
names.append(['John', QDate.fromString('1975-10-21', dateFormat)])
names.append(['Jane', QDate.fromString('1980-08-12', dateFormat)])
names.append(['Judy', QDate.fromString('1978-03-25', dateFormat)])
names.append(['James', QDate.fromString('1986-11-02', dateFormat)])
names.append(['Rita', QDate.fromString('1990-12-05', dateFormat)])
names.append(['Richard', QDate.fromString('1992-07-21', dateFormat)])
names.append(['Donna', QDate.fromString('1988-01-17', dateFormat)])
names.append(['Steve', QDate.fromString('1998-04-15', dateFormat)])
for name in names:
    query.bindValue(":name", name[0])
    query.bindValue(":date", name[1])
    query.exec_()
    lastError = query.lastError()
    if lastError.isValid():
        print(lastError.text())
form = Window()
form.resize(300, 400)
form.show()
app.exec_()




--
Sent from: http://python.6.x6.nabble.com/PyQt-f1792048.html
_______________________________________________
PyQt mailing list    [hidden email]
https://www.riverbankcomputing.com/mailman/listinfo/pyqt