Overview
This section shows a simple example of using Pandemy to write a pandas.DataFrame
to a SQLite database and reading it back again.
Save a DataFrame to a table
Let’s create a new SQLite database and save a pandas.DataFrame
to it.
# overview_save_df.py
import io
import pandas as pd
import pandemy
# Data to save to the database
data = io.StringIO("""
ItemId,ItemName,MemberOnly,Description
1,Pot,0,This pot is empty.
2,Jug,0,This jug is empty.
3,Shears,0,For shearing sheep.
4,Bucket,0,It's a wooden bucket.
5,Bowl,0,Useful for mixing things.
6,Amulet of glory,1,A very powerful dragonstone amulet.
""")
df = pd.read_csv(filepath_or_buffer=data, index_col='ItemId') # Create a DataFrame
# SQL statement to create the table Item in which to save the DataFrame df
create_table_item = """
-- The available items in General Stores
CREATE TABLE IF NOT EXISTS Item (
ItemId INTEGER,
ItemName TEXT NOT NULL,
MemberOnly INTEGER NOT NULL,
Description TEXT,
CONSTRAINT ItemPk PRIMARY KEY (ItemId)
);
"""
db = pandemy.SQLiteDb(file='Runescape.db') # Create the SQLite DatabaseManager instance
with db.engine.begin() as conn:
db.execute(sql=create_table_item, conn=conn)
db.save_df(df=df, table='Item', conn=conn)
$ python overview_save_df.py
The database is managed through the DatabaseManager
class (in this case the SQLiteDb
instance). Each SQL dialect is a subclass of DatabaseManager
. The initialization of the DatabaseManager
creates the database engine
, which is used to create a connection to the database.
The engine.begin()
method returns a context manager with an open database transaction,
which commits the statements if no errors occur or performs a rollback on error. The connection is automatically returned to the engine’s
connection pool when the context manager exits. If the database file does not exist it will be created.
The DatabaseManager.execute()
method allows for execution of arbitrary SQL statements such as creating a table.
The DatabaseManager.save_df()
method saves the pandas.DataFrame
df
to the table Item in the database db
.
Load a table into a DataFrame
The pandas.DataFrame
saved to the table Item of the database Runescape.db can easily be read back into a pandas.DataFrame
.
# overview_load_table.py
import pandemy
db = pandemy.SQLiteDb(file='Runescape.db', must_exist=True)
sql = """SELECT * FROM Item ORDER BY ItemId;""" # Query to read back table Item into a DataFrame
with db.engine.connect() as conn:
df_loaded = db.load_table(sql=sql, conn=conn, index_col='ItemId')
print(df_loaded)
$ python overview_load_table.py
ItemName MemberOnly Description
ItemId
1 Pot 0 This pot is empty.
2 Jug 0 This jug is empty.
3 Shears 0 For shearing sheep.
4 Bucket 0 It's a wooden bucket.
5 Bowl 0 Useful for mixing things.
6 Amulet of glory 1 A very powerful dragonstone amulet.
If the must_exist
parameter is set to True
pandemy.DatabaseFileNotFoundError
will be raised if the
database file is not found. This is useful if you expect the database to exist and you want to avoid creating a new
database by mistake if it does not exist. The engine.connect()
method is
similar to engine.begin()
, but without opening a transaction.
The DatabaseManager.load_table()
method supports either a table name or a
sql statement for the sql
parameter.