使用pandas Datafram中的额外列在现有sql表中创建新列

Question

我有几个pandas Dataframe，我想写进SQL database。但是，由于现有的SQL database可能没有column name中的column name，所以我得到一条错误消息，说the column in the table was not found, thus unable to append data。在

# Example:

df1 
out= column1, column2, column3, column4
     value1,  value2,  value3,  value4

df2
out= columnA, columnB, columnC
     valueA,  valueB,  valueC

# Initially I concat the df together and save it into SQL
combined_data = pandas.concat([df1, df2], axis=1,
                               join='inner')
pandas.DataFrame.to_sql(combined_data, name='table1', con=engine, 
                        if_exists='append', index=False)

但是，因为这个表已经创建了，包含所有列，如果df2有其他列，我会收到一条错误消息。在

^{pr2}$

如何构造一个代码，它将在现有的SQL table中创建新列，并使用这些列的名称作为pandas Dataframe中丢失的列名？在

我想我可以用下面的sql code添加新列

connection.execute("ALTER TABLE table1 ADD COLUMN new_column INTEGER DEFAULT 0")

但是如何确保添加的new_column跟在df2中的列名后面？在

Answer 1

我遇到了类似的问题，采取了以下方法：

1）从数据库表中获取列的列表。这有几种方法可以实现，但是我使用的是postgres而不是sqllite。有关从postgresql获取表的列名，请参见thisSE问题。This这个问题似乎回答了如何为sqlite做这件事。在

db_columns = list(engine.execute("SELECT column_name FROM information_schema.columns WHERE table_schema = 'public' AND table_name = 'my_table'")) 

这将返回元组列表，因此获取每个元组的第一个：

^{pr2}$

您可以将表加载到pandas中，然后使用dataframe的列。这显然需要更多的资源：

db_columns = pd.read_sql_query("SELECT * FROM my_table", connection).columns

2）获取数据库表列与df列的差异。我喜欢用布景，因为我觉得它们很直观。但是，它们不能维持秩序：

new_columns = set(df1.columns) - set(db_columns)

如果顺序很重要，则可以使用过滤器：

new_columns = list(filter(lambda x: x not in db_columns, df1.columns))

3）迭代新列并准备将它们添加到表中：

query = ''   
query params = []
for column in new_columns:
query+= "ALTER TABLE %s ADD COLUMN %s %s;"  
query_params.extend(["my_table", column,"text"])

在本例中，我使用了“text”，但是您可能希望用与pandas/numpy数据类型相对应的原始数据类型来替换它。np.asscalar(value)是将numpy类型转换为python类型的一种方法。有关将numpy转换为python类型的更多信息，请参见thisSO question。 最后将所有列添加到表中：

 result = connection.execute(query, query_params)