As an alrenative. you can upload a csv file into web2py. Not a solution if you want to do things dynamically and all the time. But you can use the csv option.
On Tuesday, March 31, 2015 at 4:11:18 AM UTC-4, Gael Princivalle wrote: > > Hello. > > Like a lot of users I need to clone an existing SQLlite db to new > PostgreSQL db. > I've read this post in web2py-developers by Alan Etkin: > > https://groups.google.com/forum/#!searchin/web2py-developers/sqlite$20postgres/web2py-developers/QxeJNByj6qc/cpBHsa1ymUkJ > > However I've tried to use this script and my PosgreSQL db still empty. > > My db: > dbsq = DAL('sqlite://storage.sqlite',pool_size=1,check_reserved=['all']) > dbpg = DAL('postgres://user:pass@localhost:5432/postg_myapp',pool_size=1, > check_reserved=['all']) > > My controller: > def migrate_to_pg(): > migrate(dbsq, dbpg) > return dict() > > def disable_triggers(pgdb): > for tablename in pgdb.tables(): > pgdb.executesql("ALTER TABLE %s DISABLE TRIGGER ALL;" % \ > tablename) > > def enable_triggers(pgdb): > for tablename in pgdb.tables(): > pgdb.executesql("ALTER TABLE %s ENABLE TRIGGER ALL;" % \ > tablename) > > def migrate(sqlitedb, pgdb): > """Transfer data to PostgreSQL > > Arguments: > sqlitedb: the Sqlite db source connection > pgdb: the PostgreSQL db target connection > """ > import logging > setval = "select setval('%s'::regclass" > setval += ", (SELECT MAX(%s) FROM %s));" > logging.debug("Disabling pg triggers temporarily") > disable_triggers(pgdb) > for table in pgdb.tables: > logging.debug("copying %s" % table) > query = pgdb[table]._id > 0 > rows = pgdb(query).select() > colnames = [k[k.index(".")+1:] for k in rows.colnames] > logging.debug(pgdb._lastsql) > logging.debug(colnames) > rows = sqlitedb.executesql(pgdb._lastsql) > # clean up table > pgdb.executesql("TRUNCATE %s" % table) > for i, row in enumerate(rows): > logging.debug("inserting", i, len(rows)) > sql = "INSERT INTO %s (%s) VALUES (%s)" % ( > table, > ','.join(colnames), > ','.join(["%s" for k in colnames]), > ) > logging.debug(sql) > pgdb.executesql(sql, row) > # update serials > sequence = pgdb[table]._sequence_name > if sequence in ["", None]: > sequence = "%s_%s_seq" % (table, colnames[0]) > pgdb.executesql(setval % (sequence, > colnames[0], > table)) > logging.debug("Re-enabling pg triggers") > enable_triggers(pgdb) > pgdb.commit() > logging.debug("Migration done") > > When I run migrate_to_pg I don't have any kind of error but the PosgreSQL > db still empty. > > Someone can give me a help, or another solution for this migration? > I think it's necessary for web2py having a tool for this migration, giving > an easy way for fresh web2py users like me that understand that they must > migrate some existing applications to PostgreSQL to do it without headaches. > > Thanks, regards. > > -- 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.
