python使用sqlite

前言

python通过sqlite3模块来操作sqlite。sqlite3是python的一个标准库并不需要安装，sqlite3是一个SQLite数据库DB-API2.0接口模块。

建立连接

要想使用这个模块，必须先创建一个Connection对象， 它代表数据库。

import sqlite3 
conn = sqlite3.connect('example.db')

上面的例子中，数据将存储在example.db文件中，如果文件存在则打开文件，如果文件不存在将新建一个example.db数据库文件，你也可以通过sqlite3.connect(":memory:")来创建一个内存中的数据库，通常情况下这没啥意义。

connect函数详解

函数签名：

sqlite3.connect(database[,timeout,detect_types,isolation_level,check_same_thread,factory,cached_statements,uri])

• database: 准备打开的数据库文件的路径,也可以用":memory:"在内存中打开一个数据库。
• timeout: 当一个数据库被多个连接访问的时候，如果其中一个进程修改这个数据库，在这个连接的事务提交之前，这个数据库将被一直锁定，timeout参数指定了等待锁定释放的超时时间，超过之后会引发一个异常，默认为5s
• detect_types:默认是0,即关闭类型检测。SQLite原生支持5种类型：TEXT INTEGER REAL BLOB和NULL，如果想要用其他类型必须自行添加相应的支持，使用该参数和模块级别的register_converter()函数注册转换器来简单实现。该参数可能值还包括：
  • sqlite3.PARSE_DECLTYPES:设置为这个常量后，sqlite3模块将解析它返回的每一列的声明类型，比如integer primary key,它会解析出integer，如果是number(10)他会解析出number。然后，他会在装换器字典里查找那个类型注册的装换器函数，并调用它。
  • sqlite3.PARSE_COLNAMES:设置为这个常量后，sqlite接口将解析它放回的每一列的列名，会在其中查找 [mytype] 这个形式的字符串，然后用‘mytype’来决定那个列的类型。它会尝试在转换器字典中查找‘mytype’键对应的转换器函数，然后用这个转换器函数返回的值来做为列的类型。
• isolation_level:用于设置sqlite3模块中事务控制的行为
• check_same_thread:默认情况下为True，只有当前的线程可以使用该连接。如果设置为False，则多个线程可以共享放回的连接。当多个线程使用同一个连接的时候，用户应该把写操作序列化，以避免数据损坏。
• factory:connect是一个工厂函数，默认情况下使用了sqlite3模块中它自己的Connection类，你也通过继承Connection类来创建自己的连接类，然后将其赋值给factory来使用。
• cached_statements:sqlite3模块在内部使用语句缓存来避免SQL解析开销，默认情况下缓存100条语句

其返回的是一个Connection对象。

使用连接

当有了一个Connection对象后，你就可以创建Cursor游标对象，通过游标对象你就可以对整个数据库进行增删改查了。

c = conn.cursor()
c.execute('''CREATE TABLE stocks
             (date text, trans text, symbol text, qty real, price real)''')
c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
conn.commit()
conn.close()

让我们来逐一解释上面的语句：

• conn.cursor(): 调用Connection对象的cursor()函数，放回一个cursor对象
• c.execte():该方法用于执行SQL语句
• conn.commit():用于提交当前事务，如果没有调用这个方法，那么从上一次执行commit()以来的所有的变化其他该数据连接上都是不可见的。
• conn.close():关闭数据库连接， 注意他并不会自动调用commit()方法，如果在关闭数据库连接之前没有调用commit()那么你的修改将会丢失。

我们通常的使用流程就是这样的。

Connection对象

属性：

• isolation_level:获取或设置当前默认的隔离级别
• in_transaction:这是一个只读属性，如果是在活动事务中(即数据库发生了改变但是还没有commit时)返回True，一旦你commit后返回False。
• cursor():返回一个Cursor对象
• commit():提交当前事务，如果没有调用这个方法，那么从上一次提交commit()以来所有的变化在其他数据库连接中不可见
• rollback():回滚从上一次调用commit()以来所有数据库的改变
• close():关闭数据库连接，注意它不会自动调用commit()方法，如果在关闭之间没有调用commit()那么修改将会丢失
• execute(sql):这是一个非标准的快捷方法， 它会调用cursor()方法来创建Cursor对象，并且用给定的参数来调用Cursor对象的execute()方法，最后返回的是这个游标对象。
• executemany():这也是一个非标准的快捷方法，对应Cursor对象的executemany()方法
• executescript():同上，对应Cursor对象的executescript()方法

Cursor对象

• execute(sql):执行SQL语句，可以参数化SQL语句，sqlite3模块支持两种占用符：问号和命名占用符。需要注意的是，该方法一次仅仅能执行一条SQL语句，如果想要执行多条语句请使用executescript()方法

In [1]: import sqlite3
In [2]: con = sqlite3.connect('example.db')
In [3]: data = '2018-01-08'
In [4]: trans = 'BUY'
In [5]: symbol = 'RHAT'
In [6]: qty = '120'
In [7]: price = '12.1'
In [8]: cur = con.cursor()
In [9]: cur.execute('insert into stocks values (?,?,?,?,?)',(data,trans,symbol,qty,price))
Out[9]: <sqlite3.Cursor at 0x233f51c1e30>
In [10]: con.commit()
In [16]: cur.execute('select * from stocks where date=:data and trans=:buy',{'data':data,'buy':trans})
Out[16]: <sqlite3.Cursor at 0x233f51c1e30>
In [17]: print(cur.fetchone())
('2018-01-08', 'BUY', 'RHAT', 120.0, 12.1)

• executescript(sql_script):一次执行多个SQL语句的快捷方法
• fetchone():获取查询结果集的下一行，返回单个序列，如果没有更多数据可用，则返回None
• fetchall():获取查询结果集的所有(剩余)行，并返回一个列表。

In [18]: cur.execute('select * from stocks')
Out[18]: <sqlite3.Cursor at 0x233f51c1e30>
In [19]: cur.fetchone()
Out[19]: ('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14)
In [20]: cur.fetchone()
Out[20]: ('2006-01-05', 'BUY', 'CHE', 200.0, 1.2)
In [21]: cur.fetchone()
Out[21]: ('2018-01-08', 'BUY', 'RHAT', 120.0, 12.1)
In [22]: cur.fetchone()
In [23]: cur.execute('select * from stocks')
Out[23]: <sqlite3.Cursor at 0x233f51c1e30>
In [24]: cur.fetchone()
Out[24]: ('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14)
In [25]: cur.fetchall()
Out[25]:
[('2006-01-05', 'BUY', 'CHE', 200.0, 1.2),
 ('2018-01-08', 'BUY', 'RHAT', 120.0, 12.1)]

• close():关闭这个Cursor对象

SQLite与python类型

SQLite原生支持一下类型：NULL INTEGER REAL TEXT BLOB
这五种类型对应于python中的None int float str bytes,也就是说将这些类型发送给SQLite不会出现任何问题。而且sqlite3模块默认也是这么转换的。