[python] 连接MySQL，以及多线程、多进程连接MySQL初探
#!/usr/bin/env python# -*- coding:utf-8 -*-
import MySQLdb
import logging
import time
'''
设置日志输出路径和格式
'''
logging.basicConfig(level=logging.INFO,
format='%(asctime)s %(levelname)s %(message)s',
datefmt='%Y-%m-%d %H:%M:%S',
filename='/home/sql.log',
filemode='a+')
'''
执行的SQL语句可能有多个，可以在这边进行组装
'''
def sql_assemble(*args):
sql_list = [
'delete from table_a where id = %s' % args,
'delete from table_b where content in %s' % args,
'update table_c set aa = %s' % args,
]
return sql_list
class mysqlopr(object):
def __init__(self,ip,port,username,password,dbname,char_set='utf8'):
'''
创建对象的时候就要求将参数传入进来
'''
self.ip = ip
self.port = port
self.username = username
self.password = password
self.dbname = dbname
self.char_set = char_set
def connect(self):
'''
mysql如果开启了安全模式，insert、delete和update无法执行，需要先设置SET SQL_SAFE_UPDATES = 0
执行单条SQL，使用execute(),执行多条SQL，可以使用executemany()，但只有INSERT和REPLACE会执行的快点。说明如下
executemany：This method improves performance on multiple-row INSERT and REPLACE. Otherwise it is equivalent to looping over args with execute().
'''
self.db = MySQLdb.connect(host=self.ip,port = self.port,user=self.username,passwd=self.password,db =self.dbname,.char_set)
self.cursor = self.db.cursor()
#sql_s = 'SET SQL_SAFE_UPDATES = 0'
#self.cursor.execute(sql_s)
logging.info('link to mysqldb: '+self.dbname)
def select(self,sql):
'''
sst :查询开始时间
set :查询结束时间
fetchone(): 只返回一行
fetchall(): 返回所有行
fetchmany(size): 返回size行
rowcount: 这是一个long型只读属性，返回执行execute()方法后影响的行数。
'''
try:
logging.info(sql)
self.index_list = []
sst = time.time()
self.cursor.execute(sql)
set = time.time()
#select_result = self.cursor.fetchone()
#select_result = self.cursor.fetchmany(10)
select_result = self.cursor.fetchall()
#print self.cursor.rowcount,type(self.cursor.rowcount)
logging.info('select count: ' + self.cursor.rowcount + ', cost :' + str(sst - set))
except Exception as e:
print e
logging.error("Error: unable to fecth data" + str(e))
def insert(self):
'''
和其他方法类似，这边省略
'''
pass
def update(self):
'''
和其他方法类似，这边省略
'''
pass
def delete(self,sql):
'''
dst:删除操作开始时间
det:删除操作结束时间
删除成功提交，失败回滚
'''
try:
logging.info(sql)
dst = time.time()
self.cursor.execute(sql)
self.commit()
det = time.time()
logging.info('delete row: ' + str(self.cursor.rowcount) + ', cost :' + str(det - dst))
except Exception as e:
print e
self.rollback()
logging.error(str(sql) + ',' + str(e))
def commit(self):
self.db.commit()
def rollback(self):
self.db.rollback()
def close(self):
self.cursor.close()
self.db.close()
if __name__ == '__main__':
st = time.time()
a = mysqlopr(ip='192.168.1.1',port = 8888,username='try',password='123456',dbname ='trydb',char_set='utf8')
a.connect()
'''
如果参数相同，SQL语句不同，可以现在sql_assemble方法中配置好SQL，再把参数传递进去。
或者这边直接调用对应的方法执行
这里过程省略
'''
a.close()
et = time.time()
logging.info("SQL executed over,cost: " + str(et -st))
print et - st
页:
[1]