First, set up a table of data in an SQLite database...
import sqlite3
connection = sqlite3.connect('test.db')
data = [['a', 1],
['b', 2],
['c', 2.0]]
c = connection.cursor()
c.execute('drop table if exists foobar')
c.execute('create table foobar (foo text, bar float)')
for row in data:
c.execute('insert into foobar values (?, ?)', row)
connection.commit()
c.close()
Example using pandas...
import pandas
df = pandas.io.sql.read_frame('select * from foobar', connection)
df.head()
foo | bar | |
---|---|---|
0 | a | 1 |
1 | b | 2 |
2 | c | 2 |
3 rows × 2 columns
Example using petl...
import petl.interactive as etl
tbl = etl.fromdb(connection, 'select * from foobar')
tbl.head()
foo | bar |
---|---|
a | 1.0 |
b | 2.0 |
c | 2.0 |
Example using petl with the petlx.ipython extension to provide a table caption...
import petlx.ipython
tbl.display(caption='example')
foo | bar |
---|---|
a | 1.0 |
b | 2.0 |
c | 2.0 |
Using the petlx.ipython extension you can also display two or more tables in the output of a single cell, and can customise the way values are represented...:
tbl.display(caption='example (str)')
tbl.display(caption='example (repr)', representation=repr)
foo | bar |
---|---|
a | 1.0 |
b | 2.0 |
c | 2.0 |
foo | bar |
---|---|
u'a' | 1.0 |
u'b' | 2.0 |
u'c' | 2.0 |
Using the petlx.dataframe extension you can move between petl and pandas...
import petlx.dataframe
tbl2 = etl.fromdataframe(df)
tbl2
foo | bar |
---|---|
a | 1.0 |
b | 2.0 |
c | 2.0 |
df2 = tbl.todataframe()
df2
foo | bar | |
---|---|---|
0 | a | 1 |
1 | b | 2 |
2 | c | 2 |
3 rows × 2 columns