第十一章 使用Python操作数据库

实例01：创建SQLite数据库文件

 　　创建一个mrsoft.db的数据库文件，然后执行SQL语句可创建一个 user(用户表)，user表包含id和name两个字段。

 

代码如下：

import sqlite3
# 连接到SQLite数据库
# 数据库文件是mrsoft.db，如果文件不存在，会自动在当前目录创建
conn = sqlite3.connect('mrsoft.db')
# 创建一个Cursor
cursor = conn.cursor()
# 执行一条SQL语句，创建user表
cursor.execute("create table user (id int(10) primary key, name varchar(29))")
# 关闭游标
cursor.close()
# 关闭Connection
conn.close()

 

 

实例02：新增用户数据信息

 　　由于在实例1中已经创建了user表，所以本实例可以直接操作user表，向user表中插入3条用户信息。此外，由于是新增数据，需要使用commit()方法提交事务。因为对于增加、修改和删除操作，使用commit()方法提交事务后，如果相应操作失败，可以使用rollback()方法回滚到操作之前的状态。

 

代码如下：

import sqlite3
# 连接到sQlite数据库
# 数据库文件是mrsoft.db
# 如果文件不存在，会自动在当前目录创建
conn = sqlite3.connect('mrsoft.db')
# 创建一个Cursor
cursor = conn.cursor()
#执行一条SQL语句，插入一条记录
cursor.execute('insert into user (id, name) values ("1", "MRSOFT")')
cursor.execute('insert into user (id, name) values ("2","Andy")')
cursor.execute('insert into user (id, name) values ("3","明日科技小助手")')
# 关闭游标
cursor.close()
# 提交事务
conn.commit()
# 关闭Connection
conn.close()

 

 

 

 

 

 

实例03：使用3种方法查询用户数据信息

　　分别使用fetchone、fetchmany和fetchall这3种方式查询用户信息，

 

代码如下：

import sqlite3
# 连接到SQLite数据库,数据库文件是mrsoft.db
conn =sqlite3.connect("mrsoft.db")
# 创建一个Cursor
cursor =conn.cursor()
#执行查询语句
cursor.execute("select * from user where id > ?",(0,))
# 获取查询结果
result1 = cursor.fetchall()
print(result1)
# 关闭游标
cursor.close()
# 关闭Connection
conn.close()

 

 

 运行结果如下：

 

 

 

 

 

实例04：修改用户数据信息

　　将SQLite数据库中user表ID为1的数据name字段值“mrsoft”修改为“MR”，并使用fetchAll获取表中的所有数据。

 

代码如下：

import sqlite3
# 连接到sQLite数据库，数据库文件是mrsoft.db
conn = sqlite3.connect('mrsoft.db')
# 创建一个Cursor:
cursor =conn.cursor()
cursor.execute("update user set name = ? where id = ?",("MR",1))
cursor.execute("select * from user")
result = cursor.fetchall()
print(result)
# 关闭游标
cursor.close()
# 提交事务
conn.commit()
# 关闭Connection:
conn.close()

 

 

 运行结果如下：

 

 

 

 

 

实例05：删除用户数据信息

　　将SQLite数据库中user表ID为1，2，3的数据删除，并使用fetchAll获取表中所有数据，查看删除后的结果。

 

代码如下：

import sqlite3
# 连接到SQLite数据库，数据库文件是mrsoft.db
conn = sqlite3.connect("mrsoft.db")
# 创建一个Cursor:
cursor = conn.cursor()
cursor.execute("delete from user where id = ?",(1,))
cursor.execute("delete from user where id = ?",(2,))
cursor.execute("delete from user where id = ?",(3,))
cursor.execute("select * from user")
result = cursor.fetchall()
print(result)
# 关闭游标
cursor.close()
# 提交事务
conn.commit()
# 关闭Connection:
conn.close()

 

 

 运行结果如下：

 

 

 

 

 

实例06：使用PyMySQL连接数据库

　　前面我们已经创建了一个MySQL连接“studyPython”，并且在安装数据库时设置了数据库的用户名“root”和密码“root”。下面通过connect()方法接MySQL数据库mrsoft

 

代码如下：

import pymysql
# 打开数据库连接,host:主机名或IP;user:用户名;password:密码;database:数据库名称
db = pymysql.connect(host="localhost",user= "root",password= "root",database= "mrsoft")
#使用cursor()方法创建一个游标对象cursor
cursor=db.cursor()
# 使用execute()方法执行SQL查询
cursor.execute("SELECT VERSION()")
# 使用fetchone()方法获取单条数据
data=cursor.fetchone()
print("Database version : %s " % data)
# 关闭数据库链接
db.close()

 

 

 运行结果如下：

 

 

 

实例07：创建books图书表

代码如下：

import pymysql
#打开数据库连接
db=pymysql.connect(host="localhost",user= "root",password= "root",database= "mrsoft")
# 使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()
# 使用execute()方法执行SQL，如果表存在则删除
cursor.execute("DROP TABLE IF EXISTS books")
# 使用预处理语句创建表
sql = """
CREATE TABLE books (
  id int(8) NOT NULL AUTO_INCREMENT,
  name varchar(50) NOT NULL,
  category varchar(50) NOT NULL,
  price decimal(10,2) DEFAULT NULL,
  publish_time date DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
"""
# 执行SQL语句
cursor.execute(sql)
#关闭数据库连接
db.close()

 

 

 运行结果如下：

 

 

 

 

 

实例08：向books图书表添加图书数据

代码如下：

import pymysql
# 打开数据库连接
db=pymysql.connect(host="localhost",user="root",password="root",database="mrsoft",charset="utf8")
# 使用cursor()方法获取操作游标
cursor =db.cursor()
# 数据列表
data=[("零基础学Python","Python","79.80","2018-5-20"),
      ("Python从入门到精通","Python","69.80","2018-6-18"),
      ("零基础学PHP","PHP","69.80","2017-5-21"),
      ("PHP项目开发实战入门","PHP","79.80","2016-5-21"),
      ("零基础学]ava","Java","69.80","2017-5-21"),
      ]
try:
    #执行sq1语句，插入多条数据
    cursor.executemany("insert into books(name, category, price, publish_time) values (%s,%s,%s,%s)",data)
# 提交数据
    db.commit()
except:
    # 发生错误时回滚
    db.rollback()
    # 关闭数据库连接
    db.close()

 

 

 运行结果如下：

 

 

 

 

 

实战一：获取指定数据表中的信息

　　打印MySQL中books表图书馆的名称和价格

 

代码如下：

import pymysql
db = pymysql.connect(host="localhost",user= "root",password= "root",database= "mrsoft")
cursor = db.cursor()
cursor.execute("select name,price from books")
result = cursor.fetchall()
for i in range(0,len(result)):
    print("图书：《" + str(result[i][0]) + "》，价格：￥" + str(result[i][1]) + "元")
cursor.close()
db.close()

 

 

 运行结果如下：

 

 

 

实战二：查找指定年份后的图书信息

　　查询MySQL中books表图书价格小于70元并且为2017年以后出版的所有图书效果

 

代码如下：

import pymysql
db = pymysql.connect(host="localhost",user= "root",password= "root",database= "mrsoft")
cursor = db.cursor()
cursor.execute("select name,price,publish_time from books where price < 70 and publish_time >= '2017-01-01'")
result = cursor.fetchall()
for i in range(0,len(result)):
    print("图书：《" + str(result[i][0]) + "》，价格：￥" + str(result[i][1]) + "元，出版日期：" + str(result[i][2]))
cursor.close()
db.close()

 

 

 运行结果如下：

 

 

 

 

 

 

 

实战三：批量删除指定的图书信息

　　删除MySQL中books表所有分类为PHP的图书，删除完成后查看所有图书

 

代码如下：

import pymysql
db = pymysql.connect(host="localhost",user= "root",password= "root",database= "mrsoft")
cursor = db.cursor()
cursor.execute("delete from books where category = 'PHP'")
cursor.execute("select name,price from books")
result = cursor.fetchall()
for i in range(0,len(result)):
    print("图书：《" + str(result[i][0]) + "》，价格：￥" + str(result[i][1]) + "元")
cursor.close()
db.close()

 

 

 运行结果如下：