python的数据库:基本操作

之前的数据库操作代码

import pymysql

class DAO_recruit_page:
    def __init__(self):
        pass

    def add(self, rec_year, rec_content, rec_uni_name):
        # 打开数据库连接
        db = self.get_conn()
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        # SQL 插入语句
        sql = "INSERT INTO recruit_page VALUES (null, '%s', '%s', '%s')" % (rec_year, rec_content, rec_uni_name)
        try:
            # 执行sql语句
            cursor.execute(sql)
            # 执行sql语句
            db.commit()
            print("insert ok")
        except Exception as e:
            # 发生错误时回滚
            print(e)
            print("(((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((")
            db.rollback()
        # 关闭数据库连接
        db.close()

    # 与本机的数据库建立连接
    def get_conn(self):
        conn = pymysql.connect(host='127.0.0.1', port=3306, user="root", passwd="root", db="yanxin", charset="utf8")
        return conn

    # 查询并输出所有数据
    def sel_all(self):
        cursor = self.get_conn().cursor()
        sql = 'select * from recruit_page'
        rows = cursor.execute(sql)
        res = cursor.fetchall()
        print('共有', rows, '条数据')
        for re in res:
            print(re)
        print('共有', rows, '条数据')

    # 查询表是否为空，为空返回true，不为空返回false，
    # 用于truncate()
    def is_empty(self):
        cursor = self.get_conn().cursor()
        sql='select * from recruit_page'
        rows = cursor.execute(sql)
        if rows > 0:
            return False
        else:
            return True

    # 查询学校招生简章是否已经爬取，已经爬取返回false，没爬取返回true
    def not_uni_name(self, rec_uni_name):
        cursor = self.get_conn().cursor()
        sql = 'select * from recruit_page where rec_uni_name = "%s"' % rec_uni_name
        rows = cursor.execute(sql)
        if rows > 0:
            return False
        else:
            return True

    # 清空recruit_page表
    def truncate(self):
        if self.is_empty():
            print("数据为空")
        else:
            sql = "truncate recruit_page"
            conn = self.get_conn()
            conn.cursor().execute(sql)
            conn.commit()
            print("已清空数据")

简单的demo

import pymysql


class Database:
    # 与数据库建立连接
    def get_conn(self):
        # 本机的数据库
        conn = pymysql.connect(host='127.0.0.1', port=3306, user="root2", passwd="root", db="testdb", charset="utf8")
        return conn

    # 类的方法，参数command为sql语句
    def execute(self, command):
        cursor = self.get_conn().cursor()
        try:
            # 执行command中的sql语句
            cursor.execute(command)
        except Exception as e:
            return e
        else:
            # fetchall()返回语句的执行结果，并以元组的形式保存
            return cursor.fetchall()

    def select(self):
        return self.execute("select * from category")

简单demo的调用

from DAO import Database

dao = Database()

user = dao.select()

print(user)