I've made separate simple example just to check what method would work with date in my case and didn't find any solution. we have: db table: code CSV file example_v2.csv: Trace Could you please assist in even find the documentation fit my case or indicate the direction. I've already tried a lot of methods such as: etc. Answer thanks to

How to put date(parsed from csv) in Python to mysql?

I’ve made separate simple example just to check what method would work with date in my case and didn’t find any solution.

we have:

db table:

CREATE TABLE `main_test` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `DATESTAMP` datetime DEFAULT NULL,
  `col_1` int DEFAULT NULL,
  `col_2` varchar(20) DEFAULT NULL,
  `col_3` varchar(20) DEFAULT NULL,
  `col_4` varchar(20) DEFAULT NULL,
  `col_5` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

code

import csv
import pymysql

conn = pymysql.connect(
    user="test_user",
    password="u4534@F_18bbb",
    host="localhost",
    database="example_sch",
    port=3306,
    )

with open("example_v2.csv", "r", encoding="cp1251") as f:
    reader = csv.DictReader(f, delimiter=",")

    for line in reader:
        cursor = conn.cursor()
        sql_insert = "INSERT INTO `main_test` (`DATESTAMP`,`col_1`,`col_2`,`col_3`,`col_4`,`col_5`) VALUES (%s,%s,%s,%s,%s,%s)"
        cursor.execute(sql_insert,(line["DATESTAMP"],line["csv_col1"], line["csv_col2"], line["csv_col3"], line["csv_col4"], line["csv_col5"]))
        print(line)


    conn.commit()
    cursor.close()
    conn.close()

CSV file example_v2.csv:

DATESTAMP,csv_col1,csv_col2,csv_col3,csv_col4,csv_col5,
"22.04.2022 0:00:00","15347","random_symb_1","1abc","1def","1AAAAAAAAAA",
"23.04.2022 0:00:00","25347","random_symb_2","2abc","2def","2AAAAAAAAAA",
"24.04.2022 0:00:00","3234","random_symb_3","3abc","3def","3AAAAAAAAAA",
"25.04.2022 0:00:00","45677","random_symb_4","4abc","4def","4AAAAAAAAAA",
"26.04.2022 0:00:00","55675","random_symb_5","5abc","5def","5AAAAAAAAAA",

Trace

Traceback (most recent call last):
  File "Project_1/example_db_work_v2.py", line 18, in <module>
    cursor.execute(sql_insert,(line["DATESTAMP"],line["csv_col1"], line["csv_col2"], line["csv_col3"], line["csv_col4"], line["csv_col5"]))
  File "/Library/Python/3.8/site-packages/pymysql/cursors.py", line 148, in execute
    result = self._query(query)
  File "/Library/Python/3.8/site-packages/pymysql/cursors.py", line 310, in _query
    conn.query(q)
  File "/Library/Python/3.8/site-packages/pymysql/connections.py", line 548, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/Library/Python/3.8/site-packages/pymysql/connections.py", line 775, in _read_query_result
    result.read()
  File "/Library/Python/3.8/site-packages/pymysql/connections.py", line 1156, in read
    first_packet = self.connection._read_packet()
  File "/Library/Python/3.8/site-packages/pymysql/connections.py", line 725, in _read_packet
    packet.raise_for_error()
  File "/Library/Python/3.8/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/Library/Python/3.8/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.OperationalError: (1292, "Incorrect datetime value: '22.04.2022 0:00:00' for column 'DATESTAMP' at row 1")

Could you please assist in even find the documentation fit my case or indicate the direction. I’ve already tried a lot of methods such as:

str(d.strfdate('%Y-%m-%d %H:%M:%S'))

etc.

Answer

thanks to @buran and @Dean-Van-Greunen

let me show you my version of answer as a first but not last step, thats not elegant at all, but it works.

        date = line["DATESTAMP"][6:-8] + "-" + line["DATESTAMP"][3:-13] + "-" + line["DATESTAMP"][:-16]
        time = line["DATESTAMP"][11:6] + "-" + line["DATESTAMP"][13:-3] + "-" + line["DATESTAMP"][16:]
        date_time = date + time

let me show also more elegant version, thanks to @baron for focusing my attention on the format issue in comments

def date_convert(date_to_convert):
    return datetime.strptime(date_to_convert, '%d.%m.%Y %H:%M:%S').strftime('%Y-%m-%d %H:%M:%S')

than use it this way:

date_time = date_convert(line["DATESTAMP"])

cursor.execute(sql_insert,(date_time, line["csv_col1"], line["csv_col2"], line["csv_col3"], line["csv_col4"], line["csv_col5"]))

Advertisement

Answer