créer de nouvelles colonnes dans une table sql existante, avec des colonnes supplémentaires provenant de Dataframe pandas

Question

J'ai plusieurs pandas Dataframe que je souhaite écrire dans un SQL database . Cependant, étant donné que les SQL database pourrait ne pas avoir cette particularité column name qui était dans le pandas Dataframe j'obtiens un message d'erreur disant que 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)

Cependant, comme cette table a déjà été créée, avec toutes les colonnes, si df2 devait avoir des colonnes supplémentaires, j'obtiens un message d'erreur.

df2
out= columnA, columnB, columnC, columnD, columnE, columnF
     valueA,  valueB,  valueC,  valueD,  valueE,  valueF      

Comment puis-je structurer un code, qui créerait de nouvelles colonnes dans la base de données existante ? SQL table avec les noms de ces colonnes, comme les noms de colonne manquants de pandas Dataframe ?

Je pense que je peux ajouter de nouvelles colonnes avec la commande suivante sql code

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

Mais comment m'assurer que le new_column qui a été ajouté, suit le nom de la colonne dans df2 ?

Answer 1

J'ai eu un problème similaire et j'ai adopté l'approche suivante :

1) Obtenez une liste des colonnes de la table de la base de données. Cela peut être fait de plusieurs façons, mais j'ai utilisé postgres au lieu de sqllite. Voir este Question SE pour obtenir les noms des colonnes d'une table de postgresql. Ce site La question semble répondre à la manière de le faire pour sqlite.

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

Ceci renvoie une liste de tuples, donc récupère le premier de chaque tuple :

db_columns = [x[0] for x in db_columns]

Vous pouvez bien sûr charger la table dans pandas et utiliser les colonnes du dataframe à la place. Cela nécessitera évidemment plus de ressources :

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

2) Obtenez la différence entre les colonnes de la table de la base de données et les colonnes de la df. J'aime utiliser les ensembles car je les trouve intuitifs. Cependant, ils ne préservent pas l'ordre :

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

Si l'ordre est important, vous pouvez utiliser un filtre :

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

3) Itérer sur les nouvelles colonnes et se préparer à les ajouter au tableau :

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

Dans cet exemple, j'ai utilisé "text" mais vous pouvez le remplacer par le type de données primitif qui correspond au dtype de pandas/numpy. np.asscalar(value) est un moyen de convertir les types numpy en types python. Voir este SO question pour en savoir plus sur la conversion des types numpy en types python. Enfin, ajoutez toutes les colonnes au tableau :

 result = connection.execute(query, query_params)