Python检查写入MySQL的数据是否符合字段的数据类型定义

MySQL等传统数据库都有字段数据类型定义的设计。定义好字段类型以后，往字段里面插入/更新的数据，就要复合其数据类型的定义了，不然会写入报错。程序中往数据库里写入数据的时候，为了避免写入错误的发生，往往在写入之前需要判断一下写入数据是否复合数据库定义。以下是自己写的MySQL / MariaDB中一些主要的常规数据类型的Python检查。

#!/usr/bin/env python3
# -*- coding: utf-8 -*-


"""
create_author : 蛙鳜鸡鹳狸猿
create_time   : 2019-05-10
program       : *_* check validity of data to be written into MySQL / MariaDB *_*
"""


import re


def check_tinyint_column(column, if_none=False, if_sign=False):
    """
    Check if it is valid of data to be written to satisfy TINYINT(MySQL / MariaDB) type columns.
    :param column: column(data) to write into MySQL / MariaDB.
    :param if_none: whether none value is valid or not.
    :param if_sign: whether integer values signed or not.
    :return: Boolean.
    """
    if if_none:
        if re.match("^$", column):
            return True
        else:
            pass
    elif not if_none:
        if re.match("^$", column):
            return False
        else:
            pass
    try:
        int(column)
    except ValueError:
        return False
    min_check = None
    max_check = None
    if if_sign:
        min_check = -128
        max_check = 127
    elif not if_sign:
        min_check = 0
        max_check = 255
    int_column = int(column)
    if max_check >= int_column >= min_check:
        return True
    else:
        return False


def check_smallint_column(column, if_none=False, if_sign=False):
    """
    Check if it is valid of data to be written to satisfy SMALLINT(MySQL / MariaDB) type columns.
    :param column: column(data) to write into MySQL / MariaDB.
    :param if_none: whether none value is valid or not.
    :param if_sign: whether integer values signed or not.
    :return: Boolean.
    """
    if if_none:
        if re.match("^$", column):
            return True
        else:
            pass
    elif not if_none:
        if re.match("^$", column):
            return False
        else:
            pass
    try:
        int(column)
    except ValueError:
        return False
    min_check = None
    max_check = None
    if if_sign:
        min_check = -32768
        max_check = 32767
    elif not if_sign:
        min_check = 0
        max_check = 65535
    int_column = int(column)
    if max_check >= int_column >= min_check:
        return True
    else:
        return False


def check_mediumint_column(column, if_none=False, if_sign=False):
    """
    Check if it is valid of data to be written to satisfy MEDIUMINT(MySQL / MariaDB) type columns.
    :param column: column(data) to write into MySQL / MariaDB.
    :param if_none: whether none value is valid or not.
    :param if_sign: whether integer values signed or not.
    :return: Boolean.
    """
    if if_none:
        if re.match("^$", column):
            return True
        else:
            pass
    elif not if_none:
        if re.match("^$", column):
            return False
        else:
            pass
    try:
        int(column)
    except ValueError:
        return False
    min_check = None
    max_check = None
    if if_sign:
        min_check = -8388608
        max_check = 8388607
    elif not if_sign:
        min_check = 0
        max_check = 16777215
    int_column = int(column)
    if max_check >= int_column >= min_check:
        return True
    else:
        return False


def check_int_column(column, if_none=False, if_sign=False):
    """
    Check if it is valid of data to be written to satisfy INT(MySQL / MariaDB) type columns.
    :param column: column(data) to write into MySQL / MariaDB.
    :param if_none: whether none value is valid or not.
    :param if_sign: whether integer values signed or not.
    :return: Boolean.
    """
    if if_none:
        if re.match("^$", column):
            return True
        else:
            pass
    elif not if_none:
        if re.match("^$", column):
            return False
        else:
            pass
    try:
        int(column)
    except ValueError:
        return False
    min_check = None
    max_check = None
    if if_sign:
        min_check = -2147483648
        max_check = 2147483647
    elif not if_sign:
        min_check = 0
        max_check = 4294967295
    int_column = int(column)
    if max_check >= int_column >= min_check:
        return True
    else:
        return False


def check_bigint_column(column, if_none=False, if_sign=False):
    """
    Check if it is valid of data to be written to satisfy BIGINT(MySQL / MariaDB) type columns.
    :param column: column(data) to write into MySQL / MariaDB.
    :param if_none: whether none value is valid or not.
    :param if_sign: whether integer values signed or not.
    :return: Boolean.
    """
    if if_none:
        if re.match("^$", column):
            return True
        else:
            pass
    elif not if_none:
        if re.match("^$", column):
            return False
        else:
            pass
    try:
        int(column)
    except ValueError:
        return False
    min_check = None
    max_check = None
    if if_sign:
        min_check = -9223372036854775808
        max_check = 9223372036854775807
    elif not if_sign:
        min_check = 0
        max_check = 18446744073709551615
    int_column = int(column)
    if max_check >= int_column >= min_check:
        return True
    else:
        return False


def check_char_column(column, length, if_none=False):
    """
    Check if it is valid of data to be written to satisfy CHAR(MySQL / MariaDB) type columns.
    :param column: column(data) to write into MySQL / MariaDB.
    :param length: design of data length.
    :param if_none: whether none value is valid or not.
    :return: Boolean.
    """
    pattern = None
    if if_none:
        pattern = "(^.{%d}$)|(^$)" % length
    elif not if_none:
        pattern = "^.{%d}$" % length
    if re.match(pattern, column):
        return True
    else:
        return False


def check_varchar_column(column, length, if_none=False):
    """
    Check if it is valid of data to be written to satisfy VARCHAR(MySQL / MariaDB) type columns.
    :param column: column(data) to write into MySQL / MariaDB.
    :param length: design of data length.
    :param if_none: whether none value is valid or not.
    :return: Boolean.
    """
    pos = None
    if if_none:
        pos = 0
    elif not if_none:
        pos = 1
    pattern = "^.{%d,%d}$" % (pos, length)
    if re.match(pattern, column):
        return True
    else:
        return False


def check_datetime_column(column, if_none=False):
    """
    Check if it is valid of data to be written to satisfy DATETIME / TIMESTAMP(MySQL / MariaDB) type columns.
    :param column: column(data) to write into MySQL / MariaDB.
    :param if_none: whether none value is valid or not.
    :return: Boolean.
    """
    pattern = None
    if if_none:
        pattern = r"(^\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2}$)|(^$)"
    elif not if_none:
        pattern = r"^\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2}$"
    if re.match(pattern, column):
        return True
    else:
        return False