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.
