Hmm , I tried this approach (and some other variants from this thread.):
but I run into an error after 15? minutes or so;
The error occurs during the execution of the import_from_csv...
---
Traceback (most recent call last):
File "<console>", line 1, in <module>
File "/Users/acidjunk/GIT/formatics.web2py/gluon/dal.py", line 8519, in
import_from_csv_file
*args, **kwargs)
File "/Users/acidjunk/GIT/formatics.web2py/gluon/dal.py", line 9345, in
import_from_csv_file
curr_id = self.insert(**dict(items))
File "/Users/acidjunk/GIT/formatics.web2py/gluon/dal.py", line 9114, in
insert
ret = self._db._adapter.insert(self, self._listify(fields))
File "/Users/acidjunk/GIT/formatics.web2py/gluon/dal.py", line 1360, in
insert
raise e
timeout: timed out
---
De storage.sqlite is 9.4Mb. The exported csv: just under 1Mb.
Kind Regards Rene
On Wednesday, April 23, 2014 6:25:36 PM UTC+2, Michael Beller wrote:
>
> Over the past week I've experimented with several options (I'm moving from
> sqlite to mysql on python anywhere) and here's what worked for me (and hope
> this helps others) ...
>
> The book describes two methods:
>
> - export/import all data using CSV files (
>
> http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#CSV--all-tables-at-once-
> )
> - copy between databases using script cpdb.py (
>
> http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer?search=cpdb#Copy-data-from-one-db-into-another
> )
>
> Previous comments in this thread highlight two other options:
>
> - sqlite .dump and mysql migration per
>
> http://www.realpython.com/blog/python/web2py-migrating-from-sqlite-to-mysql/#.Ulm3xmTTXCU
> - Mariano/Alan Etkin experimental script -
> https://groups.google.com/d/msg/web2py-developers/QxeJNByj6qc/cpBHsa1ymUkJ
>
> I couldn't get cpdb to work except for a simple model. I'm still learning
> both python and web2py and couldn't debug the script but believe it has
> something to do with the sequence and dependencies between tables (I have
> about 12 tables with numerous foreign keys). This is also true of using
> sqlite .dump and mysql migrate (and I also felt this bypassed web2py which
> requires a fake_migrate and preferred an option "within" web2py since I'm
> also learning MySQL at the same time).
>
> The experimental script seemed straightforward but (1) I wasn't sure how
> to execute the script with both DAL's simultaneously and (2) the primary
> advantage over CSV export/import is the retention of the source row id's
> (which isn't needed if you start with a new database schema - see my
> comments below).
>
> In the end, I used the following procedure using web2py cdv
> export/import to move my production sqlite db to mysql (which only took
> about 7 minutes to execute after learning/testing/experimenting with the
> various options) ...
>
> 1. Export all data in CSV format
> a. open console and navigate to the web2py folder
> b. start web2py in interactive console mode with:
> python web2py.py -S your_app_name -M –P
> c. export data in csv format with:
> db.export_to_csv_file(open('your_app_name_export.csv', 'wb'))
> [this stores the file in the root of the web2py directory]
> d. exit web2py interactive console mode with:
> exit()
> 2. Prepare web2py application for new database and create new database
> a. in console, navigate to application folder
> b. backup existing SQLite database (and corresponding .table files)
> with:
> cp -r databases databases_bak
> c. create empty databases folder with:
> rm -r databases
> mkdir databases
> d. change DAL connection string in app to:
> db = DAL('mysql://user_name:password@mysql.server/database_name')
> [for pythonanywhere, the database_name is in the form
> user_name$database_name]
> e. create new empty mysql database schema (from control panel in
> pythonanywhere or mysql command prompt)
> 3. Generate database tables and load data
> a. start web2py in interactive console mode with:
> python web2py.py -S your_app_name -M –P
> [this will execute the models and generate the mysql database
> tables and the .table files in the database directory]
> c. import data in csv format with:
> db.import_from_csv_file(open('your_app_name_export.csv', 'rb'))
> db.commit() # this is missing from some of the other instructions
> but is required
> d. exit web2py interactive console mode with:
> exit()
> 4. Celebrate!
>
> If you start with a new empty database, all record id's will be the same
> as the source database (and all foreign key references are maintained). If
> the database had previous transactions, the new data will maintain all
> foreign key references but the id's will not match the source data (which
> is only important if there are any code or external references to specific
> id's as Alan pointed out in his posts).
>
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
