Python operation MySQL Database

Reference: http://www.w3cschool.cc/python/python-mysql.html

Assume:

• MySQL has been installed and the database has been created TESTDB
• The user named "TestUser" is used to connect to the database TestDB and the password is "test123"
• The Python mysqldb module is already installed on your machine.

Python operation MySQL New example:

1 #Encoding:utf-82 #!/usr/bin/python3 4 ImportMySQLdb5 6 #Open a database connection7db = MySQLdb.connect ("localhost","testuser","test123","TESTDB" )8 9 #get an operation cursor using the cursor () methodTencursor =db.cursor () One  A #If the data table already exists, use the Execute () method to delete the table.  -Cursor.execute ("DROP TABLE IF EXISTS EMPLOYEE") -  the #Create a data table SQL statement -sql ="""CREATE TABLE EMPLOYEE ( - first_name CHAR () not NULL, - last_name CHAR (+), + Age INT, - SEX CHAR (1), + INCOME FLOAT)""" A  at cursor.execute (SQL) -  - #To close a database connection -Db.close ()

Example of writing data to a table:

1 #Encoding:utf-82 #!/usr/bin/python3 4 ImportMySQLdb5 6 #Open a database connection7db = MySQLdb.connect ("localhost","testuser","test123","TESTDB" )8 9 #get an operation cursor using the cursor () methodTencursor =db.cursor () One  A #SQL INSERT statement -sql ="""INSERT into EMPLOYEE (first_name, - last_name, age, SEX, INCOME) the VALUES (' Mac ', ' Mohan ', ', ' M ', ')""" - Try: -    #Execute SQL statement - cursor.execute (SQL) +    #commit to database execution - Db.commit () + except: A    #Rollback In case there was any error at Db.rollback () -  - #To close a database connection -Db.close ()

Query Example:

1 #Encoding:utf-82 #!/usr/bin/python3 4 ImportMySQLdb5 6 #Open a database connection7db = MySQLdb.connect ("localhost","testuser","test123","TESTDB" )8 9 #get an operation cursor using the cursor () methodTencursor =db.cursor () One  A #SQL Query Statements -sql ="SELECT * from EMPLOYEE - WHERE INCOME > '%d '"% (1000) the Try: -    #Execute SQL statement - cursor.execute (SQL) -    #get list of all records +Results =Cursor.fetchall () -     forRowinchResults: +FName =Row[0] ALName = row[1] atAge = 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) in except: -    Print "error:unable to FECTH data" to  + #To close a database connection -Db.close ()

Transaction Example:

1 #SQL Delete Record statement2sql ="DELETE from the EMPLOYEE WHERE age > '%d '"% (20)3 Try:4    #Execute SQL statement5 cursor.execute (SQL)6    #Submit to Database7 Db.commit ()8 except:9    #Roll Back when an error occursTenDb.rollback ()

Python operation MySQL Database