Generating raw SQL and exporting and importing data in web2py

Exporting and Importing Data in Web2py

As the name recommends in this section, we will be discussing how to import and export data, as these two things are very important to program a well-structured dynamic web content using the web2py framework.

Let us see how we do it.

CSV File (One table taken at  a time)

When Rows object is converted to a string, then it is automatically serialized in CSV.

Following is the code for it

>>> rows = db(db.personI.id == db.thing.owner_id).select()
>>> print rows
person.id,person.name,thing.id,thing.name,thing.owner_id
1,Ankit,1,Bat,1
1,Ankit,2,Chalk,1
2,Babli,3,Soap,2

We can serialize a single table in CSV and store it in a file "sample.csv":

with open('sample.csv', 'wb') as dumpfile:

dumpfile.write(str(db(db.person).select()))

In Python 3, it can be written as

>>> open('sample.csv', 'w', encoding='utf-8', newline='').write(str(db(db.person.id).select()))

We can read the csv file with the help of the following command.

with open('sample.csv', 'rb') as dumpfile:
    db.person.import_from_csv_file(dumpfile)

Or, in python, we can do it with the help of the following command.

>>> rows = db(db.person.id).select()
>>> rows.export_to_csv_file(open('sample.csv', 'w', encoding='utf-8', newline=''))

Web2py searches the CSV header for the field names before importing data. Two columns are found in this instance: "person.id" and "person.name." Both the "person." prefix and the "id" fields are disregarded. Then, all records are added and given new identifiers. The online interface for appadmin can be used to carry out either of these actions.

CSV File(All at once)

To export the database all at once, we can use the following command.

with open('anyfile.csv', 'wb') as dumpfile:
    db.export_to_csv_file(dumpfile)

To import the database all at once, we can use the following command

with open('anyfile.csv', 'rb') as dumpfile:
    db.import_from_csv_file(dumpfile)

Or, in Python we can export using the following command.

>>> db.export_to_csv_file(open('sample.csv', 'w', encoding='utf-8', newline=''))

And to import, we use the following command

>>> db.import_from_csv_file(open('sample.csv', 'r', encoding='utf-8', newline=''))

This can also be used when importing databases and exporting a database of different types.

"anyfile.csv" stores the data, and the tablename is indicated by each starting line.

TABLE tablename
field1,field2,field3,...

Two tables separated by \r\n\r\n (i.e., two empty lines). The file ends with the line.

END

If the uploaded files are not kept in the database, they are not included in the file. The upload files that are saved on the filesystem must be extracted individually; often, a zip of the "uploads" folder will do. 

If the database is not empty, the new records will be added when they are imported. Web2py will restore references so that links are not broken even when new imported records often do not have the same record id as the original (saved) data.

The uuid field in a table will be utilised to spot duplication if it is present. Additionally, a record will be changed if an imported record has the same uuid as an already-existing record.

CSV and Remote Database Synchronization

Let's see the following model.

db.define_table('person',
                Field('name'))
db.define_table('thing',
                Field('name'),
                Field('owner_id', 'reference person'))


# usage example
if db(db.person).isempty():
    nid = db.person.insert(name='Massimo')
    db.thing.insert(name='Chair', owner_id=nid)


Each record is assigned identification and is referred to by that identifier. If there are two copies of the database that are utilised by different web2py installations, the id is unique only inside each database and not across them. This is a difficulty when combining records from many databases.
For making the record uniquely identifiable across databases:
Must have a unique id(UUID)
Must have the last modification time for track in the most recent among multiple copies
reference the UUID instead of the id


After the model is changed in the following way, this can be achieved.


import uuid


db.define_table('person',
                Field('uuid', length=64),
                Field('modified_on', 'datetime', default=request.now, update=request.now),
                Field('name'))
db.define_table('thing',
                Field('uuid', length=64),
                Field('modified_on', 'datetime', default=request.now, update=request.now),
                Field('name'),
                Field('owner_id', length=64))

db.person.uuid.default = db.thing.uuid.default = lambda:str(uuid.uuid4())


db.thing.owner_id.requires = IS_IN_DB(db, 'person.uuid', '%(name)s')

# usage example
if db(db.person).isempty():
    nid = str(uuid.uuid4())
    db.person.insert(uuid=nid, name='Massimo')
    db.thing.insert(name='Chair', owner_id=nid)

Now creating a controller action for exporting the database

def export():
    s = StringIO.StringIO()
    db.export_to_csv_file(s)
    response.headers['Content-Type'] = 'text/csv'
    return s.getvalue()

Creating a controller action to sync records and import a saved copy of the other database:

def import_and_sync():
    form = FORM(INPUT(_type='file', _name='data'), INPUT(_type='submit'))
    if form.process().accepted:
        db.import_from_csv_file(form.vars.data.file, unique=False)
        #  every table
        for tablename in db.tables:
            table = db[tablename]
            #  every uuid, delete all but the latest
            items = db(table).select(table.id, table.uuid,
                                     orderby=~table.modified_on,
                                     groupby=table.uuid)
            for item in items:
                db((table.uuid == item.uuid) & (table.id != item.id)).delete()
    return dict(form=form)

We create an index manually to make the search by uuid faster. Alternatively, XML-RPC can be used to export/import the file if the records refer to uploaded files, we export/import the uploads folder's contents.

XML and HTML (one Table at a time)

Rows objects also do have an XML method (like helpers) that serializes it to XML/HTML:

>>> rows = db(db.person.id == db.thing.owner_id).select()
>>> print rows.xml()
<table>
<thead>
<tr><th>person.id</th><th>person.name</th><th>thing.id</th><th>thing.name</th><th>thing.owner_id</th></tr>
</thead>
<tbody>
<tr class="w2p_odd odd"><td>1</td><td>Alex</td><td>1</td><td>Boat</td><td>1</td></tr>
<tr class="w2p_even even"><td>1</td><td>Alex</td><td>2</td><td>Chair</td><td>1</td></tr>
<tr class="w2p_odd odd"><td>2</td><td>Bob</td><td>3</td><td>Shoes</td><td>2</td></tr>
</tbody>
</table>

For serializing the Rows in any other XML format along with custom tags, we can easily do using the universal TAG helper and the Python syntax *<iterable> allowed in function calls:

>>> rows = db(db.person).select()
>>> print TAG.result(*[TAG.row(*[TAG.field(r[f], _name=f) for f in db.person.fields]) for r in rows])


<result>
<row><field name="id">1</field><field name="name">Ankit</field></row>
<row><field name="id">2</field><field name="name">Banty</field></row>
<row><field name="id">3</field><field name="name">Charles</field></row>
</result>

Frequently Asked Questions

What is web2py?

The web2py is an open-source framework that enables us to program dynamic content on the website using the python programming language.

What is raw SQL?

There are several instances where we don't want to execute a SQL statement; we just need to generate it. These generated SQL statements are known as raw SQL.

What is the difference in commands to generate a raw SQL database?

When a command starts with an underscore, it has to generate raw SQL.

What is needed to make the record uniquely identifiable across databases?

The conditions are it must have a unique id(UUID), must have the last modification time for track in the most recent among multiple copies, and reference the UUID instead of the id.

Which field is used to spot the duplication in the table?

The uuid field in a table will be utilized to spot duplication if it is present. 

Conclusion

In this article, we extensively discussed how to generate raw SQL using web2py and how to export and import data in web2py, which is an open-source language. Very fast, fluid in nature. We started with the introduction, and then we discussed generating raw SQL commands. Ways to export and import data. In the end, we discussed a few frequently asked questions.

Now you must be curious after knowing about web2py and how simple it is to learn. You can visit Basics of Python with Data Structures and Algorithms and Free Python Foundation with DS and Algo and start your journey.

Nevertheless, you may consider our paid courses to give your career an edge over others!

Do upvote our blogs if you find them helpful and engaging!

Happy Learning!