How do I insert all the elements in a Python list into individual rows of an SQLAlchemy column?

Question

Trying to insert the elements of exp_data into individual rows of SQLAlchemy column exp_list inside the Expiration table programmatically using Python within the FastAPI framework:

exp_data = ['2020-08-27', '2020-09-03', '2020-09-10', '2020-09-17']
for i in exp_data:
   exp = Expiration(symbol=stock.symbol, exp_list=exp_data)

db.add_all([stock, exp])
db.commit() 

And my SQLAlchemy model is:

from sqlalchemy import Boolean, Column, ForeignKey, Numeric, Integer, String, Date, Float
from sqlalchemy.orm import relationship, backref

from database import Base

class Stock(Base):
    __tablename__ = "stocks"

    id = Column(Integer, primary_key=True, index=True)
    symbol = Column(String)
    price = Column(Float)

class Expiration(Base):
    __tablename__ = "expirations"

    id = Column(Integer, primary_key=True, index=True)
    symbol = Column(String, ForeignKey(Stock.symbol), index=True)
    exp_list = Column(String)

I put exp_data in the code to show the size - I am scraping this data, and I want the program to automatically insert the data into the database by simply posting an HTTP Request of the stock ticker to the server. Been receiving this error:

sqlalchemy.exc.InterfaceError: (sqlite3.InterfaceError) Error binding parameter 1 - probably unsupported type.
[SQL: INSERT INTO expirations (symbol, exp_list) VALUES (?, ?)]
[parameters: ('FB', ['2020-08-27', '2020-09-03', '2020-09-10', '2020-09-17'])]

I think the issue is with the for loop in the first code block - I have been trying to find strategies to iterate through each value, such as '2020-08-27', and insert it into individual rows. Any help is much appreciated - thank you!

Answer 1

You probably want something like

exp_data = ['2020-08-27', '2020-09-03', '2020-09-10', '2020-09-17']
exps = []
for date in exp_data:
   exps.append(Expiration(symbol=stock.symbol, exp_list=date))

instances = [stock]
instances.extend(exps)

db.add_all(instances)
db.commit() 

that is, looping over the list of dates and creating an Expiration instance for each.

Answer 2

Your column :exp_list defined as string/text column

exp_list = Column(String)

And then you pass a python list to it

exp_data = ['2020-08-27', '2020-09-03', '2020-09-10', '2020-09-17']
exp = Expiration(symbol=stock.symbol, exp_list=exp_data)

You should pass the list as string

exp = Expiration(symbol=stock.symbol, exp_list=','.join(exp_data))