Is it possible to add a list as new column into a sqlite database with python-CodePudding

I have a table in a database in sqlite3 in which I am trying to add a list with values from python as a new column. I can only find how to add a new column without values or change specific rows, could somebody help me with this?

CodePudding user response：

This is probably the sort of thing you can google.

I cant find any way to add data to a column on creation, but you can add a default value (ALTER TABLE table_name ADD COLUMN column_name NOT NULL DEFAULT default_value) if that helps at all. Then afterwards you are going to have to add the data separately. There are a few places to find how to do that. These questions might be relevant:

Populate Sqlite3 column with data from Python list using for loop

Adding column in SQLite3, then filling it

CodePudding user response：

You can read the database into a pandas dataframe, add a list as a column to that dataframe, then replace the original file from the dataframe:

import sqlite3
import pandas as pd

conn = sqlite3.connect("my_data.db")
df = pd.read_sql_query("SELECT * FROM my_table", conn)
conn.close()

df['new_column'] = my_list

conn = sqlite3.connect("my_data.db")
df.to_sql(name='my_table', if_exists='replace', con=conn)
conn.close()