sqlite数据库文件不可读符号 [英] sqlite database file unreadable symbols

问题描述

我们有一项任务，我们必须直接从数据库文件编写和查询，而无需使用任何sqlite api函数.我们获得了网站 https://www.sqlite.org/fileformat.html 的指导但我似乎无法使数据库文件看起来像可读的东西.

We have an assignment where we have to write and query from a database file directly without the use of any sqlite api functions. We were given the site https://www.sqlite.org/fileformat.html as guidance but I can't seem to get the database file to look like anything readable.

这是我要使用python的sqlite3库进行操作的基本示例

Here's a basic example of what I'm trying to do with the sqlite3 library from python

import sqlite3

con = sqlite3.connect("test.db")
cur = con.cursor()

cur.execute("PRAGMA page_size = 4096")
cur.execute("PRAGMA encoding = 'UTF-8'")
dropTable = "DROP TABLE IF EXISTS Employee"
createTable = "CREATE TABLE Employee(first int, second int, third int)"

cur.execute(dropTable)
cur.execute(createTable)

cur.execute("INSERT INTO Employee VALUES (1, 2, 3)")

con.commit()
con.close()

当我打开数据库文件时，它以"SQLite format 3"开头，然后是一堆奇怪的符号.当我使用给定的实际csv文件制作数据库时，发生了同样的事情.有一些可读的部分，但大多数是不可读的符号，与网站指定的格式完全没有相似之处.我现在有点不知所措，所以我将不胜感激有人向我指出正确的方向，说明我将如何开始解决此问题.

When I open the database file, it starts with "SQLite format 3", and then a bunch of weird symbols following it. The same thing happened when I made the databse with the actual csv file given. There's some readable parts but most of it is unreadable symbols that is in no way similar to the format specified by the website. I'm a little overwhelmed right now so I would appreciate anyone pointing me to the right direction on how I would begin fixing this mess.

推荐答案

以下是使用 FileIO 和 struct 读取二进制文件的示例:

Here is an example of reading that binary file using FileIO and struct:

from io import FileIO
from struct import *


def read_header(db):
    # read the entire database header into one bytearray
    header = bytearray(100)
    db.readinto(header)

    # print out a few of the values in the header
    # any number that is more than one byte requires unpacking
    # strings require decoding
    print('header string: ' + header[0:15].decode('utf-8')) # note that this ignores the null byte at header[15]
    page_size = unpack('>h', header[16:18])[0]
    print('page_size = ' + str(page_size))
    print('write version: ' + str(header[18]))
    print('read version: ' + str(header[19]))
    print('reserved space: ' + str(header[20]))
    print('Maximum embedded payload fraction: ' + str(header[21]))
    print('Minimum embedded payload fraction: ' + str(header[22]))
    print('Leaf payload fraction: ' + str(header[23]))
    file_change_counter = unpack('>i', header[24:28])[0]
    print('File change counter: ' + str(file_change_counter))
    sqlite_version_number = unpack('>i', header[96:])[0]
    print('SQLITE_VERSION_NUMBER: ' + str(sqlite_version_number))


db = FileIO('test.db', mode='r')
read_header(db)

这仅读取数据库标头，并且忽略标头中的大多数值.

This only reads the database header, and ignores most of the values in the header.

这篇关于sqlite数据库文件不可读符号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！