Why is mysql not accepting this datetime insert?-CodePudding

I am getting this error message:OperationalError: (1292, "Incorrect datetime value: '19/06/2020 13:15' for column test1.posts.posted_at at row 1") I am importing this code:

with connection.cursor() as cur:
    q = """
            INSERT INTO posts(postid, posted_at, num_comments, score, selftext, title, total_awards_received, upvote_ratio, id, subredditid) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
    """
    
    cur.executemany(q,postsdata)
    connection.commit()

into this table:

 q = """
        CREATE TABLE posts(
            postid INT NOT NULL,
            posted_at DATETIME, 
            num_comments INT,
            score INT,
            selftext VARCHAR(10000),
            title VARCHAR(10000),
            total_awards_received INT,
            upvote_ratio DOUBLE,
            id INT,
            subredditid INT,
            PRIMARY KEY  (postid),
            FOREIGN KEY (id) REFERENCES users(id),
            FOREIGN KEY (subredditid) REFERENCES subreddits(subredditid)

        );
    """
    cur.execute(q)
    connection.commit()

The value of the first row of the dataset (where the error is supposedly happening):

posted_at                                                 19/06/2020 13:15

Can anyone help? This has been driving me up the wall and I can't figure out why mysql will not accept this?

EDIT- MORE INFO This is what my dataframe looks like:

postsdf = df[['postid', 'posted_at', 'num_comments', 'score', 'selftext', 'title', 'total_awards_received', 'upvote_ratio', 'id']]
    postsdf["subredditid"] = subredditsdfdrop["subredditid"]
    postsdf["subredditid"] = postsdf["subredditid"].fillna(value = 0)
    postsdata = postsdf.values.tolist()

I then pass the 'postsdata' list into the insert statement at the top. I have been informed that the format of 'posted_at' value should match YYYY-MM-DD hh:mm:ss[.fraction]. What would be the line of code to do this?

CodePudding user response：

The problem is that MySQL DATETIME standard format is YYYY-MM-DD hh:mm:ss[.fraction], while you're using the following format: DD/MM/YYYY hh:mm. In order to fix this problem you should either:

change your input data to reflect that standard
change your field type to VARCHAR and apply the STR_TO_DATE function to transform it into the standard DATETIME format in your queries (if you need it)

The former choice is preferrable to the second one, though if your input data is forced to have the original format, there's always a way.