python连接mysql简单小程序

来源：互联网发布：创意数据统计图编辑：程序博客网时间：2024/06/07 21:44

前提条件：

1、安装mysql数据库。

2、新建表，表结构：

3、编译器PyCharm安装

实现代码如下：

import pymysql.cursors# 连接数据库connect = pymysql.Connect(    host='localhost',    port=3306,    user='root',    passwd='wh123',    db='spy_example01'      #数据库名为spy_example01

)# 获取游标cursor = connect.cursor()# 插入数据sql = "INSERT INTO spy_user (user_id, user_name, user_password) VALUES ( '%d', '%s', '%s' )"data = (2, 'wanghui', 'wanghui02')cursor.execute(sql % data)connect.commit()print('成功插入', cursor.rowcount, '条数据')# 修改数据sql = "UPDATE spy_user SET user_name = %s WHERE user_id = '%s' "data = ("'wangh63'", 2)cursor.execute(sql % data)connect.commit()print('成功修改', cursor.rowcount, '条数据')# 查询数据sql = "SELECT * FROM spy_user  WHERE user_id = %d  "data = (1,)cursor.execute(sql % data)print('共查找出', cursor.rowcount, '条数据')for row in cursor.fetchall():    print(row[0])    print("%d\t user_name:%s\t user_password:%s"  %row )print('共查找出', cursor.rowcount, '条数据')# 删除数据sql = "DELETE FROM spy_user WHERE user_id  = '%d' "data = (2, )cursor.execute(sql % data)connect.commit()print('成功删除', cursor.rowcount, '条数据')# 事务处理sql_1 = "INSERT INTO spy_user (user_id, user_name, user_password) VALUES ( 03, 'wanghui03', 'wangh03' )"sql_2 = "INSERT INTO spy_user (user_id, user_name, user_password) VALUES ( 04, 'wanghui04', 'wangh04' )"sql_3 = "INSERT INTO spy_user (user_id, user_name, user_password) VALUES ( 05, 'wanghui05', 'wangh05' )"try:    cursor.execute(sql_1)  # 储蓄增加1000    cursor.execute(sql_2)  # 支出增加1000    cursor.execute(sql_3)  # 收入增加2000except Exception as e:    connect.rollback()  # 事务回滚    print('事务处理失败', e)else:    connect.commit()  # 事务提交    print('事务处理成功', cursor.rowcount)# 关闭连接cursor.close()connect.close()

0 0