python保存mysql数据

之前还没有发现【HPTypecho】数据导入插件HPImport的使用, 所以直接折腾了mysql的存储。

下面这个代码与【HPTypecho】不同点在于，不会写入重复数据。 如果出现相同的primary key会直接抛出异常。

▌代码

参考：N年前在github找的代码，找不到出处了。

class SQL:
    username = 'xxxxx'
    password = 'xxxxx'
    database = 'xxxx'
    host = '127.0.0.1'
    port = 3306

    conn = None
    cur = None

    def __init__(self):
        self.connect()

    @staticmethod
    def escape(string):
        return '`%s`' % string

    def connect(self):
        connect_kvs = {
            'user': self.username,
            'password': self.password,
            'host': self.host,
            'port': self.port,
            'database': self.database
        }

        try:
            self.conn = mysql.connector.connect(**connect_kvs)
            self.cur = self.conn.cursor()
            return True
        except mysql.connector.Error as err:
            raise Exception("Connect Error: ", err)
            return False

    def insert(self, tablename, **values):
        """insert data"""
        if self.conn:
            tablename = self.escape(tablename)  
            if values:
                _keys = ",".join(self.escape(k) for k in values)
                _values = ",".join(['%s',]*len(values))
                sql_query = "insert into %s (%s) values (%s)" % (tablename,_keys,_values)
            else:
                sql_query = "replace into %s default values" % tablename

            try:
                if values:
                    self.cur.execute(sql_query,list(itervalues(values)))
                else:       
                    self.cur.execute(sql_query)
                self.conn.commit()
                return True
            except mysql.connector.IntegrityError as e:
                logger.exception("Duplicate entry for key 'PRIMARY'.", e)
                return False
            except Exception as e:
                raise Exception("Insert Error Occured: ", e)
                return False

    def select(self, query):
        if self.conn:
            try:
                self.cur.execute(query)
                keyword_list = self.cur.fetchall()
                return keyword_list
            except Exception as e:
                raise Exception("Select Error Occured:  ", e)
                return False

▌食用方法

#数据格式为dict
result={
    "id":id,
    "url": url,
    "title": title,
    "time": c_datetime,
    'content':match_content,
    "type":content_type
}

## 食用方法
sql = SQL()
sql.insert('fashenweigonggao',**result)

之前爬虫的数据都是用mysql来存储的，这里就记录一下。