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 MySQLdb
import csv
import os
import threading
import 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.7
if python 2.7 , csv have changed.
'''


#write errormassge to file
def 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): pass

def 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 mypid





print 'Commented out code '
# to kill mysql select process
def 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 mydb2
killselect()
#############################################




end_time = datetime.datetime.now()
mysqltime = (end_time - start_time).seconds
#print mysqltime




'''
##################################start handle  data###########################
at = list(resultdata)
#print at

#counts fail and success
def 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, fail
def 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 file
try:
    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-8
import MySQLdb
import  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 filedata
filedata = 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 e
mysqlInsert(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
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值