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 redef 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 Trueelse:passelif not if_none:if re.match("^$", column):return Falseelse:passtry:int(column)except ValueError:return Falsemin_check = Nonemax_check = Noneif if_sign:min_check = -128max_check = 127elif not if_sign:min_check = 0max_check = 255int_column = int(column)if max_check >= int_column >= min_check:return Trueelse:return Falsedef 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 Trueelse:passelif not if_none:if re.match("^$", column):return Falseelse:passtry:int(column)except ValueError:return Falsemin_check = Nonemax_check = Noneif if_sign:min_check = -32768max_check = 32767elif not if_sign:min_check = 0max_check = 65535int_column = int(column)if max_check >= int_column >= min_check:return Trueelse:return Falsedef 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 Trueelse:passelif not if_none:if re.match("^$", column):return Falseelse:passtry:int(column)except ValueError:return Falsemin_check = Nonemax_check = Noneif if_sign:min_check = -8388608max_check = 8388607elif not if_sign:min_check = 0max_check = 16777215int_column = int(column)if max_check >= int_column >= min_check:return Trueelse:return Falsedef 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 Trueelse:passelif not if_none:if re.match("^$", column):return Falseelse:passtry:int(column)except ValueError:return Falsemin_check = Nonemax_check = Noneif if_sign:min_check = -2147483648max_check = 2147483647elif not if_sign:min_check = 0max_check = 4294967295int_column = int(column)if max_check >= int_column >= min_check:return Trueelse:return Falsedef 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 Trueelse:passelif not if_none:if re.match("^$", column):return Falseelse:passtry:int(column)except ValueError:return Falsemin_check = Nonemax_check = Noneif if_sign:min_check = -9223372036854775808max_check = 9223372036854775807elif not if_sign:min_check = 0max_check = 18446744073709551615int_column = int(column)if max_check >= int_column >= min_check:return Trueelse:return Falsedef 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 = Noneif if_none:pattern = "(^.{%d}$)|(^$)" % lengthelif not if_none:pattern = "^.{%d}$" % lengthif re.match(pattern, column):return Trueelse:return Falsedef 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 = Noneif if_none:pos = 0elif not if_none:pos = 1pattern = "^.{%d,%d}$" % (pos, length)if re.match(pattern, column):return Trueelse:return Falsedef 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 = Noneif 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 Trueelse:return False

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！