pycharm 增删改查 mysql数据库

1、pycharm创建mysql数据表######################################################
import pymysql
# 创建连接
con = pymysql.connect(host="127.0.0.1", user="aa", password="2018", database="aa", port=3306)
# 创建游标对象
cur = con.cursor()
sql = """
create table wzx_test1224(
id int primary key auto_increment,
name varchar(30) not null,
address int(2)
)
"""
try:
# 执行创建表的sql
cur.execute(sql)
print("创建表成功")
except Exception as e:
print(e)
print("创建表失败")
finally:
# 关闭游标连接
cur.close()
# 关闭数据库连接
con.close()

2、pycharm插入单条mysql数据######################################################
import pymysql
# 创建连接
con = pymysql.connect(host="127.0.0.1", user="aa", password="2018", database="aa", port=3306)
# 创建游标对象
cur = con.cursor()
# 编写插入数据的sql
sql = "insert into wzx_test1224 (name,address) values (%s, %s)"
try:
# 执行sql
cur.execute(sql, ("小强", 18))
con.commit()
print("插入数据成功")
except Exception as e:
print(e)
con.rollback()
print("插入数据失败")
finally:
# 关闭游标连接
cur.close()
# 关闭数据库连接
con.close()

3、pycharm插入多条mysql数据######################################################
import pymysql
# 创建连接
con = pymysql.connect(host="127.0.0.1", user="aa", password="2018", database="aa", port=3306)
# 创建游标对象
cur = con.cursor()
# 编写插入数据的sql
sql = "insert into wzx_test1224 (name,address) values (%s, %s)"
try:
# 执行sql
cur.executemany(sql, [("小星星", 18),("小二", 19),("小五", 20)])
con.commit()
print("插入数据成功")
except Exception as e:
print(e)
con.rollback()
print("插入数据失败")
finally:
# 关闭游标连接
cur.close()
# 关闭数据库连接
con.close()

4、pycharm查询mysql数据######################################################
import pymysql
# 创建连接
con = pymysql.connect(host="127.0.0.1", user="aa", password="2018", database="aa", port=3306)
# 创建游标对象
cur = con.cursor()
# 编写查询的sql
sql = "select * from wzx_test1224"
try:
# 执行sql
cur.execute(sql)
# 处理结果集
wzx_test1224 = cur.fetchall()
for student in wzx_test1224:
print(student)
except Exception as e:
print(e)
print("查询所有数据失败")

5、pycharm修改mysql数据######################################################
import pymysql
# 创建连接
con = pymysql.connect(host="127.0.0.1", user="aa", password="2018", database="aa", port=3306)
# 创建游标对象
cur = con.cursor()
# 编写修改的sql
sql = 'update wzx_test1224 set name=%s where id=%s'
try:
# 执行sql
cur.execute(sql, ("薛宝钗", 1))
con.commit()
print("修改成功")
except Exception as e:
print(e)
con.rollback()
print("修改失败")

6、pycharm删除mysql数据###################################################### 

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

7、pycharm构建测试数据存储到mysql###################################################### 

from faker import Faker
f=Faker(locale='zh_CN') # 为生成数据的文化选项，默认为en_US，只有使用了相关文化，才能生成相对应的随机信息

import pymysql
# 创建连接
con = pymysql.connect(host="127.0.0.1", user="aa", password="2018", database="aa", port=3306)
# 创建游标对象
cur = con.cursor()
sql = "insert into  wzx_test1224 (name,address) values (%s, %s)"
try:
    for i in range(10):
        cur.executemany(sql, [(f.name(), f.address())])
        con.commit()
        i=i+1
    print("插入数据成功")
except Exception as e:
    print(e)
    con.rollback()
    print("插入数据失败")
finally:
    cur.close()
    con.close()