Python Note II (MySQL database operation)

python2.x using MySQLdb

python3.x using Pymysql instead of MYSQLDB

But I use PYTHON3.5,MYSQLDB or I can use it in python3.5.

1 #!/usr/bin/env python2 #_*_ coding:utf-8 _*_3 ImportPymysql4 ImportMySQLdb5 6 #Open a database connection7db = Pymysql.connect ("10.35.22.91","Root","Adminadmin","TESTDB")8 " "9 db = MySQLdb.connect (host= ' 10.35.22.91 ',Ten user= ' root ', One port = 3306, A passwd = ' adminadmin ', - db= ' Scrapydb ', - charset= ' UTF8 ') the " " - #Create a Cursor object using the cursor () method cursor -cursor =db.cursor () -  + #Execute SQL using the Execute () method, or delete if the table exists -Cursor.execute ("DROP TABLE IF EXISTS EMPLOYEE") + #To create a table using Preprocessing statements Asql ="""CREATE TABLE EMPLOYEE ( at first_name CHAR () not NULL, - last_name CHAR (+), - Age INT, - SEX CHAR (1), - INCOME FLOAT)""" - cursor.execute (SQL) in  - #SQL INSERT statement tosql ="""INSERT into EMPLOYEE (first_name, + last_name, age, SEX, INCOME) - VALUES (' Mac ', ' Mohan ', ', ' M ', ')""" the " " * # SQL INSERT statement the second way $ sql = "INSERT into EMPLOYEE (first_name,Panax Notoginseng last_name, age, SEX, INCOME) - VALUES ('%s ', '%s ', '%d ', '%c ', '%d ') '% the (' Mac ', ' Mohan ', ', ' M ', ') + " " A Try: the    #Execute SQL statement + cursor.execute (SQL) -    #commit to database execution $ Db.commit () $ except: -    #Rollback If an error occurs - Db.rollback () the  - " "Wuyi database Query Operations the python queries MySQL uses the Fetchone () method to get a single piece of data, using the Fetchall () method to get multiple data.  - Fetchone (): This method gets the next query result set. The result set is an object Wu Fetchall (): Receives all the returned result rows. - ROWCOUNT: This is a read-only property and returns the number of rows affected after executing the Execute () method About " " $ #SQL Query Statements -sql ="SELECT * from EMPLOYEE - WHERE INCOME > '%d '"% (1000) - Try: A    #Execute SQL statement + cursor.execute (SQL) the    #print (Cursor.rowcount) -    #get list of all records $Results =Cursor.fetchall () the     forRowinchResults: theFName =Row[0] theLName = row[1] theAge = Row[2] -Sex = row[3] inIncome = Row[4] the        #Print Results the       Print("fname=%s,lname=%s,age=%d,sex=%s,income=%d"%  About (fname, lname, age, sex, income)) the except: the    Print("error:unable to fetch data") the  + #Database Update Operations - #SQL UPDATE statement thesql ="UPDATE EMPLOYEE SET age = age + 1 WHERE SEX = '%c '"% ('M')Bayi Try: the    #Execute SQL statement the cursor.execute (SQL) -    #commit to database execution - Db.commit () the except: the    #Roll Back when an error occurs the Db.rollback () the  - #Database Delete operations the #SQL DELETE statement thesql ="DELETE from the EMPLOYEE WHERE age > '%d '"% (20) the Try:94    #Execute SQL statement the cursor.execute (SQL) the    #Submit Changes the Db.commit ()98 except: About    #Roll Back when an error occurs - Db.rollback ()101 102 #To close a database connection103 db.close ()104 " " the # Execute SQL query using the Execute () method106 #cursor. Execute ("Select VERSION ()")107 108 # Use the Fetchone () method to get a single data generator.109 data = Cursor.fetchone () the 111 print ("Database version:%s"% data) the 113 # Close the database connection the db.close () the " "

Error Handling

Some errors and exceptions for database operations are defined in the DB API, and the following table lists these errors and exceptions:

Exception	Description
Warning	Triggered when there is a critical warning, such as the insertion of data is truncated and so on. Must be a subclass of StandardError.
Error	Warning all other error classes. Must be a subclass of StandardError.
Interfaceerror	Triggered when there is an error in the Database interface module itself (not a database error). Must be a subclass of error.
Databaseerror	Triggers when a database-related error occurs. Must be a subclass of error.
DataError	Triggered when an error occurs when there is data processing, for example: except for 0 errors, data over-range, and so on. Must be a subclass of Databaseerror.
Operationalerror	Refers to an error that occurs when a database is manipulated by a non-user control. For example, an operation database is an error that occurs when a connection is accidentally disconnected, the database name is not found, a transaction fails, a memory allocation error, and so on. Must be a subclass of Databaseerror.
Integrityerror	Integrity-related errors, such as foreign key check failures, and so on. Must be a databaseerror subclass.
Internalerror	An internal error in the database, such as a cursor failure, a transaction synchronization failure, and so on. Must be a databaseerror subclass.
Programmingerror	program errors, such as data table (table) not found or existing, SQL statement syntax error, parameter number error, and so on. Must be a subclass of Databaseerror.
Notsupportederror	Errors are not supported, such as functions or APIs that are not supported by the database. For example, the. Rollback () function is used on the connection object, but the database does not support transactions or the transaction is closed. Must be a subclass of Databaseerror.

Python Note II (MySQL database operation)