1 What is easycsv?
2 csv statement block and csv statements
3 Some real code examples
4 More complex examples

What is easycsv?

Easycsv is a Python module that implements an abstraction layer that permits insert, delete or update data into a database directly from csv files. Easycsv is implemented on top of the Storm framework, but it can be easily extended to others ORMs such as SQLAchemy and SQLObjects.

edit

csv statement block and csv statements

Easycsv interprets csv statements blocks. Each csv statements block has one header followed by csv statements. The header defines the type that wraps the information about database table and the columns used in the statement execution.

Suppose we've created a table with this SQL:

CREATE TABLE category (name TEXT PRIMARY KEY, parent_name TEXT)

and, using the Storm framework, we've created the class:

class Category(object):
    __storm_table__ = 'category'
    name = Unicode(primary=True)
    parent_name = Unicode()
    parent = Reference(parent_name, name)

so to insert data into category table we just need to define a csv statement block:

Category, Name, Parent
+, Expenses,
+, Income,
+, Internet, Expenses
+, Salary, Income

and if we want to update the parent column of the Internet category:

Category, Name, Parent
+, Home Expenses,
, Expenses,
, Income,
~, Internet, Home Expenses
, Salary, Income

The first column in a csv statement line defines the statement action. Easycsv has three csv statement actions:

insert +
delete -
update ~

the csv statements with no statement action are ignored.

edit

Some real code examples

We will work with the category table and the Category class. As we've created the Category class in the main script, it is defined into the __main__ module. The module's information is important to easycsv find the referred type.

So we've used this code to instance the Storm stuff.

from storm.locals import *

class Category(object):
    __storm_table__ = 'category'
    name = Unicode(primary=True)
    parent_name = Unicode()
    parent = Reference(parent_name, name)

database = create_database('sqlite:')
store = Store(database)
store.execute('CREATE TABLE category (name TEXT PRIMARY KEY, parent_name TEXT)')

After this, the objects can be created:

from easycsv import StormORM

orm = StormORM(store=store)

The following examples continue by this point.

edit

Inserting rows

Define the csv statement block and execute it.

block = '''
Category, Name, Parent
+, Expenses,
+, Income,
+, Internet, Expenses
+, Salary, Income
'''

orm.execute(block, modName='__main__')

The execute method returns a 4-tuple:

(4, 0, 0, 4)

that indicates that we have four rows inserted in a total of four statements sent.

edit

Updating rows

We insert a new category called Home Expenses and update the Internet parent to this early added category.

block = '''
Category, Name, Parent
+, Home Expenses,
, Expenses,
, Income,
~, Internet, Expenses
, Salary, Income
'''

orm.execute(block, modName='__main__')

In this example, the execute method returns

(1, 1, 0, 2)

indicating that one row was inserted, one row was updated in a total of 2 statements sent. The statements without statement action were ignored.

edit

Deleteing rows

Now we want to delete the information related to income category.

block = '''
Category, Name, Parent
, Home Expenses,
, Expenses,
-, Income,
, Internet, Expenses
-, Salary, Income
'''

orm.execute(block, modName='__main__')

And now the execute method returns

(0, 0, 2, 2)

indicating that we've deleted two rows in a total of two statements sent.

edit

More complex examples

Let's create another table

store.execute('CREATE TABLE budget_entry (id_budget_entry INTEGER PRIMARY KEY,'
                  'category_name TEXT, name TEXT, date TEXT,'
                  'amount REAL, scenario TEXT, payed INTEGER )')

and its related class

class BudgetEntry(GenericBase):
    __storm_table__ = "budget_entry"
    id              = Int( name="id_budget_entry", primary=True )
    category_name   = Unicode()
    category        = Reference( category_name, Category.name )
    name            = Unicode()
    date            = Date()
    amount          = Float()
    payed           = Bool()
    scenario        = Unicode()

In the category example the name column is the primary key. So easycsv detects whether the primary key is present in the header, but the primary key could not be always declared. When the primary key is omited we need to specify which columns we want to use as key.

edit

Inserting rows

To populate budget_entry we write:

block = '''
__main__.BudgetEntry, Name, Category, Date, Amount, Scenario, Payed
+, Guitar, Expenses, 2.11.2008, -100.49, plain vanilla, false
+, Phone, Expenses, 15.12.2008, -200.0, plain vanilla, false
+, Xmas Gift, Expenses, 22.11.2008, -50.0, plain vanilla, false
'''

orm.execute(block)

Note that the type was declared with its full name (modName.Type)

edit

Updating rows

After have payed the Guitar and the Phone we need to update budget_entry.

block = '''
__main__.BudgetEntry, {Name}, Category, {Date}, Amount, Scenario, Payed
~, Guitar, Expenses, 2.11.2008, -100.49, plain vanilla, true
~, Phone, Expenses, 15.12.2008, -200.0, plain vanilla, true
, Xmas Gift, Expenses, 22.11.2008, -50.0, plain vanilla, false
'''

orm.execute(block)

We have used the columns Name and Date as keys, wrapping them with braces ({}), it works like a where clause. The delete statement works in the same way.

edit