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

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.

CodePudding user response：

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}