-1

I am trying to insert stock market csv data that I downloaded from Yahoo finance into a mysql table named 'TEST' that is in a database named 'stocks' but I am getting an error code from python:

InternalError: (1292, "incorrect date value: 'Date for column 'date at row 1")

the data that I am trying to insert has hundreads of rows that look something like this:

1995-03-31,0.141150,0.141150,0.141150,0.141150,0.105375,10000

the table that i am trying to insert this data into contains the following columns:

date DATE NOT NULL PRIMARY KEY,
open DECIMAL(10,6),
high DECIMAL(10,6),
low DECIMAL(10,6),
close DECIMAL(10,6),
adj_close DECIMAL(10,6),
volume INT,

this is the python code that i have used to insert the data into the table 

with open('/home/matt/Desktop/python_projects/csv_files/CH8_SG.csv', 
'r') as f:
     reader = csv.reader(f)
     data = next(reader)
     query = 'insert into TEST values (%s,%s,%s,%s,%s, %s, %s)'
     query = query.format(','.split('%s' * len(data)))
     cursor = connection.cursor()
     cursor.execute(query, data)
     for data in reader:
        cursor.execute(query, data)
     cursor.commit()

when i run the code pictured above I get the following error

InternalError: (1292, "incorrect date value: 'Date for column 'date at row 1")

I really think that I am close but I do not know what is going on with that error. Can anyone help me?

Rick James
  • 135,179
  • 13
  • 127
  • 222
Mattytripps
  • 57
  • 7
  • I originally thought you went down the pitfall of using string formatting for your queries, then realised I didn't understand what `query = query.format(','.split('%s' * len(data)))` did. It doesn't seem to do anything on testing. You then define `cursor = connection.cursor()` a second time. You also use `cursor.commit()` but I think it should be `connection.commit()`. – roganjosh Feb 08 '18 at 00:03
  • After `data = next(reader)`, do `print(data)`. What do you get? Also, your error could be due to blank lines. It's not possible to tell from your question. – roganjosh Feb 08 '18 at 00:07
  • print(data) after data = next(reader) did not return anything. i just got the same error message again – Mattytripps Feb 08 '18 at 01:03
  • i changed the cursor.commit to connection.commit() and i still got the same error message. – Mattytripps Feb 08 '18 at 01:04
  • Well, check out the date in the given row. – Shadow Feb 08 '18 at 02:02
  • I will check out the date tommorow. Thank you – Mattytripps Feb 08 '18 at 04:52
  • This is just a hunch. But im wondering if python is not putting quotes around the date and maybe that is why it is getting rejected. Im very new to python and mysql. I put this code together from another forumn. Do you know how i could get quotes around the date? – Mattytripps Feb 08 '18 at 04:54
  • Only 4 digits to left of decimal?? – Rick James Sep 30 '18 at 04:54

1 Answers1

-1

When you write the query to insert date time in Python, you should use convert operation in MySQL. In your case: query = 'insert into TEST values (%s,%s,%s,%s,%s, %s, %s)' => 'insert into TEST values (STR_TO_DATE(%s),%s,%s,%s,%s, %s, %s)' And make sure that there are no duplicate in "date" field.

Kien.N
  • 1
  • 5
  • I tried changing the query = line to each of the following: query = 'insert into TEST values (STR_TO_DATE(%s),%s,%s,%s,%s, %s, %s)' then i tried: query = 'insert into TEST values ((STR_TO_DATE(%s)),%s,%s,%s,%s, %s, %s)' but still no dice. Any ideas? Im so new to python that im not much of a help to myself yet. I keep getting this error message "InternalError: (1582, "Incorrect parameter count in the call to native function 'STR_TO_DATE'")" – Mattytripps Feb 08 '18 at 22:12
  • Try with this: "insert into TEST values (STR_TO_DATE(%s, '%Y-%m-%d'),%s,%s,%s,%s, %s, %s)" It should specify the format of date string in "STR_TO_DATE" function. – Kien.N Feb 09 '18 at 11:19
  • reader = csv.reader(f) data = next(reader) query = "insert into TEST values((datetime.datetime.striptime((%s),%s,%s,%s,%s, %s, %s)" query = query.format(','.split('%s' * len(data))) cursor = connection.cursor() cursor.execute(query, data) for data in reader: cursor.execute(query, data) cursor.commit() – Mattytripps Feb 09 '18 at 23:48
  • thank you for all the help that you are giving me. Python was giving me issues with the Y character so I messed around with the code and took a big step forward with the code in the above comment. But now im getting this error message: "(1064, "You have an error in your SQL syntax;... check for for the right syntax to use near '(('Date'),'Open','High','Low','Close', 'Adj Close', 'Volume')' at line 1") – Mattytripps Feb 09 '18 at 23:51
  • to me, it is obvious that the error is the way that python is formatting the date and the numbers that are to be inserted into the mysql table. Python is probably leaving quotes off the date or adding quotes to the numbers and it is messing the insert statement up. I think at this point i have to figure out how to format the output in the way the mysql wants it – Mattytripps Feb 09 '18 at 23:52
  • something else that is odd, is all i get for output is that error message that i posted. I have tried printing data and query with print(query) or data. I have put the print message in several different spots throughout the program both in the loop and out. i have even tried print('bla bla bla') and even this resulted in the same error message. – Mattytripps Feb 10 '18 at 00:01
  • Hello, when I saw your query, it look like you convert all the data to date time "((datetime.datetime.striptime((%s),%s,%s,%s,%s, %s, %s)". So, it is absolutely wrong because only first parameter is Date type. When I created the same format of your table and insert the date time type, it works properly with STR_TO_DATE(%s, '%Y-%m-%d'). With other columns, you can try to switch data type by read the text file and convert to float, interger and change the insert query to STR_TO_DATE(%s, '%Y-%m-%d'),%f,%f,%f,%f, %f, %d). The problem is only the data type, you can try to see with type() of python. – Kien.N Feb 12 '18 at 08:49