SQLite

最近在弄Mobile上的資料庫存取，由於原本是用SQLite，但是專案現在要用MySQL，所以只好把MySQL轉成SQLite，在網路上找了一些方法，最後採用python來轉，這倒是搞了我一天的時間，但最終能轉成功了！也學了些python的使用，雖然還不是很會，不過也寫了些工具來用。

這程式是用來產生gsoap的程式，輸入網址和要命名的名稱，就會產生出gsoap c++的檔案，再把檔案放到專案上編譯即可：

import os

strURL = raw_input("tell me the url ")
strHeadFileName = raw_input("tell me the head file ")
# 記得要把路徑改掉
strImportDir = "C:\\gsoap-2.7\\gSOAP\\import\\"
strExeDir = "C:\\gsoap-2.7\\gsoap\\bin\\win32\\"

strURL += "?wsdl"

strExeNameList = ["" , ""]
strParamList = ["" , ""]

strExeNameList[0] = strExeDir + "wsdl2h.exe"

strExeNameList[1] = strExeDir + "soapcpp2.exe"

strParamList[0] = "-N" + strHeadFileName + " -o" +  strHeadFileName + ".h" + " " + strURL

strParamList[1] = "-C -w -x -I" + strImportDir + " " + strHeadFileName+ ".h"

iLen = len(strExeNameList)
for i in range(iLen):
    strCmd = strExeNameList[i] + " " + strParamList[i]
    print strCmd
    os.system( strCmd )

再來這程式就是MySQL轉SQLite了，要安裝MySQLdb這模組，sqlite3在python 2.5以上都是內建的，還有，我只轉換一個table而已，有需要的就自行增加，編碼也要注意，我之前沒注意到，搞得轉出來的中文字都變亂碼..OTL：

# -*- coding: utf-8 -*-
import sys,os
import string
import MySQLdb
import sqlite3 as sqlite

# change to match your mysql server account:
bibusdb = "ec_scenic_db"
mysqlhost = "localhost"
mysqluser = "root"
mysqlpasswd = "97381687"

# output SQLite file:
destfile = "ec_scenic_db.db"

# Create Table
SqliteTabs = """
BEGIN TRANSACTION;
DROP TABLE IF EXISTS "admin_title";
CREATE TABLE admin_title ( ID INTEGER NOT NULL primary key autoincrement,ATID VARCHAR(10) default NULL COLLATE SQLITE_UTF8,ATname VARCHAR(10) default NULL COLLATE SQLITE_UTF8,ATstatus VARCHAR(1) default NULL COLLATE SQLITE_UTF8);
COMMIT;
"""

print "connecting to MySQL db..."
try:
        conn1 = MySQLdb.connect(host = mysqlhost,user =mysqluser,passwd = mysqlpasswd,db = bibusdb,use_unicode=True,charset="utf8")
except MySQLdb.Error, e:
       print "Error %d: %s" % (e.args[0], e.args[1])
       sys.exit (1)                        
       
c1 = conn1.cursor()
c1.execute ("USE " + bibusdb)

print "connecting to SQlite db..."
conn2        = sqlite.connect(destfile)
c2                 = conn2.cursor()
c2.executescript(SqliteTabs)


# Insert Data
print "querying admin_title table..."
c1.execute("SELECT * FROM admin_title  ORDER BY ID")
res = c1.fetchall()


print "copying admin_title table..."
query = u"INSERT INTO admin_title VALUES("
for x in res[0] :
        query = query + '?,'        
query = query[0:-1] + ')'
        
for src in res :
        try:
                c2.execute(query,src)
        except:
                print "Error: " + query
                break        

print "commiting changes...."


#close connection:
c1.close()
conn1.close()
conn2.commit()
c2.close()
conn2.close()

還有個重點，當使用Mobile模擬器裝置時，若你用SQlite讀取的db檔不是存放在模擬器內部，只要你想要對此db做更新或修改的話，就會出現Disk I/O Error的錯誤訊息！