python简单的表操作，mysql可不用写sql

为方便python查询数据库方便，且只需要知道表等，不需要写sql，即可查询数据。

from recordlog import recordlog
from mymysql.MysqlTool import MysqlTool
from env import MYSQL_DB, MYSQL_HOST, MYSQL_PORT, MYSQL_USER, MYSQL_PASSWD

rdlog = recordlog()
s_log, f_log = rdlog.return_logers()


class MysqlOperateData(object):
    def __init__(self):
        f_log.debug("数据库连接信息为：" + MYSQL_HOST + " " + MYSQL_PORT + " " + MYSQL_USER + " " + MYSQL_DB)
        f_log.info("start connet mysql!")
        self.sql_hand = MysqlTool(MYSQL_HOST, int(MYSQL_PORT), MYSQL_USER, MYSQL_PASSWD, MYSQL_DB)
        self.sql_hand.conn_mysql()
        f_log.info("connect mysql success!")

    def get_all_data(self, table_name, column_names=[], sql_where={}, all_flag=True):
        sql = "SELECT {} FROM " + table_name
        if len(column_names) == 0:
            sql = sql.format("*")
        else:
            column_name = ",".join(column_names)
            sql = sql.format(column_name)
        if sql_where:
            sql_where = self._make_where_sql(sql_where)
            sql = sql + " WHERE " + sql_where
        f_log.debug("查询所有数据sql：" + sql)
        result = self.sql_hand.sql_all_dql(sql) if all_flag else self.sql_hand.sql_one_dql(sql)
        return result

    def distinct_all_data(self, table_name, column_names=[], sql_where={}):
        sql = "SELECT DISTINCT {} FROM " + table_name
        if len(column_names) == 0:
            sql = sql.format("*")
        else:
            column_name = ",".join(column_names)
            sql = sql.format(column_name)
        if sql_where:
            sql_where = self._make_where_sql(sql_where)
            sql = sql + " WHERE " + sql_where
        f_log.debug("查询所有唯一数据sql：" + sql)
        result = self.sql_hand.sql_all_dql(sql)
        return result

    def update_data(self, table_name, set_sql={}, sql_where={}):
        sql = "UPDATE {} SET ".format(table_name)
        if not set_sql:
            return
        set_sql = self._make_update_set_sql(set_sql)
        sql = sql + set_sql
        if sql_where:
            sql_where = self._make_where_sql(sql_where)
            sql = sql + " WHERE " + sql_where
        f_log.debug("更新数据sql：" + sql)
        self.sql_hand.sql_dml(sql)

    def delete_data(self, table_name, sql_where={}):
        sql = "DELETE FROM {} ".format(table_name)
        if sql_where:
            sql_where = self._make_where_sql(sql_where)
            sql = sql + " WHERE " + sql_where
        f_log.debug("删除数据sql：" + sql)
        self.sql_hand.sql_dml(sql)

    def insert_data(self, table_name, column_name=[], values=[]):
        sql = "INSERT INTO " + table_name
        try:
            if not column_name or not values:
                return
            column_name = ",".join(column_name)
            values = self._make_insert_value(values)
            sql = sql + " (" + column_name + ") VALUES (" + values + ")"
            f_log.debug("插入数据sql：" + sql)
            self.sql_hand.sql_dml(sql)
        except Exception as e:
            f_log.error("插入数据报错，对应sql：" + sql)
            f_log.error("报错信息为：" + str(e))

    @staticmethod
    def _make_where_sql(keys_values):
        sql_where = []
        for key, value in keys_values.items():
            if str(value).lower() == "null":
                sql_where.append(str(key) + " is " + str(value))
            elif ">=" in str(value):
                sql_where.append(str(key) + " >= \"" + str(value).replace(">=", "") + "\"")
            elif ">" in str(value):
                sql_where.append(str(key) + " > \"" + str(value).replace(">", "") + "\"")
            elif "<" in str(value):
                sql_where.append(str(key) + " < \"" + str(value).replace("<", "") + "\"")
            else:
                sql_where.append(str(key) + " = \"" + str(value) + "\"")
        sql = " and ".join(sql_where)
        return sql

    @staticmethod
    def _make_update_set_sql(keys_values):
        set_sql = []
        for key, value in keys_values.items():
            set_sql.append(str(key) + " = \"" + str(value) + "\"")
        sql = ",".join(set_sql)
        return sql

    @staticmethod
    def _make_insert_value(values):
        insert_sql = ""
        for i in values:
            if isinstance(i, int):
                insert_sql = insert_sql + "," + str(i)
            else:
                insert_sql = insert_sql + ",\"" + i + "\""
        insert_sql = insert_sql[1:len(insert_sql)]
        return insert_sql

    def close_connect(self):
        try:
            f_log.info("关闭数据库连接！")
            self.sql_hand.close_mysql()
        except Exception as e:
            f_log.error("数据库连接关闭报错，报错信息为：" + str(e))


if __name__ == "__main__":
    MysqlOperateData()

上面类的使用样例

from mymysql.MysqlOperateData import MysqlOperateData
import datetime

mysql_handle = MysqlOperateData()
before_one_minutes = (datetime.datetime.now() - datetime.timedelta(minutes=7)).strftime("%Y-%m-%d %H:%M:%S")
where = {"check_time": before_one_minutes + ">="}
columns = ["server_ip", "server_name", "cpu_use_ratio", "mem_used_ratio", "file_sys", "check_time",
                   "ssh_status"]
result = self.mysql_handle.get_all_data("linux_info", column_names=columns, sql_where=where)