python数据库pymysql增删改查

废话不多说了，直接上代码吧……

# -*- coding: utf-8 -*-
import pymysql
import json

class MySql(object):

    # 测试
    db = pymysql.connect(  # 连接数据库服务器
        user="root",
        password="root",
        host="192.168.1.40",
        port=3306,
        db="db_collect_data",
        charset="utf8"
    )
    cursor = db.cursor()  # 创建操作游标
    cursor.execute("use db_collect_data")  # 使用数据库

    def __init__(self):
        pass

    def insert_test_db(self, item):
        sql = """INSERT INTO product(itemid, shopid) VALUES(%s,%s)"""
        values = (item['itemid'], item['shopid'])
        try:
            self.cursor.execute(sql, values)
            self.db.commit()
        except Exception as e:
            self.db.rollback()
            raise

    def select_item_dbs(self, passString):
        sql = "SELECT id,projectWebsite,updateTime FROM bidder_project_update"
        self.cursor.execute(sql)
        results = self.cursor.fetchall()
        timeString = ""
        for item in results:
            compareString = str(item[0]) + item[1]
            if compareString == passString:
                timeString = item[2]
                break
        return timeString

    def refresh_time_db(self, newestTime, idString):
        sql = "update bidder_project_update set updateTime=%s where id = %s"  # 改  bidder_project_update_copy  bidder_project_update
        values = (newestTime, idString)
        self.cursor.execute(sql, values)
        self.db.commit()
        pass

    def select_num_db(self):
        sql = "SELECT COUNT(projectID) FROM bidder_project_update"
        self.cursor.execute(sql)
        result = self.cursor.fetchone()
        return result


    def close_db(self):
        self.db.close()
        pass