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!
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.
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))