python对mysql的增删改查
程序员文章站
2022-05-30 12:28:39
...
python操作mysql数据库——增删改查操作（二）
</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>
上一篇： 怕汤撒我身上
下一篇： eclipse连接数据库代码