[PonyORM-list] what is the best way to export sqlite database to csv file with pony ?

[Alexander Kozlovsky]

Hi Vadim!

I'd like to inform you that I just added the to_dict() method, which can be
useful for converting Pony objects to CSV, JSON or any other format. I want
to show you how this method can be used.

At first, let's import a module from Pony ORM examples and get an object
which we want to convert to dictionary:

    >>> from pony.orm.examples.presentation import *
    >>> populate_database()
    >>> s = Student[1]

Now we can convert this object to a dictionary by the following way:

    >>> s.to_dict()
    {'group': 101, 'name': u'John Smith', 'dob': datetime.date(1991, 3,
20), 'gpa': 3.0, 'tel': '123-456', 'id': 1}

If we want to extract specific fields only, we can specify the list of
attributes:

    >>> s.to_dict(['id', 'name', 'group'])
    {'group': 101, 'id': 1, 'name': u'John Smith'}

or

    >>> s.to_dict("id name group")
    {'group': 101, 'id': 1, 'name': u'John Smith'}

By default the related objects are represented by its primary keys, but it
is possible to get full objects by specifying `related_objects=True`. This
way you can convert related objects recursively:

    >>> s.to_dict(['id', 'name', 'group'], related_objects=True)
    {'group': Group[101], 'id': 1, 'name': u'John Smith'}

By default, collections and lazy attributes (such as BLOBs) are not
included to the to_dict() result. It can be changed by specifying
`with_collections=True` and `with_lazy=True`.

Now I want to show how you can store database data to CSV format using
standard python csv module and new to_dict() method. Let's store the
content of the Student table to the CSV file:

    from pony.orm.examples.presentation import Student
    import csv
    fields = ['id', 'name', 'group', 'gpa', 'tel']
    csv_file = open('students.csv', 'w')
    writer = csv.DictWriter(csv_file, fields)
    writer.writeheader()
    for s in Student.select():
        writer.writerow(s.to_dict(fields))
    csv_file.close()

If you want to export all entity attributes in the same order as they
defined, you can write universal function like this:

    def export_to_csv(entity, filename):
        fields = [ attr.name for attr in entity._attrs_with_columns_ if not
attr.lazy ]
        with open(filename, 'wb') as f:
            writer = csv.DictWriter(f, fields)
            writer.writeheader()
            for obj in entity.select():
                writer.writerow(obj.to_dict(fields))

Then you can use this function in the following way:

    export_to_csv(Student, 'students.csv')

to_dict() method is available in Pony ORM development version which can be
taken from GitHub:
https://github.com/ponyorm/pony

We plan to release the new version Pony ORM 0.5.2 on Monday which includes
to_dict() method, and you will be able to install it from PyPI using
pip install pony --upgrade


Regards,
Alexander



On Mon, Aug 4, 2014 at 6:53 PM, Вадим Бажов <vadim at ideco.ru> wrote:

> Hi, folks !
> I created and filled an sqlite db with data using Pony ORM. It's clean and
> easy.
> Now i need to be able to convert the database to windows Excell-friendly
> representation for the hiegher autority.
> As i suppose, the csv is the most easy and suitable file format for that
> case.
> So, please, share your experiences with me what is the best way to do such
> an export with Pony? If pony hasn't such export ability (what i didn't
> find), tell me what tools do you usually use for that task?
>
> --
> С уважением,
> Бажов Вадим,
> Инженер отдела технической поддержки,
> Компания Айдеко
> --
> Телефоны: +7 (495) 987-32-70; +7 (495) 662-87-34 (тех. поддержка); +7
> (343) 345-15-75; Факс: +7 (343) 383-75-13
>
> Электронная почта:
> Вопросы по приобретению: sales at ideco.ru
> Технические вопросы: support at ideco.ru
> Партнерство: partners at ideco.ru
> Общие вопросы: info at ideco.ru
>
> Сайт: http://www.ideco.ru/
> Форум: http://www.ideco.ru/forum/
>
> _______________________________________________
> ponyorm-list mailing list
> ponyorm-list at ponyorm.com
> /ponyorm-list
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: </ponyorm-list/attachments/20140808/66f20765/attachment.html>