I am working on a scraping project which stores entries in 3 lists and then exports them into a dataframe and then into an excel file.
I want to transpose all into a database using sqlalchemy or mysql.
The update is made once each list has 10 entries gathered.
I need it to replace/update the value if the ean value it's already in the database.
It adds the same value even if it already exists, the primary key is auto incremented and it's not listed here.
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine("mysql+mysqlconnector://root:password@localhost:3306/scrapex",
echo=True)
Base = declarative_base()
class Project(Base):
__tablename__ = 'scrape'
ean = Column(Integer, primary_key=True)
p1 = Column(Integer)
v1 = Column(String(length=50))
def __repr__(self):
return "<Project(ean'{0}', p1='{1}', v1='{2}'>".format(
self.ean, self.p1)
Base.metadata.create_all(engine)
session_maker = sessionmaker()
session_maker.configure(bind=engine)
session = session_maker()
ean =["4234234", "53532532", "4234234", "53532532", "4234234", "53532532"]
p1=["100", "233", "100", "233", "100.2", "233.3"]
v1=["abc", "bca", "lalala", "xxxxx", "vendor", "bucketr"]
for x, y, z in zip(ean, p1, v1):
organize_closet_project = Project(ean=x, p1 =y, v1 =z)
session.add(organize_closet_project)
session.commit()
our_project = session.query(Project).filter_by(ean='ean1').first()
print(our_project)
session.close()
How can I interrogate the db in order to replace the values if the "ean" already exists in the database?
