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

2020-07-23 15:52:39 浏览数 (1)

文章目录

  • 1.插入单行数据
  • 2.插入多行数据
  • 3.判断sqlite是否存在某个表，不存在则创建
  • 4.在SQLite数据库中如何列出所有的表和索引
  • 5.sqlite避免重复插入数据
  • 6.sqlite3时游标的使用方法
    • 入门操作
    • 创建数据表
    • 插入两条数据
    • 选择（select）
    • 修改（update）
    • 删除（delete）
    • 连接对象方法：
    • 游标对象方法：
  • 7.数据库连接对象
  • 8.使用游标查询数据库
  • 9.判断cursor的结果是否为空
  • 10.SQLite 数据类型

1.插入单行数据

代码语言：javascript复制
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语句，语句末分号可有可无

方法一：

代码语言：javascript复制
data = "170141000,'张三','男',20,'EC'"
cur.execute('INSERT INTO Student VALUES (%s)' % data)

方法二：

代码语言：javascript复制
cur.execute('INSERT INTO Student VALUES(?,?,?,?,?)', (170141000,'亮','男',21,'滋麻开花'))#execute执行
con.commit()#commit提交

方法三：

代码语言：javascript复制
cur.execute("INSERT INTO Student (Sno,Sname,Sage,Sdept) values(170151000,'豪',20,'GIS'),(170151999,'喜羊羊',9,'GIS');")
con.commit()

2.插入多行数据

代码语言：javascript复制
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是否存在某个表，不存在则创建

代码语言：javascript复制
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 表看起来如下：

代码语言：javascript复制
CREATE TABLE sqlite_master (
  type TEXT,
  name TEXT,
  tbl_name TEXT,
  rootpage INTEGER,
  sql TEXT
);

通过以下语句可查询出某个表的所有字段信息

代码语言：javascript复制
PRAGMA table_info([tablename])

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

代码语言：javascript复制
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 差不多， 但它只是对于创建那些临时表的应用可见。如果要获得所有表的列表， 不管是永久的还是临时的，可以使用类似下面的命令：

代码语言：javascript复制
SELECT name FROM
   (SELECT * FROM sqlite_master UNION ALL
    SELECT * FROM sqlite_temp_master)
WHERE type='table'
ORDER BY name

程序中可以通过 sqlite_master 表得到所有表的信息。

代码语言：javascript复制
select type, name, tbl_name from sqlite_master order by type

sqlite_master的结构如下

Name

Description

type

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

name

The object’s name

tbl_name

The table the object is associated with

rootpage

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

sql

The object’s SQL definition (DDL)

5.sqlite避免重复插入数据

方法一：

代码语言：javascript复制
insert or replace into table_name( id,type) values (1,0);

方法二：

代码语言：javascript复制
insert or ignore into table_name (id,type) values (2,0);

方法三：

代码语言：javascript复制
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到数据了。

入门操作

代码语言：javascript复制
import sqlite3
conn= sqlite3.connect('somedatabase.db') # 创建数据库
cu =conn.cursor() #能获得连接的游标

创建数据表

代码语言：javascript复制
cu.execute("""create table catalog (
id integer primary key,
pid integer,
name varchar(10) UNIQUE
 
)""")

插入两条数据

代码语言：javascript复制
cu.execute("insert into catalog values(0,0,'name1')")
cu.execute("insert into catalog values(1,0,'name2')")
conn.commit()

选择（select）

代码语言：javascript复制
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）

代码语言：javascript复制
cu.execute(“update catalog set name='name2′ where id = 0″)
cx.commit()
cu.execute(“select * from catalog”)
cu.fetchone()

输出

代码语言：javascript复制
(0, 0, ‘name2′)

删除（delete）

代码语言：javascript复制
cu.execute(“delete from catalog where id= 1″)
cx.commit()
cu.execute(“select * from catalog”)
cu.fetchall()

输出

代码语言：javascript复制
[(0, 0, 'name2')]

连接对象方法：

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

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

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

游标：

代码语言：javascript复制
cu = conn.cursor()

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

代码语言：javascript复制
conn.commit()

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

游标对象方法：

代码语言：javascript复制
cu.fetchone()

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

代码语言：javascript复制
conn.close()

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

7.数据库连接对象

在调用connect函数的时候，指定库名称，如果指定的数据库存在就直接打开这个数据库，如果不存在就新创建一个再打开。

代码语言：javascript复制
cx = sqlite3.connect("E:/test.db")
代码语言：javascript复制
 也可以创建数据库在内存中。
代码语言：javascript复制
con = sqlite3.connect(":memory:")
代码语言：javascript复制
打开数据库时返回的对象cx就是一个数据库连接对象，它可以有以下操作：

1. commit()–事务提交 2. rollback()–事务回滚 3. close()–关闭一个数据库连接 4. cursor()–创建一个游标 关于commit()，如果isolation_level隔离级别默认，那么每次对数据库的操作，都需要使用该命令，你也可以设置isolation_level=None，这样就变为自动提交模式。 conn.isolation_level = None 这个就是事务隔离级别，默认是需要自己commit才能修改数据库，置为None则自动每次修改都提交,否则为""

8.使用游标查询数据库

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

游标对象有以下的操作： execute()–执行sql语句 executemany–执行多条sql语句 close()–关闭游标 fetchone()–从结果中取一条记录，并将游标指向下一条记录 fetchmany()–从结果中取多条记录 fetchall()–从结果中取出所有记录 scroll()–游标滚动

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

代码语言：javascript复制
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 种不同的不同长度的整数数据类型。

css 数据库 sqlite sql android

0 人点赞