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)
