[PyQt] 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