Python任意字典写入MySQL

前言

应用场景

爬虫数据写入数据库

优点

简洁、通用、直接复制可用

先修基础！

1、 Python 任意字典生成 insert 语句
2、 Python 任意字典生成 update 语句
3、 Python 连接 MySQL 基础

INSERT

import pymysql

class Mysql:
    def __init__(self, db_name, tb_name):
        self.db = pymysql.connect(
            host='localhost', user='root', password='yellow', charset='utf8', db=db_name)
        self.cursor = self.db.cursor()
        self.tb = tb_name

    def __del__(self):
        self.cursor.close()
        self.db.close()

    def insert(self, dt):
        ls = [(k, dt[k]) for k in dt if dt[k] is not None]
        sql = 'insert %s (' % self.tb + ','.join(i[0] for i in ls) +\
              ') values (' + ','.join('%r' % i[1] for i in ls) + ')'
        self.cursor.execute(sql)
        self.db.commit()

UPDATE

import pymysql

class Mysql:
    def __init__(self, db_name):
        self.db = pymysql.connect(
            host='localhost', user='root', password='yellow', charset='utf8', db=db_name)
        self.cursor = self.db.cursor()

    def __del__(self):
        self.cursor.close()
        self.db.close()

    def commit(self, sql):
        try:
            self.cursor.execute(sql)
            self.db.commit()
        except Exception as error:
            print('\033[031m', error, '\033[0m', sep='')

    def update(self, dt, dt_condition, table):
        sql = 'UPDATE %s SET ' % table + ','.join('%s=%r' % (k, dt[k]) for k in dt)\
              + ' WHERE ' + ' AND '.join('%s=%r' % (k, dt_condition[k]) for k in dt_condition)
        self.commit(sql)

无则插入，有则插入

from pymysql.connections import Connection


class Mysql:
    def __init__(self, password, database, host='localhost', user='root'):
        self.db = Connection(
            user=user,
            password=password,
            host=host,
            database=database,
            port=3306,
            charset='UTF8')
        self.cursor = self.db.cursor()

    def __del__(self):
        self.cursor.close()
        self.db.close()

    def commit(self, sql):
        print(sql)
        try:
            self.cursor.execute(sql)
            self.db.commit()
        except Exception as e:
            print(e)

    def fetchall(self, query):
        self.cursor.execute(query)
        return self.cursor.fetchall()  # 有数据：tuple of tuple；无数据：()

    def insert(self, dt, tb):
        ls = [(k, v) for k, v in dt.items() if v is not None]
        sql = 'INSERT %s (' % tb + ','.join(i[0] for i in ls) + \
              ') VALUES (' + ','.join('%r' % i[1] for i in ls) + ')'
        self.commit(sql)

    def update(self, dt, dt_condition, tb):
        sql = 'UPDATE %s SET ' % tb + ','.join('%s=%r' % (k, v) for k, v in dt.items()) \
              + ' WHERE ' + ' AND '.join('%s=%r' % (k, v) for k, v in dt_condition.items())
        self.commit(sql)

    def insert_or_update(self, dt, dt_condition, tb):
        """有则更新，无则插入"""
        query = 'SELECT * FROM ' + tb + ' WHERE ' + \
                ' AND '.join('%s=%r' % (k, v) for k, v in dt_condition.items())
        if self.fetchall(query) == ():
            dt.update(dt_condition)
            self.insert(dt, tb)
        else:
            self.update(dt, dt_condition, tb)