I'm sorry Willoughby but I don't understand your question. With this csv export/import the problem is that id's change.
For example if you take a look to the auth_user table in my previous post, table auth_membership will have wrong auth_user id's: auth_membership.id <https://dev.tasko.it/mompala/appadmin/select/db?orderby=auth_membership.id> auth_membership.user_id <https://dev.tasko.it/mompala/appadmin/select/db?orderby=auth_membership.group_id>1 3 (John) 2 7 (Sally) And in each application that I have most of them are in relation, so that's a problem. I've take a look to pgloader but it seems complicated to make a migration. If someone have a detailed example it could be useful. Il giorno martedì 31 marzo 2015 20:52:18 UTC+2, Willoughby ha scritto: > > Well it has a bunch of log statements, were all of those OK? > > On Tuesday, March 31, 2015 at 2:19:41 PM UTC-4, Gael Princivalle wrote: >> >> 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.
