dataframe列名获取、删除列、创建对应的数据库表

创建于：20201026
修改于：20201026

1. 获取dataframe列名

print("===1:", type(df.columns), df.columns)
print("===2:", type(df.columns.values), df.columns.values)
print("===3:", type(df.columns.values.tolist()), df.columns.values.tolist())
print("===4:", type(df.dtypes), df.dtypes)
print("===5:", type(df.dtypes.index), df.dtypes.index)

===1: <class 'pandas.core.indexes.base.Index'> Index(['cons_no', 'pro_name', 'cons_name'], dtype='object')
===2: <class 'numpy.ndarray'> ['cons_no' 'pro_name' 'cons_name']
===3: <class 'list'> ['cons_no', 'pro_name', 'cons_name']
===4: <class 'pandas.core.series.Series'> cons_no            object
pro_name           object
cons_name          object
===5: <class 'pandas.core.indexes.base.Index'> Index(['cons_no', 'pro_name', 'cons_name'], dtype='object')

df.columns 和 df.dtypes.index输出的结果类型、内容一致。

2. 删除当前dataframe的所有列

df.drop(columns=list(df.columns), axis=1, inplace=True)

list(df.columns)也可以获取dataframe列名。

3. 由dataframe的列名，创建合适的数据库表

table_content = ''
for col in cols_dtypes.index:
    table_content += str(col) + ' ' + str(cols_dtypes[col]).replace('object', 'string, '). \
            replace('int64', 'string, ').replace('float64', 'double, ')
table_content = table_content[:-2]

table_name = OUTPUTTABLE + '_test'
table_condition = "row format delimited fields terminated by ',' lines terminated by '\\n'"

sql = "create table if not exists " + table_name
sql += " (" + table_content + ")"
if table_condition:
    sql += " " + table_condition
sql += ";"
print("====In create_table(), sql is\n", sql)
try:
    self.cursor.execute(sql)
except Exception as e:
    print("In create_table(): Maybe the table exists. \nError is ", e)