python用字典转成insert语句

这个是我经常用的一个玩法,之前都是苦逼呵呵的去解析字典，有了这个就方便多了

1.组织数据生成字典

举个例子，我就弄个邮箱和年龄2个字段，后边调用

ret = 
{
	'username':ceshi@fuck.com,
	'age':''18
}

然后我们的表名叫 ceshi 就username,age,level_id这些字段

2.对这个插入库的做个简单封装，需要传1个表名、和字典数据

AdminInfo.py

#!/usr/bin/python3


from common.MysqlHelper import MysqlHelper


class AdminInfoHelper:

    def __init__(self, ip, port, db_name, db_user, db_password):
        self.ip = ip
        self.port = port
        self.db_name = db_name
        self.db_user = db_user
        self.db_password = db_password

    def insert_info_user(self, table_name, **user_info):
        mysql_helper = MysqlHelper(self.ip, self.port, self.db_name, self.db_user, self.db_password)
        ls = [(k, v) for k, v in user_info.items() if v is not None]
        sentence = 'INSERT %s (' % table_name + ','.join([i[0] for i in ls]) + \
                   ') VALUES (' + ','.join(repr(i[1]) for i in ls) + ');'
        mysql_helper.insert(sentence)
        mysql_helper.close()

MysqlHelper.py 这个类简单封装了对MySQL的操作，实例化后直接用

#!/usr/bin/python


import pymysql


class MysqlHelper:
    def __init__(self, host, port, db, user, passwd, charset='utf8mb4'):
        self.host = host
        self.port = port
        self.db = db
        self.user = user
        self.passwd = passwd
        self.charset = charset

    def connect(self):
        self.conn = pymysql.connect(host=self.host, port=self.port, db=self.db, user=self.user, passwd=self.passwd,
                                    charset=self.charset)
        self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)

    def close(self):
        self.cursor.close()
        self.conn.close()

    def get_all(self, sql):
        res = ()
        try:
            self.connect()
            self.cursor.execute(sql)
            res = self.cursor.fetchall()
            self.close()
        except Exception as e:
            print(e)
        return res

    def insert(self, sql):
        try:
            self.connect()
            self.cursor.execute(sql)
            self.conn.commit()
        except Exception as e:
            self.conn.rollback()
            print(e)

    def update(self, sql):
        try:
            self.connect()
            self.cursor.execute(sql)
            self.conn.commit()
        except Exception as e:
            self.conn.rollback()
            print(e)

    def delete(self, sql):
        try:
            self.connect()
            self.cursor.execute(sql)
            self.conn.commit()
        except Exception as e:
            self.conn.rollback()
            print(e)

from work.AdminInfo import AdminInfoHelper

admin = AdminInfoHelper('x.x.x.x', 3307, 'xxx', 'fuck', 'ooxx')

admin.insert_info_user('usersinfo', **ret)

这就完事了