Click here to Skip to main content
15,877,051 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hello, I am configuring mysql statement from a data to month number. I do get the correct data in MYSQL but not in Python. How do I get this working? The format is %m/%d/%Y.

The output from MYSQL is:
Purchase date = 09/08/2020
Date_Month = 9

The output from Python is:
Date_ID = 684
Purchase_date = 08/12/2020
Date_Month = 683

Date_ID = 685
Purchase_date = 01/09/2020
Date_Month = 684

# Dim_date
for dim_date in processed_response:

    # "date" dimension
    date_purchase = (dim_date["purchase_date"])    
    if date_purchase == None:

    # Check whether this record already exists in the dimension we created.
    date = cursor.execute("""SELECT *, MONTH (STR_TO_DATE(date_purchase, '%m/%d/%Y' )) FROM dim_date""")
    date_month = str(date)

    print (date_purchase, date_month)
    # We do this, because we don't have to insert it twice.
    sql = """
        INSERT INTO dim_date (date_purchase, date_month)
        VALUES ('"""+date_purchase+"""', '"""+date_month+"""')

# End of table
        # Execute the SQL command and commit our changes to the database...
            # Unless something goes wrong, in which case we do a rollback on our changes.

What I have tried:

I have tried to convert date another way. But this way is a must from school.
Updated 6-Jun-22 9:28am

1 solution

You have the following code:
date = cursor.execute("""SELECT *, MONTH (STR_TO_DATE(date_purchase, '%m/%d/%Y' )) FROM dim_date""")
date_month = str(date)

Your SELECT statement should return all columns from every record in dim_date. So your resulting date field will not be a month number.

To be honest, I cannot work out what you are trying to do with this code, as it looks like random statements.

You start with date_purchase so it is a simple matter to extract the month from that, without going to the database. If you want to find any record that was inserted with that value of date_purchase, then you just need something like:
    date = cursor.execute("SELECT * FROM dim_date WHERE purchase_date = " + date_purchase + ";")
# here extract the fields from any returned records.

But this is largely guesswork as I do not know the structure of your database table.
Share this answer
Megan van Bommel 6-Jun-22 15:37pm    
And how can i get the month of date_purchase?
Richard MacCutchan 6-Jun-22 15:45pm    
You need to look at all your variables to see exactly what they are. I do not know what gets returned by the statement:
date_purchase = (dim_date["purchase_date"])

What is dim_date at this point? Also, to get it in an SQL statement you just need to pass a date string to the MONTH function. To see what you would get from your statement got to MySQL Tryit Editor v1.0[^] and paste the following into the text box:
SELECT *, MONTH("05-06-22") FROM Customers;
Megan van Bommel 6-Jun-22 15:58pm    
the date_purchase will return like 12/31/2020
Megan van Bommel 6-Jun-22 15:59pm    
Dim_date is the name of the table
Megan van Bommel 6-Jun-22 15:59pm    
But I need to convert the date_purchase because it is an string format and not an date format

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900