Pandemy 1.2.0

Dependencies

The core dependencies of Pandemy are:

• pandas : powerful Python data analysis toolkit

• SQLAlchemy : The Python SQL Toolkit and Object Relational Mapper

Databases except for SQLite require a third-party database driver package to be installed. The table below lists database driver packages for supported databases and their corresponding optional dependency identifier.

To install cx_Oracle together with Pandemy run:

$ pip install Pandemy[oracle]

When using conda supply the driver package as a separate argument to the install command:

$ conda install -c conda-forge pandemy cx_oracle

Save a DataFrame to a table

Let’s create a new SQLite database and save a DataFrame to it.

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)

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 begin method of the engine 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 execute method allows for execution of arbitrary SQL statements such as creating a table. The save_df method saves the DataFrame df to the table Item in the database db.

Load a table into a DataFrame

The DataFrame saved to the table Item of the database Runescape.db can easily be read back into a DataFrame.

import pandas as pd
from pandas.testing import assert_frame_equal
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')

assert_frame_equal(df, df_loaded, check_dtype=False)
print(df)

               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 connect method of the engine is similar to begin, but without opening a transaction. The load_table method supports either a table name or a sql statement for the sql parameter.