Python: flask-sqlalchemy fill table using csv as source without listing every column of table

Question

I want to begin by stating that I know there are a lot of similar questions on this site, and the solutions do work, my issue is that, at least the ones I could find are all variations of something like this:

from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy(app)
class Example(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    Column1 = db.Column(db.String(100))
    Column2 = db.Column(db.String(100))
    Column3 = db.Column(db.String(100))

import csv

with open("source_file.csv") as f:
    reader = csv.reader(f)
    next(reader)
    for i in reader:
        new_entry = Example(
        Column1 = i[0],
        Column2 = i[1],
        Column3 = i[2])
        db.session.add(new_entry)
        db.session.commit()

This works, but it has a couple of issues, for instance if the table in question has n amount of columns, I need to write nx2 amount of lines, once to define the table and the second to fill the contents of the table.

This is sort of ok with small tables but if you are working with tables that have 100+ columns this gets ridiculous fast. Also, if for any reason the table structure changes even a little bit, it gets impossible to maintain.

So, I’m asking for help for a better way to do it, if this is still a duplicate question I apologize and ask only for guidance, I found solutions but only for SQLAlchemy, they did not work when I tried them in FlaskSQlAlchemy.

jorzel · Accepted Answer · 2022-02-08 16:11:21Z

6

Assuming that source_file.csv header items are the same as column names in the table you can do something like this:

import csv

with open("source_file.csv") as f:
    reader = csv.reader(f)
    header = next(reader)
    for i in reader:
        kwargs = {column: value for column, value in zip(header, i)}
        new_entry = Example(**kwargs)
        db.session.add(new_entry)
        db.session.commit()

You can also check whether header item exist in in columns set:

columns = [column.key for column in Example.__table__.columns]
kwargs = {header_item: value for header_item, value in zip(header, i) if column in columns}

edited Feb 8, 2022 at 16:11

answered Feb 8, 2022 at 16:00

jorzel

1,3461 gold badge8 silver badges13 bronze badges

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

1 Comment

snakecharmerb Over a year ago

You could use a csv.DictReader and skip the mapping step.

Collectives™ on Stack Overflow

Python: flask-sqlalchemy fill table using csv as source without listing every column of table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related