转:pysqlite笔记

这是一篇老笔记，原来是放在旧博客上 的，最近因为公司内部一个小东西，想使用简单点的数据库来存储数据，就想起用SQLite来做，上网搜索一些教程。竟然发现，原来一年多前，我也学过一阵 子，可惜因为不常用，现在已经基本忘记光光了，加上自己对数据库向来不是非常熟悉，就特地放到新博客上，顺便回顾一下。

这篇笔记的主要内容来源于python之sqlite3使用详解和PySqlite简明教程。

SQlite这个小型数据库，经常在Firefox或者其它软件中看到，它貌似没有独立的维护进程，而是把所有数据都存放在一个单独的文件中。上网搜了下资料，看了看Python上的pysqlite模块，它正是Python中对sqlite的封装实现。

数据库操作

Python的数据库模块都有统一的接口标准，所以数据库操作都基本上是统一的，基本上分成以下几步（假设数据库模块为db）：

1. 用db.connect()创建数据库连接，连接对象为conn。
2. 如果不需要返回查询结果，就直接调用conn.execute()。
3. 如果需要返回查询结果，则需要首先通过conn.cursor()创建游标对象cur，并使用cur.fetchone()等函数获取查询结果。
4. 根据数据库隔离级别的不同，修改数据库后，可能需要使用conn.commit()手动提交事务。
5. 调用相应的close()方法关闭cur及conn。

更加详细的数据库操作API可以参考Python的官方文档：点击这里。

PySqlite基本操作

1. 首先导入sqlite3模块

import sqlite3

2. 接着创建数据库链接

conn = sqlite3.connect('test.db')

其中"test.db"是数据库的名称，如果数据库文件不存在，就会自动创建；否则，就打开指定的数据库文件，同时创建一个数据库连接对象，该对象主要有以下操作：

• commit(): 事务提交
• rollback(): 事务回滚
• close(): 关闭一个数据库连接
• cursor(): 创建一个游标

其中，commit()方法用于提交事务，rollback()方法用于回滚至上次调用commit()方法的地方。可以通过Connection.isolation_level定义事务隔离级别，当该属性设置成None时，它会自动提交事务，不需要显式地调用commit()方法。

除了直接指定数据库文件之外，还有一种方法是在内存中创建数据库。方法是将":memory:"作为参数传递给sqlite.connect()函数：

conn = sqlite3.connect(":memory:")

3. 接下来就需要创建游标对象

cur = conn.cursor()

从网上找到一个关于游标的具体解释(来源)：

游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每 次提取一条记录的机制。游标总是与一条SQL 选择语句相关联。因为游标由结果集（可以是零条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成。当决定对结果集进行处 理时，必须声明一个指向该结果集的游标。

游标对象主要包含以下方法：

• execute(): 执行sql语句
• executemany(): 执行多条sql语句
• close(): 关闭游标
• fetchone(): 从结果中取一条记录
• fetchmany(): 从结果中取多条记录
• fetchall(): 从结果中取出所有记录

注：为了提高编程效率，PySqlite中的Connection对象同样包含execute()等函数，通过使用这些函数，可以避免创建游标对象，具体可以参考PySqlite的文档说明：点击这里，下面的例子来自前面的文档，介绍如何使用这些方法：

import sqlite3

persons = [ ("Hugo", "Boss"), ("Calvin", "Klein") ] con = sqlite3.connect(":memory:") # Create the table con.execute("create table person(firstname, lastname)") # Fill the table （这里使用PySqlite提供的占用符格式，提高安全性） con.executemany("insert into person(firstname, lastname) values (?, ?)", persons) # Print the table contents （使用迭代的方法获取查询结果） # con.execute(..)方法返回游标对象，避免手动创建游标对象。 for row in con.execute("select firstname, lastname from person"): print row print "I just deleted", con.execute("delete from person").rowcount, "rows"

4. 通过execute()方法执行一条sql语句

SQLite中sql语句在语法格式，参考这里。

例如使用create table创建数据库表格：

cur.execute('''create table stocks ( date text, trans text, symbol text, qty real, price real)''')

表格创建之后，可以使用insert语句插入数据：

cur.execute("""insert into stocks values ('2006-01-05','BUY','RHAT',100,35.14)""")

5. 提交事务

conn.commit()

6. 关闭游标及数据库链接，释放资源

cur.close() conn.close()

PySqlite其它操作

在执行插入语句的时候，经常需要使用脚本中定义的变量，PySqlite中提供了多种形式的占位符，可以方便进行变量替换，提高安全性。

例如，回到之前插入的步骤：

cur.execute("""insert into stocks values ('2006-01-05','BUY','RHAT',100,35.14)""")

在这里，使用的是字符串sql语句的形式，如果我们需要更改一些数据，就不得不重新写一次，这样一来可重用性就大大降低了。因此，Python提供 了一种机制，可以使用Python中的变量来代替sql字符串语句中的特定占位符。而对具体的语法，可以通过查询DB模块的paramstyle参数来访 问，以下是几种类型的语法说明：

'qmark': Question mark style, 
         e.g. '...WHERE name=?'
'numeric': Numeric, positional style, 
           e.g. '...WHERE name=:1'
'named': Named style, 
         e.g. '...WHERE name=:name'
'format': ANSI C printf format codes, 
          e.g. '...WHERE name=%s'
'pyformat': Python extended format codes, 
          e.g. '...WHERE name=%(name)s'

关于如何使用，可以参考这篇博客python 数据库查询参数风格。当然Python自带的文档中也有讲如何使用，也可以参考它。

目前PySqlite支持qmark和named两种形式。例如使用qmark形式，并且以元组(tuple)的形式作为第二个参数传递给execute函数：

t = ('2006-01-05','BUY','RHAT',100,35.14) cur.execute("insert into stocks values(?,?,?,?,?)", t)

当需要更多的数据的时候，这种方法就方便多了：

for t in [('2006-03-28', 'BUY', 'IBM', 1000, 45.00), ('2006-04-05', 'BUY', 'MSOFT', 1000, 72.00), ('2006-04-06', 'SELL', 'IBM', 500, 53.00)]: cur.execute('insert into stocks values (?,?,?,?,?)', t)

或者也可以使用executemany()方法来实现，该方法的第二个参数是一个序列或者迭代器，然后根据第一个参数指定的sql语句依次替换执行。上面的插入例子可以写成：

param_seq = [ ('2006-03-28', 'BUY', 'IBM', 1000, 45.00), ('2006-04-05', 'BUY', 'MSOFT', 1000, 72.00), ('2006-04-06', 'SELL', 'IBM', 500, 53.00) ] cur.executemany('insert into stocks values (?,?,?,?,?)', param_seq)

那么还有一种需求是，一次性执行多条sql语句，就好比在mysql的一些图形化界面中输入多条sql语句，然后点击执行，在PySqlite中可以通过executescript()方法来完成这个任务，例如（例子来源于官方文档）：

cur.executescript(""" create table person( firstname, lastname, age ); create table book( title, author, published ); insert into book(title, author, published) values ( 'Dirk Gently''s Holistic Detective Agency', 'Douglas Adams', 1987 ); """)

SQLite笔记

1. 如何确定数据库中是否存在某个表
在SQLite中存在一张特殊的表SQLITE_MASTER，它是只读的，该表的定义语句如下所示：

CREATE TABLE sqlite_master (
  type TEXT, name TEXT, tbl_name TEXT, rootpage INTEGER, sql TEXT );

其中，对于Table来说，type值为"table"，而name为数据库的表名称。那么查询数据库中所有的表，可以使用下面的方法：

SELECT name FROM sqlite_master
WHERE type='table'
ORDER BY name;

相应的如果，要查找是否存在表stocks可以这样做：

SELECT count(*) FROM sqlite_master where name='stocks' and type='table';

返回1则代表存在，0则表示不存在。

参考：http://sqlite.org/faq.html#q7

转载于:https://www.cnblogs.com/alecyrus/p/4809773.html