Replace a large amount of data at once with SQLAlchemy?

Question

I need to update a large amount of data daily (large means >3MB). I thought to store it as JSON, but SQLAlchemy doesn't support converting from JSON as far as I found. So now I'm trying to do it with Pickle. At the moment I'm storing every product I have in a huge Pickle file, to load it back in later and commit them. However, I keep getting errors saying my product class is not mapped, and I'm not sure what it means or how to fix it. Everything I came across while Googling didn't resemble my code in the slightest. Here is my product class:

class Product:
    id = ""
    name = ""
    store_name = ""
    brand = ""
    price = ""
    amount = ""
    info = ""
    image = ""

And here is my Pickle / Database code:

def loadall():
    with open('products.txt', mode='rb') as products_txt:
        while True:
            try:
                yield pickle.load(products_txt)
            except EOFError:
                break

Session = sessionmaker(bind=db)
    session = Session()
    products = loadall()

    with db.connect() as conn:
        session.add_all(products)
        session.commit()

(made after reading Saving and loading multiple objects in pickle file?)

I understand that this is not your question, but SqlAlchemy clearly supports [JSON as a data type](https://docs.sqlalchemy.org/en/14/core/type_basics.html#sqlalchemy.types.JSON) Now, sqlalchemy goal is not converting from JSON or any other data format; but this can be done easily with python standard library [json module](https://docs.python.org/3/library/json.html), which I would prefer using instead of pickle. — van, May 20 '21 at 12:27
If it helps me solve my problem I'll gladly try it out! I've been looking and fiddling with the JSON implementation you linked, but I can't get it to work. They use table.insert(), but I don't have a table locally, I just connect to it from my desktop. Do you perchance know how I could fix this? — Jasper Wijnhoven, May 20 '21 at 14:12
if you are using ORM, you can have table by accessing `Product.__table__`. But you do not need to. But if you wanted to use JSON column on the database, design of your table will be different: you will have JSON column which will store some attributes as JSON. I do not suggest you do that for well defined attributes like you have. I only wanted to point out that sqlalchemy can handle JSON. — van, May 22 '21 at 02:59

score 1 · Accepted Answer · answered May 22 '21 at 03:17

Below should give you an idea (i limited test data to just 2 columns):

test.py :

#!/usr/bin/env python3

import json

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.orm import Session, as_declarative, registry

## configuration
engine = create_engine("sqlite+pysqlite:///:memory:", echo=True, future=True)
mapper_registry = registry()


@as_declarative()
class Base(object):
    pass


class Product(Base):
    __tablename__ = "product"
    id = Column("id", Integer, primary_key=True)
    name = Column(String)
    info = Column(String)


def _main():
    with Session(engine) as session:
        Base.metadata.drop_all(engine)
        Base.metadata.create_all(engine)

        ## test data
        objects = [
            Product(name="no-info"),
            Product(name="red-color", info="large RED"),
        ]

        session.add_all(objects)
        session.commit()
        session.expunge_all()

        ## test data: from JSON
        # product_list = load_all_test()  # load from test code
        product_list = load_all_file()  # load from the file
        print(product_list)

        # create Product() instances from JSON
        products = [Product(**kw) for kw in product_list]
        session.add_all(products)
        session.commit()


def load_all_test():
    test_json_content = """
[
    { "name": "json-product-1", "info": "some info from json-1" },
    { "name": "json-product-2", "info": "some info from json-2" }
]
        """

    product_list = json.loads(test_json_content)
    return product_list


def load_all_file():
    with open("myjsonfile.json") as fh:
        product_list = json.load(fh)
    return product_list


_main()

I had originally fixed this by using a text file to store a whole query, however this is much more elegant. Thanks! — Jasper Wijnhoven, May 24 '21 at 13:35

Replace a large amount of data at once with SQLAlchemy?

1 Answers1