pandas在使用to_sql()保存数据到数据表中,Mysql会将bool类型的数据转为tinyint类型,比如:
此时数据表字段的类型为:
读取的时候,如果直接使用read_sql会原封不动的读取成1或0的数据,因此我们存储的时候没有办法将true/false存储进数据库,只能在读取的时候使用:
data_df = pd.read_sql_query( text('select * from test_bool_data'), con=engine.connect(), dtype={"bool_true": bool, "bool_false": bool, }) # 读取sql
这样就可以在读取出数据之后,将指定的这两列bool_true与bool_false转为bool布尔类型的数据
示例程序
import pandas as pd import numpy as np from sqlalchemy import create_engine, text def get_engine(): mysql_config = { "db": "just_test", "host": "127.0.0.1", "user": "XXXX", "password": "XXXX", "port": 3306, } engine = create_engine( "mysql+pymysql://{}:{}@{}:{}/{}".format(mysql_config['user'], mysql_config['password'], mysql_config['host'], mysql_config['port'], mysql_config['db'])) return engine def save_sql(): engine = get_engine() # 保存dataframe tmp_df = pd.DataFrame(np.random.random(size=(10, 5))) tmp_df['bool_true'] = True tmp_df['bool_false'] = False tmp_df.to_sql('test_bool_data', con=engine, if_exists='append', index=False) def read_sql(): engine = get_engine() data_df = pd.read_sql_query(text('select * from test_bool_data'), con=engine.connect(), dtype={ "bool_true": bool, "bool_false": bool, }) # 读取sql print(data_df.head()) if __name__ == '__main__': read_sql()