mysql sql导出flask-sqlalchemy模型映射代码

此sql执行会得到完整的flask-sqlalchemy模型映射代码，直接复制就可以

set @table_schema = 'test';					# 库名
set @table_name = 'c_buy_order_new';		# 表名
set @model_name = 'BuyOrder';				# 模型名

SELECT CONCAT('class ', @model_name, '(db.Model):')
union all
SELECT CONCAT("    __tablename__ = \'",@table_name,"'")
union ALL
SELECT a.* from (
SELECT
	CONCAT( 
			'    ', COLUMN_NAME, ' = db.Column(db.',
			( CASE 
					WHEN DATA_TYPE='varchar' 						THEN CONCAT('String(',CHARACTER_MAXIMUM_LENGTH,')')
					WHEN DATA_TYPE in ('int', 'tinyint', 'bigint')	THEN 'Integer'
					WHEN DATA_TYPE='datetime' 						THEN 'DateTime'
					WHEN DATA_TYPE='date' 							THEN 'Date'
					WHEN DATA_TYPE='time' 							THEN 'Time'
					WHEN DATA_TYPE='decimal' 						THEN CONCAT('DECIMAL(',NUMERIC_PRECISION,', ',NUMERIC_SCALE, ')')
					WHEN DATA_TYPE in ('text','longtext') 			THEN 'Text'
			END ),
			if(COLUMN_KEY='PRI', ', primary_key=True', ''),
			if(EXTRA='auto_increment', ', autoincrement=True', ''),
			if(COLUMN_DEFAULT is not NULL and COLUMN_DEFAULT != '', CONCAT(', default=',COLUMN_DEFAULT), ''),
			if(COLUMN_COMMENT!='', CONCAT(', info=\'', COLUMN_COMMENT, '\''), ''),
		')'
	) sqlalchemy
FROM
	information_schema.`COLUMNS`
WHERE
	TABLE_SCHEMA = @table_schema
AND TABLE_NAME = @table_name
ORDER BY
	ORDINAL_POSITION
limit 999999
) a

执行结果