217 lines
5.5 KiB
Plaintext
217 lines
5.5 KiB
Plaintext
{
|
|
"cells": [
|
|
{
|
|
"cell_type": "markdown",
|
|
"id": "3228e84e-083c-48f6-9913-9d96e2fd0a2a",
|
|
"metadata": {},
|
|
"source": [
|
|
"# Promotional campaign"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "markdown",
|
|
"id": "71a559ef-75a7-4aca-86aa-10629e047222",
|
|
"metadata": {},
|
|
"source": [
|
|
"## Imports"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 1,
|
|
"id": "8fe32f7f-09b3-4d9a-af26-4146c500bf44",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"from decimal import Decimal\n",
|
|
"from mysql.connector import CMySQLConnection, Error, connect\n",
|
|
"from mysql.connector.cursor_cext import CMySQLCursor"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "markdown",
|
|
"id": "70da0c3b-ae18-4b60-a7eb-1c0c75c3677a",
|
|
"metadata": {},
|
|
"source": [
|
|
"## Task 1"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 2,
|
|
"id": "ac95c33d-3ead-4dab-b007-4f20d37f7386",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"connection = connect(\n",
|
|
" user=\"tobias\",\n",
|
|
" unix_socket=\"/run/mysqld/mysqld.sock\",\n",
|
|
" database=\"LittleLemonDB\",\n",
|
|
" use_pure=False,\n",
|
|
" autocommit=True,\n",
|
|
")\n",
|
|
"assert isinstance(connection, CMySQLConnection)"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "markdown",
|
|
"id": "19e74d2d-2b40-4a1b-a57e-38f27106220b",
|
|
"metadata": {},
|
|
"source": [
|
|
"## Task 2"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 3,
|
|
"id": "1b386dae-78e4-44db-90e5-6da378de16fe",
|
|
"metadata": {},
|
|
"outputs": [
|
|
{
|
|
"name": "stdout",
|
|
"output_type": "stream",
|
|
"text": [
|
|
"Tables:\n",
|
|
" Addresses\n",
|
|
" Bookings\n",
|
|
" Customers\n",
|
|
" Employees\n",
|
|
" MenuItems\n",
|
|
" OrderItems\n",
|
|
" Orders\n"
|
|
]
|
|
}
|
|
],
|
|
"source": [
|
|
"try:\n",
|
|
" with connection.cursor() as cursor:\n",
|
|
" assert isinstance(cursor, CMySQLCursor)\n",
|
|
"\n",
|
|
" _ = cursor.execute(\"show tables\")\n",
|
|
" print(\"Tables:\")\n",
|
|
" for (table_name,) in cursor:\n",
|
|
" print(f\" {table_name}\")\n",
|
|
"except Error as err:\n",
|
|
" print(err)"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "markdown",
|
|
"id": "62e2485b-1391-4fc6-aa73-35d0ebd8e95d",
|
|
"metadata": {},
|
|
"source": [
|
|
"## Task 3\n",
|
|
"**SQL query in `sql/promotional_campaign.sql`:**\n",
|
|
"```sql\n",
|
|
"select\n",
|
|
" C.FirstName,\n",
|
|
" C.LastName,\n",
|
|
" C.PhoneNumber,\n",
|
|
" C.EmailAddress,\n",
|
|
" max(O.BillAmount) as MaxBillAmount\n",
|
|
"from Orders as O\n",
|
|
"inner join Bookings as B on O.BookingID = B.BookingID\n",
|
|
"inner join Customers as C on B.CustomerID = C.CustomerID\n",
|
|
"group by C.CustomerID\n",
|
|
"having MaxBillAmount > 60.00;\n",
|
|
"```"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 4,
|
|
"id": "214d0b0e-dd5b-4113-b6d7-aca020655ef3",
|
|
"metadata": {},
|
|
"outputs": [
|
|
{
|
|
"name": "stdout",
|
|
"output_type": "stream",
|
|
"text": [
|
|
"\n",
|
|
"Customers that placed an order greater then 60.00€:\n",
|
|
" Li Wei Zhang\n",
|
|
" Minimum bill amount: 82.50€\n",
|
|
" Phone number: +86123456789\n",
|
|
" Email address: li.wei.zhang@example.com\n",
|
|
" Ivanov Petrov\n",
|
|
" Minimum bill amount: 150.50€\n",
|
|
" Phone number: +70987654321\n",
|
|
" Email address: ivanov.petrov@example.com\n"
|
|
]
|
|
}
|
|
],
|
|
"source": [
|
|
"with open(\"./sql/promotional_campaign.sql\") as promo_campaign_file:\n",
|
|
" promo_campain_request = promo_campaign_file.read()\n",
|
|
" \n",
|
|
"try:\n",
|
|
" with connection.cursor() as cursor:\n",
|
|
" assert isinstance(cursor, CMySQLCursor)\n",
|
|
" _ = cursor.execute(promo_campain_request)\n",
|
|
"\n",
|
|
" print()\n",
|
|
"\n",
|
|
" print(\"Customers that placed an order greater then 60.00€:\")\n",
|
|
" for (\n",
|
|
" first_name,\n",
|
|
" last_name,\n",
|
|
" phone_number,\n",
|
|
" email_address,\n",
|
|
" min_bill_amount,\n",
|
|
" ) in cursor:\n",
|
|
" assert isinstance(first_name, str)\n",
|
|
" assert isinstance(last_name, str)\n",
|
|
" assert isinstance(phone_number, str)\n",
|
|
" assert isinstance(email_address, str | None)\n",
|
|
" assert isinstance(min_bill_amount, Decimal)\n",
|
|
" print(f\" {first_name} {last_name}\")\n",
|
|
" print(f\" Minimum bill amount: {min_bill_amount}€\")\n",
|
|
" print(f\" Phone number: {phone_number}\")\n",
|
|
" if email_address:\n",
|
|
" print(f\" Email address: {email_address}\")\n",
|
|
"except Error as err:\n",
|
|
" print(err)"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "markdown",
|
|
"id": "be3c702d-5ab6-47a6-a19b-4e26aefdbd6e",
|
|
"metadata": {},
|
|
"source": [
|
|
"## Cleanup"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 5,
|
|
"id": "e7f0a6dc-4940-4f8b-a89c-e714e9d375ba",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"connection.close()"
|
|
]
|
|
}
|
|
],
|
|
"metadata": {
|
|
"kernelspec": {
|
|
"display_name": "Python 3 (ipykernel)",
|
|
"language": "python",
|
|
"name": "python3"
|
|
},
|
|
"language_info": {
|
|
"codemirror_mode": {
|
|
"name": "ipython",
|
|
"version": 3
|
|
},
|
|
"file_extension": ".py",
|
|
"mimetype": "text/x-python",
|
|
"name": "python",
|
|
"nbconvert_exporter": "python",
|
|
"pygments_lexer": "ipython3",
|
|
"version": "3.13.0"
|
|
}
|
|
},
|
|
"nbformat": 4,
|
|
"nbformat_minor": 5
|
|
}
|