pycharm创建mysql表 pycharm中新建一个数据库

一、创建数据库实例

1 from pymysql import *
 2 # 创建数据库实例
 3 #1.建立数据库连接
 4 conn  = connect(host="192.168.20.101",user="root",password="123456")
 5 # 2.获取游标对象
 6 mycur = conn.cursor()
 7 #3.执行创建数据库实例，并返回int类型结果   1表示添加成功，如果数据库实例已经存在则抛出异常
 8 try:
 9     result = mycur.execute("create database pydb")
10     print("添加数据库实例后返回的结果：",result)
11 except ProgrammingError as msg:
12     print("添加数据库实例出现异常：",msg)
13 finally:
14     #4.关闭游标
15     mycur.close()
16     # 5.关闭连接
17     conn.close()

二、创建表

1 from pymysql import *
 2 # 1. 创建数据库连接对象
 3 conn = connect(host="192.168.20.101", user="root", password="123456",database="pydb")
 4 # 2.从连接对象上获取游标
 5 mycur = conn.cursor()
 6 # 3.执行创建数据库的表tb_user,返回int类型的结果  成功添加表的话，返回0 ；添加失败抛出异常
 7 try:
 8     result = mycur.execute("""
 9     CREATE TABLE `tb_user` (
10       `id` bigint(20) NOT NULL AUTO_INCREMENT,
11       `username` varchar(50) NOT NULL COMMENT '用户名',
12       `password` varchar(32) NOT NULL COMMENT '密码，加密存储',
13       `phone` varchar(20) DEFAULT NULL COMMENT '注册手机号',
14       `email` varchar(50) DEFAULT NULL COMMENT '注册邮箱',
15       `created` datetime NOT NULL,
16       `updated` datetime NOT NULL,
17       PRIMARY KEY (`id`),
18       UNIQUE KEY `username` (`username`) USING BTREE,
19       UNIQUE KEY `phone` (`phone`) USING BTREE,
20       UNIQUE KEY `email` (`email`) USING BTREE
21     ) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8 COMMENT='用户表'
22     """)
23     print("返回结果：%d"%result)
24 except (InternalError,ProgrammingError) as msg:
25     print("表添加出现异常，异常信息：%s"%msg)
26 finally:
27     # 4关闭游标
28     mycur.close()
29     # 5 关闭连接
30     conn.close()

三、向表中插入数据

1 from pymysql import  *
 2 # 1.建立到数据库连接
 3 conn = connect(host="192.168.20.101",user="root",password="123456",database="pydb")
 4 # 2.获取游标
 5 mycur = conn.cursor()
 6 try:
 7     # 3.开启事务
 8     conn.begin()
 9     # 4.执行插入数据，并返回影响的行数
10     rowNum1 = mycur.execute("""insert into `tb_user` values(1,'lisi','e10adc3949ba59abbe56e057f20f883e','13412345678','[e-mail]','2020-12-25 17:03:55','2020-12-25 17:03:55');""")
11     # 防止sql注入的风险，通过%s占位符，
12     rowNum2 = mycur.execute("""insert into `tb_user` values(%s,%s,%s,%s,%s,%s,%s);""",
13                             (2,'wangwu','202cb962ac59075b964b07152d234b70','13412345679','[e-mail]','2020-12-26 17:03:55','2020-12-26 17:03:55'))
14     # 在表名后添加列名
15     rowNum3 = mycur.execute("""insert into tb_user(username,password,phone,email,created,updated) values(%s,%s,%s,%s,%s,%s);""",
16                             ('zhaoliu','e10adc3949ba59abbe56e057f20f883e','13412345675','[e-mail]','2020-12-27 17:03:55','2020-12-27 17:03:55'))
17     row = rowNum1+rowNum2+rowNum3
18     print("受影响的行数：",row)
19     if row==3:
20         #提交事务
21         conn.commit()
22     else:
23         # 回滚事务
24         conn.rollback()
25 except IntegrityError as msg:
26     print("出现异常",msg)
27     # 回滚事务
28     conn.rollback()
29 finally:
30     # 关闭游标
31     mycur.close()
32     # 关闭连接
33     conn.close()

四、查询表中数据

1 from pymysql import *
 2 # 建立连接
 3 conn = connect(host="192.168.20.101",user="root",password="123456",database="pydb")
 4 # 获取游标
 5 mycur = conn.cursor()
 6 # 执行查询,并返回查询出来的数据的总行数
 7 rowNum = mycur.execute("select * from tb_user")
 8 print("共查询出 %d 行数据"%rowNum)
 9 # 获取查询出来的数据
10 results = mycur.fetchall()
11 print(type(results))
12 #遍历输出
13 for result in results:
14     # print(result)
15     print(result[0],result[1])
16 
17 #关闭游标和连接
18 mycur.close()
19 conn.close()

五、修改表中数据

1 from pymysql import *
 2 conn = connect(host="192.168.20.101",user="root",password="123456",database="pydb")
 3 mycur = conn.cursor()
 4 try:
 5     conn.begin()
 6     row = mycur.execute("update tb_user set username=%s,phone=%s,email=%s,updated=%s where id = %s",
 7                         ('zhaomin','13888889999','[e-mail]','2021-01-29 20:16:20',37))
 8     # 提交事务
 9     conn.commit()
10     if row ==1:
11         print("修改成功")
12     else:
13         print("修改失败")
14 except Exception as msg:
15     #回滚事务
16     conn.rollback()
17     print("修改失败，提示信息：",msg)
18 finally:
19     # 关闭游标
20     mycur.close()
21     # 关闭数据库连接
22     conn.close()

六、删除表中数据

1 from pymysql import *
 2 # 建立数据库连接
 3 conn = connect(host="192.168.20.101",user="root",password="123456",database="pydb")
 4 # 获取游标
 5 mycur = conn.cursor()
 6 try:
 7     # 开启事务
 8     conn.begin()
 9     # 执行删除数据
10     rowNum = mycur.execute("delete from tb_user where id = %s",(37,))
11     print("受影响的行数:",rowNum)
12     conn.commit()
13 except Exception as msg:
14     print("删除抛出异常，提示信息:",msg)
15     conn.rollback()#回滚事务
16 finally:
17     # 关闭游标和数据库连接
18     mycur.close()
19     conn.close()