Running sql statement with datetime format gives error Warning: (1292, "Incorrect date value: '2019/04/29:12:28:18' for column 'dateAdded' at row 1") #792

kabads · 2019-04-29T11:39:09Z

SQL to recreate the database:

def insertDatabaseTable():
"""This function creates a Table that will hold the media in the database."""
try:
    with dbConnection.cursor() as cursor:
        # Create a new record
        sql = ("CREATE TABLE `media` (`id` int(11) NOT NULL AUTO_INCREMENT,\n"
               "            `name` varchar(255) COLLATE utf8_bin NOT NULL,\n"
               "            `platform` varchar(255) COLLATE utf8_bin NOT NULL,\n"
               "            `dateAdded` date NOT NULL,\n"
               "            `type` varchar(255),\n"
               "            PRIMARY KEY (`id`)\n"
               "            ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin\n"
               "            AUTO_INCREMENT=1 ;")
        cursor.execute(sql)

    # connection is not autocommit by default. So you must commit to save
    # your changes.
    dbConnection.commit()
except:
    print("Couldn't connect to database to create the table.")

Insert a new row:

def enterMusic(tpe):
    name = input('Name of the music: ')
    platform = input('Platform of the music: ')
    a = mediatypes.music.Music(name, platform, tpe, dateAdded())
    music = {a: tpe}
    media.append(music)
    enterMediaToDB(a, tpe)


def enterMediaToDB(mediaObj, tpe):
    if dbConnection:
        try:
            with dbConnection.cursor() as cursor:
                sql = 'insert into media (name, platform, dateAdded, type) values ("{}", "{}", "{}", "{}");'.format(mediaObj.name, mediaObj.platform, mediaObj.dateAdded, tpe)
                print(sql)
                cursor.execute(sql)
            dbConnection.commit()
        except:
            print("Couldn't write to database.")
        try:
            # Execute SQL
            print("Execute")
        except:
            print("Cannot write to database.")
            return

So, when I run this, I get the sql statement:
insert into media (name, platform, dateadded, type) values ("games", "steam", "2019/04/29:12:28:18", "Game");

which works in the mysql command line client, but gives the error:

/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pymysql/cursors.py:329: Warning: (1292, "Incorrect date value: '2019/04/29:12:28:18' for column 'dateAdded' at row 1")
self._do_get_result()

when run from python.

There's a possibly related issue at:
https://stackoverflow.com/questions/55073124/pymysql-select-maxtimestamp-results-in-incorrect-datetime-value-0000-00-00

The text was updated successfully, but these errors were encountered:

methane · 2019-04-29T11:45:45Z

which works in the mysql command line client,

Please paste it too.

I never tried ":" between date and time. I didn't know it is allowed by MySQL or not.

kabads · 2019-04-29T12:43:54Z

Sure
insert into media (name, platform, dateadded, type) values ("games", "steam", "2019/04/29:12:28:18", "Game");

methane · 2019-04-30T03:49:59Z

Please paste it's output too. The output contains "Warning: 0"?

Note that PyMySQL only "warn", not raise an exception.

kabads · 2019-04-30T07:35:55Z

I'm raising an exception in my code - this is what I get:

insert into media (name, platform, dateAdded, type) values ("media", "mp3", "2019/04/30:08:35:03", "Music");
/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pymysql/cursors.py:329: Warning: (1292, "Incorrect date value: '2019/04/30:08:35:03' for column 'dateAdded' at row 1")
self._do_get_result()

That's all I get.

methane · 2019-04-30T08:08:00Z

It is warning. It doesn't stop your application. It only show warning.

methane · 2019-04-30T08:13:42Z

And I removed the show warning from MySQL in #774, because:

people doesn't understand warnings and file an issue here, like this issue. It's annoying.
Some warnings in MySQL seems too strict sometime. (e.g. "DROP TABLE IF EXISTS" warns when table is not exist)

kabads · 2019-04-30T11:51:22Z

Sure - it's weird how MySQL warns through PyMySQL and not execute, but executes with no warning or error on the mysql command line. If you're annoyed, then apologies. It's very difficult to work out what is going on between the application level and the database. However, I think this isn't the place to air feelings, as there is nothing I can do about that.

methane · 2019-04-30T12:41:48Z

but executes with no warning or error on the mysql command line.

Really? You only paste what you input in mysql cmdline. You didn't paste what mysql shows.
I think you just don't know how to read MySQL warning.

methane · 2019-04-30T12:43:12Z

as there is nothing I can do about that.

No. PyMySQL just translate MySQL warning to Python warning.
You can ignore the warning (don't ask me how, google is your friend), or you can fix your query.

jmehnle · 2020-07-02T19:49:50Z

@methane wrote:

And I removed the show warning from MySQL in #774, because:

people doesn't understand warnings and file an issue here, like this issue. It's annoying.

Some warnings in MySQL seems too strict sometime. (e.g. "DROP TABLE IF EXISTS" warns when table is not exist)

I applaud this move, but alas, 0.9.3 is still the most recent version of PyMySQL on PyPI and has this mis-feature included. Are you planning on making another release soon?

Diggsey · 2021-06-21T14:58:07Z

@methane I think it's a shame this feature was removed, but from this issue the reasoning is clear... Would you accept a PR to re-add the functionality but make it opt-in?

methane · 2021-06-22T00:49:51Z

I don't want to ressurect it. It was too hard to maintain. Especially, it caused many bugs for multiple resultset case.

You can use Connection.show_warnings() to fetch warnings.
On the other hand, warning_count is not accessible for now. I am considering to add Connection.warning_count in Connection._reqd_query_result().

methane closed this as completed Apr 30, 2019

methane mentioned this issue Sep 9, 2020

When insert the data, the None type will be treat as empty string instead of null #891

Closed

github-actions bot locked as resolved and limited conversation to collaborators Jun 23, 2022

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Running sql statement with datetime format gives error Warning: (1292, "Incorrect date value: '2019/04/29:12:28:18' for column 'dateAdded' at row 1") #792

Running sql statement with datetime format gives error Warning: (1292, "Incorrect date value: '2019/04/29:12:28:18' for column 'dateAdded' at row 1") #792

kabads commented Apr 29, 2019 •

edited

Loading

methane commented Apr 29, 2019

kabads commented Apr 29, 2019 •

edited

Loading

methane commented Apr 30, 2019

kabads commented Apr 30, 2019 •

edited

Loading

methane commented Apr 30, 2019

methane commented Apr 30, 2019

kabads commented Apr 30, 2019 •

edited

Loading

methane commented Apr 30, 2019

methane commented Apr 30, 2019

jmehnle commented Jul 2, 2020

Diggsey commented Jun 21, 2021

methane commented Jun 22, 2021

Running sql statement with datetime format gives error Warning: (1292, "Incorrect date value: '2019/04/29:12:28:18' for column 'dateAdded' at row 1") #792

Running sql statement with datetime format gives error Warning: (1292, "Incorrect date value: '2019/04/29:12:28:18' for column 'dateAdded' at row 1") #792

Comments

kabads commented Apr 29, 2019 • edited Loading

methane commented Apr 29, 2019

kabads commented Apr 29, 2019 • edited Loading

methane commented Apr 30, 2019

kabads commented Apr 30, 2019 • edited Loading

methane commented Apr 30, 2019

methane commented Apr 30, 2019

kabads commented Apr 30, 2019 • edited Loading

methane commented Apr 30, 2019

methane commented Apr 30, 2019

jmehnle commented Jul 2, 2020

Diggsey commented Jun 21, 2021

methane commented Jun 22, 2021

kabads commented Apr 29, 2019 •

edited

Loading

kabads commented Apr 29, 2019 •

edited

Loading

kabads commented Apr 30, 2019 •

edited

Loading

kabads commented Apr 30, 2019 •

edited

Loading