python增删改查语句_Python对MySql增删改查

pip install pymysql

import pymysql

db_config = {

'host': '127.0.0.1(ip)',

'port': 3306,

'user': '账号',

'password': '密码',

'db': '数据库名',

'charset': 'utf8'

}

# 建立连接

conn = pymysql.connect(**db_config)

# 连接是不能操作数据库的,需要生成游标来操作

# 获取cursor

cur = conn.cursor()

基本使用：insert 、 delete 、 update 、 select

try

try:

# 增(insert into values)

# 指定字段

# sql_insert = "insert into temp(name,age) values ('jy',11),('nh',22),('fe',33)"

# 全字段

# sql_insert = "insert into temp values (1, 'bk', 44, 'f')"

# 执行sql语句

# cur.execute(sql_insert)

# 删(delete from where)

# 删除表中满足条件的数据

# sql_delete = "delete from temp where id > 5"

# 删除表中所有数据

# sql_delete = "delete from temp"

# 执行sql语句

# cur.execute(sql_delete)

# 改(update set where)

# 修改满足条件的多个字段

# sql_update = "update temp set id = 0, sex = 'm' where name = 'jy' "

# 修改满足条件的单个字段

# sql_update = "update temp set sex = 'f' where id is null"

# 修改所有数据

# sql_update = "update temp set sex = 'noth'"

# 执行sql语句

# cur.execute(sql_update)

# 查(select from where)

sql_select = "select * from temp"

# 执行SQL命令，然后返回生效行数

# SQL语句都是通过execute方法执行

num = cur.execute(sql_select)

# print(num)

# 获取结果

# 取出所有 fetchall()

for i in cur.fetchall():

print(i)

# 取出一条 fetchaone()

# print(cur.fetchone())

# 取出具体几条 fetchamany(num)

# print(cur.fetchmany(5))

# 提交事务

conn.commit()

except

except Exception as message:

# 打印异常

print(message)

# 发生异常，回滚事务

conn.rollback()

finally

finally:

# 关闭游标

cur.close()

# 关闭连接

conn.close()