下面是Python源代码,当然,这只是我的一个示例,读者在实际应用中可能还需要根据实际情况作一些更改。
#
-*- coding: utf-8 -*-
import win32com.client as wc, MySQLdb as mysql
import types, sys, re, time
def getAccessData(sqlstr):
global adoConn, adoRs, fieldNames
adoRs.Open(sqlstr, adoConn, 1 , 3 )
adoRs.MoveFirst()
d = []
fieldNames = []
for i in range(adoRs.RecordCount):
d2 = []
for j in range(len(adoRs.Fields)):
d2.append(adoRs.Fields[j].Value)
if i == 0:
fieldNames.append(adoRs.Fields[j].Name)
d.append(d2)
adoRs.MoveNext()
adoRs.Close()
return d
def addToMySQL(tb, lst, d):
global mcur, mdb
print " current table: " , tb
f = open( " cmd.sql " , " w+ " )
mcur.execute( " truncate table ` " + tb + " `; " )
mcur.execute( " set names utf8; " )
for i in range(len(d)):
sqlstr = " insert into " + tb + " ( " + joinList(lst) + " ) values ( " + joinList2(d[i]) + " ) "
# print sqlstr
sqlstr = sqlstr.encode( " utf-8 " )
# print type(sqlstr)
# print sqlstr
f.write(sqlstr + " ; " )
mcur.execute(sqlstr)
mdb.commit()
f.close()
print " table %s copied! " % tb
def joinList(l):
s = ""
for i in range(len(l)):
s += " , " + str(l[i])
return s[ 1 :]
def joinList2(l):
s = ""
t = ""
# print l
for i in l:
# print i, type(i)
if type(i) == types.UnicodeType:
s += " ,' " + i.replace( " ' " , " /' " ) + " ' "
else :
if type(i) != types.IntType:
s += " ,' " + chkDateTime(str(i)) + " ' "
else :
s += " , " + str(i)
return s[ 1 :]
def chkDateTime(s):
if len(s) == 17 and len(s.partition( " / " )[0]) == 2 :
s = time.strptime(s, " %m/%d/%y %H:%M:%S " )
s = time.strftime( " %Y-%m-%d %H:%M:%S " , s)
return s
if __name__ == " __main__ " :
print " copy data from access to mysql "
tbs = [ " admin " , " ansmark " , " collection " , " difficulty " , " log_login " , " read_history " , " recommend " , " user " ]
tbs += [ " answer " , " guestbook " , " message " , " qu " , " tag " , " friend " ]
# tbs = ["friend"]
# connect to access
adoSource = " E:Studio estdb.mdb "
adoConnStr = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + adoSource
adoConn = wc.Dispatch( " ADODB.Connection " )
adoConn.Open(adoConnStr)
adoRs = wc.Dispatch( " ADODB.RecordSet " )
# connect to mysql
mdb = mysql.connect(host = " localhost " , user = " root " , passwd = " 123456 " , db = " db " )
mcur = mdb.cursor()
for tb in tbs:
sqlstr = " SELECT * FROM [ " + tb + " ]; "
fieldNames = []
d = getAccessData(sqlstr)
addToMySQL(tb, fieldNames, d)
adoConn.Close()
mcur.close()
mdb.close()
import win32com.client as wc, MySQLdb as mysql
import types, sys, re, time
def getAccessData(sqlstr):
global adoConn, adoRs, fieldNames
adoRs.Open(sqlstr, adoConn, 1 , 3 )
adoRs.MoveFirst()
d = []
fieldNames = []
for i in range(adoRs.RecordCount):
d2 = []
for j in range(len(adoRs.Fields)):
d2.append(adoRs.Fields[j].Value)
if i == 0:
fieldNames.append(adoRs.Fields[j].Name)
d.append(d2)
adoRs.MoveNext()
adoRs.Close()
return d
def addToMySQL(tb, lst, d):
global mcur, mdb
print " current table: " , tb
f = open( " cmd.sql " , " w+ " )
mcur.execute( " truncate table ` " + tb + " `; " )
mcur.execute( " set names utf8; " )
for i in range(len(d)):
sqlstr = " insert into " + tb + " ( " + joinList(lst) + " ) values ( " + joinList2(d[i]) + " ) "
# print sqlstr
sqlstr = sqlstr.encode( " utf-8 " )
# print type(sqlstr)
# print sqlstr
f.write(sqlstr + " ; " )
mcur.execute(sqlstr)
mdb.commit()
f.close()
print " table %s copied! " % tb
def joinList(l):
s = ""
for i in range(len(l)):
s += " , " + str(l[i])
return s[ 1 :]
def joinList2(l):
s = ""
t = ""
# print l
for i in l:
# print i, type(i)
if type(i) == types.UnicodeType:
s += " ,' " + i.replace( " ' " , " /' " ) + " ' "
else :
if type(i) != types.IntType:
s += " ,' " + chkDateTime(str(i)) + " ' "
else :
s += " , " + str(i)
return s[ 1 :]
def chkDateTime(s):
if len(s) == 17 and len(s.partition( " / " )[0]) == 2 :
s = time.strptime(s, " %m/%d/%y %H:%M:%S " )
s = time.strftime( " %Y-%m-%d %H:%M:%S " , s)
return s
if __name__ == " __main__ " :
print " copy data from access to mysql "
tbs = [ " admin " , " ansmark " , " collection " , " difficulty " , " log_login " , " read_history " , " recommend " , " user " ]
tbs += [ " answer " , " guestbook " , " message " , " qu " , " tag " , " friend " ]
# tbs = ["friend"]
# connect to access
adoSource = " E:Studio estdb.mdb "
adoConnStr = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + adoSource
adoConn = wc.Dispatch( " ADODB.Connection " )
adoConn.Open(adoConnStr)
adoRs = wc.Dispatch( " ADODB.RecordSet " )
# connect to mysql
mdb = mysql.connect(host = " localhost " , user = " root " , passwd = " 123456 " , db = " db " )
mcur = mdb.cursor()
for tb in tbs:
sqlstr = " SELECT * FROM [ " + tb + " ]; "
fieldNames = []
d = getAccessData(sqlstr)
addToMySQL(tb, fieldNames, d)
adoConn.Close()
mcur.close()
mdb.close()