数据库python-mysql安装参考:http://blog.phpfs.com/archives/2343.html
# -*- 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()