python 封装sql_Python 封装MySQL类

以后再也不用每次都重新写啦！

import MySQLdb

OperationalError = MySQLdb.OperationalError

class MySQL:

def __init__(self,host,user,password,port=3306,charset="utf8"):

self.host=host

self.port=port

self.user=user

self.password=password

self.charset=charset

try:

self.conn=MySQLdb.connect(host=self.host,port=self.port,user=self.user,passwd=self.password)

self.conn.autocommit(False)

self.conn.set_character_set(self.charset)

self.cur=self.conn.cursor()

except MySQLdb.Error as e:

print("Mysql Error %d: %s" % (e.args[0], e.args[1]))

def __del__(self):

self.close()

def selectDb(self,db):

try:

self.conn.select_db(db)

except MySQLdb.Error as e:

print("Mysql Error %d: %s" % (e.args[0], e.args[1]))

def query(self,sql):

try:

n=self.cur.execute(sql)

return n

except MySQLdb.Error as e:

print("Mysql Error:%s\nSQL:%s" %(e,sql))

def fetchRow(self):

result = self.cur.fetchone()

return result

def fetchAll(self):

result=self.cur.fetchall()

desc =self.cur.description

d = []

for inv in result:

_d = {}

for i in range(0,len(inv)):

_d[desc[i][0]] = str(inv[i])

d.append(_d)

return d

def insert(self,table_name,data):

columns=data.keys()

_prefix="".join(['INSERT INTO `',table_name,'`'])

_fields=",".join(["".join(['`',column,'`']) for column in columns])

_values=",".join(["%s" for i in range(len(columns))])

_sql="".join([_prefix,"(",_fields,") VALUES (",_values,")"])

_params=[data[key] for key in columns]

return self.cur.execute(_sql,tuple(_params))

def update(self,tbname,data,condition):

_fields=[]

_prefix="".join(['UPDATE `',tbname,'`','SET'])

for key in data.keys():

_fields.append("%s = %s" % (key,data[key]))

_sql="".join([_prefix ,_fields, "WHERE", condition ])

return self.cur.execute(_sql)

def delete(self,tbname,condition):

_prefix="".join(['DELETE FROM `',tbname,'`','WHERE'])

_sql="".join([_prefix,condition])

return self.cur.execute(_sql)

def getLastInsertId(self):

return self.cur.lastrowid

def rowcount(self):

return self.cur.rowcount

def commit(self):

self.conn.commit()

def rollback(self):

self.conn.rollback()

def close(self):

self.cur.close()

self.conn.close()

if __name__=='__main__':

n=MySQL('127.0.0.1','root','123456',3306)

n.selectDb('test')

tbname='map'

a=({'id':3,'x':3,'y':3},{'id':4,'x':4,'y':4},{'id':5,'x':5,'y':5})

for d in a:

n.insert(tbname,d)

n.commit()

一个重量级的MySQL-Python 封装类：facebook python mysql