01-python操作Mysql数据库

python操作数据库流程：
--创建数据库连接 Conn=pymysql.connect()
--创建游标cur=Conn.cursor()
--游标下执行sql cur.execute(sql)
--提交游标数据到数据库conn.commit()
--关闭游标cur.close()
--关闭数据库conn.close()

实例

实例1：创建数据库连接

import pymysql,re,logging,os

class OperationDb_interface(object):

def __init__(self):

self.conn=pymysql.connect(host='localhost', user='root',passwd='*****',db='my_test',port=3306,charset='utf8')#创建数据库链接

self.cur=self.conn.cursor()#创建游标

实例2：查询单条语句

 def selectOne(self,condition):
      try:
         self.cur.execute(condition)#游标下执行sql
         results=self.cur.fetchone()#获取一条结果
      except pymysql.Error,e:
         results="sql0001"#数据库执行错误
         print"Mysql Error %d:%s"% (e.args[0],e.args[1])
         logging.basicConfig(filename=os.path.join(os.getcwd(),'./log.txt'),
             level=logging.DEBUG,format='%(asctime)s %(filename)s[line:%(lineno)d] % (levelname)s %(message)s')
         logger =logging.getLogger(__name__)
         logger.exception(e)
      finally:
         return  results

实例3：查询多条语句

   def selectAll(self,condition):
       try:
           self.cur.execute(condition)
           self.cur.scroll(0,mode='absolute')#光标回到初始位置
           result=self.cur.fetchall()#返回游标中所有结果
       except pymysql.Error,e:
           result='sql0001'#数据库执行错误
           print "Mysql Error %d:%s" % (e.args[0],e.args[1])
           logging.basicConfig(filename=os.path.join(os.getcwd(),'./log.txt'),
                level=logging.DEBUG,format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s % (message)s')
           logger=logging.getLogger(__name__)
           logger.exception(e)
       finally:
           return  result

实例4：定义插入多条语句数据库操作

   def insertMore(self,condition,params):#对应的params是个tuple或list
      try:
         self.cur.executemany(condition,params)
         self.conn.commit()#提交游标数据到数据库
         return True
      except pymysql.Error,e:
         results='sql001'#数据库执行错误
         print "Mysql Error %d:%s"%(e.args[0],e.args[1])
         logging.basicConfig(filename=os.path.join(os.getcwd(),'/log.txt'),
                 level=logging.DEBUG,format='%(asctime)s%(filename)s %(filename)s[line:%(lineno)d]% (levelname)s%(message)s' )
         logger=logging.getLogger(__name__)
         logger.exception(e)
         return  False

关闭数据库

 def __del__(self):
          if self.cur !=None:
             self.cur.close()#关闭游标
          if self.conn !=None:
             self.conn.close()#关闭数据库链接

if __name__=="__main__":
   test=OperationDb_interface()#实例化类
   result=test.selectAll("select * from student")
   #result=test.insertMore("insert  into student VALUES (%s,%s);",(('003','zhaogang'),('004','Angelia'))) #对应的params是个tuple或list
   print (result)