Thanks Ron but:with CSV export: auth_user_id user 3 John 7 Sally
And then you import your CSV file you will have: auth_user_id user 1 John 2 Sally And so all relations between tables will be broken. Someone have a solution? Il giorno martedì 31 marzo 2015 15:10:53 UTC+2, Ron Chatterjee ha scritto: > > 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.
