Click here to Skip to main content
15,390,470 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
From the api, I am trying to retrieve data. This data then needs to be converted to a week number and month number. This works until I get the following error message:

Quote:
Traceback (most recent call last):
File "C:\Users\i5\OneDrive - Hogeschool Rotterdam\BIM JAAR 3\Bedrijfskunde\BDK89 - Tentamenopdracht\Opdracht 3\Bioscoop_Meru.py", line 54, in
cursor.execute("""
TypeError: can only concatenate str (not "datetime.datetime") to str


I don't know what the problem is, can someone help me?

Python
  1  ####
  2  #
  3  # Requesting data from an API and processing this with Python3.
  4  #
  5  ####
  6  
  7  # Import required packages
  8  import requests
  9  import json
 10  import pymysql
 11  import datetime
 12  
 13  # Setting all required parameters
 14  host = "localhost"
 15  username = "root"
 16  password = "root"
 17  database = "bioscoop_meru"
 18  api_url = "https://my.api.mockaroo.com/cinemavisits?key=ed7c6150"
 19  
 20  # Retrieve data from API
 21  response = requests.get(api_url)
 22  
 23  # Print(json.dumps(response.json() ,  sort_keys=True, indent=4))
 24  processed_response = response.json()
 25  
 26  # Establish the connection to the Database
 27  db = pymysql.connect(host='localhost',
 28                       user='root',
 29                       password='root',
 30                       database='bioscoop_meru')
 31  
 32  cursor = db.cursor()
 33  
 34  # Dim_date
 35  for dim_date in processed_response:
 36  
 37      # "date" dimension
 38      date_purchase = str(dim_date["purchase_date"])
 39      
 40      if date_purchase == None:
 41              continue
 42          
 43      date_week = datetime.datetime.strptime(date_purchase, "%m/%d/%Y")
 44      date_month = datetime.datetime.strptime(date_purchase, "%m/%d/%Y")
 45  
 46      if date_week == None:
 47              continue
 48      elif date_month == None:
 49              continue
 50          
 51      print(date_purchase, date_week.strftime("%W"), date_month.month)
 52      
 53      # Check whether this record already exists in the dimension we created.
 54      cursor.execute("""
 55      SELECT * FROM dim_date
 56      WHERE date_purchase = '"""+date_purchase+"""'
 57      AND date_week = '"""+date_week+""""'
 58      AND date_month = '"""+date_month+"""'
 59      """)
 60      
 61      # We do this, because we don't have to insert it twice.
 62      sql = """
 63          INSERT INTO dim_date (date_purchase, date_week, date_month)
 64          VALUES ('"""+date_purchase+"""', '"""+date_week+"""', '"""+date_month+"""')
 65          """
 66         
 67  # End of table
 68      try:
 69          # Execute the SQL command and commit our changes to the database...
 70              cursor.execute(sql)
 71              db.commit()
 72              
 73      except:
 74              # Unless something goes wrong, in which case we do a rollback on our changes.
 75              db.rollback()
 76  
 77  
 78  db.close()
 79  
 80  ###


What I have tried:

I have tried to convert it to a string, but it is already
Posted
Updated 6-Jun-22 0:17am
v2
Comments
Richard Deeming 7-Jun-22 5:37am
   
Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation/interpolation to build a SQL query. ALWAYS use a parameterized query.

Python MySQL Execute Parameterized Query using Prepared Statement[^]

1 solution

Python
     SELECT * FROM dim_date
56      WHERE date_purchase = '"""+date_purchase+"""'
57      AND date_week = '"""+date_week+""""'
58      AND date_month = '"""+date_month+"""'

The variables date_week and date_month are both datetime types, as returned by datetime.strptime at lines 43 and 44. You must convert them to string types before you try to concatenate them into another string. Use datetime.strftime to get the correct result.
   
Comments
Megan van Bommel 6-Jun-22 6:28am
   
Where does I need to put the dateline. Stratum’s. I did that at print but that didn’t work. So I think that position is not the right place?
Richard MacCutchan 6-Jun-22 6:34am
   
At lines 43 and 44 as I already mentioned.
Megan van Bommel 6-Jun-22 6:39am
   
A thnx, I will try that. I let you know if it’s working or not.
Megan van Bommel 6-Jun-22 10:27am
   
Now i get the error: descriptor 'strftime' for 'datetime.date' objects doesn't apply to a 'str' object.

This is the code:
for dim_date in processed_response:

# "date" dimension
date = (dim_date["purchase_date"])
date_purchase = str(dim_date["purchase_date"])

if date_purchase == None:
continue

date_week = datetime.datetime.strftime(date_purchase, "%m/%d/%Y")
date_month = datetime.datetime.strftime(date_purchase, "%m/%d/%Y")

if date_week == None:
continue
elif date_month == None:
continue

print(date_purchase, date_week, date_month)

# Check whether this record already exists in the dimension we created.
cursor.execute("""
SELECT * FROM dim_date
WHERE date_purchase = '"""+date_purchase+"""'
AND date_week = '"""+date_week+""""'
AND date_month = '"""+date_month+"""'
""")

# We do this, because we don't have to insert it twice.
sql = """
INSERT INTO dim_date (date_purchase, date_week, date_month)
VALUES ('"""+date_purchase+"""', '"""+date_week+"""', '"""+date_month+"""')
"""
Richard MacCutchan 6-Jun-22 10:42am
   
This code does not make a lot of sense. You have date_purchase which you convert to a string. You then try to set date_week and date_month to the same value, to use in your SQL command. Why are you trying to use three variables which all have the same value?

You then try to execute a SELECT statement to see if a row exists with those values, but you never check the result. And finally you try to INSERT a record with the same values, so possibly duplicating an existing record.

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


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