Shulou(Shulou.com)06/02 Report--

How to operate PyQt5 database, in view of this problem, this article introduces the corresponding analysis and answer in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

PyQt5 database operation 1, SQLite database 1, SQLite introduction

SQLite is a lightweight database, which implements a self-sufficient, serverless, zero-configuration, transactional SQL database engine, mainly as a database for mobile applications and a database for small desktop applications.

Official website:

Https://www.sqlite.org

2. Common operations of SQLite

Create the database file and enter the SQLite command line mode after creation.

Sqlite3 DatabaseName.db

View the existing database file and execute it in SQLite command line mode:

.databases

Open a database file that already exists, and create it if it does not exist.

Sqlite3 DatabaseName.db

View help information and execute it in SQLite command line mode:

.help

Create a table and execute it in SQLite command line mode:

Create table person (id integer primary key, name text)

Insert data into the table:

Insert into person (id, name) values (1, "zhangsan")

Query operation:

Select * from person

The structure of the query table:

.schema person

3. SQLite management tools

SQLite has a number of open source and excellent DBMS (database management system), providing an interface to operate SQLite databases.

SQLiteStudio is a very professional SQLite database management software, small size, powerful, support Chinese, installation-free.

SQLiteStudio download:

Https://sqlitestudio.pl/index.rvt?act=download

Second, connect to database 1, database driver type

In PyQt, the QSqlDatabase class is used to connect to the database, and you can use the database driver to interact with different databases, and one QSqlDatabase instance represents a database connection. The available database driver types are as follows:

QDB2 IBM DB2 driver

QMYSQL MySQL driver

QOCI Oracle calls interface driver

QODBC ODBC drivers (including MS SQL Server)

QPSQL PostgreSQL driver

QSQLITE SQLite3 driver

QSQLITE2 SQLite2 driver

2. Common methods of QSqlDatabase

The common methods of QSqlDatabase are as follows:

AddDataBase: sets the database driver type for connecting to the database

SetDatabaseName: sets the name of the database to which you connect

SetHostName: set the name of the host where the database is located

SetUserName: specifies the user name of the connection

SetPassword: sets the password of the connection object

Commit: commits the transaction and returns True if the execution is successful.

Rollback: rollback database transactions

Close: closing database connection

3. Database connection instance import sysfrom PyQt5.QtSql import QSqlDatabasefrom PyQt5.QtCore import * if _ _ name__ = "_ _ main__": app = QCoreApplication (sys.argv) db = QSqlDatabase.addDatabase ("QSQLITE") db.setDatabaseName ("/ home/user/test.db") if db.open (): print ("open DB success.") Sys.exit (app.exec_ ()) III. Execute the SQL statement

QSqlQuery has the ability to execute and manipulate SQL statements, can execute SQL queries of DDL and DML types, and QSqlQuery.exec_ () is used to perform SQL operations.

Import sysfrom PyQt5.QtSql import QSqlDatabase, QSqlQueryfrom PyQt5.QtCore import * def createDB (): db = QSqlDatabase.addDatabase ("QSQLITE") db.setDatabaseName ("/ home/user/test.db") if db.open (): query = QSqlQuery () query.exec_ ("create table person (id int primary key, name varchar (20), address varchar (30)") query.exec_ ("insert into person values (1, 'Bauer')" 'beijing')) query.exec_ ("insert into person values (2,' Jack', 'shanghai')") query.exec_ ("insert into person values (3,' Alex', 'chengdu')") db.close () if _ _ name__ = "_ _ main__": app = QCoreApplication (sys.argv) createDB () sys.exit (app.exec_ ())

After executing the SQL statement, if there are no other database operations, you need to use db.close to close the database connection, because the database connection resources are limited, and the database connections that are no longer in use must be closed, otherwise the database connection resources will eventually be exhausted and the program cannot connect to the database normally.

If you need to read and display the data of the database in the window of PyQt, you need to open the database when the window is initialized and close the database connection when the window is closed.

Import sysfrom PyQt5.QtSql import QSqlDatabase, QSqlQueryfrom PyQt5.QtCore import * from PyQt5.QtWidgets import * class MainWindow (QWidget): def _ _ init__ (self, parent=None): super (MainWindow, self). _ init__ (parent) self.db = QSqlDatabase.addDatabase ("QSQLITE") self.db.setDatabaseName ("/ home/user/test.db") self.db.open () def closeEvent (self Event): self.db.close () if _ _ name__ = = "_ _ main__": app = QApplication (sys.argv) window = MainWindow () window.show () sys.exit (app.exec_ ()) 4. Database model view

QSqlTableModel in Qt is an advanced interface that provides a readable and writable data model for reading and saving data in a single table, and you can display database tables in QTableView. When connected to the database, use seTable to set the table to query, use the setFilter function to set filter conditions, and then use the select function to query. You can use the setEditerStrategy function to set the editing policy, which can be set as follows:

QSqlTableModel.OnFieldChange: all changes are updated to the database in real time

QSqlTableModel.OnRowChange: update the current row when the user selects a different row

QSqlTableModel.OnManuallSubmit: manual submission, not automatic submission

Import sysfrom PyQt5.QtSql import QSqlDatabase, QSqlQuery, QSqlTableModelfrom PyQt5.QtCore import * from PyQt5.QtWidgets import * class MainWindow (QWidget): def _ _ init__ (self, parent=None): super (MainWindow Self). _ init__ (parent) self.db = QSqlDatabase.addDatabase ("QSQLITE") self.db.setDatabaseName ("/ home/user/test.db") self.db.open () self.model = QSqlTableModel () self.initializedModel () self.tableView = QTableView () self.tableView.setModel (self.model) self.layout = QVBoxLayout () addButton = QPushButton ("add") deleteButton = QPushButton ("delete") hLayout = QHBoxLayout () hLayout.addWidget (addButton) hLayout.addWidget (deleteButton) self.layout.addWidget (self.tableView) self.layout.addLayout (hLayout) self.setLayout (self.layout) self.resize AddButton.clicked.connect (self.onAddRow) deleteButton.clicked.connect (self.onDeleteRow) def initializedModel (self): self.model.setTable ("person") self.model.setEditStrategy (QSqlTableModel.OnFieldChange) self.model.select () self.model.setHeaderData (0, Qt.Horizontal, "ID") self.model.setHeaderData (1, Qt.Horizontal, "Name") self.model.setHeaderData (2 Qt.Horizontal, "Address") 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 () if _ _ name__ = = "_ _ main__": app = QApplication (sys.argv) window = MainWindow () window.show () sys.exit (app.exec_ ()) V, paging query 1, data preparation

Paging uses the data for the student information student table, which can be inserted using the SQLite command line using SQL statements, or you can use the Python program to create the table and insert the data.

Db = QSqlDatabase.addDatabase ("QSQLITE") db.setDatabaseName ("/ home/user/test.db") if not db.open (): return Falsequery = QSqlQuery () query.exec_ ("create table student (id int primary key, name varchar (20), sex varchar (8), age int)) Query.exec_ ("insert into student values (1, 'Bauer',' Man', 25)") query.exec_ ("insert into student values (2, 'Alex',' Man', 24)") query.exec_ ("insert into student values (3, 'Mary',' Female', 23)") query.exec_ ("insert into student values (4, 'Jack',' Man', 25)") query.exec_ ("insert into student values (5, 'xiaoming')" 'Man', 24)) query.exec_ ("insert into student values (6,' xiaohong', 'Female', 23)") query.exec_ ("insert into student values (7,' xiaowang', 'Man', 25)") query.exec_ ("insert into student values (8,' xiaozhang', 'Man', 25)") query.exec_ ("insert into student values (9,' xiaoli', 'Man', 25)") query.exec_ ("insert into student values (10)" 'xiaohan',' Man', 25) ") 2. Paging window

The paging window includes tabs, previous pages, back pages, jump buttons, etc.

Import sysfrom PyQt5.QtSql import QSqlDatabase, QSqlQuery, QSqlTableModel, QSqlQueryModelfrom PyQt5.QtCore import * from PyQt5.QtWidgets import * class DataGrid (QWidget): def _ _ init__ (self, parent=None): super (DataGrid Self). _ init__ (parent) # Database connection self.db = None # layout Manager self.layout = QVBoxLayout () # query Model self.queryModel = QSqlQueryModel () # Table View self.tableView = QTableView () self.tableView.setModel (self.queryModel) # self. TotalPageLabel = QLabel () self.currentPageLabel = QLabel () self.switchPageLineEdit = QLineEdit () self.prevButton = QPushButton ("Prev") self.nextButton = QPushButton ("Next") self.switchPageButton = QPushButton ("Switch") self.currentPage = 0 self.totalPage = 0 self.totalRecordCount = 0 self.pageRecordCount = 5 def initUI (self): self.tableView.horizontalHeader () .setStretchLastSection (True) self.tableView.horizontalHeader (). SetSectionResizeMode (QHeaderView.Stretch) self.layout.addWidget (self.tableView) hLayout = QHBoxLayout () hLayout.addWidget (self.prevButton) hLayout.addWidget (self.nextButton) hLayout.addWidget (QLabel ("jump to") self.switchPageLineEdit.setFixedWidth (40) hLayout.addWidget (self.switchPageLineEdit) hLayout.addWidget (QLabel) ("Page")) hLayout.addWidget (self.switchPageButton) hLayout.addWidget (QLabel ("current Page:")) hLayout.addWidget (self.currentPageLabel) hLayout.addWidget (QLabel ("Total pages:") hLayout.addWidget (self.totalPageLabel) hLayout.addStretch (1) self.layout.addLayout (hLayout) self.setLayout (self.layout) self.setWindowTitle ( "DataGrid") self.resize Def closeEvent (self, event): self.db.close () if _ _ name__ = = "_ _ main__": app = QApplication (sys.argv) window = DataGrid () window.initUI () window.show () sys.exit (app.exec_ ())

3. Implementation of paging query

Read the student table of the database and initialize the table data model.

Import sysfrom PyQt5.QtSql import QSqlDatabase, QSqlQuery, QSqlTableModel, QSqlQueryModelfrom PyQt5.QtCore import * from PyQt5.QtWidgets import * import reclass DataGrid (QWidget): def _ _ init__ (self, parent=None): super (DataGrid Self). _ init__ (parent) # declare database connection self.db = None # layout manager self.layout = QVBoxLayout () # query model self.queryModel = QSqlQueryModel () # Table view self.tableView = QTableView () self.tableView.setModel (self.queryModel) # Self.totalPageLabel = QLabel () self.currentPageLabel = QLabel () self.switchPageLineEdit = QLineEdit () self.prevButton = QPushButton ("Prev") self.nextButton = QPushButton ("Next") self.switchPageButton = QPushButton ("Switch") # current page self.currentPage = 1 # Total pages self.totalPage = None # Total Records Self.totalRecordCount = None # Records per page self.pageRecordCount = 4 self.initUI () self.initializedModel () self.setUpConnect () self.updateStatus () def initUI (self): self.tableView.horizontalHeader () .setStretchLastSection (True) self.tableView.horizontalHeader () .setSectionResizeMode (QHeaderView.Stretch) self.layout.addWidget (self.tableView) HLayout = QHBoxLayout () hLayout.addWidget (self.prevButton) hLayout.addWidget (self.nextButton) hLayout.addWidget (QLabel ("jump to")) self.switchPageLineEdit.setFixedWidth (40) hLayout.addWidget (self.switchPageLineEdit) hLayout.addWidget (QLabel ("page")) hLayout.addWidget (self.switchPageButton) hLayout.addWidget (QLabel ("current page:")) HLayout.addWidget (self.currentPageLabel) hLayout.addWidget (QLabel ("Total pages:") hLayout.addWidget (self.totalPageLabel) hLayout.addStretch (1) self.layout.addLayout (hLayout) self.setLayout (self.layout) self.setWindowTitle ("DataGrid") self.resize Def setUpConnect (self): self.prevButton.clicked.connect (self.onPrevPage) self.nextButton.clicked.connect (self.onNextPage) self.switchPageButton.clicked.connect (self.onSwitchPage) def initializedModel (self): self.db = QSqlDatabase.addDatabase ("QSQLITE") self.db.setDatabaseName ("/ home/user/test.db") if not self.db.open (): Return False self.queryModel.setHeaderData (0 Qt.Horizontal, "ID") self.queryModel.setHeaderData (1, Qt.Horizontal, "Name") self.queryModel.setHeaderData (2, Qt.Horizontal, "Sex") self.queryModel.setHeaderData (3, Qt.Horizontal, "Age") # get the number of records in the table sql = "SELECT * FROM student" self.queryModel.setQuery (sql Self.db) self.totalRecordCount = self.queryModel.rowCount () if self.totalRecordCount% self.pageRecordCount = 0: self.totalPage = self.totalRecordCount / self.pageRecordCount else: self.totalPage = int (self.totalRecordCount / self.pageRecordCount) + 1 # shows page 1 sql = "SELECT * FROM student limit% d"% (0, self.pageRecordCount) self.queryModel.setQuery (sql) Self.db) def onPrevPage (self): self.currentPage-= 1 limitIndex = (self.currentPage-1) * self.pageRecordCount self.queryRecord (limitIndex) self.updateStatus () def onNextPage (self): self.currentPage + = 1 limitIndex = (self.currentPage-1) * self.pageRecordCount self.queryRecord (limitIndex) self.updateStatus () def onSwitchPage (self): SzText = self.switchPageLineEdit.text () pattern = re.compile ('^ [0-9] + $') match = pattern.match (szText) if not match: QMessageBox.information (self Prompt, "Please enter a number.") Return if szText = "": QMessageBox.information (self, "prompt", "Please enter the jump page.") Return pageIndex = int (szText) if pageIndex > self.totalPage or pageIndex < 1: QMessageBox.information (self, "prompt", "No specified page, clear re-enter.") Return limitIndex = (pageIndex-1) * self.pageRecordCount self.queryRecord (limitIndex) self.currentPage = pageIndex self.updateStatus () # query records based on paging def queryRecord (self, limitIndex): sql = "SELECT * FROM student limit% djue% d"% (limitIndex Self.pageRecordCount) self.queryModel.setQuery (sql) # Update space status def updateStatus (self): self.currentPageLabel.setText (str (self.currentPage)) self.totalPageLabel.setText (str (self.totalPage)) if self.currentPage = self.totalPage: self.nextButton.setEnabled (False) else: self.nextButton.setEnabled (True) # bound Close the database connection def closeEvent (self) when the face is closed Event): self.db.close () if _ _ name__ = = "_ _ main__": app = QApplication (sys.argv) window = DataGrid () window.show () sys.exit (app.exec_ ())

The answer to the question on how to operate the PyQt5 database is shared here. I hope the above content can be of some help to you. If you still have a lot of doubts to solve, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.