MySQL Python query syntax mistake on INSERT when it seems to be satisfying?

Question

I am delevoping sample database for a Taxi service and trying to fill the PhoneCall table with some data, using Python and mysql.connector library. The PhoneCall table columns are:

• idCall, int(11), PRIMARY KEY
• Operator_idOperator, int(11), NOT NULL, FOREIGN KEY
• Client_idClient, int(11), NOT NULL, FOREIGN KEY
• datetimeValue, datetime
• Duration, int(11)

I declare an empty list and generate some data to fill the table:

• query = "INSERT INTO TaxiTest.PhoneCall (idCall, Operator_idOperator, Client_idClient, datetimeValue, Duration) VALUES (%s, %s, %s, %s, %s)"

After that, I generate data

data = []
for i in range(1500):
    idCall = i + 1
    (Operator_idOperator, Client_idClient) = (20, 21) # Sample ints
    datetime_value = datetime.now()
    duration = 60
    t_tuple = (idCall, Operator_idOperator, Client_idClient, datetime_value, duration)
    data.append(t_tuple)

Next, I am trying to INSERT data using cursor.executemany(query, data) syntax:

cnx = mysql.connector.connect(**config)
cnx.autocommit = True
cursor = cnx.cursor()
try:
    cursor.executemany(query, data)
    log = cursor.fetchall()
    print("Result: " + log)
except mysql.connector.Error as err:
    print(err.msg)

So, the result is:

• Data[0]: (1, 46, 2521, datetime.datetime(2018, 9, 17, 22, 44, 20, 159722), 55)
• You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TaxiTest.PhoneCall' at line 1

Although I have already filled about 5 tables in this database, I don't recognize any mistakes in this INSERT statement.

It seems that the only solution is to write all 1500 entries of data into output.txt file and execute that using MySQL Workbench, because there is no error while inserting with MySQL Workbench, but this is not appreciated.

P.S. This is my first question on StackOverflow. Waited this moment for so long to get some 'coins' for commenting and rating other questions and answers.

Answer 1

Without trying it, I would say it is the datetime object.

You can try this code:

from datetime import datetime
datetime_value = datetime.now()
formatted_date = datetime_value.strftime('%Y-%m-%d %H:%M:%S')