- #!/usr/bin/python
- import MySQLdb
- def 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() method
- cursor = 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 server
- db.close()
- #该片段来自于http://www.codesnippet.cn/detail/040620133768.html
来源: http://www.codesnippet.cn/detail/040620133768.html