Python & amp; MySQL & amp; PyQt

Python & MySQL & PyQt

Environment:
Python2.7 + MySQL5.6 + PyQt4

 

Simple graphical interface for database operations: Simulate and write an interface similar to navicat for visual retrieval, and use PyQt for visual programming to quickly complete the interface design, which is simpler than Tkinter design;

1. Use PyQt to design the interface:
Open PyQt, create, Select Main Window, drag the left-side control to complete the design of the interface, select an element, you can view the information of the element from the right side and modify its property value. The following is a design drawing:

Double-click an element on the design page to modify its attribute name, add text, and change the display character format;

2. After the design is complete, save it as a. ui file and convert it to a. py file:
Open cmd: Go to the PyQt installation directory:
Lib \ site-packages \ PyQt4 \ uic>
Enter the command format:
Python pyuic. py-o. py file directory + file name. ui file directory + file name

The. ui file I saved is placed in the root directory of the D Drive. The generated. py file is named nihao0.py.

3. Open Python run. py to view the interface
Add the following code to the original code:

Import sysclass MyWindow (QtGui. QMainWindow, Ui_MainWindow): # PyQt produces a class called Ui_MainWindow, which only needs to be drawn under a framework. def _ init _ (self): super (MyWindow, self ). _ init _ () self. setupUi (self) if _ name __= = "_ main _": app = QtGui. QApplication (sys. argv) # create an app and place the framework in the app to execute the app. aboutToQuit. connect (app. deleteLater) myshow = MyWindow () myshow. show () sys.exit(app.exec_()))##can also be written as app.exe c _ () sys. exit (0). The former loops the entire interface, and the latter exits the app.

Running result:

In this case, we get an interface we want to get. You only need to add various events to this interface;

 

4. dynamically generate SQL statements -- prepare
Create a database mysql and a table students. The table attributes are as follows:
DDL:

CREATE TABLE `students` (  `Sid` char(10) NOT NULL,  `Sname` char(10) DEFAULT NULL,  `Sage` int(3) DEFAULT NULL,  `Ssex` char(4) DEFAULT NULL,  `Sclass` char(7) DEFAULT NULL,  `Sdept` char(10) DEFAULT NULL,  `Saddr` char(40) DEFAULT NULL,  PRIMARY KEY (`Sid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Write the handle first:

sqlsting="select * from students where "

Then, write a clicked () event. When you click query, dynamically construct an SQL statement based on user input.
The following methods are used:
Check box: checked ()
Read input: text ()

 

5. dynamically generate SQL statements-string Construction
Construction skills:
Whether or not to add and: If the atomic formula already exists, add and. Otherwise, use a flag;
Quotation marks required or not: quotation marks are required for the numeric type, and numbers are not required;
The numeric type must be converted to the character type first. When the numeric type is converted to the numeric str () type, the data type () can be determined without writing, because the data returned from the interface is of the QtString type;
Determine when to use Like (string matching problem %): string method. find ("%"). The returned value is the position where the first % appears. If % does not exist,-1 is returned;

6. Interface display problems
Self. SQL _out = QtGui. QTextBrowser (self. centralwidget): How to Use the display box:
. SetText (str): clears the original data and displays the new string.
. Append (str): Add a new string to the end.
Self. result_out = QtGui. QTableWidget (self. centralwidget): Table box usage:
. SetItem (I, j, newItem): Place newItem in row I and column j. Note that ij starts from 0;
NewItem is declared in the format of newItem = QtGui. QTableWidgetItem (str), that is, str is displayed at (I, j ).
Note that QTableWidget can only display strings, but cannot display numbers. Before displaying data, you must determine whether it is a number:
Solution: type () and str ()

7. database connection problems:
Cleanup has been described in Python & MySQL for reference.

8. paste the Python code and running result:

#-*-Coding: UTF-8-*-# Form implementation generated from reading ui file 'd:/nihao. ui '## Created: Fri Apr 01 15:28:59 2016 # by: PyQt4 UI code generator 4.11.3 # WARNING! All changes made in this file will be lost! From PyQt4 import QtCore, QtGuiimport sysimport MySQLdbtry: _ fromUtf8 = QtCore. QString. fromUtf8except AttributeError: def _ fromUtf8 (s): return stry: _ encoding = QtGui. QApplication. unicodeUTF8 def _ translate (context, text, disambig): return QtGui. QApplication. translate (context, text, disambig, _ encoding) failed t AttributeError: def _ translate (context, text, disambig): return QtGui. QApplication. translate (context, text, disambig) class Ui_MainWindow (object): def setupUi (self, MainWindow): self. conn = MySQLdb. connect (host = 'localhost', user = 'root', passwd = '', db = 'mysql', # charset = 'utf8',) self. cur = self. conn. cursor () self. sqlstring = "select * from students where" MainWindow. setObjectName (_ fromUtf8 ("MainWindow") MainWindow. resize (760,440) self. centralwidget = QtGui. QWidget (MainWindow) self. centralwidget. setObjectName (_ fromUtf8 ("centralwidget") self. frame = QtGui. QFrame (self. centralwidget) self. frame. setGeometry (QtCore. QRect (10, 10,491,121) self. frame. setFrameShape (QtGui. QFrame. styledPanel) self. frame. setFrameShadow (QtGui. QFrame. raised) self. frame. setObjectName (_ fromUtf8 ("frame") self. check_Sid = QtGui. QCheckBox (self. frame) self. check_Sid.setGeometry (QtCore. QRect (20, 10, 71, 16) self. check_Sid.setObjectName (_ fromUtf8 ("check_Sid") self. check_Sage = QtGui. QCheckBox (self. frame) self. check_Sage.setGeometry (QtCore. QRect (20, 70, 71, 16) self. check_Sage.setObjectName (_ fromUtf8 ("check_Sage") self. check_Sname = QtGui. QCheckBox (self. frame) self. check_Sname.setGeometry (QtCore. QRect (20, 40, 71, 16) self. check_Sname.setObjectName (_ fromUtf8 ("check_Sname") self. check_Ssex = QtGui. QCheckBox (self. frame) self. check_Ssex.setGeometry (QtCore. QRect (20,100, 71, 16) self. check_Ssex.setObjectName (_ fromUtf8 ("check_Ssex") self. sid = QtGui. QLineEdit (self. frame) self. sid. setGeometry (QtCore. QRect (90, 10,113, 16) self. sid. setObjectName (_ fromUtf8 ("Sid") self. sname = QtGui. QLineEdit (self. frame) self. sname. setGeometry (QtCore. QRect (90, 40,113, 16) self. sname. setObjectName (_ fromUtf8 ("Sname") self. first_Sage = QtGui. QLineEdit (self. frame) self. first_Sage.setGeometry (QtCore. QRect (90, 70, 41, 16) self. first_Sage.setObjectName (_ fromUtf8 ("first_Sage") self. ssex = QtGui. QLineEdit (self. frame) self. ssex. setGeometry (QtCore. QRect (90,100,113, 16) self. ssex. setObjectName (_ fromUtf8 ("Ssex") self. label = QtGui. QLabel (self. frame) self. label. setGeometry (QtCore. QRect (140, 70, 16, 16) self. label. setObjectName (_ fromUtf8 ("label") self. last_Sage = QtGui. QLineEdit (self. frame) self. last_Sage.setGeometry (QtCore. QRect (160, 70, 41, 16) self. last_Sage.setObjectName (_ fromUtf8 ("last_Sage") self. check_Sdept = QtGui. QCheckBox (self. frame) self. check_Sdept.setGeometry (QtCore. QRect (270, 40, 71, 16) self. check_Sdept.setObjectName (_ fromUtf8 ("check_Sdept") self. sdept = QtGui. QLineEdit (self. frame) self. sdept. setGeometry (QtCore. QRect (340, 40,113, 16) self. sdept. setObjectName (_ fromUtf8 ("Sdept") self. sclass = QtGui. QLineEdit (self. frame) self. sclass. setGeometry (QtCore. QRect (340, 10,113, 16) self. sclass. setObjectName (_ fromUtf8 ("Sclass") self. check_Sclass = QtGui. QCheckBox (self. frame) self. check_Sclass.setGeometry (QtCore. QRect (270, 10, 71, 16) self. check_Sclass.setObjectName (_ fromUtf8 ("check_Sclass") self. saddr = QtGui. QLineEdit (self. frame) self. saddr. setGeometry (QtCore. QRect (340, 70,113, 16) self. saddr. setObjectName (_ fromUtf8 ("Saddr") self. check_Saddr = QtGui. QCheckBox (self. frame) self. check_Saddr.setGeometry (QtCore. QRect (270, 70, 71, 16) self. check_Saddr.setObjectName (_ fromUtf8 ("check_Saddr") self. find = QtGui. QPushButton (self. frame) self. find. setGeometry (QtCore. QRect (380,100, 75, 21) self. find. setObjectName (_ fromUtf8 ("find") self. SQL _out = QtGui. QTextBrowser (self. centralwidget) self. SQL _out.setGeometry (QtCore. QRect (10,140,740, 61) self. SQL _out.setObjectName (_ fromUtf8 ("SQL _out") self. result_out = QtGui. QTableWidget (self. centralwidget) self. result_out.setEditTriggers (QtGui. qiniactitemview. noEditTriggers) # The table self. result_out.setGeometry (QtCore. QRect (10,210,740,171) self. result_out.setObjectName (_ fromUtf8 ("result_out") self. result_out.setColumnCount (7) self. result_out.setRowCount (10) self. result_out.resizeColumnsToContents () self. result_out.resizeRowsToContents () item = QtGui. QTableWidgetItem () self. result_out.setHorizontalHeaderItem (0, item) item = QtGui. QTableWidgetItem () self. result_out.setHorizontalHeaderItem (1, item) item = QtGui. QTableWidgetItem () self. result_out.setHorizontalHeaderItem (2, item) item = QtGui. QTableWidgetItem () self. result_out.setHorizontalHeaderItem (3, item) item = QtGui. QTableWidgetItem () self. result_out.setHorizontalHeaderItem (4, item) item = QtGui. QTableWidgetItem () self. result_out.setHorizontalHeaderItem (5, item) item = QtGui. QTableWidgetItem () self. result_out.setHorizontalHeaderItem (6, item) self. result_out.horizontalHeader (). setdefasesectionsize (100) self. result_out.horizontalHeader (). setMinimumSectionSize (25) self. result_out.verticalHeader (). setdefasesectionsize (30) self. pushButton_2 = QtGui. QPushButton (self. centralwidget) self. pushButton_2.setGeometry (QtCore. QRect (675,390, 75, 21) self. pushButton_2.setObjectName (_ fromUtf8 ("pushButton_2") MainWindow. setCentralWidget (self. centralwidget) self. menubar = QtGui. QMenuBar (MainWindow) self. menubar. setGeometry (QtCore. QRect (0, 0,509, 23) self. menubar. setObjectName (_ fromUtf8 ("menubar") MainWindow. setMenuBar (self. menubar) self. statusbar = QtGui. QStatusBar (MainWindow) self. statusbar. setObjectName (_ fromUtf8 ("statusbar") MainWindow. setStatusBar (self. statusbar) self. retranslateUi (MainWindow) QtCore. QMetaObject. connectSlotsByName (MainWindow) def retranslateUi (self, MainWindow): MainWindow. setWindowTitle (_ translate ("MainWindow", "MainWindow", None) self. check_Sid.setText (_ translate ("MainWindow", "student ID", None) self. check_Sage.setText (_ translate ("MainWindow", "age from", None) self. check_Sname.setText (_ translate ("MainWindow", "name", None) self. check_Ssex.setText (_ translate ("MainWindow", "gender", None) self. label. setText (_ translate ("MainWindow", "to", None) self. check_Sdept.setText (_ translate ("MainWindow", "system", None) self. check_Sclass.setText (_ translate ("MainWindow", "class", None) self. check_Saddr.setText (_ translate ("MainWindow", "Address", None) self. find. setText (_ translate ("MainWindow", "query", None) self. SQL _out.setText (self. sqlstring) item = self. result_out.horizontalHeaderItem (0) item. setText (_ translate ("MainWindow", "Sid", None) item = self. result_out.horizontalHeaderItem (1) item. setText (_ translate ("MainWindow", "Sname", None) item = self. result_out.horizontalHeaderItem (2) item. setText (_ translate ("MainWindow", "Sage", None) item = self. result_out.horizontalHeaderItem (3) item. setText (_ translate ("MainWindow", "Ssex", None) item = self. result_out.horizontalHeaderItem (4) item. setText (_ translate ("MainWindow", "Sclass", None) item = self. result_out.horizontalHeaderItem (5) item. setText (_ translate ("MainWindow", "Sdept", None) item = self. result_out.horizontalHeaderItem (6) item. setText (_ translate ("MainWindow", "Saddr", None) self. pushButton_2.setText (_ translate ("MainWindow", "exit", None) def mousePressEvent (self, event): if event. self. find () = QtCore. qt. leftButton: print "nihao" def buttonTest (self): temp_sqlstring = self. sqlstring is_first = True if self. check_Sid.isChecked (): mystr = self. sid. text () if is_first: is_first = False if mystr. find ("%") =-1: temp_sqlstring + = "Sid = '" + self. sid. text () + "'" else: temp_sqlstring + = "Sid like'" + self. sid. text () + "'" else: if mystr. find ("%") =-1: temp_sqlstring + = "and Sid = '" + self. sid. text () + "'" else: temp_sqlstring + = "and Sid like'" + self. sid. text () + "'" if self. check_Sname.isChecked (): if is_first: mystr = self. sname. text () is_first = False if mystr. find ("%") =-1: temp_sqlstring + = "Sname = '" + self. sname. text () + "'" else: temp_sqlstring + = "Sname like'" + self. sname. text () + "'" else: if mystr. find ("%") =-1: temp_sqlstring + = "and Sname = '" + self. sname. text () + "'" else: temp_sqlstring + = "and Sname like'" + self. sname. text () + "'" if self. check_Sage.isChecked (): if is_first: is_first = False temp_sqlstring + = "Sage> =" + self. first_Sage.text () + \ "and Sage <=" + self. last_Sage.text () else: temp_sqlstring + = "and Sage> =" + self. first_Sage.text () + \ "and Sage <=" + self. last_Sage.text () if self. check_Ssex.isChecked (): if is_first: is_first = False temp_sqlstring + = "Ssex = '" + self. ssex. text () + "'" else: temp_sqlstring + = "and Ssex ='" + self. ssex. text () + "'" if self. check_Sclass.isChecked (): if is_first: mystr = self. sclass. text () is_first = False if mystr. find ("%") =-1: temp_sqlstring + = "Sclass = '" + self. sclass. text () + "'" else: temp_sqlstring + = "Sclass like'" + self. sclass. text () + "'" else: if mystr. find ("%") =-1: temp_sqlstring + = "and Sclass = '" + self. sclass. text () + "'" else: temp_sqlstring + = "and Sclass like'" + self. sclass. text () + "'" if self. check_Sdept.isChecked (): if is_first: mystr = self. sdept. text () is_first = False if mystr. find ("%") =-1: temp_sqlstring + = "Sdept = '" + self. sdept. text () + "'" else: temp_sqlstring + = "Sdept like'" + self. sdept. text () + "'" else: if mystr. find ("%") =-1: temp_sqlstring + = "and Sdept = '" + self. sdept. text () + "'" else: temp_sqlstring + = "and Sdept like'" + self. sdept. text () + "'" if self. check_Saddr.isChecked (): if is_first: mystr = self. saddr. text () is_first = False if mystr. find ("%") =-1: temp_sqlstring + = "Saddr = '" + self. saddr. text () + "'" else: temp_sqlstring + = "and Saddr like'" + self. saddr. text () + "'" else: if mystr. find ("%") =-1: temp_sqlstring + = "and Saddr = '" + self. saddr. text () + "'" else: temp_sqlstring + = "and Saddr like'" + self. saddr. text () + "'" self. result_out.clearContents () # Clear The information in the table if not (is_first): self.cur.exe cute (temp_sqlstring) k = 0 for I in self. cur: w = 0 for j in I: if type (j) = long: newItem = QtGui. QTableWidgetItem (str (j) else: newItem = QtGui. QTableWidgetItem (j) self. result_out.setItem (k, w, newItem) w + = 1 k + = 1 self. SQL _out.setText ("") self. SQL _out.append (temp_sqlstring) print "find button pressed" def buttonExit (self): self. conn. commit () self. cur. close () self. conn. close () self. close () def keyPressEvent (self, e): if e. key () = QtCore. qt. key_Escape: self. buttonExit () class MyWindow (QtGui. QMainWindow, Ui_MainWindow): def _ init _ (self): super (MyWindow, self ). _ init _ () self. setupUi (self) self. connect (self. find, QtCore. SIGNAL ('clicked () '), self. buttonTest) self. connect (self. pushButton_2, QtCore. SIGNAL ('clicked () '), self. buttonExit) if _ name __= = "_ main _": app = QtGui. QApplication (sys. argv) app. aboutToQuit. connect (app. deleteLater) myshow = MyWindow () myshow. show () sys.exit(app.exe c _ () Then app.exe c _ () # sys. exit (0)

Note: Make sure that the MySQL database and students table exist in mysql before running;
Running result:

Since the originally designed interface was relatively small, the length and width of each control were changed;
In this way, the simple interface is used to dynamically construct SQL statements and display the returned results on the interface;