最近在用到了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
767
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
