python-mysql删除和更新数据

为什么80%的码农都做不了架构师？>>>   

删除数据

import codecs

import MySQLdb
def connect_mysql():
    db_config = {
        'host': '192.168.48.128',
        'port': 3306,
        'user': 'xiang',
        'passwd': '123456',
        'db': 'python',
        'charset': 'utf8'
    }
    cnx = MySQLdb.connect(**db_config)
    return cnx

if __name__ == '__main__':
    cnx = connect_mysql()

    sql = '''select * from Student where StdName in (select StdName from Student group by StdName having count(1)>1 ) order by StdName;'''
    try:
        cus = cnx.cursor()
        cus.execute(sql)
        result = cus.fetchall()
        with codecs.open('select.txt', 'w+') as f:
            for line in result:
                f.write(str(line))
                f.write('\n')
        cus.close()
        cnx.commit()
    except Exception as e:
        cnx.rollback()
        print('error')
        raise e
    finally:
        cnx.close()

结果：

 

正确执行

 

 

 

 

修改数据

import codecs

import MySQLdb
def connect_mysql():
    db_config = {
        'host': '192.168.48.128',
        'port': 3306,
        'user': 'xiang',
        'passwd': '123456',
        'db': 'python',
        'charset': 'utf8'
    }
    cnx = MySQLdb.connect(**db_config)
    return cnx

if __name__ == '__main__':
    cnx = connect_mysql()

    sql = '''select *, (grade+60) as newGrade from Score where Grade <5;'''
    update = '''update Score set grade = grade + 60 where grade < 5;  '''
    try:
        cus_start = cnx.cursor()
        cus_start.execute(sql)
        result1 = cus_start.fetchall()
        print(len(result1))
        cus_start.close()

        cus_update = cnx.cursor()
        cus_update.execute(update)
        cus_update.close()

        cus_end = cnx.cursor()
        cus_end.execute(sql)
        result2 = cus_end.fetchall()
        print(len(result2))
        cus_end.close()

        cnx.commit()
    except Exception as e:
        cnx.rollback()
        print('error')
        raise e
    finally:
        cnx.close()

结果：

321

0

转载于:https://my.oschina.net/peizh/blog/1579584