CODEWITHSUNDEEP
python-3.xpyqt5
user3030327
user3030327

Reputation: 451

How to insert data from tableWidget into sqlite Database

I am trying to insert tableWidget data into SQLite db.

1) Total number of columns in a row are 9, If the table has less than 9 items data, for example: if table has items in date, name, location and item1, it shows an error like this:

item2 = [self.tableWidget.item(row, 4).text() for row in range(self.tableWidget.rowCount())] AttributeError: 'NoneType' object has no attribute 'text'

2) If table has 9 items, no error but data is not inserted into the db.

date = [self.tableWidget.item(row, 0).text() for row in range(self.tableWidget.rowCount())]
name = [self.tableWidget.item(row, 1).text() for row in range(self.tableWidget.rowCount())]

location = [self.tableWidget.item(row, 2).text() for row in range(self.tableWidget.rowCount())]
item1 = [self.tableWidget.item(row, 3).text() for row in range(self.tableWidget.rowCount())]
item2 = [self.tableWidget.item(row, 4).text() for row in range(self.tableWidget.rowCount())]
item3 = [self.tableWidget.item(row, 5).text() for row in range(self.tableWidget.rowCount())]
item4 = [self.tableWidget.item(row, 6).text() for row in range(self.tableWidget.rowCount())]
item5 = [self.tableWidget.item(row, 7).text() for row in range(self.tableWidget.rowCount())]
item6 = [self.tableWidget.item(row, 8).text() for row in range(self.tableWidget.rowCount())]
conn = sqlite3.connect('test.db')
conn.execute("INSERT INTO maintable(date,name,location,item1,item2,item3,item4,item5,item6) \
     VALUES ('%s', '%s','%s', '%s', '%s', '%s', '%s', '%s', '%s')" %(''.join(date),
                                                                     ''.join(name),
                                                                     ''.join(location),
                                                                     ''.join(item1),
                                                                     ''.join(item2),
                                                                     ''.join(item3),
                                                                     ''.join(item4),
                                                                     ''.join(item5),
                                                                     ''.join(item6)))

qTableWidget

Upvotes: 0

Views: 463

Answers (1)

musicamante
musicamante

Reputation: 48231

If an item is empty, item() will return None; you can check if the item exists within the comprehension:

item2 = [self.tableWidget.item(row, 4).text() for row in range(self.tableWidget.rowCount()) if self.tableWidget.item(row,4)]
# ...

But this is not very readable nor really optimized, as it will call item() twice for each row.
I'd suggest to use a function that returns the list based on the column:

def getColumn(self, column):
    data = []
    for row in range(self.tableWidget.rowCount()):
        item = self.tableWidget.item(row, column)
        if item:
            data.append(item.text())
    return ''.join(data)

def writeToDatabase(self):
    conn = sqlite3.connect('test.db')
    conn.execute("INSERT INTO \
        maintable(date,name,location,item1,item2,item3,item4,item5,item6) \
        VALUES ('{}', '{}','{}', '{}', '{}', '{}', '{}', '{}', '{}') \
        ".format(*[self.getColumn(c) for c in range(9)]))
    # if you don't call commit(), the data is not stored
    conn.commit()

Upvotes: 1

Related Questions