随笔-203  评论-11  文章-11 

python操作mysql进行更新的时候,必须有commit

今天在写python操作mysql的时候,出现了一个奇怪的现象,在用python代码执行update的时候,代码返回已经更新成功,但是数据库表里的字段的值却没有改变,一直没有找到错误的原因,结果寻求身边同事的帮助,问题解决,在此做个记录;

MysqlHelper.py  代码如下

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


HOST = '127.0.0.1'
PORT = 3306
USER = 'test'
PASSWORD = 'test123'
DBNAME = 'test'
CHARSET = 'utf8'


def connect():

	try:
            import MySQLdb
	except ImportError, e:
            #print e 
	    return None


        try:
	    cxn = MySQLdb.connect(host=HOST, user=USER, passwd=PASSWORD,port=PORT,db=DBNAME,charset=CHARSET)
	   
            #print cxn
            return cxn
        except MySQLdb.Error,e:
             print "Mysql Error %d: %s" % (e.args[0], e.args[1])


# 插入操作
def insert_one(cur,sql,value):
    res =  cur.execute(sql ,value)
    # 插入成功,res 返回值为1 
    if  1 != res :
        print 'failed'
    else:
        print 'success' 


def insert_many(cur,sql,values):
    res =  cur.executemany(sql ,values)
    # 插入成功,res 返回值为1 
    if  1 != res :
        print 'failed'
    else:
        print 'success' 

getRC = lambda cur: cur.rowcount if hasattr(cur,'rowcount')  else -1

# 更新操作
def update(cur,sql,params):
    cur.execute(sql  ,  params)
    return getRC(cur)

# 删除操作
def delete(cur,sql,params):
    cur.execute(sql,params)
    return getRC(cur)

# 只获取一条记录,返回的是一个元组
def fetch_one(cur,sql):
    count = cur.execute(sql)
    #print count
    result = cur.fetchone();  

    return result

# 获取多条数据;返回的是二维元组;
def fetch_all(cur,sql):
    count = cur.execute(sql)
    #print count
    results = cur.fetchall();  

    '''
    print results
    for r in results:  
        print r  
    '''
    return results

# 提交的完成操作
def finish(cxn):
    cxn.commit()
    cxn.close()

 我在其它的文件中调用

a.py

import MysqlHelper# 获取数据库各个表的主键
def getTablePrimaryKey(tablename):
    cxn = MysqlHelper.connect()
    cur = cxn.cursor()
    res =  MysqlHelper.update(cur,"UPDATE  table_primary_index "+
                                  "SET index_primary_value=index_primary_value+1"+
                                  " WHERE index_table_name =%s" , (tablename))
    print res
    result = MysqlHelper.fetch_one(cur,"SELECT * FROM  table_primary_index WHERE index_table_name='%s'" % tablename)
    print result

现象: print res  可以正确返回1,说明更新数据库成功;

          print result  也可以取到刚刚更新的值;

          但是数据库表中的值没有变更;

原因:

       python 操作mysql 是用 事物的方式来实现的,所以在update 的时候 必须有commit 提交的过程,否则数据表不会生效;

修改后的代码如下:

b.py

# 获取数据库各个表的主键
def getTablePrimaryKey(tablename):
    cxn = MysqlHelper.connect()
    cur = cxn.cursor()
    res =  MysqlHelper.update(cur,"UPDATE  table_primary_index "+
                                  "SET index_primary_value=index_primary_value+1"+
                                  " WHERE index_table_name =%s" , (tablename))
    print res
    result = MysqlHelper.fetch_one(cur,"SELECT * FROM  table_primary_index WHERE index_table_name='%s'" % tablename)
    print result

    MysqlHelper.finish(cxn)
    return result[1]

 

 

 

 

posted on 2012-09-10 20:42 mingaixin 阅读(...) 评论(...) 编辑 收藏