pycharm操作mysql数据库 创建表 向表中插入数据 操作mysql数据库查询 修改 删除数据

1，安装PyMySQL模块

2，集成环境里面操作MySQL数据库创建表

# 导入pymysql import pymysql # 创建连接 con = pymysql.connect(host="localhost", user="root", password="root", database="test", port=3306) # 创建游标对象 cur = con.cursor() # 编写创建表的sql sql = """ create table python_student( sno int primary key auto_increment, sname varchar(30) not null, age int(2), score float(3,1) ) """ try: # 执行创建表的sql cur.execute(sql) print("创建表成功") except Exception as e: print(e) print("创建表失败") finally: # 关闭游标连接 cur.close() # 关闭数据库连接 con.close() 

可打开Navicat查看创建完成的表

3，向创建的表中插入数据

# 导入pymysql import pymysql # 创建连接 con = pymysql.connect(host="localhost", user="root", password="root", database="test", port=3306) # 创建游标对象 cur = con.cursor() # 编写插入数据的sql sql = "insert into python_student (sname,age,score) values (%s, %s, %s)" try: # 执行sql cur.execute(sql, ("小强", 18, 99.5)) con.commit() print("插入数据成功") except Exception as e: print(e) con.rollback() print("插入数据失败") finally: # 关闭游标连接 cur.close() # 关闭数据库连接 con.close() 

2，插入多条数据

# 导入pymysql import pymysql # 创建连接 con = pymysql.connect(host="localhost", user="root", password="root", database="test", port=3306) # 创建游标对象 cur = con.cursor() # 编写插入数据的sql sql = "insert into python_student (sname,age,score) values (%s, %s, %s)" try: # 执行sql cur.executemany(sql, [("小强", 18, 97.5),("小二", 19, 98.5),("小五", 20, 99.5)]) con.commit() print("插入数据成功") except Exception as e: print(e) con.rollback() print("插入数据失败") finally: # 关闭游标连接 cur.close() # 关闭数据库连接 con.close() 

4，操作mysql数据库查询所有数据

# 导入pymysql import pymysql # 创建连接 con = pymysql.connect(host="localhost", user="root", password="root", database="test", port=3306) # 创建游标对象 cur = con.cursor() # 编写查询的sql sql = "select * from python_student" try: # 执行sql cur.execute(sql) # 处理结果集 students = cur.fetchall() for student in students: # print(student) sno = student[0] sname = student[1] age = student[2] score = student[3] print("sno",sno,"sname",sname,"age",age,"score",score) except Exception as e: print(e) print("查询所有数据失败") finally: # 关闭游标连接 cur.close() # 关闭数据库连接 con.close() 

5，查询mysql数据库的一条数据

# 导入pymysql import pymysql # 创建连接 con = pymysql.connect(host="localhost", user="root", password="root", database="test", port=3306) # 创建游标对象 cur = con.cursor() # 编写查询的sql sql = "select * from python_student where sname='小二'" try: # 执行sql cur.execute(sql) # 处理结果集 student = cur.fetchone() print(student) sno = student[0] sname = student[1] age = student[2] score = student[3] print("sno",sno,"sname",sname,"age",age,"score",score) except Exception as e: print(e) print("查询所有数据失败") finally: # 关闭游标连接 cur.close() # 关闭数据库连接 con.close() 

6，操作mysql数据库修改数据

# 导入pymysql import pymysql # 创建连接 con = pymysql.connect(host="localhost", user="root", password="root", database="test", port=3306) # 创建游标对象 cur = con.cursor() # 编写修改的sql sql = 'update python_student set sname=%s where sno=%s' try: # 执行sql cur.execute(sql, ("薛宝钗", 1)) con.commit() print("修改成功") except Exception as e: print(e) con.rollback() print("修改失败") finally: # 关闭游标连接 cur.close() # 关闭数据库连接 con.close() 

7，操作mysql数据库删除数据

# 导入pymysql import pymysql # 创建连接 con = pymysql.connect(host="localhost", user="root", password="root", database="test", port=3306) # 创建游标对象 cur = con.cursor() # 编写删除的sql sql = 'delete from python_student where sname=%s' try: # 执行sql cur.execute(sql, ("薛宝钗")) con.commit() print("删除成功") except Exception as e: print(e) con.rollback() print("删除失败") finally: # 关闭游标连接 cur.close() # 关闭数据库连接 con.close()