python sqlite3 学习小结

一切的理性在潜意识面前,一文不值。总想着我要做这,我要干哪,想法很好,可是碰到好看的电视剧,一直刷下去;说好的早起早睡,闹钟响后,却一直不起;碰到诱惑,又无法抗拒。谈什么提高自控力,说什么有理性,不过是麻痹自己的“鸡汤”而已。代码虽然有趣,可是能解决的问题太少,我需要多看些杂书,寻求那些无止境的答案。

sqlite其实用的不多,老实说,代码也写的不多。要过年了,也就一直在干整理内务的工作,一看我的书签里既然还保存着一些关于sqlite的资料,就趁热打铁看完,总结了下。

一: 新建(create)数据库表

连接test.db数据库,如果之前存在,直接连接;如果不存在,先创建再连接。接着创建user表,如下:

In [1]: import sqlite3

In [2]: with sqlite3.connect('/tmp/test.db') as conn:
   ...:     cur = conn.cursor()
   ...:     cur.execute('CREATE TABLE user(id integer primary key,name text,phone text,email text unique,password text)')
   ...:

In [3]:

登录到test.db,验证下:

sqlite> .schema user
CREATE TABLE user(id integer primary key,name text,phone text,email text unique,password text);
sqlite>

二:插入(insert)数据

先使用execute插入一条数据,接着使用execute many插入多条数据,如下:

In [3]: with sqlite3.connect('/tmp/test.db') as conn:
   ...:     cur = conn.cursor()
   ...:     cur.execute('insert into user(name,phone,email,password) values('lee1','1','1@qq.com','1')')
   ...:

In [4]: data = [('lee'+str(i),str(i),str(i)+'@qq.com',str(i)) for i in range(2,11)]

In [5]: data
Out[5]:
[('lee2', '2', '2@qq.com', '2'),
 ('lee3', '3', '3@qq.com', '3'),
 ('lee4', '4', '4@qq.com', '4'),
 ('lee5', '5', '5@qq.com', '5'),
 ('lee6', '6', '6@qq.com', '6'),
 ('lee7', '7', '7@qq.com', '7'),
 ('lee8', '8', '8@qq.com', '8'),
 ('lee9', '9', '9@qq.com', '9'),
 ('lee10', '10', '10@qq.com', '10')]

In [6]: with sqlite3.connect('/tmp/test.db') as conn:
   ...:     cur = conn.cursor()
   ...:     cur.executemany('insert into user(name,phone,email,password) values(?,?,?,?)',data)
   ...:

In [7]:

登录数据库,查看插入成功:

sqlite> select * from user;
1|lee1|1|1@qq.com|1
2|lee2|2|2@qq.com|2
3|lee3|3|3@qq.com|3
4|lee4|4|4@qq.com|4
5|lee5|5|5@qq.com|5
6|lee6|6|6@qq.com|6
7|lee7|7|7@qq.com|7
8|lee8|8|8@qq.com|8
9|lee9|9|9@qq.com|9
10|lee10|10|10@qq.com|10
sqlite>

三:更新(update)数据

In [9]: with sqlite3.connect('/tmp/test.db') as conn:
   ...:     cur = conn.cursor()
   ...:     cur.execute('update user set email=? where id=?',('1@foxmail.com',1))
   ...:

In [10]:

登录到数据库,看到id=1的那条记录已经更新:

sqlite> select * from user where id=1;
1|lee1|1|1@foxmail.com|1
sqlite>

四:删除(delete)数据

In [10]: with sqlite3.connect('/tmp/test.db') as conn:
   ....:     cur = conn.cursor()
   ....:     cur.execute('delete from user where id=?',(10,))
   ....:

In [11]:

登录到数据库,id=10那条数据已经被删除:

sqlite> select * from user;
1|lee1|1|1@foxmail.com|1
2|lee2|2|2@qq.com|2
3|lee3|3|3@qq.com|3
4|lee4|4|4@qq.com|4
5|lee5|5|5@qq.com|5
6|lee6|6|6@qq.com|6
7|lee7|7|7@qq.com|7
8|lee8|8|8@qq.com|8
9|lee9|9|9@qq.com|9
sqlite>

五:使用sqlite3的Row模式

别的优势不说,当你的数据库表结构不变的时候,一直使用row[0]、row[1]等访问没啥问题,当你突然新增加了一个字段呢?这个时候Row模式就派上用场了,它让你可以通过index(索引)和column name(字段名)来访问数据,如下:

In [11]: with sqlite3.connect('/tmp/test.db') as conn:
   ....:     conn.row_factory = sqlite3.Row
   ....:     cur = conn.cursor()
   ....:     cur.execute('select * from user')
   ....:     while True:
   ....:         row = cur.fetchone()
   ....:         if not row: break
   ....:         print row['phone'],row['password'],row['email'],row['id'],row['name']
   ....:
1 1 1@foxmail.com 1 lee1
2 2 2@qq.com 2 lee2
3 3 3@qq.com 3 lee3
4 4 4@qq.com 4 lee4
5 5 5@qq.com 5 lee5
6 6 6@qq.com 6 lee6
7 7 7@qq.com 7 lee7
8 8 8@qq.com 8 lee8
9 9 9@qq.com 9 lee9

我在stackoverflow上看到有一个对sqlite3.Row的包装函数,对query后的结果迭代后返回字典,很不错的想法,如下:

In [17]: import itertools
In [18]: def dict_gen(cur):
   ....:     filed_names = [d[0] for d in cur.description]
   ....:     while True:
   ....:         row = cur.fetchone()
   ....:         if not row: return
   ....:         yield dict(itertools.izip(filed_names,row))
   ....:

In [19]: with sqlite3.connect('/tmp/test.db') as conn:
   ....:     cur = conn.cursor()
   ....:     result = dict_gen(cur.execute('select * from user'))
   ....:     for item in result:
   ....:         print item
   ....:
{'phone': u'1', 'password': u'1', 'email': u'1@foxmail.com', 'id': 1, 'name': u'lee1'}
{'phone': u'2', 'password': u'2', 'email': u'2@qq.com', 'id': 2, 'name': u'lee2'}
{'phone': u'3', 'password': u'3', 'email': u'3@qq.com', 'id': 3, 'name': u'lee3'}
{'phone': u'4', 'password': u'4', 'email': u'4@qq.com', 'id': 4, 'name': u'lee4'}
{'phone': u'5', 'password': u'5', 'email': u'5@qq.com', 'id': 5, 'name': u'lee5'}
{'phone': u'6', 'password': u'6', 'email': u'6@qq.com', 'id': 6, 'name': u'lee6'}
{'phone': u'7', 'password': u'7', 'email': u'7@qq.com', 'id': 7, 'name': u'lee7'}
{'phone': u'8', 'password': u'8', 'email': u'8@qq.com', 'id': 8, 'name': u'lee8'}
{'phone': u'9', 'password': u'9', 'email': u'9@qq.com', 'id': 9, 'name': u'lee9'}

In [20]:

当然官网讲到sqlite3的Connection Objects时,有这么一个例子,使用的是还是row_factory,如下,也很有意思:

In [32]: import sqlite3

In [33]: def dict_factory(cursor,row):
   ....:     d = {}
   ....:     for idx,col in enumerate(cursor.description):
   ....:         d[col[0]] = row[idx]
   ....:     return d
   ....:

In [34]: with sqlite3.connect('/tmp/test.db') as conn:
   ....:     conn.row_factory = dict_factory
   ....:     cur = conn.cursor()
   ....:     cur.execute('select * from user where id=1')
   ....:     data = cur.fetchone()
   ....:     print data
   ....:
{'phone': u'1', 'password': u'1', 'email': u'1@foxmail.com', 'id': 1, 'name': u'lee1'}

In [35]:

使用sqlite3.Row工厂函数得到的对象可以当成字典来用,如下:

In [35]: conn = sqlite3.connect('/tmp/test.db')

In [36]: conn.row_factory = sqlite3.Row

In [37]: cur = conn.cursor()

In [38]: cur.execute('select * from user where id=1')
Out[38]: <sqlite3.Cursor at 0x109ed7ab0>

In [39]: r = cur.fetchone()

In [40]: type(r)
Out[40]: sqlite3.Row

In [41]: r[0]
Out[41]: 1

In [42]: r['id']
Out[42]: 1

In [43]: dict(r)
Out[43]:
{'email': u'1@foxmail.com',
 'id': 1,
 'name': u'lee1',
 'password': u'1',
 'phone': u'1'}

In [45]: for member in r:
   ....:     print member
   ....:
1
lee1
1
1@foxmail.com
1

In [46]: r.keys()
Out[46]: ['id', 'name', 'phone', 'email', 'password']

看完python sqlite3的使用文档后,有以下几点收获:

1.查询时,不要自己使用字符串%s去自己拼接sql(不安全,我以前也是这么做的),使用占位符?或者:name。

2.python sqlite3模块很简单,完全不需要使用orm,我当时还找了好久的orm模块,没必要,直接写sql简单方便,不需要再wrapper。

3.使用with语句管理连接对象,省得自己去commit、close,可以少写很多代码,这样看着也舒服。


参考资料:

官网 sqlite3:https://docs.python.org/2/library/sqlite3.html

python之sqlite3使用详解:http://anony3721.blog.163.com/blog/static/5119742010716104442536

SQLite - Python:http://www.runoob.com/sqlite/sqlite-python.html

A thorough guide to SQLite database operations in Python:http://sebastianraschka.com/Articles/2014_sqlite_in_python_tutorial.html

sqlite3 – Embedded Relational Database:https://pymotw.com/2/sqlite3/

SQLite Python Tutorial:http://www.tutorialspoint.com/sqlite/sqlite_python.htm

使用SQLite:http://www.liaoxuefeng.com/wiki/001374738125095c955c1e6d8bb493182103fac9270762a000/001388320596292f925f46d56ef4c80a1c9d8e47e2d5711000

SQLite Python tutorial:http://zetcode.com/db/sqlitepythontutorial/

Introduction to SQLite in Python:http://pythoncentral.io/introduction-to-sqlite-in-python/