<2> MySQL存储

import mysql.connector

"""数据模型类"""
class QingHuaModel(object):
    def __init__(self, title, time, contents):
        self.title = title
        self.time = time
        self.contents = contents

    # def __str__(self):
    #     return f'QingHuaModel [title = {self.title}, time = {self.time}, contents = {self.contents}]'


"""数据库类"""
class QingHuaDB(object):
    def __init__(self):
        # 创建数据库
        sql_db = 'create database IF NOT EXISTS qinghua_db;'
        # 获取连接对象
        self.conn = mysql.connector.connect(host='127.0.0.1', user='root', password='qwe123',
                                            auth_plugin='mysql_native_password')
        # 获取游标对象
        self.cursor = self.conn.cursor()
        # 执行sql语句，创建数据库
        self.cursor.execute(sql_db)
        # 切换数据库
        self.cursor.execute('use qinghua_db')
        # 创建数据表
        sql_table = """
             create table data_table(
                 title longtext,
                 time longtext,
                 contents longtext
            );
        """
        try:
            self.cursor.execute(sql_table)
        except:
            logger.info('表存在，删除表数据')
            self.cursor.execute('delete from data_table')

    def insert_row(self, data_model):
        sql = 'insert into data_table(title, time, contents) values(%s, %s, %s);'
        values = (data_model.title, data_model.time, data_model.contents)
        # 插入数据
        self.cursor.execute(sql, values)
        # 提交数据
        self.conn.commit()

    def close_db(self):
        try:
            self.cursor.close()
            self.conn.close()
        except BaseException as e:
            print(e)

 

 

import pymysql

class MySQLSnippet:

    def __init__(self):
        # 游式游标
        self.connect_settings = {
            "host": "127.0.0.1",
            "port": 3306,
            "user": "root",
            "passwd": "root",
            "db": "dbname",
            } # 配置数据库链接参数
        self.connection = pymysql.connect(**self.connect_settings)
        self.cursor = self.connection.cursor(cursor=pymysql.cursors.SSDictCursor)
        # self.cursor = self.connection.cursor(cursor=pymysql.cursors.DictCursor)

    def read_something(self):
        select_sql = "SELECT * FROM `wechat` LIMIT 10;"

        self.cursor.execute(select_sql)
        # 1. for 循环的方式
        for cur in self.cursor:
            print(cur)
        
        # 2. while 循环的方式
        while True: 
            data = self.cursor.fetchone()

    def write_something(self):
        insert_sql = "INSERT INTO tablename (name, address) VALUES (%s, %s)"
        # delete_sql = "DELETE FROM tablename WHERE address = 'Mountain 21'"
        # update_sql = "UPDATE tablename SET address = 'Canyon 123';
        self.cursor.execute(insert_sql, ("John", "Highway 21"))
        self.cursor.commit()

    def close(self):
        # 关闭游标
        self.cursor.close()
        self.connection.close()