Python基础之SQLite数据库

Python与SQLite数据库

一、概述

对于非常简单的应用而言，使用文件作为持久化存储通常就足够了，但是大多数复杂的数据驱动的应用则需要全功能的关系数据库。 SQLite 的目标则是介于两者之间的中小系统。它量级轻、速度快，没有服务器，很少或不需要进行管理。虽然简单，但是由于各种原因这个库很有吸引力。首先，它不需要独立的数据库服务器，也不要求任何特定的配置，只要与数据库文件连接，就可以在程序中立即开始使用（如果它不存在，会生成一个新文件）。这个数据库也支持用于改进可靠性的事务处理（甚至在系统崩溃期间也可以），还支持lock，允许多个进程同时访问同一个数据库文件。

SQLite 正在迅速流行起来，并且它还适用于不同的平台。 Python 2.5 中引入了 SQLite 数据库适配器作为 sqlite3 模块，这是 Python 首次将数据库适配器纳入到标准库当中。

SQLite 被打包在 Python 中，并不是因为它比其他数据库和适配器更加流行，而是因为它足够简单，像 DBM 模块一样使用文件（或内存）作为其后端存储，不需要服务器，也没有许可证问题。它是 Python 中其他类似的持久化存储解决方案的一个替代品，不过除此之外，它还拥有 SQL 接口。

在标准库中拥有该模块，可以使你在 Python 中使用 SQLite 开发更加快速，并且使你在有需要时，能够更加容易地移植到更加强大的 RDBMS（比如， MySQL、 PostgreSQL、Oracle 或 SQL Server）中。如果你并不需要那些强大的数据库，那么 sqlite3 已经是一个很好的选择了。

尽管标准库中已经提供了该数据库适配器，但是你还需要自己下载这个数据库本身。当安装数据库后，就可以启动 Python（并导入适配器模块）来直接进行访问了：

二、模块级函数

下列函数由sqlite3模块定义：

• connect(database [, timeout [, isolation_level [, detect_types]]])
  创建到SQLite数据库的连接。database是指定数据库文件名称的字符串。也可以是一个字符串":memory:", 此时使用内存中的数据库（注意这种数据库仅在python进程运行时存在，并在程序退出的时候丢失）。timeout参数指明，当其他连接在更新数据库时，等待内部的读写锁（reader-writer lock)释放的时间。默认情况下，timeout是5秒钟。但是INSERT或UPDATE等SQL语句时，如果一个事务没有生效，则自动开始一个新的事务。isolation_level参数是一个字符串，为用于开始这一事务底层SQL BEGIN语句提供可选修饰符，其含义与底层数据库锁相关。

  

  当返回结果时，detect_types参数可实现某些额外类型的检测（通过对SQL查询的额外解析实现），默认值是0（意味着没有额外检测）。可以将其设置为按位，或者PARSE_DECLTYPES和PARSE_COLNAMES。如果PARSE_DECLTYPES启用，审核查询SQL类型名称（如"integer"或"number(8)"）以便确定结果列的类型。 如果PARSE_COLNAMES启用，"colname[typename]"形式的特定字符串（包括双引号）可以嵌入查询，colname是列名称，typename是通过register_converter()函数注册的类型名称。这些字符串在传递到SQLite3引擎时仅转换成colname，但是当转换查询结果中的值时，将使用额外类型的说明符。例如，

  select price as "price [decimal]" from portfolio 
  

  会被解释为:

  select price as price from portfolio
  

  其结果将根据decimal转换规则进行转换。

• register_converter(typename, func)
  注册新的类型名称，共connect()的detect_types选项使用。typename是将在查询中使用的包含类型名的字符串，func是一个函数，它接受单一的字节字符串作为输入，结果返回一个python数据类型。

  例如，如果调用sqlite3.register_converter(‘decimal’, decimal.Decimal), 那么可以通过写入查询（如 select price as “price [decimal]” from stocks）将查询中的值转换成Decimal对象。

• register_adapter(type, func)
  为python类型type（用于在数据类型中存储该类型的值）注册一个适配器函数。func是一个函数，接受type类型的实例作为输入，结果返回一个int、float、utf-8编码的字节串、unicode字符串或者buffer。例如，如果要存储decimal对象，可以使用sqlite3.register_adapter(decimal.Decimal, float)

• complete_statement(s)
  如果字符串s代表由分号分隔的一个或一个以上的完整SQL语句，则返回true。这也许会在编写读取来自用户的查询的交互式程序时用到。

• enable_callback_tracebacks(flag)
  处理在转换器和适配器等用户定义回调函数中出现的异常。默认情况下，忽略异常。如果flag设置为true，回溯消息将输出到sys.stderr。

三、连接对象

由connect()函数返回的Connection对象c支持在数据库API描述的标准操作。此外，针对sqlite3模块提供下列方法。

• c.create_function(name, num_params, func)
  创建能够炸sql语句中使用的用户定义函数。name是包含函数名称的字符串，num_params是指明参数数量的整数，func是提供实现的python函数，这里有一个简单的例子：

  def toupper(s):
    return s.upper()
  
  c.create_function("toupper", 1, toupper)
  
  查询使用样例：
  c.execute("select toupper(name), foo, bar from sometable")
  

  虽然定义了一个python函数，但是函数的参数和函数的输入只能是int、float、str、unicode buffer或none

• c.create_aggregate(name, num_params, aggregate_class)
  创建可在sql语句中使用的用户定义聚合函数。name是包含函数名称的字符串，num_params是一个整数，说明输入的参数数量。aggregate_class是执行聚合操作的类。这个类必须支持不带参数的初始化，执行接受与num_params指明的参数数量相同的step(params)方法，并执行finalize()方法返回最后结果。这里有一个简单例子：

  class Averager():
    def __init__(self):
      self.total = 0.0
      self.count = 0
  
    def step(self, value):
      self.total += value
      self.count += 1
  
    def finalize(self):
      return self.total / self.count
  
  c.create_aggregate("myavg", 1, Averager)
  
  c.execute("select myavg(num) from sometable")
  

  通过重复调用带输入值的step()方法进行聚合，然后调用finalize()获得最终值。

• c.create_collation(name, func)
  注册可在SQL语句中使用的用户定义校验函数。name是包含校验函数名称的字符串，func是一个函数，它接受两个输入，并根据第一个输入是小于、等于还是大于第二个输入来返回-1, 0或者1。你可以通过sql表达式（例如， “select * from table order by colname collate name”）来使用这个用户定义函数。

• c.execute(sql [, params])
  使用c.cursor()创建游标对象，并通过params参数和sql中的sql语句执行游标execute()方法的快捷方法。

• c.executemany(sql, [, params])
  使用c.cursor()创建游标对象，并通过params参数和sql中的sql语句执行游标的executemany()方法的快捷方法。

• c.executescript(sql)
  使用c.cursor()创建游标对象，并通过sql中的sql语句执行游标的executescript()方法。

• c.interrupt()
  终止正在连接上执行的查询。也就说从一个单独的线程调用。

• c.iterdump()
  返回一个迭代器，以便将整个数据库的内容转储到一系列sql语句中，执行这些语句能够重建数据库。这可以用于任何需要导出数据库、或者需要将内存数据库转储到一个文件以便稍后进行恢复的情况。

• c.set_authorizer(auth_callback)
  注册一个授权回调函数。它在每次访问数据中的一列数据时执行。这个回调函数必须接受5个参数，即auth_callback(code, arg1, arg2, dbname, innername)。如果允许访问，这个回调函数返回的值是SQLITE_OK, 如果SQL语句由于出错而失败，返回值是SQLITE_DENY, 如果列被处理成NULL值而忽略，返回值是SQLITE_IGNORE。第一个参数code是整数操作代码。arg1和arg2是参数，它们的值取决于code。dbname是包含数据库名的字符串（通常是"main"), innername是正在尝试访问的最内部视图或触发器的名称，如果没有视图或触发器处于激活时为None。下面的表格列出了code的值和arg1与arg2参数的含义：
  

  

• c.set_progress_handler(handler, n)
  注册回调函数，每n条sqlite虚拟机指令执行一次。handler是一个没有参数的函数。

下列属性也在连接对象上定义：

• c.row_factory
  调用这个函数来创建代表每个结果行的对象。这个函数接受两个参数，用来获得结果的游标对象和带有原始结果行的元组

• c.text_factory
  调用这个函数来创建代表数据库中文本值的对象。这个函数只能接受utf-8编码的字节字符串参数。返回值应该是某种类型的字符串。默认情况下返回unicode字符串

• c.total_changes
  一个整数，代表自数据库连接打开后发生更改的行数。

连接对象的最后一个功能是，它们可以与上下文管理器协议一起使用，以便自动处理事务。例如:

conn = sqlite.connect('somedb')
with conn:
  conn.execute('insert into sometable values (?, ?)', ('foo', 'bar'))

这个例子中，commit()是在所有with数据块中的语句执行完毕并且没有错误之后自动执行的，如果出现任何异常，将执行rollback()操作，再次提示异常。

四、游标和基本操作

为了在sqlite3数据库上执行基本操作，首先必须使用cursor()方法在一个连接上创建游标对象。然后使用该游标的execute(), executemany()或者executescript()方法执行sql语句。有关这些方法的常用操作，此处不再赘述。这一部分主要是展示一组常用的数据库用例和编码样例。

• 创建新的数据库表
  下面的代码显示如何打开一个数据库并创建一个新的表：

  import sqlite3
  conn = sqlite3.connect("mydb")
  cur = conn.cursor()
  cur.execute("create table stocks (symbol text, shares integer, price real)")
  conn.commit()
  

  当定义表的时候，应使用一些原始的sqlite数据类型：text、integer、real和blob。blob类型是一个字节字符串，而text类型是utf-8编码的unicode

• 向表中插入新的值
  下列代码示意了如何向表中插入新的项

  import sqlite3
  conn = sqlite3.connect("mydb")
  cur = conn.cursor()
  cur.execute("insert into stocks values (?, ?, ?)", ('IBM', 50, 91.10))
  conn.commit()
  

  当插入值时，应当一直像上面那样使用?替代。每个？用参数元组中的值一个值代替。
  如果需要按顺序插入数据，可以像这样使用游标的executemany()方法：

  stocks = [
    ('aaa', 12, 1.1),
    ('bbb', 23, 2.2),
    ('ccc', 34, 3.3)]
  cur.executemany("insert into stocks values (?, ?, ?)", stocks)
  

• 更新现有行
  下面的代码示意了如何为现有行更新列值：

  cur.execute("update stocks set shares=? where symbol=?", (50, 'IBM'))
  

• 删除行

  cur.execute("delete from stocks where symbol=?", ('MICROSOFT', ))
  

• 执行基本查询
  下面的代码显示如何执行基本查询并获得结果：

  # 选择表中所有的列
  for row in cur.execute("select * from stocks"):
    statementes
  
  # 选择若干列
  for shares, price in cur.execute("select shares, price from stocks"):
    statements
  
  # 选择匹配行
  for row in cur.execute("select * from stocks order by shares"):
    statements
  
  # 逆向选择匹配行
  for row in cur.execute("select * from stocks order by shares desc")
    statements
  
  # 以通用列名(符号)联接表
  for row in cur.execute('''select s.symbol, s.shares, p.price 
                            from stocks as s, prices as p using(symbol)''')
    statements
  

小手一抖，点个赞再走哦~~