python mysql工具类

需求

项目开发的过程中，很多时候需要向数据库中插入一些测试数据，或者是做一些初始化操作。如果用java，需要先编译、再运行，不是很方便。

所以考虑用脚本语言写一个工具类，方便以后使用。

为了减少一些sql拼装的操作，写了个类orm的工具类。

所谓orm就是对象的属性与数据库中的表做了映射，一 一对应。 因此我们可以通过对象的属性以及操作类型（增删改查），来自动拼装出对应的sql。

实现

import pymysql


def get_connection():
    host = '192.168.2.192'
    user = 'root'
    password = "root"
    db = "test"
    charset = "utf8mb4"
    con = pymysql.connect(host=host, user=user, password=password, db=db,
                          charset=charset)
    return con


# 插入数据，如需自定义表名，则传参table_name; 如有自增列，则传参auto_id
def insert(obj, table_name='', auto_id=''):
    con = get_connection()
    attr_dict = obj.__dict__
    if table_name == '':
        table_name = table_name_format(obj.__class__.__name__)
    sql = "insert into " + table_name + " ( "
    for key in attr_dict:
        if key != auto_id:
            sql = sql + " `" + str(key) + "` , "
    sql = sql[:-2] + ")"

    sql = sql + ' values ( '
    for key in attr_dict:
        if key != auto_id:
            sql = sql + "'" + str(attr_dict[key]) + "' , "

    sql = sql[:-2] + " ) "

    print(sql)
    cur = con.cursor()
    cur.execute(sql)
    con.commit()
    con.close()


# 更新数据，如需自定义表名，则传参table_name; 如有条件，则传参id_key
def update(obj, table_name='', id_key=''):
    con = get_connection()
    attr_dict = obj.__dict__
    if table_name == '':
        table_name = table_name_format(obj.__class__.__name__)
    sql = "update " + table_name + " set "
    for key, value in attr_dict.items():
        if key != id and key != '' and key is not None:
            sql = sql + " `" + key + "` = '" + str(value) + "' , "

    sql = sql[:-2]

    if id_key != '':
        sql = sql + " where `" + id_key + "` = " + " '" + str(attr_dict[id_key]) + "' "

    print(sql)
    cur = con.cursor()
    cur.execute(sql)
    con.commit()
    con.close()


def delete(obj, id_key, table_name=''):
    con = get_connection()
    if table_name == '':
        table_name = table_name_format(obj.__class__.__name__)
    sql = "delete from " + table_name + "where id_key = " + id_key
    cur = con.cursor()
    cur.execute(sql)
    con.commit()
    con.close()


# 查询操作;返回一个二维元组
def select(obj, table_name=''):
    con = get_connection()
    if table_name == '':
        table_name = table_name_format(obj.__class__.__name__)
    sql = "select * from " + table_name
    if obj != '' and obj is not None:
        sql = sql + " where "
        attr_dict = obj.__dict__
        for key, value in attr_dict.items():
            sql = sql + "`" + key + "` = " + " '" + str(value) + "' and "
        sql = sql[:-4]

    cur = con.cursor()
    cur.execute(sql)
    res = cur.fetchall()
    con.close()
    return res


# 表名格式化
def table_name_format(table_name):
    lst = []
    for index, char in enumerate(table_name):
        if char.isupper() and index != 0:
            lst.append("_")
        lst.append(char)

    return "".join(lst).lower()

示例

如何使用这个工具类

假设数据库中有一个表为student

from common_sql import *
from student import Student

if __name__ == '__main__':
    # 添加学生
    insert(Student())
    
    # 查询数据
    res = select(Student())
    print(res)