2019-08-10

使用Python 處理 SQLite3 跨資料庫檔案間的資料表資料複製


  1. 作法:
    建立兩個connection,分別對應來源及目標資料庫檔案
    一次讀取來源的所有資料,再逐筆寫入目標資料表內
  2. 程式碼:
    import sqlite3
    def dict_factory(cursor, row):
        d = {}
        for idx, col in enumerate(cursor.description):
            d[col[0]] = row[idx]
        return d
    ##來源資料庫
    dbName="LineNotifierStock-20190731.db3"
    oConnSource=sqlite3.connect(dbName)
    ##目標資料庫
    dbName="StockNotifier.db3"
    oConnTarget=sqlite3.connect(dbName)
    ##作法:一次讀取來源的所有資料,再逐筆寫入目標資料表內
    zSQL="Select * from StdToNote
    oConnSource.row_factory = dict_factory
    cSource=oConnSource.cursor()
    cSource.execute(zSQL)
    oSource=cSource.fetchall()
    print(oSource)
    for aSource in oSource :
        zSQL="""INSERT INTO StdToNote
                (stockID,stockName,doNoteOpen,doNoteRate,
                doNotePrice,doNoteAccVol,doNoteUpDown,noteType,
                refPrice,rateHigh,rateLow,priceHigh,priceLow,memo)
                values('%s','%s','%s','%s','%s','%s','%s','%s',%f,%f,%f,%f,%f,'%s')
        """
        zSQL=zSQL%(aSource['stockID'],aSource['stockName'],aSource['isNoteOpen'], \
                aSource['isNoteRate'],aSource['isNotePrice'],aSource['isNoteAccVol'],
                aSource['isNoteUpDown'],aSource['noteType'],aSource['refPrice'], \
                aSource['rateHigh'],aSource['rateLow'],aSource['priceHigh'], \
                aSource['priceLow'],aSource['memo'])
        oConnTarget.execute(zSQL)
        oConnTarget.commit()
    oConnSource.close()
    oConnTarget.close()
    print("Data Copied Ready!!!")
  3. 查看執行結果:

沒有留言:

張貼留言