Insert csv data on certain range of date in python and mysql

Question

I just want only a certain date to be insert into my table. I have this code.

When I used only this code, all of data in csv inserted

for row in readers:
     query4 = """INSERT INTO csv(
                 csv_id, Mchn, EnNO, Mode1,
                 IODMd, Date_Time)
                 VALUES(%s,%s,%s,%s,%s,%r)
                 """

     db.session.execute(query4 % (row[0], row[1], row[2], row[4], row[5], row[6],))
db.session.commit()

but I want only a certain date to be insert so I try this add WHERE on it. Like this

for row in readers:
    query4 = """INSERT INTO csv(
                csv_id, Mchn, EnNO, Mode1,
                IODMd, Date_Time)
                VALUES(%s,%s,%s,%s,%s,%r)
                WHERE Date_Time >= (select c_start_date from c_temp c where c.id = :id)
                AND Date_Time <= (select DATE_ADD(c_end_date,interval 1 day) from c_temp c where c.id = :id)"""

     db.session.execute(query4 % (row[0], row[1], row[2], row[4], row[5], row[6],))
db.session.commit()

But when I try it, it has an error and stop it. Thank you.

python is not sql. So, if your creating insert statements via python, you have to filter the data in python. — monkut
– monkut, Commented Oct 27, 2014 at 1:36
also, query4 is only executing the last row, and not each row. I would probably generate the SQL insert statements and put the created strings in a list, then create a single transaction to INSERT. — monkut
– monkut, Commented Oct 27, 2014 at 1:37

monkut · Accepted Answer · 2014-10-28 02:24:48Z

1

If you want to filter the data to insert based on the datetime of the data, you should be evaluating the data before you create the INSERT statement.

import datetime

start_date = datetime.datetime(<value from db>)
db_end_date = datetime.datetime(<value from db>)
end_date = db_end_date + datetime.timedelta(days=1)

for row in readers:
     csvid, mchn, enno, model, iodmd, dt = row[:7]
     # parse incoming datetime value into a python datetime object
     # not sure what your format is here
     parsed_date = datetime.datetime.strptime(dt, "%Y-%m-%D")
     if start_date <= parsed_date <= end_date:
         query4 = """INSERT INTO csv(
                     csv_id, Mchn, EnNO, Mode1,
                     IODMd, Date_Time)
                     VALUES(%s,%s,%s,%s,%s,%r)
                  """

         db.session.execute(query4 % (csvid, mchn, enno, model, iodmd, dt,))
db.session.commit()

answered Oct 28, 2014 at 2:24

monkut

44.1k26 gold badges133 silver badges161 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

akbsmile Over a year ago

Hi I tried it but it gives me a syntax error. Especially when I assign start_date = select c_start_date from c_temp c where c.id = :id

monkut Over a year ago

yes, you're going to need to figure out how to query the db and get the value.

Collectives™ on Stack Overflow

Insert csv data on certain range of date in python and mysql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related