</div>

                                                    <div class="article-box__content">
                                                                                            <div class="detail-box">
                                <div class="article-detail">
                                    <div class="content">
                                                                                                                                                                                    <p>前面一篇介绍了如何连接数据库，这一篇主要是介绍如何对数据库进行增删改查的操作。</p> 

完整的Python操作mysql数据库脚本如下

# coding = utf -8
import pymysql

class  Mysql(object):
    def __init__(self):
        try:
            self.conn = pymysql.connect(
                host = '39.108.237.57',
                port = 3306,
                user = 'root',
                passwd = '380475066lhJ',
                db = 'test',
                charset = 'utf8',
            )
        except Exception as e:
            print(e)
        else:
            print("connect successfully")

            #使用cursor()方法创建一个游标对象cursor
            self.cur = self.conn.cursor()


    #创建表
    def creat_table(self):
        try:
            #使用execute()方法执行SQL语句，如果表存在则删除
            self.cur.execute("DROP TABLE IF EXISTS EMPLOYEE")
            sql = """ CREATE TABLE EMPLOYEE(
                  FIRST_NAME CHAR(20) NOT NULL,
                  LAST_NAME CHAR(20),
                  AGE INT,
                  SEX CHAR(1),
                  INCOME FLOAT )"""
            #执行SQL语句
            self.cur.execute(sql)
            print("create table success")
        except Exception as e:
            print("create table error\n "+e)



    #向数据库表中插入数据
    def add(self):
        sql = """insert into EMPLOYEE(FIRST_NAME,LAST_NAME,AGE,SEX,INCOME)
                VALUES ("John","jack",25,"F",8000)"""
        try:
            self.cur.execute(sql)
            #提交到数据库执行
            self.conn.commit()
        except Exception as e:
            print(e)
            #发生错误回滚
            self.conn.rollback()
            print("fail to add new data")
        else:
            print("insert data success")



    #向数据库表中查询数据
    def show(self):
        sql = """select * from EMPLOYEE"""
        try:
            self.cur.execute(sql)
            #fetchall()返回的结果是List,list里面再嵌套List
            res = self.cur.fetchall()
            for row in res:
                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 Exception as e:
            print(e+"select data fail")
        else:
            print("select data success")



    #向数据库中更新数据
    def update(self):
        sql = """UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = 'M'"""
        try:
            self.cur.execute(sql)
            self.conn.commit()
        except Exception as e:
            print(e)
        else:
            print("updata date success")



    #删除数据库中的数据
    def rem(self):
        sql = """delete from EMPLOYEE where sex = "M" """
        try:
            self.cur.execute(sql)
            self.conn.commit()
        except Exception as e:
            print(e)
        else:
            print("delete data success")



    #关闭数据库连接
    def close(self):
        self.cur.close()
        self.conn.close()
        print("close database success")


if __name__ == "__main__":
    mysql = Mysql()
    mysql.creat_table()
    mysql.add()
    mysql.show()
    mysql.update()
    mysql.rem()
    mysql.close()

                                                                                                <div class="tags-box">
                                    <div class="tags-box__inner">
                                        <a class="tag-item" href="https://my.oschina.net/u/4341017?q=def">def</a>                                        </div>
                                </div>
                            
                                                            <div class="copyright-box">
                                <div class="copyright-box__content">
                                                                                                                                <p><span class="label">本文转载自：</span>https://www.cnblogs.com/lhj818/p/11696652.html</p>
                                                                                                                    </div>
                                <div class="copyright-box__extra">
                                    <span class="ban clickable" ban-report="" data-id="4116281" data-obj-type="3" data-url="https://my.oschina.net/u/4341017/blog/4116281"><i class="flag icon"></i>举报</span>
                                </div>
                            </div>

                                                            <div class="action-box">
                                                                        <div class="action-item">
                                                                                    <div class="action-item__button donate" donate="" data-id="4116281" data-obj-type="16344358" data-subject="python操作mysql数据库——增删改查操作（二）" data-donater="" data-author="4341017" data-return-url="https://my.oschina.net/u/4341017/blog/4116281" data-notify-url="https://my.oschina.net/action/donate/paySuccess">                                                 <i class="oicon oicon-money-bold"></i>
                                        </div>
                                        <div class="action-item__text">打赏</div>
                                    </div>
                                                                    <div class="action-item">
                                                                            <div class="action-item__button like-btn toggle-article-like " data-id="4116281">
                                        <i class="oicon oicon-thumb-up"></i>
                                    </div>
                                    <div class="action-item__text"><span data-article-like-count="">0</span> 赞</div>
                                </div>
                                <div class="action-item">
                                                                            <div class="action-item__button collect-btn " data-id="4116281" data-user-id="4341017" data-obj-type="3" data-max="99" data-tag-required="" data-current-user-id="" data-recommend-tags="def" data-uncollected-class="oicon oicon-star" data-collected-class="oicon oicon-star">
                                        <i class="oicon oicon-star"></i>
                                    </div>
                                    <div class="action-item__text"><span data-collect-count="" data-id="4116281" data-obj-type="3">0</span> 收藏</div>
                                </div>
                                <div class="action-item">
                                                                            <div class="osc-share ui floating dropdown" data-tag="share-blog" tabindex="0">
                                        <div class="action-item__button"><i class="oicon oicon-share"></i></div>
                                        <div class="menu" tabindex="-1">
                                            <a class="item" data-platform="wechat" data-value="wechat"><i class="weixin icon"></i>微信</a>
                                            <a class="item" data-platform="qq" data-value="qq"><i class="qq icon"></i>QQ</a>
                                            <a class="item" data-platform="weibo" data-value="weibo"><i class="weibo icon"></i>微博</a>
                                        </div>
                                    </div>
                                    <div class="action-item__text">分享</div>
                                </div>
                            </div>

                                                                                                <div class="recommend-box">
                                    <div class="recommend-box__header">
                                        <h3 class="recommend-box__title">作者的其它热门文章</h3>
                                    </div>
                                    <div class="recommend-box__content">
                                        <div class="item-list">
                                                                                                                                                        <div class="item">
                                                        <a href="https://my.oschina.net/u/4341017/blog/4115992" target="_blank" title="mysql实现row_number()和row_number() over(partition by)">mysql实现row_number()和row_number() over(partition by)</a>
                                                    </div>
                                                                                                                                                                                                            <div class="item">
                                                        <a href="https://my.oschina.net/u/4341017/blog/4116113" target="_blank" title="mybatis调用mysql的存储过程（procedure），实现查询操作（student表中的某个年级中的总人数 select (1) 或者 select  (*)）">mybatis调用mysql的存储过程（procedure），实现查询操作（student表中的某个年级中的总人数 select (1) 或者 select  (*)）</a>
                                                    </div>
                                                                                                                                                                                                            <div class="item">
                                                        <a href="https://my.oschina.net/u/4341017/blog/4115970" target="_blank" title="Mysql数据处理/行转列/列转行/分割/拼接/数据复制汇总">Mysql数据处理/行转列/列转行/分割/拼接/数据复制汇总</a>
                                                    </div>
                                                                                                                                                                                                            <div class="item">
                                                        <a href="https://my.oschina.net/u/4341017/blog/4116013" target="_blank" title="Python笔记（八）MySQL">Python笔记（八）MySQL</a>
                                                    </div>
                                                                                                                                            </div>
                                    </div>
                                </div>
                                                        </div>
                    </div>