Python調用MySQL的一些用法小結

標籤：

目標：1個excel表內容匯入到資料庫中，例如：原始excel檔案為 aaa.xls 

 

首先：將aaa.xls 轉換成aaa.txt ,注意當檔案中含有中文字元時，可以通過notepad++開啟，在“格式”下選擇轉存為utf8格式或者選擇utf8格式再另存

即：

txtFile = aaa.txt

 

#引入MySQLdb庫，這個庫的安裝再另外寫

import MySQLdb

 

#定義資料庫的接入

def getDbAccessConf( confFileName ):

dbIp = "unknown"
dbUser = "unknown"
dbPasswd = "unknown"
dbName = "unknown"

patternDbIp = r"(.*)dbIp(.*)=(.*)"
patternDbUser = r"(.*)dbUser(.*)=(.*)"
patternDbPasswd = r"(.*)dbPasswd(.*)=(.*)"
patternDbName = r"(.*)dbName(.*)=(.*)"

fileData = open(confFileName)
lines = fileData.readlines()

for line in lines:
matchObj = re.match( patternDbIp, line )
if matchObj:
dbIp = matchObj.group(3).strip()

matchObj = re.match( patternDbUser, line )
if matchObj:
dbUser = matchObj.group(3).strip()

matchObj = re.match( patternDbPasswd, line )
if matchObj:
dbPasswd = matchObj.group(3).strip()

matchObj = re.match( patternDbName, line )
if matchObj:
dbName = matchObj.group(3).strip()

return dbIp, dbUser, dbPasswd, dbName

 

#定義資料內容的插入
def insertIntoDb( db ):
tableHeadMobile = ‘mobileHead‘
idxRow = 0;
cursor = db.cursor()

f= open(‘ccc.txt‘,‘r‘)
for row in f:
contents = row.split(‘\t‘)
operator = contents[0]
#print ‘\n ope: ‘ + operator,
for idx in range( 1, len(contents) ):
headMobile = contents[idx]
if "" == headMobile:
print ‘headMobile is empty. head=‘ + headMobile
continue

sqlComm = ‘select count(id) from ‘ + tableHeadMobile + ‘ where headMobile = "‘ + headMobile + ‘"‘
cursor.execute( sqlComm )
sqlData = cursor.fetchall()
rowNum = sqlData[0][0]
if 0 == rowNum:
sqlComm = ‘insert into ‘ + tableHeadMobile + ‘ values( "‘ + str(idxRow) + ‘", "‘ + operator + ‘", "‘ + headMobile + ‘" )‘
print sqlComm
cursor.execute( sqlComm )
idxRow = idxRow + 1

db.commit()
cursor.close()

 

Python調用MySQL的一些用法小結