Python Dict字典数据转换MYSQL SQL格式

python类型	mysql类型
int	INT或INTEGER
float	FLOAT或DOUBLE
str	VARCHAR或TEXT
bool	TINYINT
None	NULL
time.localtime () datetime.datetime.now ()	VARCHAR

数据

	# 测试数据
	mydata = {'name': '测试', 'age': 888, 'money': 99.7, 'false_state': False, 'true_state': True, "one_none": None,
			  "two_none": None, 'localtime': time.localtime (), 'timeNow': datetime.datetime.now ()}

dict转换为insert

	# 插入 sql_insert
	data = collections.OrderedDict ( mydata )
	keys = str ( tuple ( data.keys () ) ).replace ( "'", "`" )

	def insert_format( item ):
		if type ( item ) is bool:
			return int ( item )
		elif isinstance ( item, (datetime.datetime, datetime.date, datetime.time, time.struct_time) ):
			if type ( item ) is datetime.datetime:
				return datetime.datetime.strftime ( item, "%Y-%m-%d %H:%M:%S" )
			elif type ( item ) is time.struct_time:
				return time.strftime ( "%Y-%m-%d %H:%M:%S", item )
			else:
				return str ( item )
		else:
			return item

	vals = str ( tuple ( insert_format ( x ) for x in data.values () ) ).replace ( "None", "NULL" )
	sql_insert = '''INSERT INTO %s %s VALUES %s''' % ("test", keys, vals)
	print ( sql_insert )

 insert 语句

INSERT INTO test (`name`, `age`, `money`, `false_state`, `true_state`, `one_none`, `two_none`, `localtime`, `timeNow`) VALUES ('测试', 888, 99.7, 0, 1, NULL, NULL, '2022-05-26 18:32:29', '2022-05-26 18:32:29')

dict转换为update

# 更新 update
	def field_format( item ):
		if isinstance ( item[1], (int, float, bool) ):
			if type ( item[1] ) is bool:
				return item[0] + "=" + str ( int ( item[1] ) )
			return item[0] + "=" + str ( item[1] )
		elif item[1] is None:
			return item[0] + "=NULL"
		elif isinstance ( item[1], (datetime.datetime, datetime.date, datetime.time, time.struct_time) ):
			if type ( item[1] ) is datetime.datetime:
				return item[0] + "='" + datetime.datetime.strftime ( item[1], "%Y-%m-%d %H:%M:%S" ) + "'"
			elif type ( item[1] ) is time.struct_time:
				return item[0] + "='" + time.strftime ( "%Y-%m-%d %H:%M:%S", item[1] ) + "'"
			else:
				return item[0] + "='" + str ( item[1] ) + "'"
		else:
			return item[0] + "='" + item[1] + "'"


	field_info = ','.join ( list ( map ( lambda x: field_format ( x ), mydata.items () ) ) )
	where = ' WHERE id=1'
	sql_update = '''UPDATE {0} SET {1} {2}'''.format ( "test", field_info, where )
	print ( sql_update )

update语句 

UPDATE test SET name='测试',age=888,money=99.7,false_state=0,true_state=1,one_none=NULL,two_none=NULL,localtime='2022-05-26 18:32:29',timeNow='2022-05-26 18:32:29'  WHERE id=1

完整代码

# 测试数据
	mydata = {'name': '测试', 'age': 888, 'money': 99.7, 'false_state': False, 'true_state': True, "one_none": None,
			  "two_none": None, 'localtime': time.localtime (), 'timeNow': datetime.datetime.now ()}

	# 插入 sql_insert
	data = collections.OrderedDict ( mydata )
	keys = str ( tuple ( data.keys () ) ).replace ( "'", "`" )

	def insert_format( item ):
		if type ( item ) is bool:
			return int ( item )
		elif isinstance ( item, (datetime.datetime, datetime.date, datetime.time, time.struct_time) ):
			if type ( item ) is datetime.datetime:
				return datetime.datetime.strftime ( item, "%Y-%m-%d %H:%M:%S" )
			elif type ( item ) is time.struct_time:
				return time.strftime ( "%Y-%m-%d %H:%M:%S", item )
			else:
				return str ( item )
		else:
			return item

	vals = str ( tuple ( insert_format ( x ) for x in data.values () ) ).replace ( "None", "NULL" )
	sql_insert = '''INSERT INTO %s %s VALUES %s''' % ("test", keys, vals)
	print ( sql_insert )


	# 更新 update
	def field_format( item ):
		if isinstance ( item[1], (int, float, bool) ):
			if type ( item[1] ) is bool:
				return item[0] + "=" + str ( int ( item[1] ) )
			return item[0] + "=" + str ( item[1] )
		elif item[1] is None:
			return item[0] + "=NULL"
		elif isinstance ( item[1], (datetime.datetime, datetime.date, datetime.time, time.struct_time) ):
			if type ( item[1] ) is datetime.datetime:
				return item[0] + "='" + datetime.datetime.strftime ( item[1], "%Y-%m-%d %H:%M:%S" ) + "'"
			elif type ( item[1] ) is time.struct_time:
				return item[0] + "='" + time.strftime ( "%Y-%m-%d %H:%M:%S", item[1] ) + "'"
			else:
				return item[0] + "='" + str ( item[1] ) + "'"
		else:
			return item[0] + "='" + item[1] + "'"


	field_info = ','.join ( list ( map ( lambda x: field_format ( x ), mydata.items () ) ) )
	where = ' WHERE id=1'
	sql_update = '''UPDATE {0} SET {1} {2}'''.format ( "test", field_info, where )
	print ( sql_update )