python MySQLdb使用

最近在用到了python的MySQLdb库。所以看了下别人的操作和mysqldb文档，有了初步的体验。

1 ） mysql连接

在连接mysql时，我尝试了开启了一个连接，执行了sql语句，select sleep(100);并且在我杀死python程序的时候，在mysql中，show processlist ,发现sql语句还在后台执行。除了kill那个会话的pid，好像没发现什么方法杀死正在执行的操作。以前一直觉得，关闭会话，执行的操作就结束，碰到卡住的sql语句，ctrl+c一下（应该就是杀死那个当前线程吧）结束执行sql语句。

把自己写的脚本上传一些，这里参考了别人的杀死进程的方法：
1.起线程，超时杀之，
2.使用signal信号，超时杀之，这种有个timeout的模块，非常方便使用，不需自己写，但是无论是自己写还是调用timeout模块，都杀不掉执行中的那个执行mysql操作的python函数。普通的测试过，能杀。

import MySQLdbimport csvimport osimport threadingimport datetime##################user,host,database########myuser = 'root'myhost = 'localhost'mydatabase = 'learn'mypasswd = '123456'myselect = 'select sleep(100)'#myselect = 'select userid,name from users'second = 10#ratefile = '/home/haxian/my/rates.log'#errorlog = '/home/hexian/my/my.log'ratefile = 'E:\python_test\data.log'errorlog = 'E:\python_test\error.log'################################################################################################start_time = datetime.datetime.now()ntime = os.popen("date +'%Y-%m-%d %H:%M:%S'").read()ntime = ' '.join(ntime.split())'''this script use python 2.6.6 not python2.7if python 2.7 , csv have changed.'''#write errormassge to filedef errorMsg(msg):  try:    with open(errorlog,'a+') as f:      f.write('\n'+ntime+' '+msg+' error\n')  except Exception as e:      pass####################### start a thread to connect mysql for select data from mysql #############resultdata=()class TimeLimitExpired(Exception): passdef timelimit(timeout,errorMsg):    class FuncThread(threading.Thread):        def __init__(self):            threading.Thread.__init__(self)            self.mydb = MySQLdb.connect(myhost,myuser,mypasswd,mydatabase)            self.resultdata = None            self.myselect = myselect            self.errorMsg =errorMsg        def run(self):           try:               self.cur = self.mydb.cursor()               self.cur.execute(self.myselect)               self.resultdata = self.cur.fetchall()               print self.resultdata               self.cur.close()               self.mydb.close()               print self.mydb           except Exception as e:               self.errorMsg(str(e))               if 'open' in str(mydb):                   self.cur.close()                   self.mydb.close()        def _stop(self):          if self.isAlive():              threading.Thread._Thread__stop(self)    it = FuncThread()    it.start()    it.join(timeout)    if it.isAlive():      it._stop()      it.cur.close()      it.mydb.close()      #raise TimeLimitExpired()    else:      return it.resultdata######################################end thread#####################resultdata = timelimit(second,errorMsg)if resultdata is None:    resultdata = ()################commented out code########### select from 'show processlist' get process pid and give under function to kill.def selectpid(sqldata):    mypid = []    for x in sqldata:      print x      if x[1] ==myuser and x[2].split(':')[0]==myhost and x[-1]==myselect:           mypid.append(x[0])    return mypidprint 'Commented out code '# to kill mysql select processdef killselect():    selectsql = myselect;    regpid = "select * from information_schema.processlist where user='{0}' and db='{1}' and Host regexp '{2}';".format(myuser,mydatabase,myhost)    mydb2 = MySQLdb.connect(myhost,myuser,mypasswd,mydatabase)    others = mydb2.cursor()    others.execute(regpid)    result = others.fetchall()    pid = selectpid(result)    for x in pid:        killsql = 'kill {0}'.format(x)        others.execute(killsql)    mydb2.close()    print mydb2killselect()#############################################end_time = datetime.datetime.now()mysqltime = (end_time - start_time).seconds#print mysqltime'''##################################start handle  data###########################at = list(resultdata)#print at#counts fail and successdef anlyData(t):  count_s = 0  count_f = 0  for x in t:         a,b = x[0],x[1]         if a == "authlog_succ":              count_s += b         elif a == "authlog_fail":              count_f += b         else:             pass  return [count_s,count_f]# percentage of success, faildef pctData(counts):   s,f = counts[0],counts[1]   if s+f == 0:      return [s,f]   s_rate = s*1.0/(s+f)   s_rate = round(s_rate,3)*100   s_rate = str(s_rate)+'%'   f_rate = f*1.0/(s+f)   f_rate = round(f_rate,3)*100   f_rate = str(f_rate)+'%'   return [str(s_rate),str(f_rate)]counts = anlyData(at)rates = pctData(counts)ntime = os.popen("date +'%Y-%m-%d %H:%M:%S'").read()ntime = ' '.join(ntime.split())data =[ ntime,counts[0],counts[1],rates[0],rates[1],mysqltime]# write to filetry:    with open(ratefile, 'a+') as csvfile:        writer = csv.writer(csvfile, delimiter=",", quoting=csv.QUOTE_MINIMAL)        writer.writerow(data)except Exception as e:    errorMsg(str(e))'''

代码写的比较渣，谁叫我是菜鸡呢。^.^
过程就是，起了个线程去操作mysql，如果超时，杀掉线程，这时候回到了进程中，进程在连mysql，筛选出相关的pid，执行kill pidxxx操作。

当然这是我自己的mysql测试，自己玩玩而已，操作需谨慎。
+++++++++++++++++++++++++++++++++

2）将值插入到mysql

在mysql创建主键的时候，发现id一旦开始创建为auto_increment就删除不了，而且创建表时自增字段必须成为主键（也可以是联合主键），否则报错
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

#encoding:utf-8import MySQLdbimport  csv################全局变量##########FILEPATH =""FILE = "E:\python_test\8-29-00\data.csv"USER = "root"PASSWD = "root123"DATABASE = "learn"HOST = "localhost"##################################从文件读取数据，每行数据作为一个列表的一个元素def readFile(f):    filedata =[]    with open(f,"rb") as csvfile:        sp = csv.reader(csvfile,delimiter=",",quotechar ='"')        for row in sp:            filedata.append(row)    return filedatafiledata = readFile(FILE)print len(filedata)print filedata[0]#将数据插入到mysql中#使用少量数据进行测试filedata=filedata[1:4]def mysqlInsert(filedata):    mydb = MySQLdb.connect(HOST,USER,PASSWD,DATABASE)    cur =mydb.cursor()    try:        for x in filedata:            sql = "insert into all_innodb " \                  "values(null,'%s','%s','%s','%s','%s','%s'," \                  "'%s','%s','%s','%s','%s','%s','%s')"%tuple(x)            print sql            cur.execute(sql)        mydb.commit()    except Exception as e:        print emysqlInsert(filedata)

这里看下我的表结构：

因为都是英文，没有涉及到中文。下面再说中文的问题。我一般用python2.7，偶尔用Python3。

2.1) 这里碰到几个问题，就是我insert一次后，delete from all_innodb。删除后，重新再插一遍，这时候发现自增字段Id的值，没有因为我删除而重新从1开始。
这里需要在插入之前执行一条myql语句：

alter table all_innodb auto_increment=1;

重设，使之从1开始。

2.2) 这里还碰到个问题，因为设置到unique key的问题，所以数据如果重复，则报错，python程序爆出异常。来个异常捕捉吧。

    for x in filedata:         sql = "insert into all_innodb " \                  "values(null,'%s','%s','%s','%s','%s','%s'," \                  "'%s','%s','%s','%s','%s','%s','%s')"%tuple(x)         print sql         try:           cur.execute(sql)         except Exception as e:            continue