SQLite的Python编程

0、 引言

sqlite的python。python提供了基于sqlite3的模块，在python2.7.3版本中已经默认的安装了。这里我们只需要使用即可为何要使用python中调用sqlite模块呢？这是因为python是一门很优秀的脚本语言（这有点侮辱python啊，但是确实python可以作为脚本语言完成很多的任务），可以让我们能够更高效的完成一些的任务。

1、 利用sqlite3进行python的编程操作

上一章节我们用sqlite提供的命令通过命令行的方式对数据库进行了操作，这里我们将使用python的api接口来实现对数据库的创建，查找，删除等操作

1.1 数据库的链接

对数据库进行操作，首先需要做到能够连接到数据库上，如下所示;

import sqlite3
conn = sqlite3.connect('c:\\sqlite\\test\\testdb.db')

这里需要首先import sqlite3模块，否则的话是查找不到相应的命令的
其次，对于windows环境下，我们的全路径要加上两个‘\’号，第一个是转义符，这里不再详细说明
再次，如果testdb.db不存在的话，程序在连接前会将其创建
对数据库连接上了以后，那么就可以做相应的其他的操作了。

1.2 创建数据库表

cur = conn.cursor()
createsql = 'create table tbl_device (id int primary key not null,\
                    devname text not null,\
                    userid int not null)'
cur.execute(createsql)
conn.commit()

第一条语句是获取数据库的游标，第三条语句是执行数据库，最后一条语句是提交修改，不提交的话，实际上数据库并不会将语句执行。第二句就是创表的语句，我们发现其实和上一章的命令是一致的。

1.3 插入数据

创建了表后，如果没有数据的话，那么实际是没用的，下面我们就给表插入数据

insertsql1 = 'insert into tbl_device values (1,"ne=1",1)'
insertsql2 = 'insert into tbl_device values (2,"ne=2",1)'
cur.execute(insertsql1)
cur.execute(insertsql2)

1.4 查找数据

插入数据后，我们可以利用select命令查看插入的数据是否存在

selectsql = 'select * from tbl_device'
cur.execute(selectsql)
name=[f[0] for f in cur.description]
for row in cur.fetchall():
    for pair in zip(name,row):
        print pair

最终会显示如下的结果：

('id', 1)
('devname', u'ne=1')
('userid', 1)
('id', 2)
('devname', u'ne=2')
('userid', 1)

1.5 更新数据

同样的，可以用update命令来更新数据

updatesql = 'update tbl_device set devname=''ne=3 where id=1'
cur.execute(selectsql)

1.6 其他

对于所有的sql命令，都可以利用上面的方式进行执行语句，包括delete命令，drop命令等等，他们的行为都是一致的，这里就不再详细解读。

1.7 关闭连接

当我们执行完了所有的数据库命令，我们一般要做下面的步骤

conn.commit()
conn.close()

事物提交以及关闭数据库连接

2、基础的api接口

上面总结了sqlite在python中的实际应用，下面我们就来详细分析一下sqlite的数据库api接口

2.1 连接数据库的接口

sqlite3.connect(database [,timeout ,other optional arguments])

2.2 获取游标的接口

connection.cursor([cursorclass])

2.3 执行语句的接口

cursor.execute(sql [, optional parameters])
connection.execute(sql [, optional parameters])

2.4 执行脚本

cursor.executescript(sql_script)

2.5 提交当前的事物

connection.commit()

2.6 回滚

connection.rollback()

2.7 关闭数据库连接

connection.close()

2.8 读取下一行结果

cursor.fetchone()

2.8 读取所有剩余结果

cursor.fetchall()

基本上常见的接口就是这些了，更详细的信息可以参见这里

3 关于查找的输出显示

在上面我们使用了游标的方式执行select语句，然后将其构造成zip的格式，然后用fetchall的方式将其打印出来
这里我们直接使用游标来打印出结果，如下所示

conn1=sqlite3.connect('c:\\sqlite\\test\\testdb.db')
createsql1 = 'create table tbl_user (id int primary key not null,\
                    name text not null,\
                    age int not null,\
                    sex text)'

conn1.execute(createsql1)

insertsql3 = 'insert into tbl_user values (1,"ahmed",30,"male")'
insertsql4 = 'insert into tbl_user values (2,"bob",28,"male")'
insertsql5 = 'insert into tbl_user values (3,"cristina",18,"female")'

conn1.execute(insertsql3)
conn1.execute(insertsql4)
conn1.execute(insertsql5)

conn1.commit()


selectsql1 = 'select * from tbl_user'
cur = conn1.execute(selectsql1)

for row in cur:
    print "id = ", row[0]
    print "name = ",row[1]
    print "age = ", row[2]
    print "sex = ",row[3]

conn1.close()

这里游标直接指向了执行后的结果数据，然后直接读取游标指向的结果。
具体的输出格式如下所示：

id =  1
name =  ahmed
age =  30
sex =  male
id =  2
name =  bob
age =  28
sex =  male
id =  3
name =  cristina
age =  18
sex =  female

殊途同归，都可以将信息打印出来

4、执行sql脚本

现实中，我们不会在python脚本里写create或者insert这种语句，这样的编程是很凌乱的。实际操作中一般都是在sql脚本中编写需要执行的sql语句，然后在python脚本中调用并且执行sql脚本

python脚本文件

import sqlite3

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

cursor = conn.cursor()

f = open(r"test.sql")

cursor.executescript(f.read())

conn.commit()

conn.close()

sql脚本文件：

create table tbl_user (id integer primary key not null,name text not null,age integer not null,sex text);
insert into tbl_user values (1,"ahmed",30,"male");
insert into tbl_user values (2,"bob",28,"male");
insert into tbl_user values (3,"cristina",18,"female");

执行结果查询：

d:\eclipseworkspace\pythonlearning\src>sqlite3 testdb.db
sqlite version 3.19.2 2017-05-25 16:50:27
enter ".help" for usage hints.
sqlite> .table
company   tbl_user
sqlite> select * from tbl_user;
1|ahmed|30|male
2|bob|28|male
3|cristina|18|female