Python calls MySQLdb to complete the crud operation of MySQL

[Hardware Environment]

WIN10 64-bit

[Software Environment]

Python version: 2.7.3

Ide:jetbrains Pycharm 2016.3.2

Python Library:

1.1) Mysql-python-1.2.4b4.win32-py2.7.exe

[Construction process]

OpenCV Python Library:

1. direct EXE file installation

[Related code]

#!/usr/bin/python#-*-coding:utf-8-*-ImportMySQLdb#you have created the database TESTDB.#in the TestDB database, you have created table EMPLOYEE#The Employee table fields are first_name, last_name, age, SEX, and INCOME. #Connection Database TestDB Use the user name "TestUser", the password is "test123", you can set your own or directly use the root user name and its password, MySQL database user authorization please use the GRANT command. #The Python mysqldb module is already installed on your machine. #Open a database connectiondb = MySQLdb.connect ("localhost","testuser","test123","TESTDB" )#get an operation cursor using the cursor () methodcursor =db.cursor ()#executing SQL statements using the Execute methodCursor.execute ("SELECT VERSION ()")#Use the Fetchone () method to get a database. data =Cursor.fetchone ()Print "Database version:%s"%Data#To close a database connectiondb.close ()################################################ Creating a database table #####################################################Open a database connectiondb = MySQLdb.connect ("localhost","testuser","test123","TESTDB" )#get an operation cursor using the cursor () methodcursor =db.cursor ()#If the data table already exists, use the Execute () method to delete the table. Cursor.execute ("DROP TABLE IF EXISTS EMPLOYEE")#Create a data table SQL statementsql ="""CREATE TABLE EMPLOYEE (first_name char) not NULL, last_name char (+), age INT, S EX CHAR (1), INCOME FLOAT)"""cursor.execute (SQL)#To close a database connectiondb.close ()################################################ Database Insert #####################################################Open a database connectiondb = MySQLdb.connect ("localhost","testuser","test123","TESTDB" )#get an operation cursor using the cursor () methodcursor =db.cursor ()#SQL INSERT statementsql ="""INSERT into EMPLOYEE (first_name, last_name, age, SEX, INCOME) VALUES (' Mac ', ' Mohan ', ' M ', ' 2000 ') 
    """Try:   #Execute SQL statementcursor.execute (SQL)#commit to database executionDb.commit ()except:   #Rollback In case there was any errorDb.rollback ()#To close a database connectiondb.close ()################################################ database Query #####################################################Open a database connectiondb = MySQLdb.connect ("localhost","testuser","test123","TESTDB" )#get an operation cursor using the cursor () methodcursor =db.cursor ()#SQL Query Statementssql ="SELECT * from EMPLOYEE WHERE INCOME > '%d '"% (1000)Try:   #Execute SQL statementcursor.execute (SQL)#get list of all recordsResults =Cursor.fetchall () forRowinchResults:fname=Row[0] lname= Row[1] Age= Row[2] Sex= Row[3] Income= Row[4]      #Print Results      Print "fname=%s,lname=%s,age=%d,sex=%s,income=%d"%(fname, lname, age, sex, income)except:   Print "error:unable to FECTH data"#To close a database connectiondb.close ()################################################ Database Update #####################################################Open a database connectiondb = MySQLdb.connect ("localhost","testuser","test123","TESTDB" )#get an operation cursor using the cursor () methodcursor =db.cursor ()#SQL UPDATE statementsql ="UPDATE EMPLOYEE SET age = age + 1 WHERE SEX = '%c '"% ('M')Try:   #Execute SQL statementcursor.execute (SQL)#commit to database executionDb.commit ()except:   #Roll Back when an error occursDb.rollback ()#To close a database connectiondb.close ()################################################ Database Removal #####################################################Open a database connectiondb = MySQLdb.connect ("localhost","testuser","test123","TESTDB" )#get an operation cursor using the cursor () methodcursor =db.cursor ()#SQL DELETE statementsql ="DELETE from the EMPLOYEE WHERE age > '%d '"% (20)Try:   #Execute SQL statementcursor.execute (SQL)#Submit ChangesDb.commit ()except:   #Roll Back when an error occursDb.rollback ()#Close ConnectionDb.close ()

Python calls MySQLdb to complete the crud operation of MySQL