三、Python操作数据库核心API

Python的sqlite3模块

Python内置了SQLite数据库，通过内置的sqlite3模块可以直接访问数据库。

sqlite3提供的Python程序上遵守Python DB-API规范。

Python DB-API是为不同的数据库提供的访问接口规范。

该接口定义了一系列必需的对象和数据库存取方式，以便为各种底层数据库系统和多样的数据库接口程序提供一致的访问接口。

下面是sqlite3模块中的部分常量、函数或对象。

（1）sqlite3.version：常量，返回sqlite3模块的版本号。

（2）sqlite3.sqlite_version：常量，返回sqlite数据库的版本号。

（3）sqlite3.Connection ：数据库连接对象。

（4）sqlite3.Cursor：游标对象。

（5）sqlite3.Row：行对象。

（6）sqlite3.connect(dbname)：函数，链接到数据库，返回Connection对象。

数据库文件的扩展名为.db。

（一）Python DB-API核心类和方法

1、Connection类

close()：关闭连接；

commit()：提交当前所有事务；

rollback()：回滚当前事务；

cursor()：返回一个使用该连接的游标对象；

row_factory：指定行的类型，默认为空。

2、Row 类

keys()：获取行的所有键。

3、数据库模块（例如sqlite3）

connect()：获取数据库的连接；

paramstyle：参数样式；

4、Cursor类

close()：关闭游标；

execute(sql)：执行 sql 语句；

executemany(sql, datas)：重复执行sql语句；

fetchall()：获取结果集中所有行；

fetchone()：获取结果集中一行；

fetchmany(size)：获取结果集中多行；

description：游标的描述信息；

rowcount：获取受影响的行数；

arraysize：指定一次获取多少行；

在线文档：https://www.python.org/dev/peps/pep-0249/

（二）Python操作SQLite流程

（1）导入模块：sqlite3

（2）连接数据库得到Connection对象：sqlite3. connect(文件名)

（3）获取Cursor对象：Connection对象.cursor()

（4）执行数据库的增删查改操作：Cursor对象.execute(sql语句)等

（5）提交数据库操作： Connection对象. commit()

（6）关闭Cursor：Cursor对象.close()

（7）关闭Connection：Connection对象.close()

例：

def insert_data(items):

    connect = sqlite3.connect("school.db")  # 连接数据库    

   cursor = connect.cursor()  # 得到游标    

    insert_sql = """  insert into school (school_code, school, province,    

                     is_985, is_211, is_self_marking, school_type)                 

                     values(?, ?, ?, ?, ?, ?, ?) """

   for item in items:

         cursor.execute(insert_sql, item)

         connect.commit()

def insert_many(items):

     connect = sqlite3.connect("school.db")   # 连接数据库    

    cursor = connect.cursor()  # 得到游标   

    insert_sql = """insert into school (school_code, school, province,   

                    is_985, is_211, is_self_marking, school_type)

                    values(?, ?, ?, ?, ?, ?, ?)"""

   cursor.executemany(insert_sql, items)

   connect.commit()

例：先定义三个函数：查询、修改、删除，并利用它们完成下面任务：

查询所有既是211又是985的学校，并打印出来。

将所有的“西藏自治区”改为“西藏”。

将所有不是以“大学”或“学院”结尾 的记录删除。

def query_data(sql):

    connect = sqlite3.connect("school.db")  # 连接数据库

    cursor = connect.cursor()  # 得到游标    

    cursor.execute(sql)    

   results = cursor.fetchall()    

   connect.commit()  # 提交查询    

   for item in results:        

        print(item)

def update_data(sql):

      connect = sqlite3.connect("school.db")  # 连接数据库

     cursor = connect.cursor()  # 得到游标

    update_num = cursor.execute(sql).rowcount

   connect.commit()  # 提交查询

   print("更新了", update_num, "条记录")

def delete_data(sql):

      connect = sqlite3.connect("school.db")  # 连接数据库、

      cursor = connect.cursor()  # 得到游标

     delete_num = cursor.execute(sql).rowcount

     connect.commit()  # 提交查询

    print("删除了", delete_num, "条记录")

#（1）查询所有既是211又是985的学校，并打印出来

query_data("select * from school where is_211='是' and is_985='是' ")

#（2）将所有的“西藏自治区”改为“西藏”

update_data("update school set province='西藏' where province='西藏自治区'")

#（3）将所有不是以“大学”或“学院”结尾 的记录删除

delete_data("delete from school where school not like '%大学' and school not like '%学院'")

例： 使用sqlite3模块创建数据库 managedb，并在其中创建表goods，表中包含id、name、gnumber、price等4列，其中id为主键（primary key）。

import sqlite3               # 导入sqlite3模块

dbstr="d:/sqlite/managedb.db"  

con=sqlite3.connect(dbstr) #创建 sqlite数据库

stmt="create table goods(id int primarykey,name,gnumber integer(2),price )"

con.execute(stmt)

数据库的插入、更新和删除操作

在数中插入、更新、删除记录的一般步骤如下。

（1）建立数据库连接。

（2）创建游标对象cur，使用cur.execute(sql)方法执行SQL的insert、update、delete等语句，完成数据库记录录的插入、更新、删除操作，并根据返回值判断操作结果。

（3）提交操作。

（4）关闭数据库。

例：在goods表中完成记录的插入、更新和刪除操作。

练习：参照上面程序，完成任务要求：插入、更新和刪除操作。