How to get table names using sqlite3 through python? [duplicate]

Question

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?

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
– unutbu, Commented Jan 2, 2016 at 20:49
Opening an SQLite database manually is a good way to get a corrupted database. Use the encoding pragma instead. — Colonel Thirty Two
– Colonel Thirty Two, Commented Jan 3, 2016 at 5:53

Kenly · Accepted Answer · 2021-06-21 09:53:23Z

38

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])

edited Jun 21, 2021 at 9:53

answered Jan 2, 2016 at 20:44

Kenly

27k7 gold badges50 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

krzyhub Over a year ago

Okay. Then to print the value should I call on it fetchall? But it returns just an empty list []

Kenly Over a year ago

@krzyhub Check my edit.

krzyhub Over a year ago

Nothing was printed, but it seems that this is for some reason my unaware mistake somewhere else. But thanks.

Kenly Over a year ago

@krzyhub For new created db, we need to commit before.

Collectives™ on Stack Overflow

How to get table names using sqlite3 through python? [duplicate]

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related