一、实验目的和要求
1、学会数据库编程接口;
2、学会使用SQLite;
3、学会使用MySQL。
二、实验环境
软件版本:Python 3.10 64_bit
三、实验过程
1、实例1:创建SQLite数据库文件
创建一个mrsoft.db的数据库文件,然后执行SQL语句可创建一个 user(用户表),user表包含id和name两个字段,代码如下:
![复制代码](https://common.cnblogs.com/images/copycode.gif)
![复制代码](https://common.cnblogs.com/images/copycode.gif)
1 import sqlite3 2 # 连接到SQLite数据库 3 # 数据库文件是mrsoft.db,如果文件不存在,会自动在当前目录创建 4 conn = sqlite3.connect('mrsoft.db') 5 # 创建一个Cursor 6 cursor = conn.cursor() 7 # 执行一条SQL语句,创建user表 8 cursor.execute("create table user (id int(10) primary key, name varchar(29))") 9 # 关闭游标 10 cursor.close() 11 # 关闭Connection 12 conn.close()
![复制代码](https://common.cnblogs.com/images/copycode.gif)
![复制代码](https://common.cnblogs.com/images/copycode.gif)
2、实例2:新增用户数据信息
由于在实例1中已经创建了user表,所以本实例可以直接操作user表,向user表中插入3条用户信息。此外,由于是新增数据,需要使用commit()方法提交事务。因为对于增加、修改和删除操作,使用commit()方法提交事务后,如果相应操作失败,可以使用rollback()方法回滚到操作之前的状态。新增用户数据信息的代码如下:
![复制代码](https://common.cnblogs.com/images/copycode.gif)
![复制代码](https://common.cnblogs.com/images/copycode.gif)
1 import sqlite3 2 # 连接到sQlite数据库 3 # 数据库文件是mrsoft.db 4 # 如果文件不存在,会自动在当前目录创建 5 conn = sqlite3.connect('mrsoft.db') 6 # 创建一个Cursor 7 cursor = conn.cursor() 8 #执行一条SQL语句,插入一条记录 9 cursor.execute('insert into user (id, name) values ("1", "MRSOFT")') 10 cursor.execute('insert into user (id, name) values ("2","Andy")') 11 cursor.execute('insert into user (id, name) values ("3","明日科技小助手")') 12 # 关闭游标 13 cursor.close() 14 # 提交事务 15 conn.commit() 16 # 关闭Connection 17 conn.close()
![复制代码](https://common.cnblogs.com/images/copycode.gif)
![复制代码](https://common.cnblogs.com/images/copycode.gif)
3、实例3:使用3种方法查询用户数据信息
(1)分别使用fetchone、fetchmany和fetchall这3种方式查询用户信息,具体代码如下:
![复制代码](https://common.cnblogs.com/images/copycode.gif)
![复制代码](https://common.cnblogs.com/images/copycode.gif)
1 import sqlite3 2 # 连接到SQLite数据库,数据库文件是mrsoft.db 3 conn =sqlite3.connect("mrsoft.db") 4 # 创建一个Cursor 5 cursor =conn.cursor() 6 #执行查询语句 7 cursor.execute("select * from user where id > ?",(0,)) 8 # 获取查询结果 9 result1 = cursor.fetchall() 10 print(result1) 11 # 关闭游标 12 cursor.close() 13 # 关闭Connection 14 conn.close()
![复制代码](https://common.cnblogs.com/images/copycode.gif)
![复制代码](https://common.cnblogs.com/images/copycode.gif)
(2)运行结果如图所示:
4、实例4:修改用户数据信息
(1)将SQLite数据库中user表ID为1的数据name字段值“mrsoft”修改为“MR”,并使用fetchAll获取表中的所有数据。具体代码如下:
![复制代码](https://common.cnblogs.com/images/copycode.gif)
![复制代码](https://common.cnblogs.com/images/copycode.gif)
1 import sqlite3 2 # 连接到sQLite数据库,数据库文件是mrsoft.db 3 conn = sqlite3.connect('mrsoft.db') 4 # 创建一个Cursor: 5 cursor =conn.cursor() 6 cursor.execute("update user set name = ? where id = ?",("MR",1)) 7 cursor.execute("select * from user") 8 result = cursor.fetchall() 9 print(result) 10 # 关闭游标 11 cursor.close() 12 # 提交事务 13 conn.commit() 14 # 关闭Connection: 15 conn.close()
![复制代码](https://common.cnblogs.com/images/copycode.gif)
![复制代码](https://common.cnblogs.com/images/copycode.gif)
(2)运行结果如图所示:
5、实例5:删除用户数据信息
(1)将SQLite数据库中user表ID为1,2,3的数据删除,并使用fetchAll获取表中所有数据,查看删除后的结果。具体代码如下:
![复制代码](https://common.cnblogs.com/images/copycode.gif)
![复制代码](https://common.cnblogs.com/images/copycode.gif)
1 import sqlite3 2 # 连接到SQLite数据库,数据库文件是mrsoft.db 3 conn = sqlite3.connect("mrsoft.db") 4 # 创建一个Cursor: 5 cursor = conn.cursor() 6 cursor.execute("delete from user where id = ?",(1,)) 7 cursor.execute("delete from user where id = ?",(2,)) 8 cursor.execute("delete from user where id = ?",(3,)) 9 cursor.execute("select * from user") 10 result = cursor.fetchall() 11 print(result) 12 # 关闭游标 13 cursor.close() 14 # 提交事务 15 conn.commit() 16 # 关闭Connection: 17 conn.close()
![复制代码](https://common.cnblogs.com/images/copycode.gif)
![复制代码](https://common.cnblogs.com/images/copycode.gif)
(2)运行结果如图所示:
6、实例6:使用PyMySQL连接数据库
(1)前面我们已经创建了一个MySQL连接“studyPython”,并且在安装数据库时设置了数据库的用户名“root”和密码“root”。下面通过connect()方法接MySQL数据库mrsoft,具体代码如下:
![复制代码](https://common.cnblogs.com/images/copycode.gif)
![复制代码](https://common.cnblogs.com/images/copycode.gif)
1 import pymysql 2 # 打开数据库连接,host:主机名或IP;user:用户名;password:密码;database:数据库名称 3 db = pymysql.connect(host="localhost",user= "root",password= "root",database= "mrsoft") 4 #使用cursor()方法创建一个游标对象cursor 5 cursor=db.cursor() 6 # 使用execute()方法执行SQL查询 7 cursor.execute("SELECT VERSION()") 8 # 使用fetchone()方法获取单条数据 9 data=cursor.fetchone() 10 print("Database version : %s " % data) 11 # 关闭数据库链接 12 db.close()
![复制代码](https://common.cnblogs.com/images/copycode.gif)
![复制代码](https://common.cnblogs.com/images/copycode.gif)
(2)运行结果如图所示:
7、实例7:创建books图书表
(1)具体代码如下:
![复制代码](https://common.cnblogs.com/images/copycode.gif)
![复制代码](https://common.cnblogs.com/images/copycode.gif)
1 import pymysql 2 #打开数据库连接 3 db=pymysql.connect(host="localhost",user= "root",password= "root",database= "mrsoft") 4 # 使用cursor()方法创建一个游标对象cursor 5 cursor = db.cursor() 6 # 使用execute()方法执行SQL,如果表存在则删除 7 cursor.execute("DROP TABLE IF EXISTS books") 8 # 使用预处理语句创建表 9 sql = """ 10 CREATE TABLE books ( 11 id int(8) NOT NULL AUTO_INCREMENT, 12 name varchar(50) NOT NULL, 13 category varchar(50) NOT NULL, 14 price decimal(10,2) DEFAULT NULL, 15 publish_time date DEFAULT NULL, 16 PRIMARY KEY (id) 17 ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; 18 """ 19 # 执行SQL语句 20 cursor.execute(sql) 21 #关闭数据库连接 22 db.close()
![复制代码](https://common.cnblogs.com/images/copycode.gif)
![复制代码](https://common.cnblogs.com/images/copycode.gif)
(2)运行结果如图所示:
8、实例8:向books图书表添加图书数据
具体代码如下:
![复制代码](https://common.cnblogs.com/images/copycode.gif)
![复制代码](https://common.cnblogs.com/images/copycode.gif)
1 import pymysql 2 # 打开数据库连接 3 db=pymysql.connect(host="localhost",user="root",password="root",database="mrsoft",charset="utf8") 4 # 使用cursor()方法获取操作游标 5 cursor =db.cursor() 6 # 数据列表 7 data=[("零基础学Python","Python","79.80","2018-5-20"), 8 ("Python从入门到精通","Python","69.80","2018-6-18"), 9 ("零基础学PHP","PHP","69.80","2017-5-21"), 10 ("PHP项目开发实战入门","PHP","79.80","2016-5-21"), 11 ("零基础学]ava","Java","69.80","2017-5-21"), 12 ] 13 try: 14 #执行sq1语句,插入多条数据 15 cursor.executemany("insert into books(name, category, price, publish_time) values (%s,%s,%s,%s)",data) 16 # 提交数据 17 db.commit() 18 except: 19 # 发生错误时回滚 20 db.rollback() 21 # 关闭数据库连接 22 db.close()
![复制代码](https://common.cnblogs.com/images/copycode.gif)
![复制代码](https://common.cnblogs.com/images/copycode.gif)
(2)运行结果如图所示:
9、实战一:获取指定数据表中的信息
(1)打印MySQL中books表图书的名称和价格,代码如下:
![复制代码](https://common.cnblogs.com/images/copycode.gif)
![复制代码](https://common.cnblogs.com/images/copycode.gif)
1 import pymysql 2 db = pymysql.connect(host="localhost",user= "root",password= "root",database= "mrsoft") 3 cursor = db.cursor() 4 cursor.execute("select name,price from books") 5 result = cursor.fetchall() 6 for i in range(0,len(result)): 7 print("图书:《" + str(result[i][0]) + "》,价格:¥" + str(result[i][1]) + "元") 8 cursor.close() 9 db.close()
![复制代码](https://common.cnblogs.com/images/copycode.gif)
![复制代码](https://common.cnblogs.com/images/copycode.gif)
(2)运行结果如图所示:
10、实战二:查找指定年份之后的图书信息
(1)查询MySQL中books表图书价格小于70元并且为2017年以后出版的所有图书,代码如下:
![复制代码](https://common.cnblogs.com/images/copycode.gif)
![复制代码](https://common.cnblogs.com/images/copycode.gif)
1 import pymysql 2 db = pymysql.connect(host="localhost",user= "root",password= "root",database= "mrsoft") 3 cursor = db.cursor() 4 cursor.execute("select name,price,publish_time from books where price < 70 and publish_time >= '2017-01-01'") 5 result = cursor.fetchall() 6 for i in range(0,len(result)): 7 print("图书:《" + str(result[i][0]) + "》,价格:¥" + str(result[i][1]) + "元,出版日期:" + str(result[i][2])) 8 cursor.close() 9 db.close()
![复制代码](https://common.cnblogs.com/images/copycode.gif)
![复制代码](https://common.cnblogs.com/images/copycode.gif)
(2)运行结果如图所示:
11、实战三:批量删除指定的图书信息
(1)删除MySQL中books表所有分类为PHP的图书,删除完成后查看所有图书,代码如下:
![复制代码](https://common.cnblogs.com/images/copycode.gif)
![复制代码](https://common.cnblogs.com/images/copycode.gif)
1 import pymysql 2 db = pymysql.connect(host="localhost",user= "root",password= "root",database= "mrsoft") 3 cursor = db.cursor() 4 cursor.execute("delete from books where category = 'PHP'") 5 cursor.execute("select name,price from books") 6 result = cursor.fetchall() 7 for i in range(0,len(result)): 8 print("图书:《" + str(result[i][0]) + "》,价格:¥" + str(result[i][1]) + "元") 9 cursor.close() 10 db.close()
![复制代码](https://common.cnblogs.com/images/copycode.gif)
![复制代码](https://common.cnblogs.com/images/copycode.gif)
(2)运行结果如图所示: