python 操作mysql数据库（增删改查）

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import pymysql

# 打开数据库连接
db = pymysql.connect("localhost","root","qq123456","TESTDB" )

# 使用cursor()方法获取操作游标
cursor = db.cursor()

# # 如果数据表已经存在使用 execute() 方法删除表
# cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

cursor.execute("SELECT VERSION()") # 使用execute方法执行SQL语句
data = cursor.fetchone() # 使用 fetchone() 方法获取一条数据库。
print ("Database version : %s " % data)

# # 创建数据表SQL语句
# sql = """CREATE TABLE EMPLOYEE (
#          FIRST_NAME  CHAR(20) NOT NULL,
#          LAST_NAME  CHAR(20),
#          AGE INT,
#          SEX CHAR(1),
#          INCOME FLOAT )"""

# SQL插入语句
sql2 = """INSERT INTO EMPLOYEE(FIRST_NAME,LAST_NAME, AGE, SEX, INCOME)
            VALUES ('hazy', 'cc', 25, 'M', 2000)"""

try:
    cursor.execute(sql2)
    db.commit()
except:
    db.rollback()

cursor.execute(sql2)

# SQL 查询语句
sql3 = "SELECT * FROM EMPLOYEE \
       WHERE INCOME > '%d'" % (1000)
try:
   # 执行SQL语句
   cursor.execute(sql3)
   # 获取所有记录列表
   results = cursor.fetchall()
   for row in results:
      fname = row[0]
      lname = row[1]
      age = row[2]
      sex = row[3]
      income = row[4]
      # 打印结果
      print ("fname=%s,lname=%s,age=%d,sex=%s,income=%d" % \
             (fname, lname, age, sex, income ))
except:
   print ("Error: unable to fecth data")

# SQL 修改语句
sql4 = "UPDATE EMPLOYEE SET AGE = AGE + 1" \
       "WHERE SEX = '%c'" % ("M")
try:
    cursor.execute(sql4)
    db.commit()
except:
    db.rollback()

# # SQL 删除语句
# sql5 = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
# try:
#    cursor.execute(sql5)
#    db.commit()
# except:
#    db.rollback()

# 关闭数据库连接
db.close()

增：INSERT INTO…VALUES…
删：DELETE FROM … WHERE…
改：UPDATE… SET…
查:SELECT * FROM … WHERE…