16

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?

Sumithran
  • 6,217
  • 4
  • 40
  • 54
krzyhub
  • 6,285
  • 11
  • 42
  • 68
  • 2
    [The database's text encoding](https://www.sqlite.org/fileformat2.html#enc) 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 Jan 02 '16 at 20:49
  • 1
    Opening an SQLite database manually is a good way to get a corrupted database. [Use the `encoding` pragma instead.](http://sqlite.org/pragma.html#pragma_encoding) – Colonel Thirty Two Jan 03 '16 at 05:53

1 Answers1

37

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])
Kenly
  • 24,317
  • 7
  • 44
  • 60