Simple CRUD Table Using SQLite3 and PyQt5 in Python

This post is about creating a simple GUI, which can do some procedure of SQLite database.It can show the table, edit, and delete data, a simple part of CRUD.

The program uses a database file which contain a table already. The GUI will do to show, edit, and delete data from the table. SQLite database is saved as database.db and must be inside the same category as the program main script, main.py.

The main sciprt main.py and database.db can be downloaded from my GITHUB repository.

https://github.com/WahyuNoorIchwan/simple-stock-table-sqlite3-pyqt5.

The program only focus on show, edit, and delete data. So, it does not have other features like data type verification. So it is possible that the program will show some errors when you run it. So, feel free to modify it further. 

Let's elaboreate the script on the following paragraphs.

 

Main Window Class Initiation

Creation of PyQt5 GUI is always be started by main window class initiation. The script will only use main window class. All of processes will be divided into class methods.

1  # Class - Main Window
2  class mainWindow(QtWidgets.QMainWindow):
3      def __init__(self):
4          super(mainWindow, self).__init__()
5          
6          # Setup Main Window
7          self.setWindowTitle("Simple Database")
8          self.setFixedSize(600, 800)
9          
10         # Main Window Frame  Layout
11         self.mainFrame = QtWidgets.QFrame()
12         self.mainLayout = QtWidgets.QVBoxLayout()
13  
14         # Create Window Layout - Buttons and Table
15         self.createButtons()
16         self.createTable()
17         
18         # Fill Table when app is starting
19         self.fillTable()
20         
21         # Setup Main Frame to Main Window
22         self.mainFrame.setLayout(self.mainLayout)
23         self.setCentralWidget(self.mainFrame)

• # Setup Main Window: Set attributes of QMainWindow, such as window title and window size
  
• # Main Window Frame & Layout: The widgets are placed inside a frame (QFrame) and then the frame is set as Central widget of Main window.
• There are 2 group of widgets, table and buttons. So, QVBoxLayout is used since they are vertically arranged.
• Table and buttons are created separately. Both by self.createTable() and self.createButtons() method. Widget is placed into layout inside those methods.
• Table is stored in self.table, and still empty after created. Use self.fillTable() method to fill the table using existed data inside SQLite table.
  
• After all widgets are set, set Frame layout and self.mainFrame as centralWidget of the main window.
  

 

Method: createButtons

This method handle buttons creation. There are three button placed horizontally. So, we need to create a widget, place buttons with horizontal layout, and then add those widget into main layout.

 

24     # Method - Create Buttons
25     def createButtons(self):
26         # Layout is consisted of buttons and table
27         
28         # Buttons are grouped horizontaly
29         group = QtWidgets.QWidget()
30         group_ly = QtWidgets.QHBoxLayout()
31         group_ly.setContentsMargins(0, 0, 0, 0)
32       
33         add_button = QtWidgets.QPushButton("Add Data")
34         add_button.clicked.connect(self.addDataDialog)
35         group_ly.addWidget(add_button)
36         
37         del_button = QtWidgets.QPushButton("Delete Data")
38         del_button.clicked.connect(self.deleteData)
39         group_ly.addWidget(del_button)
40         
41         # Setup buttons group & add to main layout
42         group.setLayout(group_ly)
43         self.mainLayout.addWidget(group)

• First, create a widget using QtWidgets to contain three buttons. Use QHBoxLayout to place them horizontally.
• Widget is inside group variable and layout in group_ly.
  
• add_button is button to handle add data process. It is connected to self.addData method when clicked.
  
• del_button is button to handle data deletion process. It is connected to self.delData method when clicked.
• Add each of them into group_ly. Setup group_ly into group. Add add group into self.mainLayout
  

 

Method: createTable

The next method is createTable to create an empty table. Table creation and fill process are separated, so self.fillTable method can be used again to refresh table contents after an action.

 

45     # Method - Create Table
46     def createTable(self):
47         # Create Table Widget - Setup Number of Row, Columns, Headers
48         self.table = QtWidgets.QTableWidget()
49         
50         self.table.setColumnCount(4)
51         self.table.setHorizontalHeaderLabels(["", "Name", "Phone", "Address"])
52                 
53         # Add Table to Main Layout
54         self.mainLayout.addWidget(self.table)

• Create table by using QtTableWidget. Store in self.table (as an attribute), so it can accessed by another method, such as self.fillTable.
• Set row and column count unless the data will not appear. self.table is empty at first, so rowCount will be set later in self.fillTable method.
• Use .setColumnCount() to set column number, the table always has 4 columns. Then .setHorizontalHeaderLabels using labels inside a list.
  
• self.table is ready and add it into self.mainLayout.
  

 

Method: fillTable

Method to fill self.table using data from SQLite. This method also will be utilized to refresh table contents when an action is done.

There are two process in this method, querying data from SQlite file database.db. Then fill the data into self.table.

 

55     # Method Fill - Table
56     def fillTable(self):
57         # First Get Data from Database then Insert to Table Widget
58         
59         # Get Data from SQLite Table
60         conn = sqlite3.connect("database.db")
61         cursor = conn.cursor()
62         
63         data = cursor.execute("""SELECT * FROM profile""").fetchall()
64         conn.close()
65         
66         # Adjust row number to data number
67         self.table.setRowCount(len(data))
68         
69         # Fill Table Cells using data from Database
70         for i in range(len(data)):
71             # Add Check Box
72             self.table.setCellWidget(i, 0, QtWidgets.QCheckBox())
73             
74             for j in range(3):
75                 self.table.setItem(i, j+1, 
76                                    QtWidgets.QTableWidgetItem(str(data[i][j])))
                
77         # Setup colum width to fit contents
78         self.table.resizeColumnsToContents()

• Create connection and cursor of sqlite3, inside variable conn and cursor. sqlite3 query processes are conducted using cursor.
• Method .fetchall() will get all row in sqlite3 table into a list. Close conn to avoid error of "database is locked"
• Set self.table.setRowCount, based on data length. This method will always reset row number, adapt to number of data.
• Create for loop i, in range of data length. Add checkbox using QCheckBox at first column of each rows. This checkbox will be used to select data that will deleted.
  
• Fill next rows by creating another for loop, j in range 3, because there are 3 other columns to be filled.
  
• self.table cells are filled with QTableWidgetItem, using method .setItem(row, col, QTreeWidgetItem()). Row is i-th row and col is j-th column.
• QTreeWidgetItem only accept string input, so don't forget to convert each data as string.
• Lastly, use method .resizeColumnsToContents() to adjust contents width to contents width.
  

 

Method addDataDialog

Move to adding data into SQLite table. Get data using a dialog, with forms inside. There are 3 forms, name, phone number, and address.

79     # Method - Add Data
80     def addDataDialog(self):
81         self.addDialog = QtWidgets.QDialog()
82         self.addDialog.setWindowTitle("Add Data")
83         dialog_ly = QtWidgets.QGridLayout()
84         
85         dialog_ly.addWidget(QtWidgets.QLabel("Name"), 0, 0)
86         self.name_form = QtWidgets.QLineEdit()
87         dialog_ly.addWidget(self.name_form, 0, 1)
88         
89         dialog_ly.addWidget(QtWidgets.QLabel("Phone"), 1, 0)
90         self.phone_form = QtWidgets.QSpinBox()
91         self.phone_form.setMinimum(0)
92         self.phone_form.setMaximum(999999999)
93         dialog_ly.addWidget(self.phone_form, 1, 1)
94         
95         dialog_ly.addWidget(QtWidgets.QLabel("Address"), 2, 0)
96         self.address_form = QtWidgets.QLineEdit()
97         dialog_ly.addWidget(self.address_form, 2, 1)
98         
99         # Add Button
100        addButton = QtWidgets.QPushButton("Add Data")
101        addButton.clicked.connect(self.addData)
102        dialog_ly.addWidget(addButton, 3, 1)
103        
104        # Setup Dialog
105        self.addDialog.setLayout(dialog_ly)
106        self.addDialog.exec_()

• Create a dialog and it's layout in self.addDialog and dialog_ly. Labels and forms are arranged like a grid, it is created by using QGridLayout. self.addDialog is an attribute, it can accessed by self.addData() to close it later.
  
• Adding widget into QGridLayout, using method .addWidget(row, col, widget). Row and col is index for widget position inside the grid.
  
• First row is  self.name_form and it's label. First column is label using QLabel, and the second  is self.label, created using QLineEdit.
  
• second row is self.phone_form and it's label, same as before. self.phone_form is for phone number, so it should only accept integer input.
• Use QSpinBox, since it only accept integer number. Method .setMinimum(0) and .setMaximum(999999999) allow self.phone_form to accept up to 9 digit integer number.
• Third column contain self.address_form. Using QLineEdit, same as first row.
• Set dialog_ly into addDialog, use .exec_() method to show self.addDialog.
  

 

Method addData

 This method is used to add data from self.addDialog into SQLite table. First, get data from the forms. Format it, and add into SQLite table.

 

107     # Method - Add Data
108     def addData(self):
109         # Get Data to Tuple for SQLite Insert
110         batch = (self.name_form.text(), self.phone_form.value(),
111                  self.address_form.text())
112         
113         # Insert Data to SQL
114         conn = sqlite3.connect("database.db")
115         cursor = conn.cursor()
116         
117         cursor.execute("""INSERT INTO profile VALUES (?, ?, ?)""",
118                        batch)
119         conn.commit()
120         conn.close()
121         
122         # Update Table 
123         self.fillTable()
124         
125         # Close Forms Dialog
126         self.addDialog.close()

• SQLite INSERT accept data in form of tuple. Get data from self.name_form, self.phone_form, and self.address_form, arrange inside batch tuple.
• Create conn and cursor, same as in self.fillTable() method. Creating conn and cursor for each action should minimize "database is locked" error.
• Execute INSERT query. Number of (?) depend on column number in sqlite table. Make sure, number of data inside batch also same as them.
• conn.commit() to execute the query / sqlite transaction. Commit is only needed by SQLite query that alter database contents.
• Close conn as usual. The data should be already inside SQLite table.
  
• Call self.fillTable() to update self.table contents.
• Close self.addDialog.
  

 

Method deleteData

The last method, to delete data from SQLite table, and update self.table. Checked data will be deleted.

127     # Method - Delete Data
128     def deleteData(self):
129         # Delete data based on check box
130        
131         # Connection and Cursor to delete SQL data and fill table
132         conn = sqlite3.connect("database.db")
133         cursor = conn.cursor()
134         
135         # First get name of checked (want to delete)
136         # name will be used to delete record on database
137         deletedName = []
138         for i in range(self.table.rowCount()):
139             # Add Name to deleted if checked
140             if self.table.cellWidget(i, 0).isChecked():
150                 deletedName.append(self.table.item(i, 1).text())
160                 
161         # Delete Data From SQL
162         for name in deletedName:
163             cursor.execute("""DELETE FROM profile WHERE name='{}'""".format(
164                 name))
165         
166         # Commit Change
167         conn.commit()
168         
169         # Refresh table contents
170         self.fillTable()

• Create conn and cursor, as always.
• Create deletedName to contain which rows that will be deleted.
  
• Loop through self.table row, using for i in range number of row. Use self.table.rowCount() to get number of row.
• Check first row of self.table, using method .isChecked(). If checked, get "name" of each row and append to deltedName.
• "name" value is used because it is primary key of "profile" table. Table primary key usually is used to delete a row.
•  Loop again through deleteName, to delete all of selected rows. Execute "DELETE" query.
• "DELETE" query use "WHERE" clause to delete certain rows. 
• The clause use "name={}" where "{}" is filled with name in deletedName. This is why primary key column is used. It has unique value, so only one row will be deleted.
• For example, if there are 3 row with same "andy" value, 3 rows will be deleted.
• Commit change. Make sure .commit() is outside for loop. conn.commit() inside a loop will cause slow execution.
• Again, call self.fillTable() to refresh self.table contents after deletion. 
  

 

Berbagi :