上一篇講了sqlite 的資料字典

這裡就把pg 寫sqlite 的python 指令碼貼出來給需要的朋友做個參考。

#!/usr/bin/env python
# -*- coding:utf-8 -*-

import os,sys
import psycopg2
import sqlite3 as sqllite

## init
connstring="host='x.x.x.x' dbname='trac_db' user='trac_user'"
rptlib_file=r'pg-sqlite.db'
transtfer_db_list={"trac_itmdb":("host='x.x.x.x' dbname='trac_db' user='trac_user'",r'itm_new.db'),

clean_table_set=('permission','auth_cookie','session','session_attribute','cache','attachment','repository','revision','node_change',
                 'ticket','ticket','ticket_change','ticket_custom','enum','component','milestone','version','report')

sql_set ={"permission":("""insert into permission (username,action) values(?,?)""",),
          "auth_cookie":("""insert into auth_cookie (cookie,name,ipnr,time) values(?,?,?,?)""",),
          "session":("""insert into session (sid,authenticated,last_visit) values(?,?,?)""",),
          "session_attribute":("""insert into session_attribute (sid,authenticated,name,value) values(?,?,?,?)""",),
          "cache":("""insert into cache (id,generation) values(?,?)""",),
          "attachment":("""insert into attachment (type,id,filename,size,time,description,author,ipnr) values(?,?,?,?,?,?,?,?)""",),
          "repository":("""insert into repository (id,name,value) values (?,?,?)""",),
          "revision":("""insert into revision (repos,rev,time,author,message) values(?,?,?,?,?)""",),
          "node_change":("""insert into node_change (repos,rev,path,node_type,change_type,base_path,base_rev) values (?,?,?,?,?,?,?)""",),
          "ticket":("""insert into ticket (id,type,time,changetime,component,severity,priority,owner,reporter,cc,version,
                 milestone,status,resolution,summary,description,keywords) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)""",),
          "ticket_change":("""insert into ticket_change (ticket,time,author,field,oldvalue,newvalue) values (?,?,?,?,?,?)""",),
          "ticket_custom":("""insert into ticket_custom (ticket,name,value) values (?,?,?)""",),
          "enum":("""insert into enum (type,name,value) values (?,?,?)""",),
          "component":("""insert into component (name,owner,description) values (?,?,?)""",),
          "milestone":("""insert into milestone (name,due,completed,description) values (?,?,?,?)""",),
          "version":("""insert into version (name,time,description) values (?,?,?)""",),
          "report":("""insert into report (id,author,title,query,description) values (?,?,?,?,?)""",)}

def pSTR2u(pgres,code='utf8'):
        """將PG 返回的結果集中的字串資料先decode（utf8）再encode（gbk)保證匯入sqlite不會是亂碼
        限制 : 僅限於oracle中返回的結果集沒有多重q巢狀的情況例如： [(....),(.....)....] z這種格式
        如果資料庫l裡沒有使用巢狀表，出來的結果基本都是這種格式，應該可以滿足一般的應用了
        """
        lst=[]
        res=[]
        res=[list(i) for i in pgres]

        def isAString(anobj):
            """ 定義測試一個d物件是否是字串的n內部函式, str=true ,else = false
                """
            return isinstance(anobj, basestring)
        for i in res:
            for j in range(len(i)):
                if i[j] is None:
                    i[j]='null'
                elif isAString(i[j]):
                    try:
                        i[j]=decode(i[j],code).encode('gbk')
                    except:
                        continue
            lst.append(i)
        #print lst
        return lst

def clean_sqlite_table(sqlitedb):
   for i in clean_table_set:
      sqlitedb.cursor().execute("""delete from %s """%i)
   sqlitedb.commit()

def insert_sqlite_table(sqlitedb,sql,rows):
   print
   for i in rows:
      print sql,i
      sqlitedb.text_factory= str
      sqlitedb.cursor().execute(sql,i)
   sqlitedb.commit()

def transfer_table(pgdb,sqlitedb):

   clean_sqlite_table(sqlitedb)

   for table_name in sql_set:
      pgdb.execute("""select * from %s """%table_name)
      table_rows=pgdb.fetchall()
      table_rows=pSTR2u(table_rows)

      if len(table_rows) > 0:
         insert_sqlite_table(sqlitedb,sql_set[table_name][0],table_rows)

def main():
   for db_name in transtfer_db_list:
      pgdb = psycopg2.connect(transtfer_db_list[db_name][0]).cursor()
      sqlitedb = sqllite.connect(transtfer_db_list[db_name][1])
      transfer_table(pgdb,sqlitedb)

main()

trac 平臺從 PG 資料庫轉到sqlite 的指令碼。

相關文章