Forums

Hi,

I have this (after insertion) in my SQL table :

(1, datetime.datetime(2019, 5, 17, 17, 16, 14), 
(2, datetime.datetime(2019, 5, 17, 17, 16, 47), 
(3, datetime.datetime(2019, 5, 17, 17, 17, 18)

Why datetime.datetime is in my table and not only date !!! But when I print "now" variable i have only the date without dateime.datetime

But look my code (creation of the table)

cursor.execute("""
CREATE TABLE IF NOT EXISTS TableName (
    id INT AUTO_INCREMENT PRIMARY KEY,
    date DATETIME
);
""")

i use this line to take the date (was with datetime classe before but change nothing for my problem)

now = time.strftime("%Y-%m-%d %H:%M:%S")

Code of insertion

cursor.execute("""INSERT INTO TableName (date) VALUES(%s)""", (now))

that certainly sounds like a coding problem- something that perhaps you should get coding help on (eg: on codementors).

What do you get when you describe the MySQL table?

I tried do delete and create new table but the problem come from your configuration. I tried an another tutorial :

https://www.tutorialspoint.com/How-to-store-and-retrieve-a-date-into-MySQL-database-using-Python

But the problem is always here :(

I browsed Internet for hours, I tested different types of columns, different ways to retrieve the date, I created several tables... Nothing I always have the string datetime.datetime which is displayed before the date (after insertion)!

I found absolutely no one who has this problem on the Web, very rare thing! I really think the problem comes from your service (buggy version of MSQL or something else?).

What do you get when you describe the MySQL table? I see the types of columns I had defined nothing abnormal

Can you try to reproduce this on your side ? And copy past the result of you SQL to retrieve the data from the table here ?

First import :

from datetime import datetime

Connect to your DDB

conn = mysql.connector.connect(host="",user="",password="", database="")
cursor = conn.cursor()
#conn.close()

Second create table like this :

cursor.execute("""
CREATE TABLE IF NOT EXISTS tableName (
    id INT AUTO_INCREMENT PRIMARY KEY,
    date_col DATETIME
);
""")

dateB = datetime.now()
 timeNow = dateB.strftime('%Y-%m-%d %H:%M:%S')

        cursor.execute("INSERT INTO tableName (date_col) VALUES(%s)", (timeNow))

Now request your table like this :

cursor.execute('SELECT * FROM tableName')
print('''Result of "SELECT * FROM tableName":''')
result = cursor.fetchall()
for r in result:
    print(r)

I'm not a beginner in databases and this problem is driving me crazy!

The type of your date column is DATETIME, so it's going to include time information. Also, you are formatting the current time into now including the time, so that will be included.

If you insert "dateB " variable in your table (and not timeNow) will also insert something like this in the DATETIME column in your SQL table : datetime.datetime(2019, 5, 17, 17, 17, 18)

The problem still remain !

I don't understand why you're calling it a problem. It's a DATETIME column so, when you query it, mysql connector returns a Python datetime object. That's what it's supposed to do.

Wait, is it normal that "Datetime Datetime (my date...)" is in the DATATIME field? Because with SQLite3 I didn't have that I just had the date that fit in and not the string like here "Datetime Datetime (my date...)"

Won't that be a problem to make requests on the date column ?

SQLite does not have a date field, so they are emulated with other types of fields (like string fields). MySQL does have native DATETIME, so the datetimes are treated as Python datetimes.

If your code is assuming that the datetime fields are being returned as strings, then you may need to change it to handle the datetime objects that are returned instead.

Ok thank you ! I was very confused about that.

If somebody has the same problem you can use this in your sql request :

SELECT DATE_FORMAT(colDateName,\'%m-%d-%Y\') FROM  YourTable