CODEWITHSUNDEEP
pythonmysqltimestampsql-timestamp
MaybeWeAreAllRobots
MaybeWeAreAllRobots

Reputation: 1205

How to use mysql's CURRENT_TIMESTAMP from python?

I want to use the MySQLs function CURRENT_TIMESTAMP from python code, but can't figure out how to pass this in my INSERT or UPDATE queries.

    try:
    mycursor.execute(
        "CREATE TABLE IF NOT EXISTS test (pkID bigint unsigned NOT NULL AUTO_INCREMENT , tsTest TIMESTAMP, PRIMARY KEY (pkID));")
    sqlInsert = "INSERT INTO test (tsTest) VALUES (%s);"
    valsInsert = ['CURRENT_TIMESTAMP', ]
    mycursor.execute(sqlInsert, valsInsert)
except mysql.connector.Error as err:
    print("MySQL error: {}".format(err))

I've tried: 'CURRENT_TIMESTAMP', 'CURRENT_TIMESTAMP()' and even mysql.connector.Timestamp MySQL just throws: Incorrect datetime value:

Yes, I know I can use python's Now() function, but this might be problematic if the code is run on a different server than the DB. So, I'd prefer to use the mySQL function for all timestamps. TIA!

Upvotes: 0

Views: 282

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562931

Query parameters are always treated as scalar values, not expressions or keywords. So it's executed as if you had run this query:

INSERT INTO test (tsTest) VALUES ('CURRENT_TIMESTAMP')

This is like a string literal, not the CURRENT_TIMESTAMP function. MySQL may attempt to cast the string value to whatever the column's data type is, but this is not going to work in the case of a timestamp or datetime.

If you want to insert an expression, just put the expression your query, not using a parameter.

sqlInsert = "INSERT INTO test (tsTest) VALUES (CURRENT_TIMESTAMP);"

Upvotes: 0

Related Questions