Pony ORM

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:

$ pip3 install pony

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  
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 DataFrame.to_sql function:

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

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

[309]