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:

1. 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

2. 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()

3. 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",

4. 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.

CodePudding user response：

try something within these lines:

import csv
from datetime import datetime

# skip some code for brevity
cursor = conn.cursor()
with open("example_v2.csv", "r", encoding="cp1251") as f:
    reader = csv.reader(f, delimiter=",")

    for line in reader:
        line[0] = datetime.strptime(line[0], '%d.%m.%Y %H:%M:%S').strftime('%Y-%m-%d %H:%M:%S')
        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)

Note, the code could use exectemany, or read/pass dict if header in the csv file is same as field names in the database table, etc.

CodePudding user response：

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"]))