Sqlite3数据库初接触基本操作
前言
笔者接触到一个全新的数据库，知道该数据库的类型是 Sqlite3， 但不知道该数据的具体组成比如具体由哪些表或者数据内容。
对数据概况探索
1. 经过查阅 “https://blog.csdn.net/luoshabugui/article/details/108327936”参考该博客（感谢作者：丸子叮咚响），知道数据有以下基本内容：每一个 SQLite 数据库都有一个叫 sqlite_master 的表，该表会自动创建。该表存储数据库的元信息, 如表(table), 索引(index), 视图(view), 触发器(trigger), 可通过select查询相关信息。
lines = c.execute("select * from sqlite_master;")
1 ===== <sqlite3.Cursor object at 0x00000286A3456C70> 2 type name tbl_name rootpage sql 3 ('table', 'IndexContent', 'IndexContent', 0, 'CREATE VIRTUAL TABLE IndexContent USING fts4(type, content, contentindex, oid, ext1, ext2, ext3, ext4, ext5, ext6, ext7, ext8, ext9, exts, notindexed=content, notindexed=exts ,compress=qqcompress, uncompress=qquncompress )') 4 ('table', 'IndexContent_content', 'IndexContent_content', 3, "CREATE TABLE 'IndexContent_content'(docid INTEGER PRIMARY KEY, 'c0type', 'c1content', 'c2contentindex', 'c3oid', 'c4ext1', 'c5ext2', 'c6ext3', 'c7ext4', 'c8ext5', 'c9ext6', 'c10ext7', 'c11ext8', 'c12ext9', 'c13exts')") 5 ('table', 'IndexContent_segments', 'IndexContent_segments', 4, "CREATE TABLE 'IndexContent_segments'(blockid INTEGER PRIMARY KEY, block BLOB)") 6 ('table', 'IndexContent_segdir', 'IndexContent_segdir', 5, "CREATE TABLE 'IndexContent_segdir'(level INTEGER,idx INTEGER,start_block INTEGER,leaves_end_block INTEGER,end_block INTEGER,root BLOB,PRIMARY KEY(level, idx))") 7 ('index', 'sqlite_autoindex_IndexContent_segdir_1', 'IndexContent_segdir', 6, None) 8 ('table', 'IndexContent_docsize', 'IndexContent_docsize', 7, "CREATE TABLE 'IndexContent_docsize'(docid INTEGER PRIMARY KEY, size BLOB)") 9 ('table', 'IndexContent_stat', 'IndexContent_stat', 8, "CREATE TABLE 'IndexContent_stat'(id INTEGER PRIMARY KEY, value BLOB)") 10 ('table', 'TroopIndex', 'TroopIndex', 0, 'CREATE VIRTUAL TABLE TroopIndex USING fts4(content TEXT,contentindex TEXT,ext4 TEXT,ext5 TEXT,ext6 TEXT,ext7 TEXT,ext8 TEXT,ext9 TEXT,oId INTEGER,type INTEGER,ext1 TEXT,ext2 TEXT,ext3 TEXT,exts BLOB, notindexed=exts, compress=qqcompress, uncompress=qquncompress)') 11 ('table', 'TroopIndex_content', 'TroopIndex_content', 9, "CREATE TABLE 'TroopIndex_content'(docid INTEGER PRIMARY KEY, 'c0content', 'c1contentindex', 'c2ext4', 'c3ext5', 'c4ext6', 'c5ext7', 'c6ext8', 'c7ext9', 'c8oId', 'c9type', 'c10ext1', 'c11ext2', 'c12ext3', 'c13exts')") 12 ('table', 'TroopIndex_segments', 'TroopIndex_segments', 10, "CREATE TABLE 'TroopIndex_segments'(blockid INTEGER PRIMARY KEY, block BLOB)") 13 ('table', 'TroopIndex_segdir', 'TroopIndex_segdir', 11, "CREATE TABLE 'TroopIndex_segdir'(level INTEGER,idx INTEGER,start_block INTEGER,leaves_end_block INTEGER,end_block INTEGER,root BLOB,PRIMARY KEY(level, idx))") 14 ('index', 'sqlite_autoindex_TroopIndex_segdir_1', 'TroopIndex_segdir', 12, None) 15 ('table', 'TroopIndex_docsize', 'TroopIndex_docsize', 13, "CREATE TABLE 'TroopIndex_docsize'(docid INTEGER PRIMARY KEY, size BLOB)") 16 ('table', 'TroopIndex_stat', 'TroopIndex_stat', 14, "CREATE TABLE 'TroopIndex_stat'(id INTEGER PRIMARY KEY, value BLOB)") 17 ('table', 'UpgradeCursor', 'UpgradeCursor', 15, 'CREATE TABLE UpgradeCursor (id INTEGER PRIMARY KEY AUTOINCREMENT, cursor INTEGER)') 18 ('table', 'sqlite_sequence', 'sqlite_sequence', 16, 'CREATE TABLE sqlite_sequence(name,seq)') 19 ('table', 'SyncCursor', 'SyncCursor', 17, 'CREATE TABLE SyncCursor (id INTEGER PRIMARY KEY AUTOINCREMENT, cursor INTEGER)') 20 ('table', 'DeleteCursor', 'DeleteCursor', 18, 'CREATE TABLE DeleteCursor (id INTEGER PRIMARY KEY AUTOINCREMENT, cursor INTEGER)') 21 ('table', 'TroopCursor', 'TroopCursor', 19, 'CREATE TABLE TroopCursor(id INTEGER PRIMARY KEY AUTOINCREMENT, cursor INTEGER)') 22 ('table', 'NewTroopCursor', 'NewTroopCursor', 20, 'CREATE TABLE NewTroopCursor(id INTEGER PRIMARY KEY AUTOINCREMENT, cursor INTEGER)')
lines = c.execute("select name from sqlite_master where type = 'table' order by name; ")； # and name = 'IndexContent_content';
1 ===== <sqlite3.Cursor object at 0x0000019CB74D6C70> 2 ('DeleteCursor',) 3 ('IndexContent',) 4 ('IndexContent_content',) 5 ('IndexContent_docsize',) 6 ('IndexContent_segdir',) 7 ('IndexContent_segments',) 8 ('IndexContent_stat',) 9 ('NewTroopCursor',) 10 ('SyncCursor',) 11 ('TroopCursor',) 12 ('TroopIndex',) 13 ('TroopIndex_content',) 14 ('TroopIndex_docsize',) 15 ('TroopIndex_segdir',) 16 ('TroopIndex_segments',) 17 ('TroopIndex_stat',) 18 ('UpgradeCursor',) 19 ('sqlite_sequence',)
lines = c.execute("PRAGMA table_info(sqlite_master)")
| type | 记录项目的类型，如table、index、view、trigger |
| name | 记录项目的名称，如表名、索引名等 |
| tbl_name | 记录所从属的表名，如索引所在的表名。对于表来说，该列就是表名本身 |
| rootpage | 记录项目在数据库页中存储的编号。对于视图和触发器，该列值为0或者NULL |
| sql | 记录创建该项目的SQL语句 |
