0

Using the sqlite3 module in Python, is there a way to return the output of "PRAGMA table_info()" in a Pandas dataframe? I am not sure how to implement it in any of the Pandas read_sql functions.

For example,

conn = sqlite3.connect(db)
info = conn.execute("PRAGMA table_info('mytable')").fetchall()
conn.close()
print info

Returns [(0, u'id', u'INTEGER', 0, None, 1), (1, u'name', u'TEXT', 1, None, 0), (2, u'date', None, 0, current da...
Using the SQLite command line, PRAGMA table_info(mytable); returns the following, which is much easier to read:

cid         name        type        notnull     dflt_value  pk        
----------  ----------  ----------  ----------  ----------  ----------
0           id          integer     0                       1         
1           name        text        1                       0         
2           date                    0           current_da  0
a11
  • 3,122
  • 4
  • 27
  • 66
  • `pd.DataFrame(info)` ? OR with column names `pd.DataFrame(info, columns=['cid', 'name', 'type', 'notnull', 'dflt_value pk'])` ? – furas Apr 01 '21 at 01:08

1 Answers1

2

If you have

info = [(0, u'id', u'INTEGER', 0, None, 1), (1, u'name', u'TEXT', 1, None, 0), (2, u'date', None, 0, 'current da', 0)]

then

import pandas as pd

df = pd.DataFrame(info, columns=['cid', 'name', 'type', 'notnull', 'dflt_value', 'pk'])

print(df)

Result:

   cid  name     type  notnull     dflt_value  pk
0    0    id  INTEGER        0           None   1
1    1  name     TEXT        1           None   0
2    2  date     None        0     current da   0

EDIT:

Not tested

import sqlite3
import pandas as pd

conn = sqlite3.connect(db)
cur = conn.cursor()
info = cur.execute("PRAGMA table_info('mytable')").fetchall()
columns = [item[0] for item in cur.description]

df = pd.DataFrame(info, columns=columns)

print(df)

The same without cursor and fetchall()

import sqlite3
import pandas as pd

conn = sqlite3.connect(db)

info = conn.execute("PRAGMA table_info('mytable')")
columns = [item[0] for item in info.description]

df = pd.DataFrame(info, columns=columns)

print(df)

It should also works with pd.read_sql() and pd.read_sql_query()

import sqlite3
import pandas as pd

conn = sqlite3.connect(db)

df = pd.read_sql("PRAGMA table_info('mytable')", conn)
#df = pd.read_sql_query("PRAGMA table_info('mytable')", conn)

print(df)
furas
  • 134,197
  • 12
  • 106
  • 148
  • that's true, but it requires specifying the column names. does pandas have a built-in way to do this with the read_sql() functions? – a11 Apr 01 '21 at 03:02
  • I think you should rather search column names in some function in `sqlite3` - if I not wrong it could be something with name `description` – furas Apr 01 '21 at 03:07
  • see `coursor.description` and/or `connection.row_factory = sqlite3.Row` in answers for [Is there a way to get a list of column names in sqlite?](https://stackoverflow.com/questions/7831371/is-there-a-way-to-get-a-list-of-column-names-in-sqlite) – furas Apr 01 '21 at 03:11
  • I added example with `cursor.description` but I didn't test it - I don't have database to test it. – furas Apr 01 '21 at 03:17
  • `df = pd.read_sql("PRAGMA table_info('mytable')", conn)` works, I didn't know you could pass pragma into there. thank you! – a11 Apr 01 '21 at 14:29