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

Question

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)


Base.metadata.create_all(engine)

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.

Thanks!

Kulasangar · Accepted Answer · 2023-01-05 12:21:39Z

1

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"] = ""

answered Jan 5, 2023 at 12:21

Kulasangar

9,5326 gold badges65 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rebecca D. Over a year ago

To simple :-D Yes, it's working! However it would be great if someone knows a solution using the already defined class.

Kulasangar Over a year ago

I"m not sure whether I followed your above comment properly.. so this does not create an additional column in your table? or you're looking for some other solution?

Rebecca D. Over a year ago

It's actually doing what it is supposed to do, thanks! I'm just curious if there is another solution using the columns already lined out in the class definition.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related