Python 操作MySql数据库

最新推荐文章于 2024-07-20 18:48:57 发布

土豆拍死马铃薯

最新推荐文章于 2024-07-20 18:48:57 发布

阅读量298

点赞数

分类专栏： Python学习文章标签： Python mysql 数据库

本文链接：https://blog.csdn.net/csj941227/article/details/75088896

版权

Python学习专栏收录该内容

34 篇文章 0 订阅

订阅专栏

一、Python DBAPI（类比JDBC）

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

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

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

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

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

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

from chenjie.path import Path

import pymysql.cursors

class 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()