Creating additional column while importing csv via df.to_sql in sqlalchemy framework

I’ve to import csv data in sql using sqlAlchemy.
The csv has to columns (x, y) but I need to add a third column (delta_y) in the sql database to store processed data.

Using the following code it reads the csv to the sql database but is not creating the actual empty column in the database. Is there a smooth way to inherit was is mapped out in the class?

from sqlalchemy import Column, Integer, Float, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, update

engine = create_engine('sqlite:///hausarbeit_db.sqlite3', echo=True)
Base = declarative_base()

class Test(Base):
    __tablename__ = "test"
    id = Column(Integer, primary_key=True)
    x = Column(Float)
    y = Column(Float)
    delta_y = Column(Float)


file_name = 'Beispiel-Datensaetze//test.csv'
df = pd.read_csv(file_name)
df.to_sql('test', con=engine, index_label="id", if_exists='replace')
TEST = Base.metadata.tables['test']

I’m also happy to hear any other hints or tips around the code above.


>Solution :

Can’t you add a new empty column in the data-frame after reading from the csv

df["delta_y"] = np.nan 

# or 

df["delta_y"] = ""

Leave a Reply