How do I display the table structure in SQLite3 with python?

Question

How do I display structure in SQLite3 in python?

python 3.7
sqlite3

import sqlite3
conn = sqlite3.connect('test.db')
print ('Opened database successfully')

print ('Table created sucessfully');
conn.execute('''PRAGMA table_info('company');''')
conn.close()

Rocky_Mental · Accepted Answer · 2019-08-20 03:11:56Z

4

''' This is the code you should add

a= conn.execute("PRAGMA table_info('Table_Name')")

for i in a:

     print(i)

answered Aug 20, 2019 at 3:11

Rocky_Mental

1681 gold badge2 silver badges12 bronze badges

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

1 Comment

Samurai Over a year ago

thanks bro for quick and correct answer it is working :) :)

Wolfgang Fahl · Accepted Answer · 2020-08-31 14:30:54Z

The sqlite3 wrapper in the open source project https://github.com/WolfgangFahl/DgraphAndWeaviateTest (which i am an committer for) has a function added based on issue 10: adds support to retrieve Schema information from sqlite3

def getTableList(self):
        '''
        get the schema information from this database
        '''
        tableQuery="SELECT name FROM sqlite_master WHERE type='table'"
        tableList=self.query(tableQuery)
        for table in tableList:
            tableName=table['name']
            columnQuery="PRAGMA table_info('%s')" % tableName
            columns=self.query(columnQuery)
            table['columns']=columns
        return tableList

That will return a list of dicts that you can print.

see python unit testcase with output:

 [{'name': 'Person', 'columns': [{'cid': 0, 'name': 'name', 'type': 'TEXT', 'notnull': 0, 'dflt_value': None, 'pk': 1}, {'cid': 1, 'name': 'born', 'type': 'DATE', 'notnull': 0, 'dflt_value': None, 'pk': 0}, {'cid': 2, 'name': 'numberInLine', 'type': 'INTEGER', 'notnull': 0, 'dflt_value': None, 'pk': 0}, {'cid': 3, 'name': 'wikidataurl', 'type': 'TEXT', 'notnull': 0, 'dflt_value': None, 'pk': 0}, {'cid': 4, 'name': 'age', 'type': 'FLOAT', 'notnull': 0, 'dflt_value': None, 'pk': 0}, {'cid': 5, 'name': 'ofAge', 'type': 'BOOLEAN', 'notnull': 0, 'dflt_value': None, 'pk': 0}, {'cid': 6, 'name': 'lastmodified', 'type': 'TIMESTAMP', 'notnull': 0, 'dflt_value': None, 'pk': 0}]}]

which you can pretty-print as you see fit for the example Table declaration:

CREATE TABLE Person(name TEXT PRIMARY KEY,born DATE,numberInLine INTEGER,wikidataurl TEXT,age FLOAT,ofAge BOOLEAN,lastmodified TIMESTAMP)

which in this case is automatically derived from a list of dict of data...

There is also some plantuml support now to get plantuml source code which can be rendered e.g. to

def testEntityInfo(self):
        '''
        test creating entityInfo from the sample record
        '''
        listOfRecords=Sample.getRoyals()
        entityInfo=EntityInfo(listOfRecords[:3],'Person','name',debug=True)
        self.assertEqual("CREATE TABLE Person(name TEXT PRIMARY KEY,born DATE,numberInLine INTEGER,wikidataurl TEXT,age FLOAT,ofAge BOOLEAN,lastmodified TIMESTAMP)",entityInfo.createTableCmd)
        self.assertEqual("INSERT INTO Person (name,born,numberInLine,wikidataurl,age,ofAge,lastmodified) values (:name,:born,:numberInLine,:wikidataurl,:age,:ofAge,:lastmodified)",entityInfo.insertCmd)
        self.sqlDB=SQLDB(debug=self.debug,errorDebug=True)
        entityInfo=self.sqlDB.createTable(listOfRecords[:10],entityInfo.name,entityInfo.primaryKey)
        tableList=self.sqlDB.getTableList()
        if self.debug:
            print (tableList)
        self.assertEqual(1,len(tableList))
        personTable=tableList[0]
        self.assertEqual("Person",personTable['name'])
        self.assertEqual(7,len(personTable['columns']))

Collectives™ on Stack Overflow

How do I display the table structure in SQLite3 with python?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related