Welcome to the Treehouse Community
Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.
Start your free trialAsher Orr
Python Development Techdegree Graduate 9,409 PointsWhile importing csv entries to a database: How can I update the db IF a duplicate csv entry is found?
Hi everyone! My project has a function called add_csv(). It adds entries in a CSV file to a database.
def add_csv():
with open('inventory.csv') as csvfile:
data = csv.reader(csvfile)
next(data)
for row in data:
product_in_db = session.query(Product).filter(
Product.product_name == row[0]).one_or_none()
if product_in_db == None:
product_name = row[0]
product_price = clean_price(row[1])
product_quantity = clean_quantity(row[2])
date_updated = clean_date_updated(row[3])
new_product = Product(product_name=product_name, product_price=product_price,
product_quantity=product_quantity, date_updated=date_updated)
session.add(new_product)
#Note: This is where the code begins dealing with a duplicate value.
elif product_in_db == True:
if product_in_db.date_updated < new_product.date_updated:
session.add(new_product)
session.delete(product_in_db)
elif product_in_db.date_updated > new_product.date_updated:
pass
session.commit()
As you can see, I want to check if a duplicate product_name value exists in the database.
If there IS a duplicate value, the function should check which date_updated value is the most recent. Only that entry should exist in the database.
For example, let's say I have this entry on line 10 of the csv file:
Wine - Chateau Bonnet,$7.50,66,3/10/2018
And then on line 20, this duplicate exists (same product_name value.)
Wine - Chateau Bonnet,$7.41,54,3/10/2020
Since the line 20 product is the most recently updated entry, I would like for my database entry to reflect that entry's information- not the information from 2018.
I'm having trouble getting this to work, though. If there are 2 duplicates, only the one which comes first in the CSV gets saved to the database. It doesn't matter what the date is!
For example, let's say this is line 19 and 20 in the CSV:
Wine - Chateau Bonnet,$7.50,66,3/10/2018
Wine - Chateau Bonnet,$7.41,54,3/10/2020
When I check my database, I see the Wine entry from 2018 (but not the 2020 one.)
If I switch the order in the CSV, though, then re-create my database, I only see the wine entry from 2020 (not the 2018 one - that one isn't saved to the database.)
Can anyone help me understand why this is happening, and what I can do to remedy this problem?
Thank you for reading!
Note: If it helps, here is my database model:
from sqlalchemy import (create_engine, Column,
Integer, String, DateTime)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
engine = create_engine('sqlite:///inventory.db', echo=False)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()
class Product(Base):
__tablename__ = "products"
product_id = Column(Integer, primary_key=True)
product_name = Column("Product Name", String)
product_price = Column("Product Price", Integer)
product_quantity = Column("Product Quantity", Integer)
date_updated = Column("Date Updated", DateTime)
def __repr__(self):
return f'Name: {self.product_name}, Quantity: {self.product_quantity}, Price: {self.product_price}, Date Updated: {self.date_updated}'
if __name__ == "__main__":
Base.metadata.create_all(engine)
1 Answer
Steven Parker
231,269 PointsInstead of elif product_in_db == True:
, try using just a plain else:
. I'm guessing that when an item is found, the value is going to be the actual item and not be equal to True
.
Asher Orr
Python Development Techdegree Graduate 9,409 PointsAsher Orr
Python Development Techdegree Graduate 9,409 PointsHey Steven, thanks for commenting! That helped me solve the problem. I needed to use an else, but there was one other issue:
I noticed that my new_product variable, in the first iteration of my code, was unbound. I updated my code to actually assign it a value:
That and changing the
elif product_in_db == True:
solved the problem. Appreciate you as always! Thanks again for responding.