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?
1
3
5
7
8
import
requests
9
import
json
10
import
pymysql
11
import
datetime
13
14
host =
"
localhost"
15
username =
"
root"
16
password =
"
root"
17
database =
"
bioscoop_meru"
18
api_url =
"
https://my.api.mockaroo.com/cinemavisits?key=ed7c6150"
20
21
response = requests.get(api_url)
23
24
processed_response = response.json()
26
27
db = pymysql.connect(host=
'
localhost'
,
28
user=
'
root'
,
29
password=
'
root'
,
30
database=
'
bioscoop_meru'
)
32
cursor = db.cursor()
34
35
for
dim_date
in
processed_response:
37
38
date_purchase =
str
(dim_date[
"
purchase_date"
])
40
if
date_purchase == None:
41
continue
43
date_week = datetime.datetime.strptime(date_purchase,
"
%m/%d/%Y"
)
44
date_month = datetime.datetime.strptime(date_purchase,
"
%m/%d/%Y"
)
46
if
date_week == None:
47
continue
48
elif
date_month == None:
49
continue
51
print
(date_purchase, date_week.strftime(
"
%W"
), date_month.month)
53
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
"""
)
61
62
sql =
"""
63
INSERT INTO dim_date (date_purchase, date_week, date_month)
64
VALUES ('"""
+date_purchase+
"""
', '"""
+date_week+
"""
', '"""
+date_month+
"""
')
65
"""
67
68
try:
69
70
cursor.execute(sql)
71
db.commit()
73
except:
74
75
db.rollback()
78
db.close()
80
What I have tried:
I have tried to convert it to a string, but it is already
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.
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 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+"""')
"""