15
votes

I have a problem to get data from the sqlite3 database. I can't find out the names of tables and their encoding. When I open DB through sqlitebrowser names were just unreadable characters. Connection to DB is fine.

conn = sqlite3.connect('my.db')
conn_cursor = conn.cursor()
conn.text_factory = str

But how can I get the names of tables and their encoding?

1
The database's text encoding is stored as a 4-byte big-endian int at offset 56 in the sqlite database header: header = open('my.db').read(100); import struct; encoding_type = struct.unpack('>i', header[56:60])[0]. The encoding_type is equal to either 1, 2, or 3 and corresponds to UTF-8, UTF-16LE, or UTF-16BE respectively. - unutbu
Opening an SQLite database manually is a good way to get a corrupted database. Use the encoding pragma instead. - Colonel Thirty Two

1 Answers

32
votes

You can use this query to get tables names.

res = conn.execute("SELECT name FROM sqlite_master WHERE type='table';")
for name in res.fetchall():
    print(name[0])