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]