Is there a way to get a schema of a database from within python?

user_1230086 0
Viewed 50096

I'm trying to find out a way to find the names of tables in a database(if any exist). I find that from a sqlite cli I can use:

>.tables

Then for the fields:

>PRAGMA TABLE_INFO(table_name)

This obviously doesn't work within python. Is there even a way to do this with python or should I just be using the sqlite command-line?

database-schema sqlite python
10 Answers

To get the schema information, IMHO, below also works:

select sql from sqlite_master where type='table';
user_46981050

make the connection to the database

connection = connect_db('./database_name.db')

print the table names

table_names = [t[0] for t in connection.execute("SELECT name FROM sqlite_master WHERE type='table';")]
print(table_names)
user_136510460

Assuming the name of the database is my_db and the name of the table is my_table, to get the name of the columns and the datatypes:

con = sqlite.connect(my_db)
cur = con.cursor()
query =  "pragma table_info({})".format(my_table)
table_info = cur.execute(query).fetchall()

It returns a list of tuples. Each tuple has the order, name of the column and data type.

user_118452400
Related