Pony ORM is another Python ORM package. Pony ORM is written in pure Python and has automatic query optimization and a GUI database schema editor. It also supports automatic transaction management, automatic caching, and composite keys. Pony ORM uses Python generator expressions, which are translated in SQL. Install it as follows:

$ sudo pip install pony
$ pip freeze|grep pony
pony==0.5.1

Import the packages we will need in this example. Refer to the pony_ride.py file in this book's code bundle:

from pony.orm import Database, db_session 
from pandas.io.sql import write_frame
import statsmodels.api as sm

Create an in-memory SQLite database:

db = Database('sqlite', ':memory:')

Load the sunspots data and write it to the database with the pandas write_frame() function:

with db_session:
    data_loader = sm.datasets.sunspots.load_pandas()
    df = data_loader.data
    write_frame(df, "sunspots", db.get_connection())
    print db.select("count(*) FROM sunspots")

The number of rows in the sunspots table is printed as follows:

[309]