增删改查核心代码
时间: 2023-07-07 14:11:40 浏览: 50
好的,以下是增删改查的核心代码示例:
增加(Create):
```python
# 连接数据库
db = pymysql.connect(host='localhost', user='root', password='123456', database='test')
# 创建游标对象
cursor = db.cursor()
# SQL 插入语句
sql = "INSERT INTO students(name, age, gender) VALUES ('Tom', 18, 'Male')"
# 执行SQL语句
try:
cursor.execute(sql)
# 提交到数据库
db.commit()
print("数据插入成功")
except:
# 发生错误时回滚
db.rollback()
print("数据插入失败")
# 关闭数据库连接
db.close()
```
删除(Delete):
```python
# 连接数据库
db = pymysql.connect(host='localhost', user='root', password='123456', database='test')
# 创建游标对象
cursor = db.cursor()
# SQL 删除语句
sql = "DELETE FROM students WHERE id = 1"
# 执行SQL语句
try:
cursor.execute(sql)
# 提交到数据库
db.commit()
print("数据删除成功")
except:
# 发生错误时回滚
db.rollback()
print("数据删除失败")
# 关闭数据库连接
db.close()
```
修改(Update):
```python
# 连接数据库
db = pymysql.connect(host='localhost', user='root', password='123456', database='test')
# 创建游标对象
cursor = db.cursor()
# SQL 更新语句
sql = "UPDATE students SET age = 20 WHERE id = 1"
# 执行SQL语句
try:
cursor.execute(sql)
# 提交到数据库
db.commit()
print("数据更新成功")
except:
# 发生错误时回滚
db.rollback()
print("数据更新失败")
# 关闭数据库连接
db.close()
```
查询(Retrieve):
```python
# 连接数据库
db = pymysql.connect(host='localhost', user='root', password='123456', database='test')
# 创建游标对象
cursor = db.cursor()
# SQL 查询语句
sql = "SELECT * FROM students"
# 执行SQL语句
try:
cursor.execute(sql)
# 获取所有记录列表
results = cursor.fetchall()
for row in results:
id = row[0]
name = row[1]
age = row[2]
gender = row[3]
# 打印结果
print("id=%d, name=%s, age=%d, gender=%s" % (id, name, age, gender))
except:
print("Error: unable to fetch data")
# 关闭数据库连接
db.close()
```
以上是Python语言中使用pymysql模块进行增删改查操作的核心代码。