I'm trying to export data from SQlite3 database to .csv, using pyqt5, QSqlTableModel and csv. I can export all the data from my database, I'm trying to export only the selected rows from QSqlTableModel, I really liked to mess around QSqlTableModel class, the write/read interaction is really amazing.
I got to this stage by following a couple of examples from others users.
def on_Click(self):
with open("cadastro.csv", 'w', newline='') as csvfile:
csvwriter = csv.writer(csvfile, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
if self.tableView.selectionModel().hasSelection():
names = []
rows = self.tableView.selectionModel().selectedIndexes()
for i in rows:
name = self.tableView.model().index(i.row(), i.column()).data()
names.append(name)
print(name)
csvwriter.writerow(
['id', 'instituicao', 'crianca', 'data_de_nascimento', 'manequim', 'calcado', 'responsavel'])
csvwriter.writerow(names)
# csvwriter.writerows(map(lambda x: [x], names))
# print(i.row())
else:
print('No row selected!')
So I'm trying to select rows and save that selection on a csv file like that.
The result is:
and I need something like that :
Also here is the rest of the code.
class MainWindow(QWidget):
def __init__(self, parent=None):
super(MainWindow, self).__init__(parent)
self.db = QSqlDatabase.addDatabase("QSQLITE")
self.db.setDatabaseName("bolsa_natal.db")
self.db.open()
self.model = QSqlTableModel()
self.initializedModel()
self.tableView = QTableView()
# turn off row numbers
self.tableView.verticalHeader().setVisible(False)
# turn off horizontal headers
# self.tableView.horizontalHeader().setVisible(False)
self.tableView.resizeColumnsToContents()
self.tableView.resizeRowsToContents()
self.tableView.horizontalHeader().setDefaultAlignment(Qt.AlignLeft)
self.source_model = self.model
# self.initializedModel()
self.proxy_model = QSortFilterProxyModel(self.source_model)
self.searchcommands = QLineEdit("")
self.searchcommands.setObjectName(u"searchcommands")
self.searchcommands.setAlignment(Qt.AlignLeft)
self.buttonCadastro = QPushButton('to .csv', self)
self.buttonCadastro.clicked.connect(self.exportCadastro)
self.proxy_model.setSourceModel(self.source_model)
self.tableView.setModel(self.proxy_model)
# hide columns from the maintableview
# self.tableView.hideColumn(0)
# self.tableView.hideColumn(3)
# self.tableView.hideColumn(4)
# self.tableView.hideColumn(5)
# self.tableView.hideColumn(6)
# self.tableView.hideColumn(7)
self.proxy_model.setFilterRegExp(QRegExp(self.searchcommands.text(), Qt.CaseInsensitive,
QRegExp.FixedString))
# search all columns
self.proxy_model.setFilterKeyColumn(-1)
# enable sorting by columns
self.tableView.setSortingEnabled(True)
# set editing disabled for my use
# self.tableView.setEditTriggers(QAbstractItemView.NoEditTriggers)
self.tableView.setWordWrap(True)
self.layout = QVBoxLayout()
self.command_description = QLabel()
self.command_description.setWordWrap(True)
# self.command_requires_label = QLabel("Command Requires:")
# self.command_requires_label.setWordWrap(True)
hLayout = QHBoxLayout()
# hLayout.addWidget(self.command_requires_label)
self.layout.addWidget(self.tableView)
self.layout.addWidget(self.searchcommands)
self.layout.addWidget(self.buttonCadastro)
self.layout.addWidget(self.command_description)
self.layout.addLayout(hLayout)
self.setLayout(self.layout)
self.resize(730, 600)
self.searchcommands.textChanged.connect(self.searchcommands.update)
self.searchcommands.textChanged.connect(self.proxy_model.setFilterRegExp)
self.searchcommands.setText("")
self.searchcommands.setPlaceholderText(u"buscar")
# self.tableView.clicked.connect(self.listclicked)
# self.startDate.dateChanged.connect(self.startDate.update)
# self.startDate.dateChanged.connect(self.FilterBetweenDates)
# self.endDate.dateChanged.connect(self.endDate.update)
# self.endDate.dateChanged.connect(self.FilterBetweenDates)
# Save Button
def exportCadastro(self):
with open('cadastro.csv', 'w') as stream: # 'w'
writer = csv.writer(stream, lineterminator='\n')
for rowNumber in range(self.model.rowCount()):
fields = [
self.model.data(
# self.tableView.selectionModel().selectedRows()
self.model.index(rowNumber, columnNumber),
Qt.DisplayRole
)
for columnNumber in range(self.model.columnCount())
]
print(fields)
writer.writerow(fields)
def initializedModel(self):
self.model.setTable("cadastro")
self.model.setEditStrategy(QSqlTableModel.OnFieldChange)
# self.model.setEditStrategy(QSqlTableModel.OnManualSubmit)
self.model.select()
self.model.setHeaderData(0, Qt.Horizontal, "ID")
self.model.setHeaderData(1, Qt.Horizontal, "instituicao")
self.model.setHeaderData(2, Qt.Horizontal, "crianca")
self.model.setHeaderData(3, Qt.Horizontal, "data_de_nascimento")
self.model.setHeaderData(4, Qt.Horizontal, "manequim")
self.model.setHeaderData(5, Qt.Horizontal, "calcado")
self.model.setHeaderData(6, Qt.Horizontal, "responsavel")
def onAddRow(self):
self.model.insertRows(self.model.rowCount(), 1)
self.model.submit()
def onDeleteRow(self):
self.model.removeRow(self.tableView.currentIndex().row())
self.model.submit()
self.model.select()
def closeEvent(self, event):
self.db.close()
# def listclicked(self, index):
# row = index.row()
# cmd = self.proxy_model.data(self.proxy_model.index(row, 3))
# cmd_requires = self.proxy_model.data(self.proxy_model.index(row, 4))
# cmd_description = self.proxy_model.data(self.proxy_model.index(row, 5))
# print(cmd_description)
# self.command_description.setText(cmd_description)
# self.command_requires_label.setText('This command requires being executed via: ' + cmd_requires.upper())
if __name__ == "__main__":
app = QApplication(sys.argv)
window = MainWindow()
window.show()
sys.exit(app.exec_())
