python的数据库_python操作轻量级数据库

scroll()--游标滚动

1. 建表

cu.execute("create table catalog (id integer primary key,pid integer,name varchar(10) UNIQUE,nickname text NULL)")

上面语句创建了一个叫catalog的表，它有一个主键id，一个pid，和一个name，name是不可以重复的，以及一个nickname默认为NULL。

2. 插入数据

请注意避免以下写法：

# Never do this -- insecure 会导致注入攻击

pid=200

c.execute("... where pid = ‘%s‘" % pid)

正确的做法如下，如果t只是单个数值，也要采用t=(n,)的形式，因为元组是不可变的。

for t in[(0,10,‘abc‘,‘Yu‘),(1,20,‘cba‘,‘Xu‘)]:

cx.execute("insert into catalog values (?,?,?,?)", t)

简单的插入两行数据,不过需要提醒的是,只有提交了之后,才能生效.我们使用数据库连接对象cx来进行提交commit和回滚rollback操作.

cx.commit()

3.查询

cu.execute("select * from catalog")

要提取查询到的数据,使用游标的fetch函数,如:

In [10]: cu.fetchall()

Out[10]: [(0, 10, u‘abc‘, u‘Yu‘), (1, 20, u‘cba‘, u‘Xu‘)]

如果我们使用cu.fetchone(),则首先返回列表中的第一项,再次使用,则返回第二项,依次下去.

4.修改

In [12]: cu.execute("update catalog set name=‘Boy‘ where id = 0")

In [13]: cx.commit()

注意,修改数据以后提交

5.删除

cu.execute("delete from catalog where id = 1")

cx.commit()

6.使用中文

请先确定你的IDE或者系统默认编码是utf-8,并且在中文前加上u

x=u‘鱼‘

cu.execute("update catalog set name=? where id = 0",x)

cu.execute("select * from catalog")

cu.fetchall()

[(0, 10, u‘\u9c7c‘, u‘Yu‘), (1, 20, u‘cba‘, u‘Xu‘)]

如果要显示出中文字体，那需要依次打印出每个字符串

In [26]: for item in cu.fetchall():

....: for element in item:

....: print element,

....: print

....:

0 10 鱼 Yu

1 20 cba Xu

7.Row类型

Row提供了基于索引和基于名字大小写敏感的方式来访问列而几乎没有内存开销。 原文如下：

sqlite3.Row provides both index-based and

case-insensitive name-based access to columns with almost no memory overhead. It

will probably be better than your own custom dictionary-based approach or even a

db_row based solution.

Row对象的详细介绍

classsqlite3.RowA Row instance serves as a highly optimized

row_factory for Connection objects. It tries to mimic a tuple in most

of its features.

It supports mapping access by column

name and index, iteration, representation, equality testing and len().

If two Row objects have exactly the same columns and

their members are equal, they compare equal.

Changed in version 2.6: Added

iteration and equality (hashability).

keys()This method returns a tuple of

column names. Immediately after a query, it is the first member of each tuple in

Cursor.description.

New in version

2.6.

下面举例说明

In [30]: cx.row_factory = sqlite3.Row

In [31]: c = cx.cursor()

In [32]: c.execute(‘select * from catalog‘)

Out[32]:

In [33]: r = c.fetchone()

In [34]: type(r)

Out[34]:

In [35]: r

Out[35]:

In [36]: print r

(0, 10, u‘\u9c7c‘, u‘Yu‘)

In [37]: len(r)

Out[37]: 4

In [39]: r[2]

#使用索引查询

Out[39]: u‘\u9c7c‘

In [41]: r.keys()

Out[41]: [‘id‘, ‘pid‘, ‘name‘, ‘nickname‘]

In [42]: for e in r:

....: print e,

....:

0 10 鱼 Yu

使用列的关键词查询

In [43]: r[‘id‘]

Out[43]: 0

In [44]: r[‘name‘]

Out[44]: u‘\u9c7c‘