引言

记录python和mysql结合使用过程中的一些问题

1 一个比较标准的mysql数据库类声明

注意建立连接时候的charset编码和cursorclass等属性，后面一个属性是让返回值以字典的形式返回。

注意点：

• 查询结果返回的生成器（适用于数据比较大的情况
• 插入数据和更新数据需要注意commit()方法使用

from conf import configs as cf
import pymysql


class DB(object):
    def __init__(self, table):
        """initialization and connect mysql"""
        self.connect = pymysql.connect(host=cf['DB']['host'], port=cf['DB']['port'], user=cf['DB']['user'],password=cf['DB']['psw'], db=cf['DB']['db'], charset='utf8mb4',cursorclass=pymysql.cursors.DictCursor)
        self.cursor = self.connect.cursor()
        self.table = table
        print('mysql connects successfully')

    def __enter__(self):
        return self

    def __exit__(self, exc_type, exc_val, exc_db):
        self.cursor.close()
        self.connect.close()
        print('mysql is closed')

    def get_data_by_filed(self, field):
        """ get value by fields, filed is like url or url,release_date. :param field: such as url,release_date if exist more than one filed, otherwise url or release_date :return: generator """
        sql = "SELECT %(field)s FROM %(table)s " % {"field": field, "table": self.table}
        # print(sql)
        try:
            self.cursor.execute(sql)
            res = self.cursor.fetchall()
            for r in res:
                yield r
        except Exception as e:
            print("get_data_by_filed ERROR: ", e)

    def insert_data(self, field, value):
        """ insert data :param field: such as "(url, id)" :param value: ('https://weibo.com/121212112/dafadfa', '1faf12e1vad) :return: """
        sql = "INSERT INTO %(table)s %(field)s VALUES %(value)s" % {'table': self.table, 'field': field, "value": value}
        print(sql)
        try:
            self.cursor.execute(sql)
            self.connect.commit()
        except Exception as e:
            self.connect.rollback()
            print("insert_data ERROR:", e)

    def update_values_by_ID(self, field, value, _id):
        """ update value by id :param field: 'content' :param value: '【扩散！#四川九寨沟地震#最全应急电话】九寨沟' :return: """
        sql = "UPDATE %(table)s SET %(field)s = '%(value)s' WHERE ID = '%(id)s'" % {'table': self.table, 'field': field, "value": value, "id":_id}
        print(sql)
        try:
            self.cursor.execute(sql)
            self.connect.commit()
        except Exception as e:
            self.connect.rollback()
            print("update_value_by_ID ERROR:", value ,e)

配置文件

configs = {
    'DB': {
        'user': 'root',
        'psw': 'psw',
        'port': 3306,
        'host': '127.0.0.1',
        'db': 'public_opinion'
    }
}