sqlite3基本操作 python_Python SQLite 基本操作和经验技巧（一）

文章目录1.插入单行数据

2.插入多行数据

3.判断sqlite是否存在某个表，不存在则创建

4.在SQLite数据库中如何列出所有的表和索引

5.sqlite避免重复插入数据

6.sqlite3时游标的使用方法入门操作

创建数据表

插入两条数据

选择（select）

修改（update）

删除（delete）

连接对象方法：

游标对象方法：

7.数据库连接对象

8.使用游标查询数据库

9.判断cursor的结果是否为空

10.SQLite 数据类型

1.插入单行数据import sqlite3

con = sqlite3.connect('xxxx/test.db')

cur = con.cursor() #创建游标对象

cur.execute("create table Student(SNO char(10) UNIQUE primary key,Sname char(20),Ssex char(2),Sage SMALLINT,Sdept char(20));")#上一行引号内为SQL语句，语句末分号可有可无

方法一：data = "170141000,'张三','男',20,'EC'"

cur.execute('INSERT INTO Student VALUES (%s)' % data)

方法二：cur.execute('INSERT INTO Student VALUES(?,?,?,?,?)', (170141000,'亮','男',21,'滋麻开花'))#execute执行

con.commit()#commit提交

方法三：cur.execute("INSERT INTO Student (Sno,Sname,Sage,Sdept) values(170151000,'豪',20,'GIS'),(170151999,'喜羊羊',9,'GIS');")

con.commit()

2.插入多行数据data_ToBeInserted = ["170140009,'凡','男',20,'人文'","170140000,'翔','男',20,'GIS'","170140001,'豪','男',20,'GIS'","170140002,'飞','男',20,'人文'","170100001,'亮','男',27,'海棠二楼'"]

sql_insert = "INSERT INTO Student VALUES" #SQL语句一

sql_values = "" #SQL语句二

for i in range(0,len(data_ToBeInserted)): #列表下标索引，一一提取一行数据

sql_values += '(' #增加execute语句所需的左括号

sql_values += data_ToBeInserted[i] #插入数据

sql_values += '),' #右括号

sql_values = sql_values.strip(',') #去除最后一行数据的逗号，也可replace为分号

sql_todo = sql_insert + sql_values

3.判断sqlite是否存在某个表，不存在则创建create_tb_cmd='''

CREATE TABLE IF NOT EXISTS USER

(NAME TEXT,

AGE INT,

SALARY REAL);

'''

conn.execute(create_tb_cmd)

4.在SQLite数据库中如何列出所有的表和索引

在一个 C/C++ 程序中（或者脚本语言使用 Tcl/Ruby/Perl/Python 等） 你可以在一个特殊的名叫 SQLITE_MASTER 上执行一个SELECT查询以获得所有 表的索引。每一个 SQLite 数据库都有一个叫 SQLITE_MASTER 的表， 它定义数据库的模式。 SQLITE_MASTER 表看起来如下：CREATE TABLE sqlite_master (

type TEXT,

name TEXT,

tbl_name TEXT,

rootpage INTEGER,

sql TEXT

);

通过以下语句可查询出某个表的所有字段信息PRAGMA table_info([tablename])

对于表来说，type 字段永远是 ‘table’，name 字段永远是表的名字。所以，要获得数据库中所有表的列表， 使用下列SELECT语句：SELECT name FROM sqlite_master

WHERE type='table'

ORDER BY name;

查询table，type 段是’table’，name段是table的名字;

查询indices，type段是’index’, name 是index的名字，tbl_name是index所拥有的table的名字。

不管是表还是索引，sql 字段是原先用 CREATE TABLE 或 CREATE INDEX 语句创建它们时的命令文本。对于自动创建的索引（用来实现 PRIMARY KEY 或 UNIQUE 约束），sql字段为NULL。

SQLITE_MASTER 表是只读的。不能对它使用 UPDATE、INSERT 或 DELETE。 它会被 CREATE TABLE、CREATE INDEX、DROP TABLE 和 DROP INDEX 命令自动更新。

临时表不会出现在 SQLITE_MASTER 表中。临时表及其索引和触发器存放在另外一个叫 SQLITE_TEMP_MASTER 的表中。SQLITE_TEMP_MASTER 跟 SQLITE_MASTER 差不多， 但它只是对于创建那些临时表的应用可见。如果要获得所有表的列表， 不管是永久的还是临时的，可以使用类似下面的命令：SELECT name FROM

(SELECT * FROM sqlite_master UNION ALL

SELECT * FROM sqlite_temp_master)

WHERE type='table'

ORDER BY name

程序中可以通过 sqlite_master 表得到所有表的信息。select type, name, tbl_name from sqlite_master order by type

sqlite_master的结构如下

NameDescriptiontypeThe object’s type (table, index, view, trigger)

nameThe object’s name

tbl_nameThe table the object is associated with

rootpageThe object’s root page index in the database (where it begins)

sqlThe object’s SQL definition (DDL)

5.sqlite避免重复插入数据

方法一：insert or replace into table_name( id,type) values (1,0);

方法二：insert or ignore into table_name (id,type) values (2,0);

方法三：IF NOT EXISTS(SELECT * FROM table_name WHERE ….) THEN INSERT INTO ... ELSE UPDATE SET ...

6.sqlite3时游标的使用方法

cursor就是一个Cursor对象，这个cursor是一个实现了迭代器（def__iter__()）和生成器（yield）的MySQLdb对象，这个时候cursor中还没有数据，只有等到fetchone()或fetchall()的时候才返回一个元组tuple，才支持len()和index()操作，这也是它是迭代器的原因。cursor只能用一次，即每用完一次之后记录其位置，等到下次再取的时候是从游标处再取而不是从头再来，而且fetch完所有的数据之后，这个cursor将不再有使用价值了，即不再能fetch到数据了。

入门操作import sqlite3

conn= sqlite3.connect('somedatabase.db') # 创建数据库

cu =conn.cursor() #能获得连接的游标

创建数据表cu.execute("""create table catalog (

id integer primary key,

pid integer,

name varchar(10) UNIQUE

)""")

插入两条数据cu.execute("insert into catalog values(0,0,'name1')")

cu.execute("insert into catalog values(1,0,'name2')")

conn.commit()

选择（select）cu.execute("select * from catalog")

cu.fetchall()

[(0, 0, 'name1'), (1, 0, 'name2')]

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

cu.fetchall()

[(1, 0, 'name2')]

修改（update）cu.execute(“update catalog set name='name2′ where id = 0″)

cx.commit()

cu.execute(“select * from catalog”)

cu.fetchone()

输出(0, 0, ‘name2′)

删除（delete）cu.execute(“delete from catalog where id= 1″)

cx.commit()

cu.execute(“select * from catalog”)

cu.fetchall()

输出[(0, 0, 'name2')]

连接对象方法：

commit 方法总是可用的，但如果数据库不支持事务，它就没有任何作用。如果关闭了连接但还有未提交的事务，它们会隐式地回滚—但是只有在数据库支持持回滚的时候才可以。

rollback 方法可能不可用，因为不是所有的数据库都支持事务（事务是一系列动作）。如果可用，那么就可以“撤销”所有未提交的事务。

cursor 方法将我们引入另外一个主题：游标对象。通过游标扫行SQL 查询并检查结果。游标连接支持更多的方法，而且可能在程序中更好用。

游标：cu = conn.cursor()

能获得连接的游标，这个游标可以用来执行SQL查询。conn.commit()

完成插入并且做出某些更改后确保已经进行了提交，这样才可以将这些修改真正地保存到文件中。

游标对象方法：cu.fetchone()

fetchall()返回结果集中的全部数据，结果为一个tuple的列表。每个tuple元素是按建表的字段顺序排列。注意，游标是有状态的，它可以记录当前已经取到结果的第几个记录了，因此，一般你只可以遍历结果集一次。在上面的情况下，如果执行fetchone()会返回为空。这一点在测试时需要注意。conn.close()

可以在每次修改数据库后都进行提交，而不是仅仅在准备关闭才提交，准备关闭数据时，使用close 方法。

7.数据库连接对象

在调用connect函数的时候，指定库名称，如果指定的数据库存在就直接打开这个数据库，如果不存在就新创建一个再打开。cx = sqlite3.connect("E:/test.db")也可以创建数据库在内存中。con = sqlite3.connect(":memory:")打开数据库时返回的对象cx就是一个数据库连接对象，它可以有以下操作：

1.commit()–事务提交

2.rollback()–事务回滚

3.close()–关闭一个数据库连接

4.cursor()–创建一个游标

关于commit()，如果isolation_level隔离级别默认，那么每次对数据库的操作，都需要使用该命令，你也可以设置isolation_level=None，这样就变为自动提交模式。

conn.isolation_level = None 这个就是事务隔离级别，默认是需要自己commit才能修改数据库，置为None则自动每次修改都提交,否则为""

8.使用游标查询数据库我们需要使用游标对象SQL语句查询数据库，获得查询对象。 通过以下方法来定义一个游标。cu=cx.cursor()

游标对象有以下的操作：

execute()–执行sql语句

executemany–执行多条sql语句

close()–关闭游标

fetchone()–从结果中取一条记录，并将游标指向下一条记录

fetchmany()–从结果中取多条记录

fetchall()–从结果中取出所有记录

scroll()–游标滚动

9.判断cursor的结果是否为空db = sqlite3.connect("test/user.db")

c=db.cursor()

cursor=c.execute("select info,flag,result from user where info='XXX'")

print(len(list(cursor)))

10.SQLite 数据类型

存储类描述NULL值是一个 NULL 值。

INTEGER值是一个带符号的整数，根据值的大小存储在 1、2、3、4、6 或 8 字节中。

REAL值是一个浮点值，存储为 8 字节的 IEEE 浮点数字。

TEXT值是一个文本字符串，使用数据库编码（UTF-8、UTF-16BE 或 UTF-16LE）存储。

BLOB值是一个 blob 数据，完全根据它的输入存储。

SQLite 的存储类稍微比数据类型更普遍。INTEGER 存储类，例如，包含 6 种不同的不同长度的整数数据类型。