MySQL增加元祖_封装的mysql，改良了一下+1

import MySQLdb

import sys

from mod_config import DB_Confg,CommParams

from logger import Log

reload(sys)

sys.setdefaultencoding('utf-8')

log = Log()

class MysqldbHelper:

#获取数据库连接

def __init__(self):

self.conn =MysqldbHelper.__getCon()

self.cur = self.conn.cursor(MySQLdb.cursors.DictCursor)

@staticmethod

def __getCon():

try:

conn=MySQLdb.connect(host=DB_Confg.Host_Sql(),

user=DB_Confg.User_sql(),

passwd=DB_Confg.Password_Sql(),

db=DB_Confg.Dbname_Sql(),

port=DB_Confg.Port_Sql(),

charset=DB_Confg.Charset_Sql())

return conn

except MySQLdb.Error,e:

log.info("Mysqldb Error:%s" %e)

#查询方法，使用con.cursor(MySQLdb.cursors.DictCursor),返回结果为字典

def Getall(self,sql,param = None):#手动输入sql语句

'''

@summary: 执行查询，并取出所有结果集

@param sql:查询ＳＱＬ，如果有查询条件，请只指定条件列表，并将条件值使用参数[param]传递进来

@param param: 可选参数，条件列表值(元组)

@return: result list(字典对象)/boolean 查询到的结果集

'''

try:

if param == None:

rep_sql = self.cur.execute(sql)

else:

rep_sql = self.cur.execute(sql,param)

if rep_sql > 0:

result = self.cur.fetchall()

else:

result = "None"

return result

except MySQLdb.Error,e:

log.info("Mysqldb Error:%s" %e)

self.cur.close()

self.conn.close()

def Getone(self,sql,param = None):

'''

@summary: 执行查询，并取出第一条

@param sql:查询ＳＱＬ，如果有查询条件，请只指定条件列表，并将条件值使用参数[param]传递进来

@param param: 可选参数，条件列表值(元组/列表)，单个查询返回列表

@return: result list/boolean 查询到的结果集

'''

try:

if param == None:

rep_sql = self.cur.execute(sql)

else:

rep_sql = self.cur.execute(sql,param)

if rep_sql > 0:

result = self.cur.fetchone()

else:

result = False

return result

except MySQLdb.Error,e:

log.info("Mysqldb Error:%s" %e)

finally:

self.cur.close()

self.conn.close()

def GetNum(self,sql, num, param = None):

'''

@summary: 执行查询，并取出num条结果

@param sql:查询ＳＱＬ，如果有查询条件，请只指定条件列表，并将条件值使用参数[param]传递进来

@param num:取得的结果条数

@param param: 可选参数，条件列表值(元组)

@return: result list/boolean 查询到的结果集,返回tuple

'''

try:

if param == None:

rep_sql = self.cur.execute(sql)

else:

rep_sql = self.cur.execute(sql,param)

if rep_sql > 0:

result = self.cur.fetchmany(num)

else:

result = False

return result

except MySQLdb.Error,e:

log.info("Mysqldb Error:%s" %e)

self.cur.close()

self.conn.close()

#带参数的更新方法,eg:sql='insert into pythontest values(%s,%s,%s,now()',params=(6,'C#','good book')

def InsertOne(self,sql,value):

'''

:param sql: 语法 inster into tables_name(a,b,c)vales(%s,%s,%s)

:param value: %s的参数

:return:

'''

try:

self.cur.execute(sql,value)

self.conn.commit()

return self.__getInsertId()

except MySQLdb.Error,e:

print "Mysql Error %s"%e

self.conn.rollback()

finally:

self.cur.close()

self.conn.close()

#使用executemany插入时，数量量超过1M时，会报错，因为mysql中设置了最大的是1M的数据量；这时就需要去修改mysql的设置

def IsertMany(self,values,sql):

'''

:param values: %s参数,必须是tuple的类型

:param sql: mysql语法格式(insert into tables_name(a,b,c) values(%s,%s,%s)

:return: 插入的速度是execute的十倍快

eg:

for i in range(2):

values.append((i,i))

print len(values)

'''

try:

count = self.cur.executemany(sql,values)

self.conn.commit()

return count

except MySQLdb.Error,e:

log.info("Mysql Error: %s"%e)

self.conn.rollback()

finally:

self.cur.close()

self.conn.close()

def __getInsertId(self):

"""

获取当前连接最后一次插入操作生成的id,如果没有则为０

"""

self.cur.execute("SELECT @@IDENTITY AS id")

result = self.cur.fetchall()

return result[0]['id']

def updateByParam(self,sql,params = None):

'''cursor.execute("SELECT * FROM t1 WHERE id = %s", (5,))'''

try:

if params == None:

count = self.cur.execute(sql)

else:

count = self.cur.execute(sql,params)

self.conn.commit()

return count

except MySQLdb.Error,e:

self.conn.rollback()

print "Mysqldb Error:%s" %e

finally:

self.cur.close()

self.conn.close()

def delete_information(self,sql,params = None):

try:

if params == None:

count =self.cur.execute(sql)

elif type(params) == type(tuple()):

count =self.cur.executemany(sql,params)

else:

#executemany必须是tuple的类型才可以使用

count =self.conn.executemany(sql,params)

self.conn.commit()

return count

except MySQLdb.Error,e:

self.conn.rollback()

print "Mysql Error %s" %e

finally:

self.cur.close()

self.conn.close()

if __name__ == "__main__":

# a = MysqldbHelper()

# # sql="select * from phoneuser WHERE account = %s and alias = %s"

# # sql="select * from camera WHERE cid = %s"

# # print sql

# # par = ('290200000011')

# fd = a.Getone("select * from camera WHERE cid = '%s'" %290200000011)

# # fd = a.GetNum(sql,1,par)

# print fd

# for row in fd:

# print row[""]

#循环插入的例子

# fd=a.select(sql)

# value=[1,'hi rollen']

# cur.execute('insert into test values(%s,%s)',value)

#

# values=[]

# for i in range(20):

# values.append((i,'hi rollen'+str(i)))

#

# cur.executemany('insert into test values(%s,%s)',values)

#

# cur.execute('update test set info="I am rollen" where id=3')

#

# conn.commit()

# cur.close()

# conn.close()

'''遇到的坑

1.mysql中%s的参数中不要传数字值，会引起mysql warning；

mysql Warning | 1292 | Truncated incorrect DOUBLE value: '25a3c516a4c15eda917963e48a254'  |

可能造成的原因：

on running a MySQL query, it could be caused by using a numeric value against a CHAR/VARCHAR column.

2.executemany使用时，传的参数必须是元祖

3.fetchone返回的是dict

4.fetchall返回是元祖。所以取值时，需要先fd[0]["information"]

'''