CODEWITHSUNDEEP
pythonpandassqlite
a11
a11

Reputation: 3406

sqlite how to return the output of "PRAGMA table_info()" in a Pandas dataframe?

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

Upvotes: 0

Views: 2454

Answers (1)

furas
furas

Reputation: 143216

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)

Upvotes: 2

Related Questions