Click here to Skip to main content
15,394,479 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.

Quote:
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:
            continue

    
    # 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
    try:
        # Execute the SQL command and commit our changes to the database...
            cursor.execute(sql)
            db.commit()
            
    except:
            # Unless something goes wrong, in which case we do a rollback on our changes.
            db.rollback()
    
db.close()


What I have tried:

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

1 solution

You have the following code:
Python
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:
Python
    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.
   
Comments
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
Richard MacCutchan 6-Jun-22 16:18pm
   
But you are mixing date types and strings in the wrong places. You really need to stop writing code and thinking carefully about each step you need to solve whatever problem this is.
Megan van Bommel 7-Jun-22 1:06am
   
So can you make the correct code for me than, because this is not working the value is still zero. It’s an school project 😀
Richard MacCutchan 7-Jun-22 2:52am
   
Sorry, but it is still not clear exactly what you are trying to do. And as I mentioned earlier I do not have details of your database structure.

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