Python操作MySQL数据库

# -*- coding: UTF-8 -*-
import MySQLdb
import time
#警告信息try except是无法捕捉的
from warnings import filterwarnings
filterwarnings('error', category = MySQLdb.Warning)
cursor = conn = delete_id = update_id = 0
try:
    conn = MySQLdb.connect(host='10.200.10.203', user='test', passwd='test', db='test', port=3306,charset='utf8')
    cursor = conn.cursor()
    #创建表
    create_tabl_sql = """CREATE TABLE IF NOT EXISTS `test` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `name` varchar(200) NOT NULL,
      `create_time` datetime NOT NULL,
      `content` text NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8"""
    #cursor.execute(create_tabl_sql)
except MySQLdb.Warning, w:
    print "警告信息 %s" % str(w)
except MySQLdb.Error, e:
    print "错误信息 %d %s" % (e.args[0], e.args[1])

# 插入数据
name = '张三'
create_time = time.strftime('%Y-%m-%d %H:%M:%S')
content = '这是内容'
insert_data_sql = "insert into `test` (`name`, `create_time`, `content`) values ('" + name + "','" + create_time + "','" + content + "')"
print "执行语句: %s" % insert_data_sql
try:
    cursor.execute(insert_data_sql)
    conn.commit()
except :
    # 异常回滚
    print '插入异常回滚'
    conn.rollback()
# 查询数据
select_data_sql = "select * from test order by id desc limit 2"
print "执行语句: %s" % select_data_sql
try:
    cursor.execute(select_data_sql)
    results = cursor.fetchall()
    for row in results:
        print '编号:%d' % (row[0])
        print u'姓名:%s' % (row[1])
        print u'内容:%s' % (row[3])
    update_id = results[0][0]
    delete_id = results[1][0]
except :
    # 异常回滚
    print '查询数据失败'

# 删除数据
delete_data_sql = "delete from `test` WHERE id  = %d " % (delete_id)
print '执行语句: %s' % delete_data_sql
try:
   cursor.execute(delete_data_sql)
   conn.commit()
except:
    print '删除异常回滚'
    conn.rollback()
# 更新数据
name_2 = '李四'
content_2 = '这是新内容'
update_data_sql = "update `test` set name ='" + name_2 + "',content='" + content_2 + "' where id = %d" % (update_id)
print '执行语句: %s' % update_data_sql
try:
    cursor.execute(update_data_sql)
    conn.commit()
except:
    print '更新异常回滚'
    conn.rollback()

cursor.close()
conn.close()