Cloning DB with Web2py

[Gaël Princivalle]

Hello.

After the migration from Webfaction to Opalstack I'm still having some troubles with PostgreSQL.

In a first time I was unable to run he scheduler workers, but now installing the Psycog2 library like that this problem is resolved.

pip2 install uwsgi psycopg2-binary http://effbot.org/downloads/Imaging-1.1.7.tar.gz 

My scheduler tasks import in tables some csv files.

Depending of the functions I have:

No errors, but the tables still empty.

This ticket: <class '_csv.Error'> line contains NULL byte

That's quite difficult to understand where's the problem.

I was thinking to clone the PostgreSQL db to a MariaDB one.

How can I do that?

Thanks.

[Gaël Princivalle]

I've tried also like that.

http://www.web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Copy-data-from-one-db-into-another

(env) [user@server web2py]$ python scripts/cpdb.py -f applications/myapp/databases -y postgres://user:pass@localhost:5432/mypostgdb -Y mysql://user:pass@localhost:3306/mymariadb -d ../gluon
gluon path not found
EXCEPTION: could not set DAL
No module named dal
EXCEPTION: could not make a copy of the database
global name 'DAL' is not defined

Of course the web2py directory have the gluon folder and inside there's the dal.py file:

[Clemens]

I'm using the csv export in a simple controller, thus the user (admin) can have a db backup on one click:

(the path and filename in my app is dynamic and not fixed as follows, simplified for better understanding)

if sys.version_info[0] == 2:
    db.export_to_csv_file(open('<path_to_your_backup>/<filename>.csv', 'wb'))
else:

    db.export_to_csv_file(open('<path_to_your_backup>/<filename>.csv', 'w', encoding='utf-8', newline=''))

To recover a database I'm using the console as follows:

python <your_path_to_web2py>/web2py/web2py.py -S <your_application_by_name> -M -P

>>> filename = '<path_to_your_backup>/<filename>.csv'

>>> db.import_from_csv_file(open(str(filename), 'r', encoding='utf-8')); db.commit()

or for python2

>>> db.import_from_csv_file(open(filename, 'rb')); db.commit()

Regards

Clemens

[Gaël Princivalle]

Thank you Clemens.

I'm using Python2.

I was thinking about moving from PosgreSQL to MariaDB becuse the 'import_from_csv_file' don't works.

I was thinking that is was a Psycog2 library problem, but it's the same with mariaDB.

Now I have a MariaDB DB, and for achieving your suggestions I must use import_from_csv_file.

And it don't works, there's not any error.

[user@server web2py7]$ source env/bin/activate
(env) [user@server web2py7]$ cd web2py
(env) [user@server web2py]$ python web2py.py -S myapp -M -P
web2py Web Framework
Created by Massimo Di Pierro, Copyright 2007-2020
Version 2.20.4-stable+timestamp.2020.05.03.05.18.50
Database drivers available: sqlite3, psycopg2, pyodbc, pymysql, imaplib
Python 2.7.16 (default, Nov  1 2020, 19:29:00)
[GCC 4.8.5 20150623 (Red Hat 4.8.5-39)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> filename = 'myfile.csv'
>>> db.import_from_csv_file(open(filename, 'rb')); db.commit()

After that there's no feedback and the DB still empty.

What could affect like that the import_from_csv_file function?

[Clemens]

I have to ask, just to be sure:

Opening the csv file with an editor (or MS Excel). Is it fine, containing all data, not empty? Does the export works correctly?

And a second question:

Where is the csv stored? Is it really in the same location as the web2py.py file? If not you have use the full path to the csv file and not only the filename?

Regards

Clemens