How to access the mysql database using MySQLdb in python

This article mainly introduces how python uses MySQLdb to access the mysql database. The example analyzes the skills related to using MySQLdb module to operate mysql databases in Python, which has some reference value, for more information about how to use MySQLdb to access the mysql database, see the example in this article. Share it with you for your reference. The details are as follows:

#!/usr/bin/pythonimport MySQLdbdef doInsert(cursor,db): #insert # Prepare SQL query to INSERT a record into the database. sql = "UPDATE EMPLOYEE SET AGE = AGE+1 WHERE SEX = '%c'" %('M') try:  cursor.execute(sql)  db.commit() except:  db.rollback()def do_query(cursor,db): sql = "SELECT * FROM EMPLOYEE \     WHERE INCOME > '%d'" % (1000) try:   # Execute the SQL command   cursor.execute(sql)   # Fetch all the rows in a list of lists.   results = cursor.fetchall()   print 'resuts',cursor.rowcount   for row in results:    fname = row[0]    lname = row[1]    age = row[2]    sex = row[3]    income = row[4]    # Now print fetched result    print "fname=%s,lname=%s,age=%d,sex=%s,income=%d" % \        (fname, lname, age, sex, income ) except:   print "Error: unable to fecth data"def do_delete(cursor,db): sql = 'DELETE FROM EMPLOYEE WHERE AGE > {}'.format(20) try:  cursor.execute(sql)  db.commit() except:  db.rollback()def do_insert(cursor,db,firstname,lastname,age,sex,income): sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \    LAST_NAME, AGE, SEX, INCOME) \    VALUES ('%s', '%s', '%d', '%c', '%d' )" % \    (firstname,lastname,age,sex,income) try:  cursor.execute(sql)  db.commit() except:  db.rollback()# Open database connection# change this to your mysql account#connect(server,username,password,db_name)db = MySQLdb.connect("localhost","hunter","hunter","pydb" )# prepare a cursor object using cursor() methodcursor = db.cursor()do_query(cursor,db)doInsert(cursor,db)do_query(cursor,db)do_delete(cursor,db)do_query(cursor,db)do_insert(cursor,db,'hunter','xue',22,'M',2000)do_insert(cursor,db,'mary','yang',22,'f',5555)do_insert(cursor,db,'zhang','xue',32,'M',5000)do_insert(cursor,db,'hunter','xue',22,'M',333)do_query(cursor,db)# disconnect from serverdb.close()

I hope this article will help you with Python programming.