Sql month statement in Python not working

Question

0.00/5 (No votes)

See more:

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 6-Jun-22 7:29am

Megan van Bommel

Updated 6-Jun-22 9:28am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard MacCutchan · Accepted Answer · 2022-06-06T09:28:00

Solution 1

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.

Posted 6-Jun-22 9:28am

Richard MacCutchan

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.