Python3.6 连接mysql 数据库，增删改查，及多线程简单运用

readme:

       导入 pymysql 连接数据库，完成数据处理后的增删改查操作。导入到其他Python文件就可以直接调用。后面一个文件是多线程操作， 另一个文件是处理曲线拟合和积分的然后数据和数据库交互的运用。

aliyunMySQL_test.py

import pymysqlclass oilCheckMysql:    def __init__(self,host,user,passwd,db,tableName):        self.host = host        self.user = user        self.passwd =passwd        self.db=db        self.tableName =tableName    def serachMySQL(self):        #  db = pymysql.connect(host='**.**.**.**', user='root', passwd='*****', db="zabbix", charset="utf8") # lab420 校罐        db = pymysql.connect(host=str(self.host),user=str(self.user), passwd = str(self.passwd), db=str(self.db), charset="utf8")        cur = db.cursor()        try:            # 查询            cur.execute("SELECT * FROM "+str(self.tableName))            for row in cur.fetchall():                #   print(row)                id = row[0]                startHeight= row[1]                endHeight = row[2]                oilV = row[3]                calculateV = row[4]                ratioV=row[5]                print("id=%d ,startHeight=%f,endHeight =%f, oilV=%f, ratio=%f  ",                    (id, startHeight, endHeight, oilV,calculateV,ratioV))        except:            print("MySQL Error: unable to fecth data of station_stock")        cur.close()        db.close()    def insertMySQL(self,ID,CH1,CH2,CHV,calV,ratV):        conn = pymysql.connect(host=str(self.host), user=str(self.user), passwd=str(self.passwd),                               db=str(self.db),charset="utf8")        cur = conn.cursor()        try:            sqli = "insert into "+str(self.tableName)+"(id,startHeight,endHeight,oilV,calculateV,ratioV) values(%s,%s,%s,%s,%s,%s)"            param =(ID,CH1,CH2,CHV,calV,ratV)            cur.execute(sqli, param)            cur.close()            conn.commit()            conn.close()        except:            print("MySQL Error :unable to insert data")    def updateMySQL(self,id,calV,ratV):        conn = pymysql.connect(host=str(self.host), user=str(self.user), passwd=str(self.passwd),                               db=str(self.db),charset="utf8")        cur = conn.cursor()        try:         #   sqli = "update checkVolume set calculateV=23.34 ,ratioV=1.6  where id = 123"          #  cur.execute(sqli)            sqlit='update '+str(self.tableName)+' set calculateV='+str(calV)+',ratioV='+str(ratV)+'where id='+str(id)            cur.execute(sqlit)            cur.close()            conn.commit()            conn.close()        except:            print("MySQL Error :unable to update data")    # delete a record    def deleteByID(self, id):        conn = pymysql.connect(host=str(self.host), user=str(self.user), passwd=str(self.passwd),                               db=str(self.db),charset="utf8")        cur = conn.cursor()        try:            sqlit='delete from '+ str(self.tableName) +' where id>'+ str(id)            cur.execute(sqlit)            cur.close()            conn.commit()            conn.close()        except :            print("MySQL Error :unable to delete data")def main():    CheckH1=[12.2,23.4]    CheckH2=[10.8,23.3]    CheckV=[13200.1,12500]    CalculateV=[10020.4,13000]    ratioV=[0.23,0.12]    ckl =oilCheckMysql('***.**.***.***','root','123456',"oilCheck",'checkVolume')    ckl.insertMySQL(39,CheckH1[0],CheckH2[0],CheckV[0],CalculateV[0],ratioV[0])    ckl.updateMySQL(39,CalculateV[0],ratioV[0])    ckl.serachMySQL()    ckl.deleteByID(38)if __name__ == '__main__':    main()

myThread.py

import threadingfrom time import sleep,ctimeloops =(4,2)class MyThread(threading.Thread):    def __init__(self,func,args,name=''):        threading.Thread.__init__(self)        self.name = name        self.func = func        self.args = args    def run(self):        self.func(*self.args)def loop(nloop,nsec):    print('start loop',nloop, 'at:',ctime())    sleep(nsec)    print('loop',nloop,'don at',ctime())def main():    print('starting at:',ctime())    threads =[]    nloops=range(len(loops))    for i in nloops:        t =MyThread(loop,(i,loops[i]), loop.__name__)        threads.append(t)    for i in nloops:        threads[i].start()    for i in nloops:        threads[i].join()    print ('All Done at:',ctime())if __name__=='__main__':    main()

PolynomialFitting1.py

###最小二乘法试验###import numpy as npimport pymysqlfrom scipy.optimize import leastsqfrom scipy import integrate###绘图，看拟合效果###import matplotlib.pyplot as pltfrom sympy import *from aliyunMySQL_test import oilCheckMysqlfrom myThread import MyThreadfrom time import ctime,sleeppath='E:\PythonProgram\oilChecking\oildata.txt'h_median =[]      # 高度相邻中位数h_subtract =[]          #相邻高度作差v_subtract =[]          #相邻体积作差select_h_subtr =[]           #筛选的高度作差    ########select_v_subtr =[]           #筛选的体积作差VdtH=[]                     #筛选的V 和 t 的 倒数。CheckH1 = []CheckH2 = []CheckH1_H2=[]CheckV=[]vCalcute = []ratioV=[]def loadData(Spath):    try:        with open(Spath,'r') as f0:             for i in f0:                tmp=i.split()                h_median.append(float(tmp[0]))                h_subtract.append(float(tmp[1]))                v_subtract.append(float(tmp[2]))        print ("source lie0",len(h_median))    except:        print("cannot open file!")def removeBadPoint(h_med,h_sub,v_sub):    for val in h_sub:        position=h_sub.index(val)        if 0.01 > val > -0.01:            del h_sub[position]            del h_med[position]            del v_sub[position]    for  vmin in v_sub:         pos =v_sub.index(vmin)         if 18 > vmin > -18:             del h_med[pos]             del h_sub[pos]             del v_sub[pos]         elif vmin > 85:             del h_med[pos]             del h_sub[pos]             del v_sub[pos]         else :             pass    v_dt_h_ay = [(y/x) for x, y in zip(h_sub, v_sub)]    return v_dt_h_aydef selectRightPoint(h_med,h_subtr,v_dt_h_ay):    for val in v_dt_h_ay:        pos = v_dt_h_ay.index(val)        if val > 20 :            del v_dt_h_ay[pos]            del h_med[pos]            del h_subtr[pos]    for val in v_dt_h_ay:        ptr = v_dt_h_ay.index(val)        if val < 14:            del v_dt_h_ay[ptr]            del h_med[ptr]            del h_subtr[ptr]def writeFile(h_mp, v_dt_h):    s='\n'.join(str(num)[1:-1] for num in h_mp)    v='\n'.join(str(vdt)[1:-1] for vdt in v_dt_h)    open(r'h_2.txt','w').write(s)    open(r'v_dt_h.txt','w').write(v)    print("write h_median: ",len(h_mp))    # print("V_dt also is (y-x) : ",v_dt_h,end="\n")    print("Write V_dt_h : ",len(v_dt_h))def readFileCheck():    path2 = 'E:\PythonProgram\oilChecking\CheckingData1.txt'    with open(path2, 'r') as f0:        for i in f0:            tmp = i.split()            CheckH1.append(float(tmp[0]))            CheckH2.append(float(tmp[1]))            CheckV.append(float(tmp[2]))    CheckH12 = [(y-x) for x, y in zip(CheckH1, CheckH2)]    return CheckH12def integralCalculate(coeff,CH1,CH2,chkv):    x = Symbol('x')    a, b, c, d = coeff[0]    y = a * x ** 3 + b * x ** 2 + c * x + d    i=0    while (i< len(CH1)) :        m = integrate(y, (x, CH1[i], CH2[i]))-80        vCalcute.append(abs(m))        i=i+1    ratioV[:] = [(((y-x)/x)*100) for x,y in zip(chkv,vCalcute)]    print("求导结果：",vCalcute)    print("求导长度 len(VCalcute): ",len(vCalcute))    print("V 误差百分比大小：",ratioV)    file=open('ratio.txt','w')    file.write(str(ratioV))    file.close    return vCalcute ###需要拟合的函数func及误差error###def func(p,x):    a,b,c,d=p    return a*x**3+b*x**2+c*x+d #指明待拟合的函数的形状，设定的拟合函数。def error(p,x,y):    return func(p,x)-y #x、y都是列表，故返回值也是个列表def leastSquareFitting(h_mp,v_dt_hl):    p0=[1,2,6,10]     #a,b,c 的假设初始值，随着迭代会越来越小    #print(error(p0,h_mp,v_dt_h,"cishu"))  #目标是让error 不断减小    #s="Test the number of iteration" #试验最小二乘法函数leastsq得调用几次error函数才能找到使得均方误差之和最小的a~c    Para=leastsq(error,p0,args=(h_mp,v_dt_hl)) #把error函数中除了p以外的参数打包到args中    a,b,c,d=Para[0]           #leastsq 返回的第一个值是a,b,c 的求解结果，leastsq返回类型相当于c++ 中的tuple    print(" a=",a," b=",b,"  c=",c," d=",d)    plt.figure(figsize=(8,6))    plt.scatter(h_mp,v_dt_hl,color="red",label="Sample Point",linewidth=3) #画样本点    x=np.linspace(200,2200,1000)    y=a*x**3+b*x**2+c*x+d    integralCalculate(Para,CheckH1,CheckH2,CheckV)    plt.plot(x,y,color="orange",label="Fitting Curve",linewidth=2) #画拟合曲线  #  plt.plot(h_mp, v_dt_hl,color="blue",  label='Origin Line',linewidth=1)  #画连接线    plt.legend()    plt.show()def freeParameterFitting(h_mp,v_dt_hl):    z1 = np.polyfit(h_mp, v_dt_hl, 6)   # 第一个拟合，自由度为6        # 生成多项式对象    p1 = np.poly1d(z1)    print("Z1:")    print(z1)    print("P1:")    print(p1)    print("\n")    x = np.linspace(400, 1700, 1000)    plt.plot(h_mp, v_dt_hl, color="blue", label='Origin Line', linewidth=1)  # 画连接线    plt.plot(x, p1(x), 'gv--', color="black", label='Poly Fitting Line(deg=6)', linewidth=1)    plt.legend()    plt.show()def operatMysql(h_mp,v_dt_h):    print('operatMysql start at : ',ctime())    sql = oilCheckMysql('***.**.***.***','root','*****',"oilCheck",'checkVolume')    i=0    while(i<len(CheckV)):       t=i+1      # sql.insertMySQL(t,float(CheckH1[i]),float(CheckH2[i]),float(CheckV[i]),float(vCalcute[i]),float(ratioV[i]))       sql.updateMySQL(t,float(vCalcute[i]),float(ratioV[i]))       i=i+1    sql.serachMySQL()    print('operatMysql finished at : ', ctime())def main():    loadData(path)    CheckH1_H2[:]=readFileCheck()    print("CheckH1_H2 length: ",len(CheckH1_H2))    # 去除被除数为0对应的点，并得到v 和 h 求导 值的列表    VdtH[:] = removeBadPoint(h_median, h_subtract, v_subtract)    print("h_median1：", len(h_median))    print("VdtH1 : ", len(VdtH))    #  赛选数据，去除异常点    selectRightPoint(h_median, h_subtract, VdtH)    print("h_median2：", len(h_median))    print("h_subtract: ", len(h_subtract))    print("VdtH2 : ", len(VdtH))    h_mp = np.array(h_median)    v_dt_h = np.array(VdtH)    # 最小二乘法作图    leastSquareFitting(h_mp, v_dt_h)    # 多项式自由参数法作图    freeParameterFitting(h_mp, v_dt_h)    threads = []    funcs = [writeFile,operatMysql]    nfuncs = range(len(funcs))    for i in nfuncs:        t= MyThread(funcs[i],(h_mp,v_dt_h),funcs[i].__name__)        threads.append(t)    for i in nfuncs:        threads[i].start()    for i in nfuncs:        threads[i].join()if __name__ == '__main__':    main()