python的mysql小代碼

標籤：NPU   %s   技術   star   串連   one   sql   readlines   idt   

我因為懶，就想寫個批量insert資料的小代碼 這裡是代碼

 

# _*_ encoding:utf-8 _*_import osimport MySQLdbimport numpy as npdbname = ‘spj‘db = MySQLdb.connect(host="localhost", port=3306, user="root", passwd="1234", db=dbname,charset="utf8")  # 進行插入操作時需要聲明編碼類別型cursor = db.cursor()def dp_insert(file_name,putin):    file = open(file_name, "r")    for line in file.readlines():#插入        arr = line.split()        sql = putin+‘ values ‘+‘(‘+"‘"+arr[0]+"‘"+‘,‘+"‘"+arr[1]+"‘"+‘,‘+"‘"+arr[2]+"‘"+‘,‘+"‘"+arr[3]+"‘"‘)‘+‘;‘        try:            cursor.execute(sql)            db.commit()        except:            print("Error")    file.close()def select(putin):    sql = putin    try:        cursor.execute(sql)        results = cursor.fetchall()        for row in results:            print(row)        db.commit()    except:        print("Error: unable to fecth data")def describe(putin):    sql = putin    try:        cursor.execute(sql)        results = cursor.fetchall()        for row in results:            print(row)        db.commit()    except:        print("Error: unable to fecth data")def delete_line(infile,outfile):    infopen = open(infile,‘r‘)    outfopen = open(outfile,‘w‘)    lines = infopen.readlines()    for line in lines:        if line.split():            outfopen.writelines(line)        else:            outfopen.writelines("")    infopen.close()    outfopen.close()    os.remove(infile)    os.rename(outfile,infile)def main():    file_name = ‘p.txt‘    print("please input sql:")    putin = input()    delete_line(file_name, ‘new.txt‘)  # test pass    if putin.startswith(‘insert‘):  # +‘,‘+"‘"+arr[2]+"‘"        print("資料的path")        file_name = input()        dp_insert(file_name, putin)    elif putin.startswith(‘select‘):        select(putin)    elif putin.startswith(‘describe‘):        describe(putin)    else:        db.close()        os._exit()if __name__ == ‘__main__‘:    main()    print("next?")    if input()==‘Y‘:        main()    else:        db.close()        os._exit()    db.close()    #insert 模板    #insert into p(pno, sname, color, weight)

 

（insert語句：insert into j(jno,jname,city)）

 

這裡是一些檔案處理的代碼，之前放在代碼裡，看著難受,放在這裡

# 讀取path目錄下的檔案名稱，返迴文件名list列表 def readFileName(path): lists = [] for root, dirs, files in os.walk(path): for file in files: lists.append(os.path.join(root, file)) return lists# 刪除路徑為filepath的檔案 def delFile(filepath): os.remove(filepath) print "ok"‘‘‘‘‘‘#轉化為數組for line in open("p.txt","r"): arr =line.split() print(arr)#轉化為列表file = open("p.txt", "r")list_arr = file.readlines()for i in range(len(list_arr)): list_arr[i] = list_arr[i].strip()a = np.array(list_arr)print(a)file.close()‘‘‘#sql=‘‘+‘‘+‘‘+‘‘ 加號串連 sql=‘%s%s%s’ % (‘‘,‘‘,‘‘)

python的mysql小代碼