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語句 |
浙公網安備 33010602011771號