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