Python 操作MySql数据库

来源：互联网发布：linux下utf8转gbk 编辑：程序博客网时间：2024/06/07 18:10

一、Python DBAPI（类比JDBC）

Python访问数据库的统一接口规范。

包括数据库连接对象connection，好比高速公路.

数据库交互对象cursor，好比火车

数据库异常类exceptions，好比事故

使用Python DB API访问数据库的流程：

开始à创建connectionà获取cursorà执行查询/执行命令/获取数据/处理数据à关闭cursorà关闭connectionà结束

from chenjie.path import Pathimport pymysql.cursorsclass PathDao:    def __init__(self):        self.__connection = pymysql.connect(host='127.0.0.1',                                     port=3306,                                     user='root',                                     password='',                                     db='python',                                     charset='utf8mb4',                                     cursorclass=pymysql.cursors.DictCursor)        self.__connection.autocommit(False)    def save(self,path):        try:            with self.__connection.cursor() as cursor:                print("--------------------------------------------------------------------------------------------------------")                sql = "insert into Path values('%s','%s','%s',%s,%s)"%(path.url,path.date,path.flag,path.changci,path.isFinished)                print(sql)                count = cursor.execute(sql)                print(count)                #result = cursor.fetchone()                #print(result)                self.__connection.commit()                print("save success")        except Exception as e:            print("save failed :" + e)            self. __connection.rollback()        finally:            self.__connection.close()    def getAllPath(self):        try:            with self.__connection.cursor() as cursor:                sql = "SELECT * FROM `path`"                print(sql)                count = cursor.execute(sql)                print(count)                result = cursor.fetchall()                print(result)                self.__connection.commit()                print("get success")                return result        except Exception as e:            print("get failed :" + e)            return None        finally:            self.__connection.close()    def getUnVisitedPath(self):        try:            with self.__connection.cursor() as cursor:                print("--------------------------------------------------------------------------------------------------------")                sql = "SELECT * FROM `path` where isFinished = 0 limit 0,1"                print(sql)                count = cursor.execute(sql)                print(count)                result = cursor.fetchone()                print(result)                self.__connection.commit()                print("get success")                return result        except Exception as e:            print("get failed :" + e)            return None        finally:            self.__connection.close()if __name__ == "__main__":    PathDao().getAllPath()

阅读全文

0 0