pymysql操作MySQL数据库

一、查询数据库中的数据

 1 #!/usr/bin/env python
 2 # -*- coding: utf-8 -*-
 3 
 4 import pymysql
 5 
 6 #打开数据库连接
 7 conn = pymysql.connect(host="localhost", user="root", password="admin@163.com",
 8                        port=3306, db="db1")
 9 
10 #使用cursor()方法获取操作游标
11 cursor = conn.cursor()
12 
13 #查询操作
14 sql = "select * from tab1"
15 try:
16     cursor.execute(sql) #执行sql语句
17 
18     results = cursor.fetchall()  #获取查询的所有记录
19     print("id", "name", "email")
20 
21     #遍历结果
22     for row in results:
23         id = row[0]
24         name = row[1]
25         email = row[2]
26         print(id, name, email)
27 except Exception as e:
28     raise e
29 
30 finally:
31     cursor.close()  #关闭游标
32     conn.close()  #关闭连接

selectDB.py

二、向数据库中插入数据

 1 #!/usr/bin/env python
 2 # -*- coding: utf-8 -*-
 3 import pymysql
 4 
 5 #连接数据库
 6 conn = pymysql.connect(host="localhost", user="root", password="admin@163.com",
 7                        port=3306, db="db1")
 8 
 9 #使用cursor()方法获取游标
10 cursor = conn.cursor()
11 
12 sql_insert = """insert into tab1(nid, name, email) values (4, 'tt', 'tt@163.com')"""
13 
14 try:
15     cursor.execute(sql_insert)
16 
17     #提交数据
18     conn.commit()
19 
20 except Exception as e:
21     #错误回滚
22     conn.rollback()
23 
24 finally:
25     cursor.close()
26     conn.close()

insertDB.py

三、更新数据库中的数据

 1 #!/usr/bin/env python
 2 # -*- coding: utf-8 -*-
 3 import pymysql
 4 
 5 #打开数据库连接
 6 conn = pymysql.connect(host="localhost", user="root", password="admin@163.com",
 7                        port=3306, db="db1")
 8 #使用cursor()方法获取操作游标
 9 cursor = conn.cursor()
10 # 更新操作
11 sql_update = """update tab1 set email='yusheng_liang08@163.com' where nid='1'"""
12 
13 try:
14     cursor.execute(sql_update)
15 
16     conn.commit()
17 
18 except Exception as e:
19     conn.rollback()
20 
21 finally:
22     cursor.close()
23     conn.close()

updateDB.py

四、删除数据库中的数据

 1 #!/usr/bin/env python
 2 # -*- coding: utf-8 -*-
 3 import pymysql
 4 
 5 #打开数据库连接
 6 conn = pymysql.connect(host="localhost", user="root", password="admin@163.com",
 7                      port=3306, db="db1")
 8 #使用cursor()方法获取操作游标
 9 cursor = conn.cursor()
10 #删除操作
11 sql_delete = "delete from tab1 where name='tt'"
12 try:
13 
14     cursor.execute(sql_delete)
15     conn.commit()  #提交数据
16 
17 except Exception as e:
18     conn.rollback()
19 
20 finally:
21     cursor.close()
22     conn.close()

deleteDB.py